openpyxl を使って Excel ファイルに列を追加しデータを追記したい

前提・実現したいこと

Python3でopenpyxlを使ってExcelのデータを読み込みTwitterへ検索しています。
各国を検索したらExcelのD列に処理済みの結果を記載したいですが、
上手く処理できないです。

Excel Data

国	都市	地域数
アメリカ	カリフォルニア	22
アメリカ	フロリダ	22
フランス	パリ	13
日本	東京	26
日本	沖縄	10
日本	京都	11

pandas dataframe 変換後のprint(lst)

[['アメリカ', 'カリフォルニア', 'フロリダ'], ['フランス', 'パリ'], ['日本', '東京', '沖縄']]

実現したいエクセルの結果　RESULT Excel Data
例：ループで完了している場合、
下記のように処理済み のフラグをつけたいです。
元々はエクセルにはヘッダーがないですのでD列からつけたいです。

国	都市	地域	フラグ
アメリカ	カリフォルニア	22	処理済み
アメリカ	フロリダ	22
フランス	パリ	13	処理済み
日本	東京	26	処理済み
日本	沖縄	14
日本	京都	11

試したこと

現在の結果
下記のコードだとフランスまでしか処理済み書き込みされないです。

国	都市	地域	フラグ
アメリカ	カリフォルニア	22	処理済み
アメリカ	フロリダ	22	処理済み
フランス	パリ	13	処理済み
日本	東京	26
日本	沖縄	14
日本	京都	11

Code

# Excel用ライブラリ読込
import openpyxl
from selenium.webdriver.common.keys import Keys
import time
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.support.select import Select  
from selenium import webdriver
import pyautogui
import pandas as pd

# Excelファイルを開く
v_wb = openpyxl.load_workbook("test.xlsx")

# アクティブなシートを変数へ
v_ws = v_wb.active
# シートのロード
ws = v_wb.worksheets[0]

# convert to pandas dataframe
df = pd.DataFrame(ws.values)
print(df)


# generate search words
lst = (
  df.groupby(1, as_index=False)[2]
    .agg(lambda x: x.tolist())
    .apply(lambda x: [x[1]] + x[2], axis=1)

    .tolist()
)
print(lst)

URL = "https://twitter.com/search-advanced?lang=en"

# ブラウザを開く。 #options=option background 
options = Options()
options.add_experimental_option('detach', True)
driver = webdriver.Chrome(executable_path="C:\Program Files\chromedriver_win32\chromedriver.exe", options=options)

for i, query in enumerate(lst):
    # Googleの検索TOP画面を開く。
    if i > 0:
      driver.execute_script('window.open()')
    driver.switch_to.window(driver.window_handles[i])
    driver.get(URL)

    # 2秒待機
    time.sleep(2)

    # country
    country = driver.find_element_by_name("allOfTheseWords")
    country.send_keys(query[0])

    # 2秒待機
    time.sleep(2)

    # 都市1つ以上がある場合、c列を入力
    if len(query) > 1:
      city2 = driver.find_element_by_name("thisExactPhrase")
      city2.send_keys(query[1])

    # 都市2つ以上がある場合、c列を入力
    if len(query) > 2:
      city2 = driver.find_element_by_name("anyOfTheseWords")
      city2.send_keys(query[2])

    # 都市3つ以上がある場合、c列を入力
    if len(query) > 3:
      city3 = driver.find_element_by_name("noneOfTheseWords")
      city3.send_keys(query[3])
      
    # 処理済みのでデータをD列へ入力
    v_ws['D'+str(i+1)].value = str("処理済み")      

# EXCEL保存  
v_wb.save("test.xlsx")

補足情報（FW/ツールのバージョンなど）

Python3, Excel

自分の力不足が大きいのですが、調べていてイマイチ記述方法がわかりません。
申し訳ないのですが、皆さんのお知恵をお借りできないでしょうか？
よろしくお願いいたします。

###『追記の質問コード』


row = 1
for i, query in enumerate(lst):
    # Googleの検索TOP画面を開く。
    if i > 0:
      driver.execute_script('window.open()')
    driver.switch_to.window(driver.window_handles[i])
    driver.get(URL)

    # 2秒待機
    time.sleep(2)

    # country
    country = driver.find_element_by_name("allOfTheseWords")
    country.send_keys(query[0])

    # 2秒待機
    time.sleep(2)

    # 都市1つ以上がある場合、c列を入力
    if len(query) > 1:
      city2 = driver.find_element_by_name("thisExactPhrase")
      city2.send_keys(query[1])

    # 都市2つ以上がある場合、c列を入力
    if len(query) > 2:
      city2 = driver.find_element_by_name("anyOfTheseWords")
      city2.send_keys(query[2])

    # 都市3つ以上がある場合、c列を入力
    if len(query) > 3:
      city3 = driver.find_element_by_name("noneOfTheseWords")
      city3.send_keys(query[3])
  
    print('D'+str(row), str("処理済み")  )
    # 処理済みのでデータをD列へ入力
    v_ws['D'+str(row)].value = str("処理済み")  
    row += len(query) - 1
  
