Create multiple column pandas from single column and feed in values

Question

I have a dataframe that looks like this (df1):

I want to recreate the following dataframe(df2) to look like df1:

The number of years in df2 goes up to 2020.

So, essentially for each row in df2, a new row for each year should be created. Then, new columns should be created for each month. Finally, the value for % in each row should be copied to the column corresponding to the month in the "Month" column.

Any ideas? Many thanks.

Answer 1

This is pivot:

(df2.assign(Year=df2.Month.str[:4],
            Month=df2.Month.str[5:])
    .pivot(index='Year', columns='Month', values='%')
)

More details about pivoting a dataframe here.