CODEWITHSUNDEEP
pandas
user5871190
user5871190

Reputation: 161

Pivoting one level of index in multilevel index

Suppose I have a dataframe with a multi-index:

                   col1   col2
 date        id    
 2016-04-05   A     100     99
 2016-04-05   B     101     98
 2016-04-05   C     102     95
 ...
 2016-04-12   A      90     65
 2016-04-12   B      91     62
 2016-04-12   C      93     64

How do I take col1 and pivot the 'id' level of the index into a frame where the index is only 'date' and the columns are the values of 'id'?

       date     A     B     C 
 2016-04-05   100   101   102
 2016-04-06    80    87    83
 ...
 2016-04-12    90    91    93

Thanks

Upvotes: 3

Views: 76

Answers (1)

jezrael
jezrael

Reputation: 862791

I think you can first drop col2, then unstack with droplevel, rename_axis and last reset_index:

df = df.drop('col2', axis=1).unstack('id')
df.columns = df.columns.droplevel(0)
df = df.rename_axis(None, axis=1).reset_index()
print df
        date    A    B    C
0 2016-04-05  100  101  102
1 2016-04-12   90   91   93

Next solution with pivot:

print df.reset_index()
        .pivot(index='date', columns='id', values='col1')
        .rename_axis(None, axis=1)
        .reset_index()

        date    A    B    C
0 2016-04-05  100  101  102
1 2016-04-12   90   91   93

Upvotes: 3

Related Questions