Pandas date range offset table

Question

Suppose I have a DataFrame with a single column indexed by date like so

             Quantity
Date                   
2019-01-31   442.000000
2019-02-28   292.416340
2019-03-31  1012.330000
2019-04-30   101.409200
2019-05-31   707.260364
2019-06-30  1326.754243
2019-07-31  1939.685200
2019-08-31   804.825600
2019-09-30  1356.020333
2019-10-31   917.221287
2019-11-30   132.204600
2019-12-31    32.911980

I want to convert this into a multi-column dataframe where each column represents the value at an offset from the index date. In the case of a maximum of 2 offsets, the result should look something like this

             0            1            2
Date                   
2019-01-31   442.000000   292.416340  1012.330000
2019-02-28   292.416340  1012.330000   101.409200
2019-03-31  1012.330000   101.409200   707.260364
.
.
.
2019-11-30   132.204600    32.911980          NaN
2019-12-31    32.911980          NaN          NaN

I am looking for a way to do this compactly with pandas, avoiding loops if possible

Does this answer your question? [Shift column in pandas dataframe up by one?](https://stackoverflow.com/questions/20095673/shift-column-in-pandas-dataframe-up-by-one) — Ben.T, May 08 '20 at 17:24
or maybe more [this one](https://stackoverflow.com/questions/48818213/make-multiple-shifted-lagged-columns-in-pandas) — Ben.T, May 08 '20 at 17:30
Can you be more specific about what the issue is? Please provide a [mcve], and see [ask], [help/on-topic]. — AMC, May 08 '20 at 17:50

score 1 · Accepted Answer · answered May 08 '20 at 17:26

You can do assignment:

ret = pd.DataFrame()
for i in range(3):
    ret[i] = df['Quantity'].shift(-i)

Output:

                      0            1            2
Date                                             
2019-01-31   442.000000   292.416340  1012.330000
2019-02-28   292.416340  1012.330000   101.409200
2019-03-31  1012.330000   101.409200   707.260364
2019-04-30   101.409200   707.260364  1326.754243
2019-05-31   707.260364  1326.754243  1939.685200
2019-06-30  1326.754243  1939.685200   804.825600
2019-07-31  1939.685200   804.825600  1356.020333
2019-08-31   804.825600  1356.020333   917.221287
2019-09-30  1356.020333   917.221287   132.204600
2019-10-31   917.221287   132.204600    32.911980
2019-11-30   132.204600    32.911980          NaN
2019-12-31    32.911980          NaN          NaN

Pandas date range offset table

1 Answers1