I want to do a time series analysis and I want that each sublevel (level=delta_mins) get reversed for each date. So basically I want to transform df1 to df2.
df1
date delta_mins A B C
2019-12-2 20 a1 b1 c1
30 a2 b2 c2
40 a3 b3 c3
50 a4 b4 c4
60 a5 b5 c5
2019-12-3 20 d1 e1 f1
30 d2 e2 f2
40 d3 e3 f3
50 d4 e4 f4
60 d5 e5 f5
2019-12-4 20 g1 h1 i1
30 g2 h2 i2
40 g3 h3 i3
50 g4 h4 i4
60 g5 h5 i5
transform to df2
date delta_mins A B C
2019-12-2 60 a5 b5 c5
50 a4 b4 c4
40 a3 b3 c3
30 a2 b2 c2
20 a1 b1 c1
2019-12-3 60 d5 e5 f5
50 d4 e4 f4
40 d3 e3 f3
30 d2 e2 f2
20 d1 e1 f1
2019-12-4 60 g5 h5 i5
50 g4 h4 i4
40 g3 h3 i3
30 g2 h2 i2
20 g1 h1 i1
I beleive you need DataFrame.sort_index with ascending=False for second level:
df = df.sort_index(level=[0, 1], ascending=[True, False])
print (df)
A B C
date delta_mins
2019-12-02 60 a5 b5 c5
50 a4 b4 c4
40 a3 b3 c3
30 a2 b2 c2
20 a1 b1 c1
2019-12-03 60 d5 e5 f5
50 d4 e4 f4
40 d3 e3 f3
30 d2 e2 f2
20 d1 e1 f1
2019-12-04 60 g5 h5 i5
50 g4 h4 i4
40 g3 h3 i3
30 g2 h2 i2
20 g1 h1 i1
If not possible you can create helper level by GroupBy.cumcount and DataFrame.set_index, sorting and last drop it:
df = (df.set_index(df.groupby(level=0).cumcount(), append=True)
.sort_index(level=[0, 2], ascending=[True, False])
.reset_index(level=2, drop=True))
print (df)
A B C
date delta_mins
2019-12-02 60 a5 b5 c5
50 a4 b4 c4
40 a3 b3 c3
30 a2 b2 c2
20 a1 b1 c1
2019-12-03 60 d5 e5 f5
50 d4 e4 f4
40 d3 e3 f3
30 d2 e2 f2
20 d1 e1 f1
2019-12-04 60 g5 h5 i5
50 g4 h4 i4
40 g3 h3 i3
30 g2 h2 i2
20 g1 h1 i1