I have a multiindex pandas dataframe that looks like this
ID I II III
METRIC a b c d a b c d a b c d
2015-08-01 0 1 2 3 20 21 22 23 40 41 42 43
2015-08-02 4 5 6 7 24 25 26 27 44 45 46 47
2015-08-03 8 9 10 11 28 29 30 31 48 49 50 51
where it is indexed by the dates (2015-08-01
, 2015-08-02
, 2015-08-03
, etc.), the first-level columns (I
, II
, III
) are ID
s and the second-level columns are corresponding METRIC
s (a
, b
, c
, d
). I would like to reshape it to the following
METRIC a b c d
ID
I 2015-08-01 0 1 2 3
2015-08-02 4 5 6 7
2015-08-03 8 9 10 11
II 2015-08-01 20 21 22 23
2015-08-02 24 25 26 27
2015-08-03 28 29 30 31
III 2015-08-01 40 41 42 43
2015-08-02 44 45 46 47
2015-08-03 48 49 50 51
I have (unsuccessfully) looked into using .pivot
, .stack
, and .melt
, but they don't give me what I am looking for. I currently loop over ID
s and build a list of dataframes and concat
them together as a new dataframe to get what I want.
Any suggestions would be greatly appreciated.
Let's use stack
, swaplevel
and sort_index
:
df.stack(0).swaplevel(0,1).sort_index()
Output:
METRIC a b c d
ID
I 2015-08-01 0 1 2 3
2015-08-02 4 5 6 7
2015-08-03 8 9 10 11
II 2015-08-01 20 21 22 23
2015-08-02 24 25 26 27
2015-08-03 28 29 30 31
III 2015-08-01 40 41 42 43
2015-08-02 44 45 46 47
2015-08-03 48 49 50 51
You can let transpose
or T
do some of the work for you.
df.T.stack().unstack(1)
METRIC a b c d
ID
I 2015-08-01 0 1 2 3
2015-08-02 4 5 6 7
2015-08-03 8 9 10 11
II 2015-08-01 20 21 22 23
2015-08-02 24 25 26 27
2015-08-03 28 29 30 31
III 2015-08-01 40 41 42 43
2015-08-02 44 45 46 47
2015-08-03 48 49 50 51
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