Pandas Dataframeの列の値を条件付きで判定して、グループごとの合計を求める

Question

実現したいこと

以下のようなデータがあります。

# 'date1','date2','K','V'
d = [
(3,1,1,22),
(3,1,2,22),
(3,1,1,20),
(3,1,2,20),
(3,1,3,50),
(3,2,3,5),
(3,2,2,5),
(4,1,1,53),
(4,1,2,53),
(4,1,1,52),
(4,1,2,52),
(4,2,2,21),
(5,1,1,32),
(5,1,1,31),
(5,1,2,31),
(5,2,1,12),
(5,1,3,32),
(5,1,2,32),
(5,2,3,33)
]

次のようなDataframeを作りたいと考えています。

date1	date2	M12	M3
3	1	50	84
3	2	5	5
4	1	210	0
4	2	21	0
5	1	32	126
5	2	33	12

（※下記のご指摘にあったように、この表はM12,M3が逆でしたので、訂正致しました）

date1かつdate2でgroup分けしたい
Kが1か2のときの全てのVの合計をM12
Kが3のときの全てのVの合計をM3
（A&Bの重複はありません）

質問

質問
Pandasを使っての上記のDataframeの作成

以上、よろしくお願い致します。

該当のソースコード

import pandas as pd

d = [
(3,1,1,22),
(3,1,2,22),
(3,1,1,20),
(3,1,2,20),
(3,1,3,50),
(3,2,3,5),
(3,2,2,5),
(4,1,1,53),
(4,1,2,53),
(4,1,1,52),
(4,1,2,52),
(4,2,2,21),
(5,1,1,32),
(5,1,1,31),
(5,1,2,31),
(5,2,1,12),
(5,1,3,32),
(5,1,2,32),
(5,2,3,33)
]

df = pd.DataFrame(data = d, columns=['date1','date2','K','V'])

# M12とM3のseriesを作って、最後にmergeしようと考えました
df12 =  df.groupby(['date1', 'date2']).filter(lambda x : (x['K'] == 1 or x['K'] == 2))
df3 =  df.groupby(['date1', 'date2']).filter(lambda x : x['K'] == 3)

　　（省略）

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

上記ソースのgroupby部分。

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

Python 3.11.2
pandas 1.5.3

Answer 1

dfx = df.groupby(['date1', 'date2'])['K']\
        .agg(M12 = lambda k: df.loc[k.index[k.isin((1, 2))], 'V'].sum(),
              M3 = lambda k: df.loc[k.index[k.eq(3)], 'V'].sum())\
        .reset_index()

print(dfx)

date1	date2	M12	M3
3	1	84	50
3	2	5	5
4	1	210	0
4	2	21	0
5	1	126	32
5	2	12	33

Comments

[kanchiru]

ご回答ありがとうございます。
想像以上に複雑でした。
複雑でまだ調べきれておらず、理解が途中です。

恐縮ですが、さきに追加の質問をさせてください。
値を変えると、下記のようなエラーがでます。
データ数が多い別のデータでも下記のようなエラーがでました。
また、実際には、date1はYYYYMMDDのようなフォーマットを使いたいと考えております。

・（例）三行だけにした場合
d=[
(3,1,1,22),
(3,1,2,22),
(3,1,1,20)
]

d=[
(20230403,1,1,22),
(20230403,1,2,22),
(20230403,1,1,20)
]

---------------------------------------------------------------------------
ValueError Traceback (most recent call last)
Cell In[63], line 5
1 df = pd.DataFrame(data = d, columns=['date1','date2','K','V'])
3 dfx = df.query('K in (1, 2, 3)')
4 dfx = dfx.groupby(['date1', 'date2', df['K'].eq(3)])['V'].sum().unstack()\
----> 5 .set_axis(['M12', 'M3'], axis=1).fillna(0, downcast='infer')\
6 .reset_index().rename_axis(None, axis=1)
8 print(dfx)

File ~/lcworks/kzt2/venv/lib/python3.11/site-packages/pandas/util/_decorators.py:331, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
325 if len(args) > num_allow_args:
326 warnings.warn(
327 msg.format(arguments=_format_argument_list(allow_args)),
328 FutureWarning,
329 stacklevel=find_stack_level(),
330 )
--> 331 return func(*args, **kwargs)

　（略）

File ~/lcworks/kzt2/venv/lib/python3.11/site-packages/pandas/core/internals/base.py:70, in DataManager._validate_set_axis(self, axis, new_labels)
67 pass
69 elif new_len != old_len:
---> 70 raise ValueError(
71 f"Length mismatch: Expected axis has {old_len} elements, new "
72 f"values have {new_len} elements"
73 )

ValueError: Length mismatch: Expected axis has 1 elements, new values have 2 elements

[melian]

エラーの原因は K の値が 1 か 2 の行しか存在しないためです。少し考えてみます。
=> 修正しました。お試しください。

Answer 2

質問のテーブル、

4	1	0	210
4	2	0	21

↑
M12とM3が逆じゃないですか？
逆が正ということで進めます。

「# M12とM3のseriesを作って、最後にmergeしようと考えました」ということだけど、
集計してから無理やりマージしようとするんじゃなくて、
うまく集計できるようにデータを整えてから集計してみる、というやりかた

言葉で表すと
「3列目が1又は2なら、4列目をそのままで5列目に0を追加」
「3列目が3なら、4列目を0で置き換え、5列目に元の4列目の値をコピー」
↓
で、このように加工したデータを、date1、date2でgroupbyすれば、求める答えが出るはず
3列目はいらないんで削除。

#%%
import pandas as pd

# Input data
d = [
    (3,1,1,22),
    (3,1,2,22),
    (3,1,1,20),
    (3,1,2,20),
    (3,1,3,50),
    (3,2,3,5),
    (3,2,2,5),
    (4,1,1,53),
    (4,1,2,53),
    (4,1,1,52),
    (4,1,2,52),
    (4,2,2,21),
    (5,1,1,32),
    (5,1,1,31),
    (5,1,2,31),
    (5,2,1,12),
    (5,1,3,32),
    (5,1,2,32),
    (5,2,3,33)
]



df = pd.DataFrame(data = d, columns=['date1','date2','K','V'])

df['V2'] = df.apply(lambda row: 0 if row['K'] in [1, 2] else row['V'], axis=1)
df['V'] = df.apply(lambda row: 0  if row['K'] == 3 else row['V'], axis=1)

df = df.drop('K', axis=1)
df = df.groupby(['date1', 'date2'], as_index=False).sum()
print(df)

自分はこれが限界。細かい最適化とかもっとシンプルであっと驚くような方法はmelian大先生が教えてくれるでしょう。

Comments

[kanchiru]

ご回答ありがとうございます。
なるほど、gropubyの前に、M12とM3を場合わけでつくっておくのですね。

apply中のlambdaの使い方がきちんと理解できていなかったのですが、
上記のサンプルでよく理解できるようになりました。
おかげさまでこれからはもっとうまくapplyを使えるようになると思います。

ありがとうございました。

（ご指摘にあるよう、質問中のM12とM3列が逆になっていました。その旨を追記の上、訂正しました。ご指摘ありがとうございます）
（適切なご回答をいただき恐縮なのですが、今回は先にまた一行での含蓄のあるご回答いただいたmelian様をベストアンサーにさせていただきました）