Pivot a pandas df in a specific way-CodePudding

I have the following dataset and I would like to make some major adjustments (pivot the data in a certain way). (The day goes all the way to December just didn't include it for convenience reasons)

Input:

Series ID       View Description          Jan-10   Feb-10   Mar-10   Apr-10 
Food            12-Month Percent Change   219.98   210.98   205      202  
Drinks          Original Data Value       215.46   205.04   206      203   
Food at Home    Original Data Value       202.88   203      207      199.98

Desired output:

Series ID     View Description          Month    Year   Value
Food          12-Month Percent Change   January  2010   219.98
Food          12-Month Percent Change   February 2010   210.98
Food          12-Month Percent Change   March    2010   205
Food          12-Month Percent Change   April    2010   202
Drinks        Original Data Value       January  2010   215.45
Drinks        Original Data Value       February 2010   205.04
Drinks        Original Data Value       March    2010   206
Drinks        Original Data Value       April    2010   203
Food at Home  Original Data Value       January  2010   202.88
Food at Home  Original Data Value       February 2010   203
Food at Home  Original Data Value       March    2010   207
Food at Home  Original Data Value       April    2010   199.98

Note: In reality the data includes all 12-months and also the Year column should be able to take any values. Any recommendations are be much appreciated!

CodePudding user response：

here is one way to do it. use melt to stack the DF, sort and then split the date into year and month



df2=df.melt(id_vars=['Series ID','View Description' ],
            var_name='date', 
            value_name='value'
           ).sort_values(['View Description','Series ID'])

df2['year']=pd.to_datetime(df2['date'],format='%b-%y').dt.year
df2['month']=pd.to_datetime(df2['date'],format='%b-%y').dt.month_name()
df2.drop(columns='date', inplace=True)
df2

    Series ID       View Description             value  year    month
0   Food            12-Month Percent Change     219.98  2010    January
3   Food            12-Month Percent Change     210.98  2010    February
6   Food            12-Month Percent Change     205.00  2010    March
9   Food            12-Month Percent Change     202.00  2010    April
1   Drinks          Original Data Value         215.46  2010    January
4   Drinks          Original Data Value         205.04  2010    February
7   Drinks          Original Data Value         206.00  2010    March
10  Drinks          Original Data Value         203.00  2010    April
2   Food at Home    Original Data Value         202.88  2010    January
5   Food at Home    Original Data Value         203.00  2010    February
8   Food at Home    Original Data Value         207.00  2010    March
11  Food at Home    Original Data Value         199.98  2010    April