I have a dataframe that has the shape like this:
PX_LAST PX_OPEN PX_CLOSE ticker source timestamp
0 1 2 3 A LSE 20180101
1 4 5 6 A LSE 20180102
1 7 8 9 B LSE 20180101
1 10 11 12 B LSE 20180102
....
I want to massage it to the following format:
A B
LSE LSE
PX_LAST, PX_CLOSE, PX_OPEN PX_LAST, PX_CLOSE, PX_OPEN
timestamp
20180101 1 2 3 7 8 9
20180102 4 5 6 10 11 12
....
I tried to first use set_index
to set the ticker and source columns to the row index and use unstack
to push them on to the column axis which does seem to work
df.set_index(['timestamp', 'ticker', 'source'])
.unstack(level=[1,2])
.swaplevel(0,1,axis=1)
.swaplevel(1,2,axis=1)
This does the trick, but has two problems: 1) it is very verbose, with all the swaplevel call we need to do to get the columns into the right shape. 2) It seems it does not do the grouping I wish it does, i.e the result I get is like this:
LSE LSE LSE LSE ...
PX_LAST PX_LAST PX_CLOSE PX_CLOSE ...
timestamp
20180101 1 7 2 8 ...
20180102 4 8 5 11 ...
Is there a cleaner way to do this so I can get the format I want?