Pivot reshape dataframe

Question

I need help reshaping a DataFrame that i got from a csv file.

In this file I have the first column as ID and then 97 columns that represent sale units of 4 products during time.

the data is like

id    p1_201501   p1_201502 ......  p1_201812  p2_201501..... p2_2018012   p3_.. 

x12     125         12        ......  06          500     ..... 14         2...              
nz15     250         16        ......  600         423     ..... 312        56...     
....

The Id represent the area in which the product is sold. And I have 700 rows (i.e 700 df['id'].nunique() ) And so I need to find the yearly sales trend of a given product. So i thought the solution is to change the dataframe in order to have years as rows and the quantity for each product (p1, p2, p3)

Something like:

id    date        p1   p2    p3
x12   201501     12   500   32 
      201502    .... 
      201503    ....
      ....
      201812    .....
nz15  201501    .....
      201502    .....
      .....
      201812    .....

But I don't know if it is the right choice to find the saled trend in this case and if so How to change the first dataframe to be like the second I know some about pivot reshape. But I can't find how to do it and how to keep the id (the area in which the product is sold)

Any help please?

Answer 1

my intial dataframe :

     id  p1_201501  p1_201502  p1_201803  p2_201801  p2_201812
0   x12        125         12          6        500         14
1  nz15        250         16        600        423         32
2  qz15        350          4         20        223         32

my program:

import pandas as pd

df = pd.melt(df, id_vars=["id"], var_name="product", value_name="values").reset_index(drop=True)
df['date'] = df['product'].str.split('_',n = 1, expand=True)[1]
df['year'] = df['date'].astype(str).str[:-2]
df['month'] = df['date'].astype(str).str[4:]

#drop unused columns
df.drop(['product','date'],axis=1,inplace=True)

df = df[['id', 'year','month','values']]
df = df.sort_values(['id', 'year','month'], ascending=[True,True,True]).reset_index(drop=True)
df.set_index(['id','year','month'])

#just sum each annual total sales for each id
df = df.groupby(['id','year'])['values'].sum()

print(df)

final result:

id    year
nz15  2015     266
      2018    1055
qz15  2015     354
      2018     275
x12   2015     137
      2018     520