Pandas transpose on double column index

Question

I have a data frame like the one below

And I want to transform it like following

Its a Double column index Transformation and I don't mind which of price/quantity, or date is the first level of column index. I think date is top and splits for price/quantity but couldn't create the table like this here.Its a data frame with about a million lines and all dates of year, so do you have any idea for the most efficient code to to this? Empty cells could also be 0s

SAMPLE DATA

CID	PROD_ID	PRICE	DATE	QUANTITY
123	x	1	31-12-20	5
123	x	1	1-1-21	5
123	x	1	2-1-21	7
123	y	2	2-1-21	11
789	z	5	1-1-21	22
256	a	4	1-1-21	9
324	b	4	1-1-21	16
789	z	5.5	2-1-21	22
324	a	4.5	2-1-21	23
324	e	8	2-1-21	3

Please provide your sample data in text instead of picture here. Probably your question was downvoted because of this. More importantly, contributors here are less willing to answer your question if can't get your sample data by copy and paste for testing. — SeaBean, Jul 08 '21 at 09:55
Then use `df = df.pivot_table(index=['CID','PROD_ID'], columns='DATE')` — jezrael, Jul 08 '21 at 10:32

score 1 · Accepted Answer · answered Jul 08 '21 at 10:33

Use pandas.pivot_table

pd.pivot_table(
    df,
    values=['PRICE', 'QUANTITY'],
    index=['CID', 'PROD_ID'],
    columns=['DATE'],
    aggfunc=sum
)

Output

             PRICE                 QUANTITY                
DATE        1-1-21 2-1-21 31-12-20   1-1-21 2-1-21 31-12-20
CID PROD_ID                                                
123 x          1.0    1.0      1.0      5.0    7.0      5.0
    y          NaN    2.0      NaN      NaN   11.0      NaN
256 a          4.0    NaN      NaN      9.0    NaN      NaN
324 a          NaN    4.5      NaN      NaN   23.0      NaN
    b          4.0    NaN      NaN     16.0    NaN      NaN
    e          NaN    8.0      NaN      NaN    3.0      NaN
789 z          5.0    5.5      NaN     22.0   22.0      NaN

Thanks a lot. And how do I access a cell here? – Poulos Spyros Jul 08 '21 at 11:25 — Poulos Spyros, Jul 08 '21 at 11:25
Use multiindexing `df.loc[(123, 'x'), ('PRICE', '1-1-21')]` – Vishnudev Krishnadas Jul 08 '21 at 11:29 — Vishnudev Krishnadas, Jul 08 '21 at 11:29

Pandas transpose on double column index

1 Answers1