I have a df with the following precipitation data:
Year Month Model Day 1 Day 2 Day 3 Day 4 Day 5 Day 7 ... Day 31 0 2006 1 CNRM 0.00 0.00 0.00 0.00 0.00 0.00 ... 0.00 1 2006 2 CNRM 0.00 5.47 0.00 0.00 0.00 0.01 ... 5.34 2 2006 3 CNRM 0.00 0.00 0.00 0.00 0.00 0.00 ... 1.88 3 2006 4 CNRM 0.00 0.00 0.00 0.00 0.00 0.01 ... 1.12 4 2006 5 CNRM 1.56 0.00 0.00 0.00 0.00 0.00 ... 6.37
What I need is to move the 31 'Day' columns to an unic 'Day' column and later combine the three date columns (Year, Month and Day) in a single 'Date' column.
I wanted to try 'melt' and 'pivot':
x = data.melt(['Year','Month','Model'])
x = x.pivot(index = ['Year','Month'],columns = ['Model'],values = 'Precipitation').reset_index()
but I get an error when pivoting because the index is duplicated.
What I don't know is how can I extend the length of the df to get a daily date column and move each day column value to the correspondent place in row.