Pandas df.pivot() and pd.pivot_table() with multiple indexes

Question

I'm trying to cast a long-format df with multiple indexes into a wide-format df. Why does df_in.pivot() fail and/or why is pd.pivot_table returning results with a weird hierarchical index so that I can't access the columns I am trying to cast?

# input table
df_in = pd.DataFrame({'idx1':range(2)*4, 'idx2':['a']*4+['b']*4, 'field': ['f1']*2+['f2']*2+['f1']*2+['f2']*2, 'value': np.array(range(2)*4)*2+1})
'''
  field  idx1 idx2  value
0    f1     0    a      1
1    f1     1    a      3
2    f2     0    a      1
3    f2     1    a      3
4    f1     0    b      1
5    f1     1    b      3
6    f2     0    b      1
7    f2     1    b      3
'''

# want something like this
pd.DataFrame({'idx1':range(2)*2, 'idx2': ['a']*2+['b']*2, 'a':[1,3]*2, 'b':[1,3]*2})
'''
   a  b  idx1 idx2
0  1  1     0    a
1  3  3     1    a
2  1  1     0    b
3  3  3     1    b
'''

#doesn't work => ValueError: all arrays must be same length
df_in.pivot(index=['idx1','idx2'], columns =['field'])

#doesn't work => weird hierarchical index
pd.pivot_table(df_in, index=['idx1','idx2'], columns =['field'])

'''
          value   
field        f1 f2
idx1 idx2         
0    a        1  1
     b        1  1
1    a        3  3
     b        3  3
'''
# doesn't work => KeyError: 'f1'
pd.pivot_table(df_in, index=['idx1','idx2'], columns =['field'])['f1']

# doesn't work => KeyError: 'f1'
pd.pivot_table(df_in, index=['idx1','idx2'], columns =['field']).reset_index()['f1']

akuiper · Accepted Answer · 2017-03-01 03:28:00Z

2

To avoid the multi-level columns, explicitly specify the value column as a string instead of a list would be sufficient:

df_in.pivot_table(values='value', index=['idx1', 'idx2'], columns='field').reset_index()

#field  idx1  idx2  f1  f2
#0        0     a    1   1
#1        0     b    1   1
#2        1     a    3   3
#3        1     b    3   3

If you do have multi-level columns, you can use tuple to access them, for instance:

df_out = df_in.pivot_table(values=['value'], index=['idx1', 'idx2'], columns='field')

gives a data frame with multi-level columns, to access f1 column, you can do:

df_out[('value', 'f1')]

gives:

#idx1  idx2
#0     a       1
#      b       1
#1     a       3
#      b       3
#Name: (value, f1), dtype: int64

edited Mar 1, 2017 at 3:28

answered Mar 1, 2017 at 3:19

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas df.pivot() and pd.pivot_table() with multiple indexes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related