|Practice| Geo | Country | Details | Jan | Feb | March| 1Q20| April | May | June | 2Q20 |Year| id|
|Cont |America|Chile |Cost |20 |30 |40 |90 |50 |60 |70 |180 |2020| 1 |
|Des |America|Chile |Cost |21 |31 |41 |93 |51 |61 |71 |183 |2020| 1 |
|MJX |America|Chile |Cost |22 |32 |42 |96 |52 |62 |72 |182 |2020| 1 |
|PM |America|Chile |Cost |23 |33 |43 |99 |53 |63 |73 |189 |2020| 1 |
|Vid |America|Chile |Cost |24 |34 |44 |102 |54 |64 |74 |192 |2020| 1 |
Above is the source table and I the output to be with columns as: Practice |Geo |Country |Details | Month| Month amount | Quarter | Quarter_amount |id |
My approach was to do double melt and then do concat but during concat I am not getting desired result and getting too many NAN in final table. Desired output screenshot is as under
snippet from my code is as under:
df1 = subdata2.melt(id_vars=['Practice', 'Geo', 'Country', 'Details','Year', 'id'],
value_vars=['Jan', 'Feb', 'Mar', '1Q20', 'Apr', 'May', 'Jun'],
var_name='Month', value_name='Month_amount')
df2= subdata2.melt(id_vars=['Practice', 'Geo', 'Country', 'Details','Year', 'id'],
value_vars=['1Q20','2Q20'],
var_name='Quarter', value_name='Quarter_amount')
print(subdata2)
df1 = df1.set_index(['Practice', 'Geo', 'Country', 'Details','Year', 'id', df1.groupby(['Practice', 'Geo', 'Country', 'Details','Year', 'id']).cumcount()])
df2 = df2.set_index(['Practice', 'Geo', 'Country', 'Details','Year', 'id', df2.groupby(['Practice', 'Geo', 'Country', 'Details','Year', 'id']).cumcount()])
print(df1)
print(df2)
df3 = (pd.concat([df1, df2],axis=1)
.sort_index(level=5)
.reset_index(level=5, drop=False)
.reset_index())
print("df3 \n",df3)
