在 Python 中透视/解锁 Pandas 数据帧

Question

>我有以下数据帧

01/01/2017             02/01/2017
Productid   ProductName    Sales     Discount     Sales     Discount
1           abc            100       12           234       23
2           xyz            156       13           237       13
3           pqr            300       12           198       18

我需要将其转换为以下数据帧。

Productid   ProductName    Date          Sales      Discount
1           abc            01/01/2017    100        12
1           abc            02/01/2017    234        23
2           xyz            01/01/2017    156        13
2           xyz            02/01/2017    237        13
3           pqr            01/01/2017    300        12
3           pqr            02/01/2017    198        18

如何在 Python 中执行此操作？

Answer 1

多索引很难直接复制。因此，首先根据OP的原始数据帧初始化数据帧。

df = pd.read_clipboard() #reading part of OP's Dataframe
df
Productid   ProductName Sales   Discount    Sales.1 Discount.1
0           1           abc   100         12        234         23
1           2           xyz   156         13        237         13
2           3           pqr   300         12        198         18
df.columns = ['Productid', 'ProductName', 'Sales', 'Discount', 'Sales', 'Discount']
df.set_index(keys=['Productid','ProductName'],inplace=True)
df
Sales  Discount    Sales   Discount
Productid   ProductName             
1           abc    100        12      234         23
2           xyz    156        13      237         13
3           pqr    300        12      198         18
array = [['01/01/2017','01/01/2017','02/01/2017','02/01/2017'],
['Sales', 'Discount', 'Sales',  'Discount']]
df.columns = pd.MultiIndex.from_arrays(array) #setting multi-index

假设这是 OP 的数据帧：

df
01/01/2017         02/01/2017
Sales  Discount    Sales   Discount
Productid   ProductName             
1           abc    100        12      234         23
2           xyz    156        13      237         13
3           pqr    300        12      198         18

使用stack和level=0参数的解决方案，然后reset_index()level=[0,1]并再次reset_index()。最后使用rename将index列的名称更改为Date：

df = df.stack(level=0).reset_index(level=[0,1]).reset_index()
df.rename(columns={'index':'Date'},inplace=True)
df[['Productid', 'ProductName','Date','Sales','Discount']]
Productid   ProductName       Date  Sales   Discount
0           1           abc 01/01/2017    100         12
1           1           abc 02/01/2017    234         23
2           2           xyz 01/01/2017    156         13
3           2           xyz 02/01/2017    237         13
4           3           pqr 01/01/2017    300         12
5           3           pqr 02/01/2017    198         18