使用多索引列展平DataFrame [英] Flatten DataFrame with multi-index columns

问题描述

我想将从数据透视表派生的Pandas DataFrame转换为行表示，如下所示.

I'd like to convert a Pandas DataFrame that is derived from a pivot table into a row representation as shown below.

我在这里:

import pandas as pd
import numpy as np
df = pd.DataFrame({
    'goods': ['a', 'a', 'b', 'b', 'b'],
    'stock': [5, 10, 30, 40, 10],
    'category': ['c1', 'c2', 'c1', 'c2', 'c1'],
    'date': pd.to_datetime(['2014-01-01', '2014-02-01', '2014-01-06', '2014-02-09', '2014-03-09'])
})
# we don't care about year in this example
df['month'] = df['date'].map(lambda x: x.month)
piv = df.pivot_table(["stock"], "month", ["goods", "category"], aggfunc="sum")
piv = piv.reindex(np.arange(piv.index[0], piv.index[-1] + 1))
piv = piv.ffill(axis=0)
piv = piv.fillna(0)
print piv

结果

stock            
goods        a       b    
category    c1  c2  c1  c2
month                     
1            5   0  30   0
2            5  10  30  40
3            5  10  10  40

这就是我想要去的地方.

And this is where I want to get to.

goods category month stock
    a       c1     1     5
    a       c1     2     0
    a       c1     3     0
    a       c2     1     0
    a       c2     2    10
    a       c2     3     0
    b       c1     1    30
    b       c1     2     0
    b       c1     3    10
    b       c2     1     0
    b       c2     2    40
    b       c2     3     0

以前，我用过

piv = piv.stack()
piv = piv.reset_index()
print piv

摆脱多索引，但这是因为我现在在两列(["goods", "category"])上旋转:

to get rid of the multi-indexes, but this results in this because I pivot now on two columns (["goods", "category"]):

      month category stock    
goods                    a   b
0         1       c1     5  30
1         1       c2     0   0
2         2       c1     5  30
3         2       c2    10  40
4         3       c1     5  10
5         3       c2    10  40

有人知道我如何摆脱列中的多重索引，并将结果放入示例格式的DataFrame中?

Does anyone know how I can get rid of the multi-index in the column and get the result into a DataFrame of the exemplified format?

推荐答案

>>> piv.unstack().reset_index().drop('level_0', axis=1)
   goods category  month   0
0      a       c1      1   5
1      a       c1      2   5
2      a       c1      3   5
3      a       c2      1   0
4      a       c2      2  10
5      a       c2      3  10
6      b       c1      1  30
7      b       c1      2  30
8      b       c1      3  10
9      b       c2      1   0
10     b       c2      2  40
11     b       c2      3  40

然后，您所要做的就是将最后一列的名称从0更改为stock.

then all you need is to change last column name from 0 to stock.

这篇关于使用多索引列展平DataFrame的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！