Pandas 数据透视表排列无聚合

问题描述

我想在不进行聚合的情况下对 Pandas 数据框进行透视，而不是垂直呈现透视索引列，我想水平呈现它.我尝试使用 pd.pivot_table 但我没有得到我想要的.

I want to pivot a pandas dataframe without aggregation, and instead of presenting the pivot index column vertically I want to present it horizontally. I tried with pd.pivot_table but I'm not getting exactly what I wanted.

data = {'year': [2011, 2011, 2012, 2013, 2013],
        'A': [10, 21, 20, 10, 39],
        'B': [12, 45, 19, 10, 39]}

df = pd.DataFrame(data)
print df
    A   B  year
0  10  12  2011
1  21  45  2011
2  20  19  2012
3  10  10  2013
4  39  39  2013

但我想要:

year      2011     2012      2013
cols     A    B   A    B    A    B
0       10    12  20   19   10   10
1       21    45  NaN  NaN  39   39

推荐答案

您可以先通过 cumcount，然后是 stack 和 unstack:

You can first create column for new index by cumcount, then stack with unstack:

df['g'] = df.groupby('year')['year'].cumcount()
df1 = df.set_index(['g','year']).stack().unstack([1,2])
print (df1)

year  2011        2012        2013
         A     B     A     B     A     B
g
0     10.0  12.0  20.0  19.0  10.0  10.0
1     21.0  45.0   NaN   NaN  39.0  39.0

如果需要设置列名使用 rename_axis(pandas 0.18.0 中的新增内容):

If need set columns names use rename_axis (new in pandas 0.18.0):

df['g'] = df.groupby('year')['year'].cumcount()
df1 = df.set_index(['g','year'])
        .stack()
        .unstack([1,2])
        .rename_axis(None)
        .rename_axis(('year','cols'), axis=1)
print (df1)
year  2011        2012        2013
cols     A     B     A     B     A     B
0     10.0  12.0  20.0  19.0  10.0  10.0
1     21.0  45.0   NaN   NaN  39.0  39.0

pivot，但您需要通过 然后按 sort_index:

Another solution with pivot, but you need swap first and second level of Multiindex in columns by swaplevel and then sort it by sort_index:

df['g'] = df.groupby('year')['year'].cumcount()
df1 = df.pivot(index='g', columns='year')
df1 = df1.swaplevel(0,1, axis=1).sort_index(axis=1)
print (df1)
year  2011        2012        2013
         A     B     A     B     A     B
g
0     10.0  12.0  20.0  19.0  10.0  10.0
1     21.0  45.0   NaN   NaN  39.0  39.0
print (df1)

year  2011        2012        2013
         A     B     A     B     A     B
g
0     10.0  12.0  20.0  19.0  10.0  10.0
1     21.0  45.0   NaN   NaN  39.0  39.0

这篇关于Pandas 数据透视表排列无聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！