IDごとに検索して集計する時間を短縮したい

Question

検索値ごとに個数をカウントしたい

Pythonを始めて2ヶ月目の初心者です。（プログラミング言語を学ぶのも初めてです。）
Pythonで、マスタデータから、検索IDごとのデータ個数を集計する作業をしたいです。
マスタデータは約17万件×12ヶ月分、検索IDは約2万3000件あります。

処理時間が長い

現在だと、マスタデータを5000件にしても集計に40分ほどかかります。
これを短縮する方法はないでしょうか？

私のPC仕様は以下の通りです；
Windows10 pro
プロセッサ Intel Core i5-7200U@2.50GHz 2.70GHz
実装RAM 8.00GB

また、Python 3.10.0を利用しています。

該当のソースコード

import openpyxl

#検索IDのブック、シート
wb_ID=openpyxl.load_workbook("検索ID.xlsx")
ws_ID=wb_master.worksheets[0]

#マスタデータのブック、シート
wb_data=openpyxl.load_workbook("マスタデータ.xlsx", data_only=True)
ws_data=wb_data.worksheets[0]

#検索IDの全データリスト
ID_list=[]

for row in ws_ID.iter_rows():
    if row[0].value is None:
        break
    value_list=[]
    for c in row:
        value_list.append(c.value)
    ID_list.append(value_list)

#集計結果を入れるリスト
result_list=[]

#検索IDごとに処理
for ID in ID_list:
    dataID=ID[0]
    #マスタデータのカウント
    data_count=0
    #マスタデータの検索
    for row in ws_data.iter_rows():
        #検索条件（検索IDが一致）
        if row[0].value==dataID:
            data_count=trip_count+1

    if data_count>0:
        result_list.append([dataID,data_count])

#集計結果用シートを追加
ws_new=wb_dataSum.create_sheet(title="集計結果")
#集計結果書き込み
for result in result_list:
    ws_new.append(result)
#別名で保存
wb_dataSum.save("集計結果.xlsx")

###試した方法

「Python 検索時間 短縮」などでウェブを検索していますが、結果は今のところ芳しくありません。
この質問を書いている間に、検索IDを短くすればいいのかな？と思いついたくらいです。。

補足情報）検索IDの成り立ち

検索IDは英数字とハイフンの組み合わせ10～12桁からなっています。
例）Q3-01Q3-24、Q3-127Q3-108 など

マスタデータは、A列とB列にそれぞれ「Q3ｰ整数」が振られています。
その組み合わせごとに集計したかったので、A列とB列の順列組み合わせを検索IDとしています。
例）A列にQ3-01、B列にQ3-24があるマスタデータには、検索IDとしてQ3-01Q3-24を振っています。

Answer 1

• 検索IDを短くすればいいのかな？と思いついたくらいです。

それほど速くはなりません。

openpyxlを使って読み込んでも、自分で計算していると計算部分が非常に遅くなります。
計算部分を高速化するにはその部分をマシン語で高速計算ライブラリを利用するのが常套手段です。
Pythonではnumpyを使うと高速計算ライブラリを呼び出すことができます。しかし、numpy単独ではIDのようなものと数値の混在しているデータを読み込んで処理するのは難しいです。

今回のExcelデータのようなものを処理したい場合、pandasを使うと、その中からopenpyxlを呼び出してデータを読み込み、numpyを呼び出してデータを高速に処理することができますので、現在のコードよりはずっと速くすることが可能です。もちろん、下手なプログラムを書けば速くならないこともよくあります。

pandas documentation
note.nkmk.me pandas

を読んで、まずコードを書き換えてみてください。
高速化についての質問は、その後に再度行われる方が良いと思います。

Comments

[Kozyma]

ご回答ありがとうございます。openpyxl単体だと限界があるのですね。
高速化について一般的な方法をご教示いただき、大変ありがたいです。
pandasは、書籍で読んで、表の読み書きができるくらいものくらいの認識しかありませんでした。pandasからいろいろ呼び出して動かすことができるのですね。これからリンク先の記事を拝読します。取り急ぎ御礼まで申し上げます。

Answer 2

補足情報に「マスタデータは、A列とB列それぞれ…」とありますが、貼ってもらったコードだとA列のみ読んでいるように見えます。
このへんがよく分からないので、ダミーデータでかまわないでので入力データと期待する出力のサンプルをもらえないでしょうか。

処理時間の短縮については、現在のプログラムの感じだと

• マスタデータだけに注目してIDごとの出現回数をカウントする
• その後で検索IDごとにマスタデータでの出現回数をチェックする

で十分に高速になると思います。

import openpyxl
from collections import Counter

#検索IDのブック、シート
wb_ID=openpyxl.load_workbook("検索ID.xlsx")
ws_ID=wb_ID.worksheets[0]
"""
検索IDの中身
A列
Q-123
Q-456
Q-789
"""

#マスタデータのブック、シート
wb_data=openpyxl.load_workbook("マスタデータ.xlsx", data_only=True)
ws_data=wb_data.worksheets[0]
"""
マスタデータの中身
A列		B列
Q-123	Q-456
Q-123	Q-789
Q-456	Q-123
Q-111	Q-222
"""

# マスタデータのA列を見て、IDの出現回数を集計
id_cnt = Counter(row[0].value for row in ws_data.iter_rows())
print(id_cnt)  # Counter({'Q-123': 2, 'Q-456': 1, 'Q-111': 1})

