データ集計の高速化を行いたいです

Question

実現したい

日付	ID	付番	カウント率
…	…	…
2021-12-31	14	1
2022-01-01	10	3
2022-01-01	11	2
2022-01-01	12	2
2022-01-02	15	3
2022-01-02	12	2
2022-01-02	18	1
2022-01-03	11	1
…	…	…
2022-12-31	11	3

上記のようなデータフレームにおいて、各行の"カウント率"列に下記④の値を入力したいです。
①各行の日付の前日を基準日として、その１８０日以内の日付が含まれる行をデータフレームから抽出し、
②上記①で抽出した行のうち、IDが一致する行を抽出したときの行数をAとし、
③上記②で抽出した行のうち、付番が１または２である行を抽出したときの行数をBとしたときの、
④B/A×100の値
（上記データフレームの一番下の行を例にすると、2022.6.30~2022.12.30の間で、IDが11である行数をAとし、Aのなかで付番が1または2である行数をBとし、B/A×100の値を"カウント率"列に入力したいです）

前提

jupyter labを使用してpythonでのデータ集計を行おうとしています。
自身で下記コードを書いたのですが、実際はデータフレームの行数が２００万近くあり、処理時間がとても長く実用に耐えませんでした。
初歩的な質問で恐縮ですが、高速化を図れる術をご教示いただきたいです。宜しくお願いいたします。

発生している問題・エラーメッセージ

エラーメッセージ

該当のソースコード

def sample(row):
    all = len(df[(row["日付"] >df["日付"])&
        (df["日付"]> row["日付"] - timedelta(days=180))&
        (df["ID"] == row["ID"])
        ] )
    count = len(df[((row["日付"] > df["日付"])&
        (df["日付"]> row["日付"] - timedelta(days=180))&
        (df["ID"] == row["ID"]))&
        (df["付番"].isin([1,2]))    
        ] )
    try:
        count_rate = count/all * 100
    except ZeroDivisionError:
       count_rate = np.nan
    row["カウント率"] = count_rate
    return row

df_new = df.apply(sample,axis=1)

試したこと

swifterを使用しましたが処理時間がほとんど変わらず、またnp.vectorizeを試みたのですが知識不足により実装することができませんでした。

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

ここにより詳細な情報を記載してください。

Comments

[Orlofsky]

>行数が200万近くあり、処理時間がとても長く実用に耐えませんでした。

それでだけデータがあったら、通常データをデータベースに登録してSQL言語でSELECT文を発行して集計します。200万行程度なら数秒で集計できるでしょう。

[auto_chiri7]

ご回答ありがとうございました。SQLも視野に入れてみます。

Answer 1

• isin([1, 2])は、ループの中で何度も計算するのは無駄なので、最初に計算してしまうのがいいです。
• ID毎の処理になっているようなので、IDでgroupbyするといいと思います。
• 期間に対しての計算はrollingを使ったらできます。closedにleftを指定することで当日を除去します。
• B/A は、boolの列に対して平均(mean)をとることで計算できます。

count_ratio = (
    df.assign(x=df['付番'].isin([1, 2]))
      .groupby('ID')
      .rolling('181D', on='日付', closed='left')['x']
      .mean() * 100).rename('カウント率')

# 同じID, 日付のデータが複数ある場合
count_ratio = count_ratio[~count_ratio.index.duplicated()]

df = df.join(count_ratio, on=['ID', '日付'])

Comments

[auto_chiri7]

ご回答ありがとうございました。処理時間が大幅に短縮され、大変助かりました。
高速化でき、処理時間が一番短縮されましたのでベストアンサーにさせていただきます。

[can110]

詳細確認できていませんが、結果の行数が元の行数よりも増えるような気がします。

[bsdfan]

can110さん
ご指摘ありがとうございます。IDと日付が同じデータが複数あるケースでは、データが増えてしまうようです。
追記・修正しました。

Answer 2

ID毎に、1,2または1,2以外の２種類の日付を昇順にキューで管理することで処理速度はあがると思います。
ただし、このコードでも200万行だとデータ分布にもよりますが数分はかかります。

import pandas as pd
from collections import deque
from io import StringIO
from itertools import product

s = """date,ID,no
2022-01-01,1,1
2022-01-01,1,2
2022-01-01,1,3
2022-02-01,1,1
2022-02-01,1,2
2022-02-01,1,3
2022-08-01,1,1
"""
df = pd.read_csv(StringIO(s), parse_dates=['date'])

# 2192000 rows
#data = product(pd.date_range('2020-01-01','2022-12-31'), range(100), range(20))
#df = pd.DataFrame(data=data, columns=['date','ID','no'])

print(df)

# 古い日付を削除
def remove(q,dt):
    while len(q) > 0:
        if (dt - q[0]).days <= 181:
            break
        q.popleft()

# （同日を除く）対象数を取得
def count(q,dt):
    cnt = len(q)
    for i in reversed(q):
        if (dt - i).days >= 1:
            break
        cnt -= 1
    return cnt

# 行毎の処理
def func(row):
    dt, id, no = row['date'], row['ID'], row['no']

    # ID毎に管理
    if id not in que:
        que[id] = [deque(), deque()] # [1,2以外, 1,2のみ]
    qs = que[id]

    # 古いものは不要なので削除
    remove(qs[0], dt)
    remove(qs[1], dt)

    # 対象数（同日を除く）を得る
    o, b = count(qs[0], dt), count(qs[1], dt)
    a = o+b
    qs[no in (1,2)].append(dt) # いずれかに振り分けて追加

    return a,b # とりあえず分かりやすいようにa,bを返す


df = df.sort_values('date')
que = {} # キー=ID, 値=[1,2以外の日付, 1,2の日付]
df['ab'] = df.apply(func, axis=1)
print(df)
"""
        date  ID  no      ab
0 2022-01-01   1   1  (0, 0)
1 2022-01-01   1   2  (0, 0)
2 2022-01-01   1   3  (0, 0)
3 2022-02-01   1   1  (3, 2)
4 2022-02-01   1   2  (3, 2)
5 2022-02-01   1   3  (3, 2)
6 2022-08-01   1   1  (3, 2)
"""

Comments

[auto_chiri7]

ご回答ありがとうございました。参考にさせていただきます。

Answer 3

pandas.DataFrame.rolling() を使う方法。(※ 処理速度に関しては不明)

import pandas as pd
import io

pd.set_option('display.unicode.east_asian_width', True)

csv_data = '''
日付,ID,付番
2021-12-31,14,1
2022-01-01,10,3
2022-01-01,11,2
2022-01-01,12,2
2022-01-02,15,3
2022-01-02,12,2
2022-01-02,18,1
2022-01-03,11,1
2022-12-31,11,3
'''
df = pd.read_csv(io.StringIO(csv_data), parse_dates=['日付'])

#
def aggregate(idx):
    dfi = df.loc[idx[:-1]]
    A = dfi['ID'].eq(df.loc[idx[-1], 'ID'])
    B = dfi[A]['付番'].isin((1, 2)).sum()
    ratio = (B / A.sum() * 100)
    return ratio

df['カウント率'] = df.sort_values('日付').reset_index()\
                     .rolling('181D', on='日付')['index']\
                     .apply(aggregate)
print(df)

Comments

[auto_chiri7]

ご回答ありがとうございました。rolling()を使ったことがなかったため活用させていただきます。