I need to melt the top column level [6,3,4] of the following dataframe while preserving the value columns [a,b,c] and the sorting of the id columns [index,key]. In other words, as with melt, the variable column [6,3,4] should be the outer hierarchical level.
np.random.seed(1)
( pd.DataFrame
( { (i,j):np.arange(10) + (i*ord(j))
for i in [6,3,4] for j in ["a","b","c"]
}
, index=np.random.randint(10,size=10)
)
. rename_axis("key")
. reset_index().reset_index()
)
index key 6 3 4
a b c a b c a b c
0 0 5 582 588 594 291 294 297 388 392 396
1 1 8 583 589 595 292 295 298 389 393 397
2 2 9 584 590 596 293 296 299 390 394 398
3 3 5 585 591 597 294 297 300 391 395 399
4 4 0 586 592 598 295 298 301 392 396 400
5 5 0 587 593 599 296 299 302 393 397 401
6 6 1 588 594 600 297 300 303 394 398 402
7 7 7 589 595 601 298 301 304 395 399 403
8 8 6 590 596 602 299 302 305 396 400 404
9 9 9 591 597 603 300 303 306 397 401 405
I had originally discarded the following as I was under the impression it would not preserve the sort order of the other columns. After learning about sort_remaining=False, now I wonder if the sort order of the other columns is guaranteed to be preserved? Edit: It should be with the stable algorithm, per the linked numpy documentation.
( b.rename_axis(["l1","l2"], axis=1)
. set_index(["index","key"])
. stack("l1")
. sort_index(level="l1", sort_remaining=False, kind="stable")
. reset_index()
)
That said, I'm not impressed with the above as unlike melt it doesn't preserve the column order. There is reindex which requires swaplevel but I'm not sure if that's stable either. Also, very complicated.
( b.rename_axis(["l1","l2"], axis=1)
. set_index(["index","key"])
. pipe
( lambda df: df
. stack("l1")
. swaplevel(0,"l1")
. reindex
( df.columns.get_level_values(0).drop_duplicates()
, level="l1"
)
. swaplevel("l1",-1)
)
. reset_index()
)
Expected output:
l2 index key l1 a b c
0 0 5 6 582 588 594
1 1 8 6 583 589 595
2 2 9 6 584 590 596
3 3 5 6 585 591 597
4 4 0 6 586 592 598
5 5 0 6 587 593 599
6 6 1 6 588 594 600
7 7 7 6 589 595 601
8 8 6 6 590 596 602
9 9 9 6 591 597 603
10 0 5 3 291 294 297
11 1 8 3 292 295 298
12 2 9 3 293 296 299
13 3 5 3 294 297 300
14 4 0 3 295 298 301
15 5 0 3 296 299 302
16 6 1 3 297 300 303
17 7 7 3 298 301 304
18 8 6 3 299 302 305
19 9 9 3 300 303 306
20 0 5 4 388 392 396
21 1 8 4 389 393 397
22 2 9 4 390 394 398
23 3 5 4 391 395 399
24 4 0 4 392 396 400
25 5 0 4 393 397 401
26 6 1 4 394 398 402
27 7 7 4 395 399 403
28 8 6 4 396 400 404
29 9 9 4 397 401 405