I have dataframe like this:
i want to reshape it to:
I Have tried:
df2.pivot(index ='color', columns ='quarter') but getting error
KeyError: 'color'
During handling of the above exception, another exception occurred:
Also Stack and unstack but not getting desired result. Please help.
Use DataFrame.unstack with DataFrame.swaplevel and DataFrame.sort_index:
df2 = pd.DataFrame({
'color':list('abcabc'),
'B':[4,5,4,5,5,4],
'C':[7,8,9,4,2,3],
'D':[1,3,5,7,1,0],
'quarter':['FY2015_16_Q1'] * 3 + ['FY2015_16_Q2'] * 3
}).set_index(['quarter','color'])
print (df2)
B C D
quarter color
FY2015_16_Q1 a 4 7 1
b 5 8 3
c 4 9 5
FY2015_16_Q2 a 5 4 7
b 5 2 1
c 4 3 0
print (df2.index)
MultiIndex([('FY2015_16_Q1', 'a'),
('FY2015_16_Q1', 'b'),
('FY2015_16_Q1', 'c'),
('FY2015_16_Q2', 'a'),
('FY2015_16_Q2', 'b'),
('FY2015_16_Q2', 'c')],
names=['quarter', 'color'])
df = df2.unstack(0).swaplevel(0,1, axis=1).sort_index(axis=1, level=0)
Or DataFrame.stack with unstack:
df = df2.stack().unstack([0,2])
print (df)
quarter FY2015_16_Q1 FY2015_16_Q2
B C D B C D
color
a 4 7 1 5 4 7
b 5 8 3 5 2 1
c 4 9 5 4 3 0
print (df2.info())?