python - Pandas groupby 然后枢轴没有返回所需的输出

Question

我有一个数据框df，如下所示:

CA          Amount    Payment_Mode    Trans    
1001        230       Non-Digi        Normal
1001        330       Website         Normal
1001         40       Website         PPI
1002        100       Card            PPI
1002        700       Website         PPI
1002        100       Card            Normal

现在我想要一个结果数据帧df_f，如下所示，其中Payment_Mode计数和Trans计数按列捕获:

CA          Amount     Non-Digi     Website     Card     Normal    PPI
1001        600          1             2          0        2        1
1002        900          0             1          2        1        2

我的方法:

df_1 = df.groupby(['CA']).agg(Amount = ('Amount','sum'),
                          Payment_Mode = ('Payment_Mode','count'),
                          Trans = ('Trans','count')).reset_index()
df_f = df_1.pivot(index='CA', columns = 'Payment_Mode', values = '???') <-- This is where I am stuck

这方面有什么线索吗？

Answer 1

使用DataFrame.melt与 DataFrame.pivot_table首先，然后添加聚合总和列:

df_1 = (df.groupby('CA').sum()
          .join(df.melt(id_vars='CA',value_vars=['Payment_Mode','Trans'])
                  .pivot_table(index='CA', columns='value', values = 'variable',aggfunc='count', fill_value=0))
          .reset_index())
print (df_1)
     CA  Amount  Card  Non-Digi  Normal  PPI  Website
0  1001     600     0         1       2    1        2
1  1002     900     2         0       1    2        1