Transposing a multi index data frame

Question

Consider the following dataframe:

data = {
  'A': [0, 0, 0, 1, 1, 1, 1],
  'B': list('ABCABCD'),
  'C': range(12, 19)
}
pd.DataFrame(data).set_index(['A', 'B'])

      C
A B    
0 A  12
  B  13
  C  14
1 A  15
  B  16
  C  17
  D  18

I need to convert this dataframe to

Context: I am doing a groupby operation, and the apply function is return a series. groupby then converts that to a multi index, but it is easiest for me to represent my data with group labels as columns instead of rows.

`df['C'].unstack('A')`, or `df.rename_axis([None, None])['C'].unstack(0)` for the exact output — user3483203, Dec 12 '19 at 22:28
@user3483203 That doesn't answer that. >>> df['C'].unstack() B A B C D A 0 12.0 13.0 14.0 NaN 1 15.0 16.0 17.0 18.0 — skgbanga, Dec 12 '19 at 22:30
Please review [ask], and provide a [mcve], showing your current attempt. — user3483203, Dec 12 '19 at 22:32

score 0 · Answer 1 · answered Dec 12 '19 at 22:31

You can try with pd.pivot_table():

import pandas as pd 
import matplotlib.pyplot as plt
data = {'A': [0, 0, 0, 1, 1, 1, 1],'B': list('ABCABCD'),'C': range(12, 19)}
df = pd.DataFrame(data).set_index(['A', 'B'])
print(df)
new_df = pd.pivot_table(df,columns=['A'],index=['B'],values=['C'])
print(new_df)

Output:

A     0     1
B
A  12.0  15.0
B  13.0  16.0
C  14.0  17.0
D   NaN  18.0

Transposing a multi index data frame

1 Answers1