python - 在 python 中将列重复为行？

Question

   Fruit      January Shipments   January Sales   February Shipments   February Sales  
 ------------ ------------------- --------------- -------------------- ---------------- 
  Apple                      30              11                   18               31   
  Banana                     12              49                   39               14   
  Pear                       25              50                   44               21   
  Kiwi                       41              25                   10               25   
  Strawberry                 11              33                   35               50   

我正在努力实现以下结果:

 Fruit       Month     Shipments   Sales  
 ------------ ---------- ----------- ------- 
  Apple        January          30      11   
  Banana       January          12      49   
  Pear         January          25      50   
  Kiwi         January          41      25   
  Strawberry   January          11      33   
  Apple        February         18      31   
  Banana       February         39      14   
  Pear         February         44      21   
  Kiwi         February         10      25   
  Strawberry   February         35      50   

我已经尝试过 pandas.pivot 和 pandas.pivot_table 但没有成功。我正在创建两个数据框 (Fruit/Month/Shipments) 和 (Fruit/Month/Sales)，并通过循环将两者连接成一个，但我希望有一种更简单的方法来做到这一点。

Answer 1

一种方法是使用将列修改为多层，然后使用堆栈。假设您的数据框称为 df。首先将 Fruit 列设置为索引，然后定义多级列:

df = df.set_index('Fruit')
# manual way to create the multiindex columns
#df.columns = pd.MultiIndex.from_product([['January','February'],
#                                         ['Shipments','Sales']], names=['Month',None])
# more general way to create the multiindex columns thanks to @Scott Boston
df.columns = df.columns.str.split(expand=True)
df.columns.names = ['Month',None]

你的数据看起来像:

Month        January        February      
           Shipments Sales Shipments Sales
Fruit                                     
Apple             30    11        18    31
Banana            12    49        39    14
Pear              25    50        44    21
Kiwi              41    25        10    25
Strawberry        11    33        35    50

现在你可以在 level 0 和 reset_index 上使用 stack

df_output = df.stack(0).reset_index()

给出

        Fruit     Month  Sales  Shipments
0       Apple  February     31         18
1       Apple   January     11         30
2      Banana  February     14         39
3      Banana   January     49         12
4        Pear  February     21         44
5        Pear   January     50         25
6        Kiwi  February     25         10
7        Kiwi   January     25         41
8  Strawberry  February     50         35
9  Strawberry   January     33         11

最后，如果您希望月份列中的值按特定顺序排列，您可以使用 pd.Categorical:

df_output['Month'] = pd.Categorical(df_output['Month'].tolist(), ordered=True,
                                    categories=['January','February'])

排序时设置一月在二月之前。现在，做

df_output = df_output.sort_values(['Month'])

给出结果:

        Fruit     Month  Sales  Shipments
1       Apple   January     11         30
3      Banana   January     49         12
5        Pear   January     50         25
7        Kiwi   January     25         41
9  Strawberry   January     33         11
0       Apple  February     31         18
2      Banana  February     14         39
4        Pear  February     21         44
6        Kiwi  February     25         10
8  Strawberry  February     50         35

我看到它不完全是预期的输出(水果列中的顺序和列的顺序)，但如果需要，两者都可以轻松更改。