CODEWITHSUNDEEP
pythonpandasstackpivot-table
sfortney
sfortney

Reputation: 2123

Unstacking DataFrame with Multiple 'Value' Columns in Python Pandas

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?

Upvotes: 2

Views: 2887

Answers (1)

piRSquared
piRSquared

Reputation: 294488

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

enter image description here

Upvotes: 1

Related Questions