在dataframe中仅透视两列并添加其中一列

Question

我有一个数据帧，如：

             m_id     p_id          coins     price  game_level
0            5012     26456         5120.0  15990.0   level 5
1            5012     26457        15360.0  12000.0   level 4
2            5012     26470        15360.0   8990.0   level 2
3            5012     26471        30720.0   9990.0   level 1
4            5013     26472        15360.0   4990.0   level 1
5            5013     26473        30720.0   5990.0   level 4
6            5014     26456         5120.0  15990.0   level 5
7            5014     26457        15360.0  12000.0   level 4
8            5015     26470        15360.0   8990.0   level 2
9            5015     26471        30720.0   9990.0   level 1

我想要这个df。（枢轴和数字列硬币和水平，以及添加此m_id.p_id的价格字段，您可以将其删除）：

   m_id    coins 1   coins 2   coins 3   coins 4  price   game_level 1  game_level 2   game_level 3   game_level 4
0  5012     5120.0   15360.0   15360.0   30720.0  46970        level 5       level 4        level 2        level 1
1  5013    15360.0   30720.0         0         0  10980        level 1       level 4              0              0
2  5014     5120.0   15360.0         0         0  27990        level 5       level 4              0              0
3  5015    15360.0   30720.0         0         0  16980        level 2       level 1              0              0

有什么想法吗

Answer 1

您可以通过创建一个运行编号列，然后使用groupby和unstack来解决这个问题

# Create running number column for each m_id
df['running_num'] = df.groupby(['m_id']).cumcount()+1

result = (df.groupby(["m_id","running_num"])[['coins', 'game_level']]
            .agg(lambda x: x)
            .unstack(fill_value=0)
            .reset_index())

# Rename columns
result.columns = [f"{x} {y}" if y != "" else x for x,y in result.columns]

result

   m_id  coins 1  coins 2  coins 3  coins 4 game_level 1 game_level 2  \
0  5012   5120.0  15360.0  15360.0  30720.0      level 5      level 4   
1  5013  15360.0  30720.0      0.0      0.0      level 1      level 4   
2  5014   5120.0  15360.0      0.0      0.0      level 5      level 4   
3  5015  15360.0  30720.0      0.0      0.0      level 2      level 1   

  game_level 3 game_level 4  
0      level 2      level 1  
1            0            0  
2            0            0  
3            0            0  

您可以按添加价格列

result = result.merge(df.groupby(['m_id'], as_index=False).price.sum(), on='m_id')

result

   m_id  coins 1  coins 2  coins 3  coins 4 game_level 1 game_level 2  \
0  5012   5120.0  15360.0  15360.0  30720.0      level 5      level 4   
1  5013  15360.0  30720.0      0.0      0.0      level 1      level 4   
2  5014   5120.0  15360.0      0.0      0.0      level 5      level 4   
3  5015  15360.0  30720.0      0.0      0.0      level 2      level 1   

  game_level 3 game_level 4    price  
0      level 2      level 1  46970.0  
1            0            0  10980.0  
2            0            0  27990.0  
3            0            0  18980.0