This is my original dataframe:
| A | B | C | D | E | Year 2020_AA | Year 2020_AB | Year 2021_AA | Year 2021_AB |
|---|---|---|---|---|---|---|---|---|
| abc | abc | NaN | NaN | abc | 0 | 0 | 0 | 0 |
I want to transform the dataframe into:
| A | B | C | D | E | Year | AA | AB |
|---|---|---|---|---|---|---|---|
| abc | abc | NaN | NaN | abc | 2020 | 0 | 0 |
| abc | abc | NaN | NaN | abc | 2021 | 0 | 0 |
I tried using following lines of code – df.melt converts the data into a below structure but pivot_table does not work and shows empty dataframe:
# Melt the DataFrame
df_melt = df.melt(id_vars=['A', 'B', 'C', 'D', 'E'], var_name='Type', value_name='Value')
# Split the 'Type' column into 'Delivery Year' and 'Type'
df_melt[['Delivery Year', 'Type']] = df_melt['Type'].str.rsplit('_', n=1, expand=True)
# Pivot the DataFrame
pivot_df = df_melt.pivot_table(index=['A', 'B', 'C', 'D', 'E', 'Delivery Year'], columns='Type', values='Value', fill_value=0).reset_index()
| A | B | C | D | E | Type | Value | Delivery Year |
|---|---|---|---|---|---|---|---|
| abc | abc | NaN | NaN | abc | AA | 0 | Year 2020 |
| abc | abc | NaN | NaN | abc | AB | 0 | Year 2020 |
| abc | abc | NaN | NaN | abc | AA | 0 | Year 2021 |
| abc | abc | NaN | NaN | abc | AB | 0 | Year 2021 |
Any guidance is most appreciated.