【python・エクセル操作】データを、法則に沿って複数シートに書き込みたい

実現したいこと

お世話になっております。

pythonで**『スクレイピングし、そのデータをKWに沿って、シート毎に格納する方法』**を探しております。
1つのKWをスクレイピングし、シートに格納するところまでは行けたのですが、2つ以上のKWを検索した際のURLをそれぞれのシートに記入するところで一週間近く作業が止まっております、、。

何卒、ご教授頂けますと幸いです。

以下が、試行錯誤したコードとなります。

ソースコード

前提:エクセル内に『Sheet1』というシートが存在する。

結果：『犬』『猫』でGoogle検索した際のURLが、『Sheet1』のシート内にA列・B列の間に列追加し、新しくできたB2以降に書き込みされる。

(『犬』『猫』のシートは作成されるも、そこに書き込みがされない。)

理想：検索したKWに応じて、『犬』『猫』の各シートにA列・B列の間に列追加し、新しくできたB2以降に書き込みされる。

python
1import openpyxl
2import datetime
3import os
4from urllib import request as req
5from urllib import error
6from urllib import parse
7import bs4
8from time import sleep
9
10#KWを入力すると、そのKWでのGoogleの文字検索結果を表示。range(0,50,10)で5ページ目までをスクレイピング。range(0,40,10)だと4ページ目までをスクレイピング。
11keyword = ["犬","猫"]
12urls = []
13sleepCounter = 0
14for keywords in keyword:
15    if not os.path.exists(keywords):
16        os.mkdir(keywords)
17    urlKeyword = parse.quote(keywords)
18    x=0
19    for x in range(0,21,10):
20        url = "https://www.google.com/search?q="+ urlKeyword +"&rlz=1C1GCEA_enJP788JP788&ei=gI5NXN2uHYvBoATW1JPACQ&start="+ str(x) +"&sa=N&ved=0ahUKEwjd7p_d5o3gAhWLIIgKHVbqBJgQ8tMDCIEC&biw=1920&bih=969"
21        headers = {"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:47.0) Gecko/20100101 Firefox/47.0",}
22        request = req.Request(url=url, headers=headers)
23        page = req.urlopen(request)
24#Google検索結果のタイトル直下にある緑色のURLを取得し、変数urlsに格納
25        html = page.read().decode('utf-8')
26        htmls = bs4.BeautifulSoup(html, "html.parser")
27        for k in htmls.find_all("cite",class_="iUh30"):
28            sleepCounter += 1
29            if sleepCounter > 1:
30                sleep(1)
31                sleepCounter = 0
32                urls.append(k.string)
33
34# ==================================================================================================
35
36# ブックを取得
37book = openpyxl.load_workbook('~~~Excel名~~~')
38# keywordを変数newsheetにコピー
39newsheet = keyword
40# シートを取得(現在、『犬』『猫』が存在している)
41removesheet = book.sheetnames
42# newsheetから、現在シートにあるシート名を削除する
43for a in removesheet:
44    try:
45        newsheet.remove(a)
46    except ValueError:
47        pass
48# newsheetにある要素のシート名を、エクセル内に作成する
49for b in newsheet:
50    book.create_sheet(str(b))
51# シートを取得
52sheet = book['Sheet1']
53# シートのA,B列の間に行を追加
54sheet.insert_cols(2,1)
55# シートのB1に書き込み
56sheet["B1"] = datetime.date.today()
57# シートのB2から書き込み
58for i,s in enumerate(urls):
59    cell = 'B{}'.format(i+2)
60    sheet[cell] = s
61# 保存する
62book.save('~~~Excel名~~~')
63

試したこと

◆スクレイピングしたURLの先頭にKWをつけて分別し、シート毎に記入
◆変数を動的生成にし、変数毎にデータを格納し、シート毎に記入

meg_

2019/07/29 13:20

下記部分で「Sheet1」を指定しているのが原因ではないでしょうか？ # シートを取得 sheet = book['Sheet1']

行動規範の内容に同意します

回答1件

ベストアンサー

問題の根本としては、meg_さんのコメントにある通り、書き込み先(sheet)にSheet1をしているためです。

ただ、これを単純にsheet = book['犬']と変えても、書き込み先が変わるだけで「犬のシートに犬に関するURL、猫のシートに猫に関するURLを書き込む」ことはできません。

解決策として、urlsリストを各KW用に分割・格納するのが良いかと思います。

以下、提案コードになります。

python
1import openpyxl
2import datetime
3import os
4from urllib import request as req
5from urllib import error
6from urllib import parse
7import bs4
8from time import sleep
9
10#KWを入力すると、そのKWでのGoogleの文字検索結果を表示。range(0,50,10)で5ページ目までをスクレイピング。range(0,40,10)だと4ページ目までをスクレイピング。
11keywords = ["犬","猫"]
12
13#URLを格納するためのリストをKW毎に作成
14urls = []
15for k in keywords:
16    urls.append([])
17    
18sleepCounter = 0
19
20#KW毎にスクレイピング＆リストへ格納
21for i,keyword in enumerate(keywords):
22    if not os.path.exists(keyword):
23        os.mkdir(keyword)
24    urlKeyword = parse.quote(keyword)
25    x=0
26    for x in range(0,21,10):
27        url = "https://www.google.com/search?q="+ urlKeyword +"&rlz=1C1GCEA_enJP788JP788&ei=gI5NXN2uHYvBoATW1JPACQ&start="+ str(x) +"&sa=N&ved=0ahUKEwjd7p_d5o3gAhWLIIgKHVbqBJgQ8tMDCIEC&biw=1920&bih=969"
28        headers = {"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:47.0) Gecko/20100101 Firefox/47.0",}
29        request = req.Request(url=url, headers=headers)
30        page = req.urlopen(request)
31#Google検索結果のタイトル直下にある緑色のURLを取得し、変数urlsに格納
32        html = page.read().decode('utf-8')
33        htmls = bs4.BeautifulSoup(html, "html.parser")
34        for k in htmls.find_all("cite",class_="iUh30"):
35            sleepCounter += 1
36            if sleepCounter > 1:
37                sleep(1)
38                sleepCounter = 0
39                #犬のリストには犬に関するURL、猫のリストには猫に関するURLが入る
40                urls[i].append(k.string)
41
42
43# ==================================================================================================
44
45# ブックを取得
46book = openpyxl.load_workbook('Excel1.xlsx')
47# keywordを変数newsheetにコピー
48newsheet = keywords
49# シートを取得(現在、『犬』『猫』が存在している)
50removesheet = book.sheetnames
51# newsheetから、現在シートにあるシート名を削除する
52for a in removesheet:
53    try:
54        newsheet.remove(a)
55    except ValueError:
56        pass
57# newsheetにある要素のシート名を、エクセル内に作成する
58for b in newsheet:
59    book.create_sheet(str(b))
60#各KWのリスト内容を、各KWに関するシートに書き込み
61for index,name in enumerate(keywords):
62    # KWに関するシートを取得
63    sheet = book[name]
64    # シートのA,B列の間に行を追加
65    sheet.insert_cols(2,1)
66    # シートのB1に書き込み
67    sheet["B1"] = datetime.date.today()
68    # シートのB2から書き込み
69    for i,s in enumerate(urls[index]):
70        try:
71            cell = 'B{}'.format(i+2)
72            #私の環境ではそのまま書き込むとエラーが発生したので、とりあえず文字列にして書き込み
73            sheet[cell] = str(s)
74        except:
75            print('error')
76            pass
77    # 保存する
78book.save('Excel2.xlsx')