# 検索IDごとに、id_cntでの出現回数を確認してリスト化
result_list=[]
for row in ws_ID.iter_rows():
    result_list.append([row[0].value, id_cnt[row[0].value]])
print(result_list)  # [['Q-123', 2], ['Q-456', 1], ['Q-789', 0]]

#集計結果用シートを追加
ws_new=wb_ID.create_sheet(title="集計結果")
#集計結果書き込み
for result in result_list:
    ws_new.append(result)
#別名で保存
wb_ID.save("集計結果.xlsx")
"""
集計結果
A列	B列
Q-123	2
Q-456	1
Q-789	0
"""

Comments

[Kozyma]

ご回答ありがとうございます。説明が不足しており、失礼いたしました。
当検索作業をするまえに、別pyファイルで「先頭行を追加して元A列と元B列の文字列を＆で連結する処理」をしました。そのため、上記の記述では、新A列を検索するようになっています。

検索IDの中身は：
A列
Q1-127Q1-36
Q1-127Q1-37
Q1-127Q1-38
Q1-127Q1-39
・・・
・・・
・・・

マスタデータの中身は：

A列 B列 C列
Q1-127Q1-36 Q1-127 Q1-36
Q1-127Q1-37 Q1-127 Q1-37
Q1-127Q1-38 Q1-127 Q1-38
Q1-127Q1-39 Q1-127 Q1-39
・・・
・・・
・・・

となっています。

期待する出力結果は
A列 B列
Q1-127Q1-36 5
Q1-127Q1-37 10
Q1-127Q1-38 7
Q1-127Q1-39 9
・・・

という格好のものが出てきてほしいところでした。

ご教示いただいた「出現する回数をカウントする」は素晴らしいアイデアだと思いました。
なんで気がつかなかったのか･･･。これでしたら、すぐ試せそうですので、やってみます。
取り急ぎ御礼まで申し上げます。

（補足）そもそもなぜ検索IDを設定して検索したのかというと、このマスタファイルが12ヶ月分あって、最後に総計を求める作業があることが発端でした。当初12ヶ月分の各小計ファイルを横に並べて合計を求めようという魂胆がありましたので、こんな検索をかけるような格好になった･･･のだと思います。参考書籍をもとに打ち込んでいますので、書籍に書いてあることをつぎはぎしていった結果でもあります。

[Kozyma]

いま手元のファイルで実行したところ、とても速く結果がでたのですが、
検索IDごとの集計結果がすべて0で返ってきてしまいました。

また、id_cntの結果も；

Counter({None: 9712})

と返ってきました。

試しに文字列だから.valueはいらないのかな？と思い、row[0].valueをrow[0]と置き換えてやってみたのですが、こんどはエラーです。引き続き修正すべきところを探しますが、改善すべき点についてまたアドバイスをいただければ幸いです。

[退会済みユーザー]

手元に同じデータが無いので、解析は難しいですね。a) マスタデータのA列にIDデータがあるか目視でチェック, b) ws_data=wb_data.worksheets[0] を ws_data=wb_data['Excelファイルのシート名'] に変えて1枚目のシートではなくシート名を指定して開く あたりはどうでしょうか?

[Kozyma]

原因が分かりました。

参照しているマスタデータのA列には「＝B1＆C1」の式が入っているのですが、これを値にしたら期待通り、IDごとの出現数がでて、出てこないIDには0が入った集計結果が手に入りました。

▼A列には式が入っていました
A列 、B列、C列
＝B1＆C1、Q1-127、Q1-36
＝B2＆C2、Q1-127、Q1-37
＝B3＆C3、Q1-127、Q1-38
・・・
・・・

▼A列の式を値に直しました
A列 、B列、C列
Q1-127Q1-36、Q1-127、Q1-36
Q1-127Q1-37、Q1-127、Q1-37
Q1-127Q1-38、Q1-127、Q1-38
・・・
・・・

しかし、疑問が残ります。こうならないように, ファイルの読み込みには「data_only=True」を入れていたのですけど、不要だったのですかね。
個人的には、すべてのファイルに「値として貼り付け」の作業をするのも、ミスがでそうでいやなので、原因は分かりましたけど、解決したとは言いがたいです。もうすこし調べます。

[Kozyma]

原因がわかりました（その2）。

マスタデータA列の式は、Pythonでopenpyxlを使って入力していました。
マスタデータを開くと、計算した結果が表示されるので、そのままファイルを閉じてから、上記処理を行っていたのですが、それがNGになる原因でした。計算した結果が表示されたのを確認して、Excelファイルを保存してから（←大事だったのはこの作業でした）、もう一度処理を行ったら、式のままで、IDごとに集計された結果が手に入りました。

参考にさせていただいたスレッドはこちらです。
▼openpyxl セルの値で読み込む data_only=Trueについて
https://teratail.com/questions/328690

openpyxlでは式の計算ができないということについては、書籍やウェブサイトなどいたるところに注意事項として書いてあったので、知っていたのですが、、、知っていただけじゃダメですね。

このExcelでファイルを開いて保存する作業だけは、openpyxlでは避けて通れないようなので、これにて解決した･･･ということにしたいと思います。pandasを使えば解決するかもなので、今後はpandas方面も開拓したいです。

たくさんのアドバイスやヒントを惜しみなくくださったppaulさま、ftlobwさま、貴重なお時間をくださってありがとうございます！