Pandas-合并两列时透视表

Question

我有一个表，它有两个分类列和几个数字列。我希望在将两个分类列合并为数字列的名称的同时，将表转到某个索引上(在本例中，索引是客户端(。有没有一种方法可以在没有循环的情况下做到这一点？

这是一个示例表：

案例>>4015<17.66352173><1>39220.89442719117816.0300630643.27374<2>40151.788854382<100122>1.153402921.632993162<100122>16.89884719>td>6735.594059<2>4015<1.414213562>16701.958441.133333333<1><2906>79.5>td>6399.977109><1095445115>

客户	项目	支出	value_mean	value_std
MisterA	项目_15	100122	9655.566667	12.70499327
MisterA	项目_15	2	9653
MisterA	项目_15		7	8953	94.4	2.065591118
MisterA	项目_16	65519.4
MisterA		项目_16		8953	21093.5
MisterA		项目_16	7	8953	30665.3
MisterA	项目_16	65517.8
MisterA	项目_16	65518.86667
MisterA	项目_16	5	3109	65519
MisterA	项目_18	78.84444444
MisterA	项目_18	5	3109	5820
MisterB	项目_15		7	9063	94.6	1.6465545205
MisterB	项目_15	9636	14.38749457
MisterB	项目_15		6	8968	93.6	1.264911064
MisterB	项目_16	5	4016	65519
MisterB	项目_16			8968	22375.3
MisterB	项目_16		7	9063	36482.5	31091.74401
MisterB	项目_16			98966	65518.78
MisterB	项目_18	1.914854216
MisterB	项目_18	5	4016	6257
MisterB	项目_18		6	8968	13304.3	52.38330947
MisterB	项目_18	2	4015	78.8

单向->使用CCD_ 1，然后重命名CCD_。如果需要，可以使用fill_Value=0。

df1 = df.pivot_table(index='Client', columns=['Project', 'Case'], values=[
'Spending', 'value_mean', 'value_std'])
df1.columns = [f'{j}_Case_{k}_{i}' for i, j, k in df1.columns]

使用.stack()+.unstack()：然后可以将列序列保持在原始的逐行3列序列之后，如预期输出中的列序列所示：

df2 = df.set_index(['Client', 'Project', 'Case']).stack().unstack([1,2]).unstack()
df2.columns = df2.columns.map(lambda x: f'{x[0]}_{x[1]}_{x[2]}')
df2 = df2.reset_index()

结果：

print(df2)

Client  Project_15_4_Spending  Project_15_4_value_mean  Project_15_4_value_std  Project_15_2_Spending  Project_15_2_value_mean  Project_15_2_value_std  Project_15_7_Spending  Project_15_7_value_mean  Project_15_7_value_std  Project_16_1_Spending  Project_16_1_value_mean  Project_16_1_value_std  Project_16_6_Spending  Project_16_6_value_mean  Project_16_6_value_std  Project_16_7_Spending  Project_16_7_value_mean  Project_16_7_value_std  Project_16_2_Spending  Project_16_2_value_mean  Project_16_2_value_std  Project_16_4_Spending  Project_16_4_value_mean  Project_16_4_value_std  Project_16_5_Spending  Project_16_5_value_mean  Project_16_5_value_std  Project_18_4_Spending  Project_18_4_value_mean  Project_18_4_value_std  Project_18_5_Spending  Project_18_5_value_mean  Project_18_5_value_std  Project_15_6_Spending  Project_15_6_value_mean  Project_15_6_value_std  Project_18_1_Spending  Project_18_1_value_mean  Project_18_1_value_std  Project_18_6_Spending  Project_18_6_value_mean  Project_18_6_value_std  Project_18_2_Spending  Project_18_2_value_mean  Project_18_2_value_std
0  MisterA               100122.0              9655.566667               12.704993                 4015.0                   9653.0               17.663522                 8953.0                     94.4                2.065591                 3922.0                  65519.4                0.894427                 8953.0                  21093.5             17816.03006                 8953.0                  30665.3             30643.27374                 4015.0                  65517.8                1.788854               100122.0              65518.86667                1.153402                 3109.0                  65519.0                1.632993               100122.0                78.844444               16.898847                 3109.0                   5820.0             6735.594059                    NaN                      NaN                     NaN                    NaN                      NaN                     NaN                    NaN                      NaN                     NaN                    NaN                      NaN                     NaN
1  MisterB                    NaN                      NaN                     NaN                 4015.0                   9636.0               14.387495                 9063.0                     94.6                1.646545                    NaN                      NaN                     NaN                 8968.0                  22375.3             16701.95844                 9063.0                  36482.5             31091.74401                    NaN                      NaN                     NaN                98966.0              65518.78000                1.133333                 4016.0                  65519.0                1.414214                    NaN                      NaN                     NaN                 4016.0                   6257.0             6399.977109                 8968.0                     93.6                1.264911                 2906.0                     79.5                1.914854                 8968.0                  13304.3               52.383309                 4015.0                     78.8                1.095445