如何将索引与pandas数据框架中的列进行透视和连接

Question

我正在尝试做以下数据帧转换，希望您能帮助我。

我有一个按周索引的数据帧，

Name  revenue cost margin
week1  jon     10      4   6
week2  jon     11      3   8
week1  mary    15     10   5
week2  mary     6      8  -2
....    ...     ..     ..  .. 

根据列名，我如何pivot该索引并与列连接，创建index * len(columns)

要得到以下结果?

Name  revenue_week1 cost_week1 margin_week1 revenue_week2 cost_week2 margin_week2
jon          10         4            6             11         3            8
mary         15         10           5              6         8           -2
....         ...         ..           ..             ..        ..           ..

谢谢!

Answer 1

打开堆栈:

df=df.set_index('Name',append=True).unstack(0)
df.columns=[f"{x}_{y}" for x,y in df.columns]
df=df.reset_index()

输出:

Name     revenue_week1   revenue_week2   cost_week1  cost_week2  margin_week1    margin_week2
0       jon     10                  11              4           3           6               8
1       mary    15                  6               10          8           5              -2

Answer 2

您也可以选择reset_index和pivot:

df2 = df.reset_index().pivot(index='Name', columns='index')
df2.columns = ['%s_%s' % (a,b) for a,b in df2.columns]
df2

输出:

revenue_week1  revenue_week2  cost_week1  cost_week2  margin_week1  margin_week2
Name                                                                                  
jon              10             11           4           3             6             8
mary             15              6          10           8             5            -2