I have a multilevel column pandas dataframe with orders from an online retailer. Each line has info from a single order. This results in multiple items within the same row. I need to create a single row for each item sold in the orders. Because of this there are hundreds of columns.
orderID orderinfo orderline orderline orderline orderline
nan orderaspect1 0 0 1 1
nan nan itemaspect1 itemaspect2 itemaspect1 itemaspect2
0 1 2 3 4 5 6
1 10 20 30 40 50 60
2 100 200 300 400 500 600
each row with the same number under orderline needs to have its own row which includes the info from order iD and all the order aspects. It needs to look something like this.
orderID orderinfo orderline orderline item #
nan nan itemaspect1 itemaspect2
0 1 2 3 4 0
1 10 20 30 40 0
2 100 200 300 400 0
3 1 2 5 6 1
4 10 20 50 60 1
5 100 200 500 600 1
This way there is a row for each ITEM instead of a row for each ORDER. I've tried using melt and stack to unpivot followed by pivoting but I've run into issue with that and the multiindex columns. Nothing is formatting it correctly.
[edit]
It should look like this using code.
i = pd.DatetimeIndex(['2011-03-31', '2011-04-01', '2011-04-04', '2011-04-05',
'2011-04-06', '2011-04-07', '2011-04-08', '2011-04-11',
'2011-04-12', '2011-04-13'])
cols = pd.MultiIndex.from_product([['orderID', "orderlines"],['order data', 0, 1]])
df = pd.DataFrame(np.random.randint(10, size=(len(i), 6)),index=i, columns=cols)
