Pandas分组和透视表

Question

我有一个pandas的df，设置如下：

    product salesperson positionHours  levelHours 
0      soap        john            10          25
1      nuts        john            15          27
2      soap        doug            12          29
3      nuts        doug            11          24
4      soap        tory            19          20
5      nuts        tory            20          20

我正在尝试实现以下功能，如何在pandas中完成？

    product     measurement   john  doug  tory 
0      soap   positionHours     10    12    19
1                levelHours     25    29    20 
3      nuts   positionHours     15    11    20
4                levelHours     27    24    20 

- guy

Answer 1

有很多方法可以实现这个目标。首先想到的两种方法是：

融合，然后透视：

(df.melt(["product", "salesperson"], var_name="measurement")
 .pivot(index=["product", "measurement"], columns="salesperson", values="value")
 .rename_axis(None, axis=1))

                       doug  john  tory
product measurement                    
nuts    levelHours       24    27    20
        positionHours    11    15    20
soap    levelHours       29    25    20
        positionHours    12    10    19

数据透视表，然后堆叠

(df.pivot(index="product", columns="salesperson", values=["positionHours", "levelHours"])
 .stack(0)
 .rename_axis(index=["product", "measurement"], columns=None))

                       doug  john  tory
product measurement                    
nuts    levelHours       24    27    20
        positionHours    11    15    20
soap    levelHours       29    25    20
        positionHours    12    10    19

先设置索引，然后执行unstack/stack组合操作。

(df.set_index(["product", "salesperson"])
 .rename_axis("measurement", axis=1)
 .unstack(1)
 .stack(0)
 .rename_axis(None, axis=1))

                       doug  john  tory
product measurement                    
nuts    levelHours       24    27    20
        positionHours    11    15    20
soap    levelHours       29    25    20
        positionHours    12    10    19

- Cameron Riddell