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
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.
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.
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.
By using DataFrame. droplevel() or DataFrame. columns. droplevel() you can drop a level from multi-level column index from pandas DataFrame.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With