I have the following table:

The Value columns correspond to Years 2021-2019. I want to duplicate each value for every month of the corresponding year. In other words, I want to restructure it into a DataFrame like this:

CodePudding user response：

Let's try with melt assign and explode "Month":

out = (pd.melt(df.rename(columns={'Value1':2021, 'Value2':2020, 'Value3':2019}), 
               id_vars=['Index','Name'], value_vars=[2021, 2020, 2019], 
               var_name='Year', value_name='Value')
       .assign(Month=pd.Series([[*range(1,13)]])).ffill()
       .explode('Month')
       [['Index','Name','Month','Year','Value']]
       .sort_values(by=['Index','Name','Year'], ascending=[True, True, False])
       .reset_index(drop=True)
      )

Output:

     Index Name Month  Year  Value
0        1  abc     1  2021    136
1        1  abc     2  2021    136
2        1  abc     3  2021    136
3        1  abc     4  2021    136
4        1  abc     5  2021    136
..     ...  ...   ...   ...    ...
139      4  mno     8  2019    165
140      4  mno     9  2019    165
141      4  mno    10  2019    165
142      4  mno    11  2019    165
143      4  mno    12  2019    165

[144 rows x 5 columns]