[FIXED] Transpose multiples rows to columns along y index pandas

Issue

I have a sql request which gives the followed result:

Primary Key on pair (CodeProduct/ShopeName)

CodeProduct	ShopeName	Price	Stock
0001	FruitsStore	2,2	322
0001	BigStore	2,1	5666
0002	FruitStore	3,3	33333
0003	HelloStore	5,99	65

This request return 1000 lines and it has 20 differents Shop.

I put this result into a dataframe:

mytable = pd.read_sql(myrequest,db)

I want transpose the ShopeName in columns and for each ShopeName a column Price And Stock.

I almost succeed with:

mytable = pd.pivot_table(mytable , index='CodeProduct', columns='ShopName', values=['Price','Stock'],aggfunc='first', dropna=False)


              Stock                        ...        Price
ShopName CARREFOUR CITY CARREFOUR EXPRESS  ... MARCHE PROXI U EXPRESS
CodeProduct                       
20281               NaN               NaN  ...         4.21       NaN
24721               NaN               NaN  ...          NaN       NaN
29597               NaN               NaN  ...          NaN       NaN
30176               NaN               NaN  ...         2.69       NaN
38633               NaN               NaN  ...         4.02       NaN
...                 ...               ...  ...          ...       ...
945969              NaN               NaN  ...         2.04       NaN
946700             58.0               NaN  ...         2.21       NaN
946706             57.0               NaN  ...         2.17       NaN
946795            101.0               NaN  ...         3.25      3.32
971047              NaN               NaN  ...         1.95       NaN

But I have like hierachical index and not ShopeName_Price, Shopename_Stock

Solution

You can just reassign the headers.

df.columns=[f"{j}_{i}" for i,j in df.columns]

df being your dataframe variable .

Answered By - Bigya Pradhan