I have a multiindex DataFrame that I want to use its columns as rows and then rename the column to the name of the measure.
arrays = [np.array(['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux']),
np.array(['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two'])]
df = pd.DataFrame(np.random.randn(8, 4), index=arrays)
0 1 2 3
bar one 0.049409 0.533468 0.528360 -1.437937
two 2.081377 -0.945535 0.237531 -0.781147
baz one 0.005216 1.158222 -1.178232 -1.470667
two -0.043834 -0.320864 -1.568357 0.803620
foo one -0.758539 -1.009726 0.139992 0.281034
two -1.806000 0.206872 -0.728195 1.051045
qux one -1.106591 -0.621868 -1.139649 -0.185527
two 0.176220 -0.961532 3.587891 0.627658
I want to make my dataframe look like this:
measure_name
bar one 0 0.049409
two 1 -0.945535
one 2 0.528360
two 3 -0.781147
I can't figure out how to do this. I tried pd.melt() but that gets rid of the multiindex and I need to have a way to tie the values from the columns to the index.
Thanks in advance!
IIUC:
df.stack().to_frame('measure_name')
measure_name
bar one 0 0.562183
1 2.090766
2 -0.164342
3 0.499693
two 0 -0.174269
1 -0.997726
2 0.820774
3 0.243022
baz one 0 -0.158621
1 0.520945
2 -0.356393
3 0.465289
two 0 -1.187833
1 0.886986
2 1.415511
3 0.940117
foo one 0 -0.010860
1 0.126255
2 1.131045
3 -0.899853
two 0 -1.121544
1 -0.327184
2 0.074396
3 0.214501
qux one 0 -0.028317
1 -1.476114
2 1.415711
3 -0.355655
two 0 0.285167
1 1.535384
2 0.074326
3 -1.860993