I have a long timeseries with a weekly index spanning several years and a measurement, 'weight', and maybe some other, forgettable columns.
I'd like to reshape this into a dataframe with week (1..52) as the index, columns named 2017, 2018, 2019, and the value of those cells = 'weight'.
As a concrete example:
dates = pd.date_range('1/1/2017', periods=150, freq='W')
df = pd.DataFrame(index = dates)
df['weight'] = (df.index.year*100.1) + df.index.week *4
df['other'] = "ignore"
df['week'] = df.index.week
df['year'] = df.index.year
I thought I should be able to do something with pivot() or maybe melt() but I've failed so far. EG:
df.pivot(values="weight", index="week", columns='year') just throws a casade of index errors. Suspect I need to do a few layers of stack/unstack melt/pivot perhaps, but not sure. Didn't see an equivalent example in https://pandas.pydata.org/pandas-docs/stable/user_guide/reshaping.html
