I have a DataFrame with MultiIndex looking like this after printing in the console:
value indA indB scenarioId group 2015-04-13 1 A -54.0 1.0 1.0 B -160.0 1.0 1.0 C -15.0 0.0 1.0 2 A -83.0 1.0 1.0 3 A -80.0 2.0 2.0 4 A -270.0 2.0 2.0 2015-04-14 1 A -56.0 1.0 1.0 B -1.0 1.0 1.0 C -60.0 0.0 1.0 2 A -32.0 1.0 1.0 3 A -91.0 2.0 2.0 4 A -17.0 2.0 2.0
I got it after I used the groupby
and sum
functions on my initial dataset.
I would like to keep the same format, but order it according to the value
column. I have tried hard to do it using the sorting functions, but I think that the fact of having the first index (for the dates) of the MultiIndex without name is a problem.
Essentially, the output should look like this:
value indA indB scenarioId group 2015-04-13 1 B -160.0 1.0 1.0 A -54.0 1.0 1.0 C -15.0 0.0 1.0 2 A -83.0 1.0 1.0 3 A -80.0 2.0 2.0 4 A -270.0 2.0 2.0 2015-04-14 1 C -60.0 1.0 1.0 A -56.0 1.0 1.0 B -1.0 0.0 1.0 2 A -32.0 1.0 1.0 3 A -91.0 2.0 2.0 4 A -17.0 2.0 2.0
Could someone enlighten me on this please?
Thanks in advance.
You can use sort_values
+ sort_index
:
print (df.sort_values('value').sort_index(level=[0,1], sort_remaining=False))
value indA indB
scenarioId group
2015-04-13 1 B -160.0 1.0 1.0
A -54.0 1.0 1.0
C -15.0 0.0 1.0
2 A -83.0 1.0 1.0
3 A -80.0 2.0 2.0
4 A -270.0 2.0 2.0
2015-04-14 1 C -60.0 0.0 1.0
A -56.0 1.0 1.0
B -1.0 1.0 1.0
2 A -32.0 1.0 1.0
3 A -91.0 2.0 2.0
4 A -17.0 2.0 2.0
Another solution - sort_values
with reset_index
and set_index
:
df = df.reset_index()
.sort_values(['level_0','scenarioId','value'])
.set_index(['level_0','scenarioId','group'])
print (df)
value indA indB
level_0 scenarioId group
2015-04-13 1 B -160.0 1.0 1.0
A -54.0 1.0 1.0
C -15.0 0.0 1.0
2 A -83.0 1.0 1.0
3 A -80.0 2.0 2.0
4 A -270.0 2.0 2.0
2015-04-14 1 C -60.0 0.0 1.0
A -56.0 1.0 1.0
B -1.0 1.0 1.0
2 A -32.0 1.0 1.0
3 A -91.0 2.0 2.0
4 A -17.0 2.0 2.0