Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Transposing selected MultiIndex levels in Pandas DataFrame

I have a MultiIndexed DataFrame:

import pandas as pd
import numpy as np

l0, l1 = ['A', 'B'],['a', 'b']
c0 = ['c1', 'c2', 'c3']
data = np.arange(12).reshape(4,3)
df = pd.DataFrame(data=data, 
                  index=pd.MultiIndex.from_product([l0,l1]),
                  columns=c0)

>>>
     c1  c2  c3
A a   0   1   2
  b   3   4   5
B a   6   7   8
  b   9  10  11

I want to transpose a level of the MultiIndex and of the columns so that I result in:

df2 = pd.DataFrame(index=pd.MultiIndex.from_product([l0, c0]),
                   columns=l1)

>>>
    a    b
A c1  NaN  NaN
  c2  NaN  NaN
  c3  NaN  NaN
B c1  NaN  NaN
  c2  NaN  NaN
  c3  NaN  NaN

And obviously I want to populate the right values. My solution is currently to use map with an iterator but it feels like Pandas would have some native way of doing this. Am I right, is there a better (faster) way?

from itertools import product
def f(df, df2, idx_1, col_0):
    df2.loc[(slice(None), col_0), idx_1] = \
        df.loc[(slice(None), idx_1), col_0].values
m = map(lambda k: f(df, df2, k[0], k[1]), product(l1, c0))
list(m) # <- to execute

>>> df2
>>>
      a   b
A c1  0   3
  c2  1   4
  c3  2   5
B c1  6   9
  c2  7  10
  c3  8  11
like image 912
Attack68 Avatar asked Sep 29 '18 08:09

Attack68


People also ask

How do I change a specific index in pandas?

To change the index values we need to use the set_index method which is available in pandas allows specifying the indexes. where, inplace parameter accepts True or False, which specifies that change in index is permanent or temporary. True indicates that change is Permanent.

How do I change multiple index in pandas?

We can easily convert the multi-level index into the column by the reset_index() method. DataFrame. reset_index() is used to reset the index to default and make the index a column of the dataframe.

How do I convert MultiIndex to single index in pandas?

To revert the index of the dataframe from multi-index to a single index using the Pandas inbuilt function reset_index(). Returns: (Data Frame or None) DataFrame with the new index or None if inplace=True.

How do I get rid of Multi-Level index in pandas?

By using DataFrame. droplevel() or DataFrame. columns. droplevel() you can drop a level from multi-level column index from pandas DataFrame.


1 Answers

First stack the columns and then unstack the level that you want to become new columns:

df.stack().unstack(level=1)
Out: 
      a   b
A c1  0   3
  c2  1   4
  c3  2   5
B c1  6   9
  c2  7  10
  c3  8  11
like image 90
ayhan Avatar answered Nov 02 '22 07:11

ayhan