I have the following df:
sales2001 sales2002 sales2003 sales2004
200012 19.12 0.98
200101 19.1 0.98 2.3
200102 21 0.97 0.8
...
200112 19.12 0.99 2.4
200201 0.98 2.5
200202 0.97 0.8 1.2
I would like to shift the content in order to align it a timegap view, as follow:
sales+1y sales+2y
200012 19.12 0.98
200101 0.98 2.3
200102 0.97 0.8
...
200112 0.99 2.4
200201 0.98 2.5
200202 0.8 1.2
basically aligning the forecasted data points to a fixed timegap to the index. I tried with iterrows and dynamically calling the columns given the index but cannot make it work. do you guys have any suggestion?
Another option is to use pd.wide_to_long and pivot:
# here I assume the index name is index
new_df = pd.wide_to_long(df.reset_index(), 'sales', i='index', j='sale_end').reset_index()
# if index is datetime, then use dt.year
new_df['periods'] = new_df['sale_end'] - new_df['index']//100
# pivot
new_df.dropna().pivot(index='index',columns='periods', values='sales')
output:
periods -1 0 1 2
idx
200012 NaN NaN 19.12 0.98
200101 NaN 19.10 0.98 2.30
200102 NaN 21.00 0.97 0.80
200112 NaN 19.12 0.99 2.40
200201 0.98 2.50 NaN NaN
200202 0.97 0.80 1.20 NaN