# EXCEL保存  
v_wb.save("test.xlsx")

meg_

2021/09/12 03:49

print(lst)の結果はどうなっていますか？

fideo

2021/09/12 05:40 編集

回答ありがとうございます。下記のように出力されます。 pandas dataframe 変換後のprint(lst) [['アメリカ', 'カリフォルニア', 'フロリダ'], ['フランス', 'パリ'], ['日本', '東京', '沖縄']]

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

回答1件

ベストアンサー

簡単にデバッグしてみました。

Python
1lst = [['アメリカ', 'カリフォルニア', 'フロリダ'], ['フランス', 'パリ'], ['日本', '東京', '沖縄']]
2
3for i, query in enumerate(lst):
4    print(query,':')
5    # 都市1つ以上がある場合、c列を入力
6    if len(query) > 1:
7        print('  都市1つ以上')
8    # 都市2つ以上がある場合、c列を入力
9    if len(query) > 2:
10        print('  都市2つ以上')
11    # 都市3つ以上がある場合、c列を入力
12    if len(query) > 3:
13        print('  都市3つ以上')
14        
15    print('  D'+str(i+1), str("処理済み")  )
16#['アメリカ', 'カリフォルニア', 'フロリダ'] :
17#  都市1つ以上
18#  都市2つ以上
19#  D1 処理済み
20#['フランス', 'パリ'] :
21#  都市1つ以上
22#  D2 処理済み
23#['日本', '東京', '沖縄'] :
24#  都市1つ以上
25#  都市2つ以上
26#  D3 処理済み

現在の結果

下記のコードだとフランスまでしか処理済み書き込みされないです。

ループ処理の部分とエクセルへの書き込み処理(行)が合っていないと思われます。

書き込み行を指定する変数を追加したコードが下記です。

Python
1row = 1
2for i, query in enumerate(lst):
3    print(query,':')
4    # 都市1つ以上がある場合、c列を入力
5    if len(query) > 1:
6        print('  都市1つ以上')
7    # 都市2つ以上がある場合、c列を入力
8    if len(query) > 2:
9        print('  都市2つ以上')
10    # 都市3つ以上がある場合、c列を入力
11    if len(query) > 3:
12        print('  都市3つ以上')
13        
14    print('  D'+str(row), str("処理済み")  )
15    row += len(query) - 1
16#['アメリカ', 'カリフォルニア', 'フロリダ'] :
17#  都市1つ以上
18#  都市2つ以上
19#  D1 処理済み
20#['フランス', 'パリ'] :
21#  都市1つ以上
22#  D3 処理済み
23#['日本', '東京', '沖縄'] :
24#  都市1つ以上
25#  都市2つ以上
26#  D4 処理済み

【追記】
D列全てに”処理済み”と記入したいのであれば、エクセルへの書き込み処理部分を下記の様にすれば良いかと思います。

python
1for j in range(len(query) - 1):
2  print('  D'+str(row + j), str("処理済み")  )
3row += len(query) - 1

【追記】

ソートはしていないですが、勝手にソートアルファベット順になっていますね

1,2,3,4のエクセル順番でできますでしょうか。

Pandasのgroupby()の引数にsortがあり、デフォルトでTrueなのでこれをFalseに設定すれば良いでしょう。

投稿2021/09/12 06:05

編集2021/09/13 10:55

meg_

総合スコア10580

fideo

2021/09/13 02:48 編集

ありがとうございます。問題なく上記の方で出来ました。 len関数の使い方について勉強になりました。ちなみにですが、全てD列に都市2つ以上があっても"処理済み"と記載したい場合 len(query)のみや+ 1 に変更しましたが上手くいきませんでした。別の方法で行うべきでしょうか。度々質問ですいません。。

fideo

2021/09/13 03:10 編集

また下記のようにイタリアを追加しましたら、上手く処理済みのフラグが追加されないことが分かりました。こちらように出力されます。 [['アメリカ', 'カリフォルニア', 'フロリダ'], ['イタリア', 'ローマ'], ['フランス', 'パリ'], ['日本', '東京', '沖縄']] ['アメリカ', 'カリフォルニア', 'フロリダ'] : 都市1つ以上都市2つ以上 D1 処理済み D1 処理済み ['イタリア', 'ローマ'] : 都市1つ以上 D3 処理済み D3 処理済み ['フランス', 'パリ'] : 都市1つ以上 D4 処理済み D4 処理済み ['日本', '東京', '沖縄'] : 都市1つ以上都市2つ以上 D5 処理済み D5 処理済み 1 アメリカカリフォルニア処理済み 2 アメリカフロリダ 3 フランスパリ　　　　　処理済み 4 日本東京　　　　　　　処理済み 5 日本沖縄　　　　　　　処理済み 6 イタリアローマ毎回データが増える想定だと、上記のようになります。データが増えても上記の例のように6 イタリアローマ　処理済み追加できないでしょうか。ご確認をお願い致します。

meg_

2021/09/13 03:15

> また下記のようにイタリアを追加しましたら、上手く処理済みのフラグが追加されないことが分かりました。当方では再現しません。上手くいかないコードを質問に追記いただけませんか？

