Unstacking DataFrame with Multiple 'Value' Columns in Python Pandas

Question

Say I have a DataFrame that looks like this:

     keys    sample   verify  
0    foo       a        1      
1    bar       b        2      
2   monty      c        3      
3    foo       d        4      
4    bar       e        5   
5   monty      f        6

That I want in this form:

     foo_1    bar_1    monty_1  foo_2  bar_2  monty_2
0     a        b        c        1      2       3
1     d        e        f        4      5       6

What is the best way of unstacking? I have tried both pandas.pivot_table() and the pandas.unstack() function but the pivot table wont work with alphabetic values and the unstack function doesn't work the way I think it should (i.e. inverse of stack). I assume you could unstack column by column and join the dataframes at the end, the problem I am mostly having is with the unstack function and what it is doing. Any thoughts on the best way to do this?

piRSquared · Accepted Answer · 2016-08-02 22:44:52Z

1

df2 = df.set_index('keys').T.reset_index(drop=True) \
    .T.groupby(level=0).apply(lambda df: df.reset_index(drop=True)) \
    .stack().unstack(1).T

df2.columns = df2.columns.set_levels((df2.columns.levels[1] + 1).astype(str), level=1)
df2.columns = df2.columns.to_series().str.join('_')

df2

edited Aug 2, 2016 at 22:44

answered Aug 2, 2016 at 22:35

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

2 Comments

sfortney Over a year ago

Works great! Thanks a ton

piRSquared Over a year ago

@sfortney glad I could help

Collectives™ on Stack Overflow

Unstacking DataFrame with Multiple 'Value' Columns in Python Pandas

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related