python - 计算每个月的支出金额，这取决于另一个列值 ID

Question

我正在尝试根据月份列获取每种类型 ID 上花费的金额

数据集:

ID  TYPE_ID Month_year  Amount
100 1   jun_2019    20
100 1   jul_2019    30
100 2   jun_2019    10
200 1   jun_2019    50
200 1   jun_2019    30
100 2   jul_2019    20
200 2   jun_2019    40
200 2   jul_2019    10
200 2   jun_2019    20
200 1   jul_2019    30
100 1   jul_2019    10

输出:根据每种类型 ID，我想根据月份计算支出。列值 TYPEID_1_jun2019 告诉我该特定月份完成的交易数量。 Amount_type1_jun2019 根据我的类型 ID 告诉我每个月的支出总额。

ID  TYPEID_1_jun2019    Amount_type1_jun2019    TYPEID_1_jul2019    Amount_type1_jul2019    TYPEID_2_jun2019    Amount_type2_jun2019    TYPEID_2_jul2019    Amount_type2_jul2019
100 1   20  2   40  1   10  1   20
200 1   80  1   30  2   60  1   10

编辑:我还想计算每个 ID 的平均每月支出

输出:还包括这些列，

ID      Average_type1_jul2019   Average_type1_jun2019

100              20                        10

我用来计算平均值的公式是7 月份类型 ID 1 的支出金额除以总月数。

Answer 1

首先将 Month_year 转换为日期时间以获得正确的顺序，然后创建辅助列 type 并使用 size 聚合 sum ， reshape DataFrame.unstack ，按 DataFrame.sort_index 排序最后将 MultiIndex 与日期时间展平为原始格式:

df['Month_year'] = pd.to_datetime(df['Month_year'], format='%b_%Y')
df1 = (df.assign(type=df['TYPE_ID']).groupby(['ID','Month_year','TYPE_ID'])
        .agg({'Amount':'sum', 'type':'size'})
        .unstack([1,2])
        .sort_index(axis=1, level=[1,2]))

df1.columns = df1.columns.map(lambda x: f'{x[0]}_{x[2]}_{x[1].strftime("%b_%Y")}')
df1 = df1.reset_index()
print (df1)
    ID  Amount_1_Jun_2019  type_1_Jun_2019  Amount_2_Jun_2019  \
0  100                 20                1                 10   
1  200                 80                2                 60   

   type_2_Jun_2019  Amount_1_Jul_2019  type_1_Jul_2019  Amount_2_Jul_2019  \
0                1                 40                2                 20   
1                2                 30                1                 10   

   type_2_Jul_2019  
0                1  
1                1  

编辑:

#removed sorting anf flatteting MultiIndex
df['Month_year'] = pd.to_datetime(df['Month_year'], format='%b_%Y')
df1 = (df.assign(type=df['TYPE_ID']).groupby(['ID','Month_year','TYPE_ID'])
        .agg({'Amount':'sum', 'type':'size'})
        .unstack([1,2]))
print (df1)
               Amount                          type                 
Month_year 2019-06-01     2019-07-01     2019-06-01    2019-07-01   
TYPE_ID             1   2          1   2          1  2          1  2
ID                                                                  
100                20  10         40  20          1  1          2  1
200                80  60         30  10          2  2          1  1

<小时/>

#get number of unique mmonth_year per ID and type and divided by Amount
df2 = df.groupby(['ID','TYPE_ID'])['Month_year'].nunique().unstack()
df3 = df1.xs('Amount', axis=1, level=0).div(df2, level=1)
#added top level Average
df3.columns = pd.MultiIndex.from_tuples([('Average', a, b) for a, b in df3.columns])
print (df3)
       Average                       
    2019-06-01       2019-07-01      
             1     2          1     2
ID                                   
100       10.0   5.0       20.0  10.0
200       40.0  30.0       15.0   5.0

<小时/>

#join together, sorting and flatten MultiIndex
df5 = pd.concat([df1, df3],axis=1).sort_index(axis=1, level=[1,2])
df5.columns = df5.columns.map(lambda x: f'{x[0]}_{x[2]}_{x[1].strftime("%b_%Y")}')
df5 = df5.reset_index()
print (df5)
    ID  Amount_1_Jun_2019  Average_1_Jun_2019  type_1_Jun_2019  \
0  100                 20                10.0                1   
1  200                 80                40.0                2   

   Amount_2_Jun_2019  Average_2_Jun_2019  type_2_Jun_2019  Amount_1_Jul_2019  \
0                 10                 5.0                1                 40   
1                 60                30.0                2                 30   

   Average_1_Jul_2019  type_1_Jul_2019  Amount_2_Jul_2019  Average_2_Jul_2019  \
0                20.0                2                 20                10.0   
1                15.0                1                 10                 5.0   

   type_2_Jul_2019  
0                1  
1                1