fideo

2021/09/13 03:28 編集

早速回答ありがとうございます。コードはこちらになります。こちら側でforの指定とかエクセル保存の何か間違えていますでしょうか。ご確認をお願いいたします。ーーーーーーーーーーーーーーーーーーーーーーー for i, query in enumerate(lst): # Googleの検索TOP画面を開く。 if i > 0: driver.execute_script('window.open()') driver.switch_to.window(driver.window_handles[i]) driver.get(URL) # 2秒待機 time.sleep(2) # country country = driver.find_element_by_name("allOfTheseWords") country.send_keys(query[0]) # 2秒待機 time.sleep(2) # 都市1つ以上がある場合、c列を入力 if len(query) > 1: city2 = driver.find_element_by_name("thisExactPhrase") city2.send_keys(query[1]) # 都市2つ以上がある場合、c列を入力 if len(query) > 2: city2 = driver.find_element_by_name("anyOfTheseWords") city2.send_keys(query[2]) # 都市3つ以上がある場合、c列を入力 if len(query) > 3: city3 = driver.find_element_by_name("noneOfTheseWords") city3.send_keys(query[3]) row = 1 for i, query in enumerate(lst): print(query,':') # 都市1つ以上がある場合、c列を入力 if len(query) > 1: print(' 都市1つ以上') # 都市2つ以上がある場合、c列を入力 if len(query) > 2: print(' 都市2つ以上') # 都市3つ以上がある場合、c列を入力 if len(query) > 3: print(' 都市3つ以上') print(' D'+str(row), str("処理済み") ) # 処理済みのでデータをD列へ入力 v_ws['D'+str(row)].value = str("処理済み") row += len(query) - 1 # EXCEL保存 v_wb.save("test")

meg_

2021/09/13 04:03

この欄ではインデントがないので例えミスがあったとしても分かりません。ですので、質問に追記してください。

fideo

2021/09/13 04:32

丁寧にありがとうございます。追加致しました。ご確認をお願い致します。

meg_

2021/09/13 06:32 編集

追記いただいたコードでループ内の下記※以外をコメントアウトした場合に D1 処理済 D3 処理済 D4 処理済 D5 処理済と表示されますか？ ※ print('D'+str(row), str("処理済み") ) row += len(query) - 1

fideo

2021/09/13 07:18

その通りです。下記のように表示されます。よろしくお願い致します。 D1 処理済み D3 処理済み D4 処理済み D5 処理済み

meg_

2021/09/13 07:55

リストデータが[['アメリカ', 'カリフォルニア', 'フロリダ'], ['イタリア', 'ローマ'], ['フランス', 'パリ'], ['日本', '東京', '沖縄']]であれば出力は合っているかと思いますが。

fideo

2021/09/13 08:00

はい、上記のように出力されますが、ただエクセルの順番を見るとローマは六番(最後)にあります。リストとエクセルの順番の関係で上手く書き込みできないでしょうか。

meg_

2021/09/13 08:03

2021/09/13 12:05 編集のコメントに対してです。先程気がつきましたが、 > こちらように出力されます。 [['アメリカ', 'カリフォルニア', 'フロリダ'], ['イタリア', 'ローマ'], ['フランス', 'パリ'], ['日本', '東京', '沖縄']] と > 1 アメリカカリフォルニア処理済み 2 アメリカフロリダ 3 フランスパリ　　　　　処理済み 4 日本東京　　　　　　　処理済み 5 日本沖縄　　　　　　　処理済み 6 イタリアローマで都市の並びが異なっています。それが上手くいかなかった原因ではないでしょうか？

meg_

2021/09/13 08:05

もしかしてリストはソートしてますか？その場合は元のデータ(エクセル)とずれる可能性がありますね。

fideo

2021/09/13 08:25 編集

確かにそうですね、ソートはしていないですが、勝手にソートアルファベット順になっていますね 1,2,3,4のエクセル順番でできますでしょうか。こちらでも調べてみます。

fideo

2021/09/14 01:06

ありがとうございます。sort=Falseを入れましたら、問題なくエクセルの順番になりました。ただ追記でした質問で下記のようにやってみましたら、アメリカ　カリフォルニア処理済みまでしかフラグ記載されないです。もし分かればこちらも教えていただけるとありがたいです。【追記】 D列全てに”処理済み”と記入したいのであれば、エクセルへの書き込み処理部分を下記の様にすれば良いかと思います。 for j in range(len(query) - 1): print(' D'+str(row + j), str("処理済み") ) row += len(query) - 1

meg_

2021/09/14 01:13

> ただ追記でした質問で下記のようにやってみましたら、アメリカ　カリフォルニア処理済みまでしかフラグ記載されないです。そうなんですね。。 for j in range(len(query) - 1): で都市の数だけループして、row += len(query) - 1 で次の国へ行番号が進むようにしたつもりでしたが。実際のコード・データでデバッグしないとちょっと原因は分からないですね。

fideo

2021/09/14 01:29

回答ありがとうございます。色々と試してみます。

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