使用不同的过滤将数据帧连接到自身

Question

我有一个遵循以下结构的df：

store       day               type              sales       orders

amazon      2021-10-10        web               10          1
amazon      2021-10-10        retail            500         50
facebook    2021-10-10        retail            300         50
facebook    2021-09-05        retail            10          50
apple       2021-09-01        web               5           1
uber        2021-08-01        web               50          1
uber        2021-08-01        retail            60          1
...

我正在尝试有一个每周sales & orders by store, day & type的df_res，这样输出将如下所示：

day             type      sales_amazon        orders_amazon       sales_facebook       orders_facebook     sales_apple        orders_apple        sales_uber      orders_uber
2021-08-01      web       0                   0                   0                    0                   0                  0                   50              1     
2021-08-01      rtail     0                   0                   0                    0                   0                  0                   60              1    
2021-10-10      web       10                  1                   0                    0                   0                  0                   0               0      
2021-10-10      retail    500                 50                  300                  50                  0                  0                   0               0 
...   

我试过了：

# main df to be joined on
df_res = df[df.store.isin(['amazon'])].groupby(['store','type', pd.Grouper(key = 'day', freq = 'W-MON', 
                                               label = 'right')])[['store','day','orders','sales','type']].sum().reset_index()

# merging on main df each store df
for branch in ['facebook', 'apple', 'apple', 'uber']:
    df_res = df_res.merge(df[df.store.isin([branch])].groupby(['store','type', pd.Grouper(key = 'day', freq = 'W-MON',
                          label = 'right')])[['store','day','orders','sales','type']].sum().reset_index(), 
                          on =['day','type'], suffixes= [f'_{branch}', f'_{branch}'], how = 'outer')

但这并没有产生我想要的结构，我试着使用join，但这抛出了一个不同的长度错误，因为在某些情况下，给定商店的特定date & type组合没有销售。

Answer 1

您可以对MultiIndex进行pivot和返工：

df2 = (df.pivot_table(index=['day', 'type'], columns='store',
                      values=['sales', 'orders'], fill_value=0)
         .sort_index(axis=1, level=1)
       )

df2.columns = df2.columns.map('_'.join)
df2.reset_index()

输出：

          day    type  orders_amazon  sales_amazon  orders_apple  sales_apple  orders_facebook  sales_facebook  orders_uber  sales_uber
0  2021-08-01  retail              0             0             0            0                0               0            1          60
1  2021-08-01     web              0             0             0            0                0               0            1          50
2  2021-09-01     web              0             0             1            5                0               0            0           0
3  2021-09-05  retail              0             0             0            0               50              10            0           0
4  2021-10-10  retail             50           500             0            0               50             300            0           0
5  2021-10-10     web              1            10             0            0                0               0            0           0