Python pandas "shift" to append rows beyond end of dataframe?

Question

I'm new to pandas and was wondering if there was a way to automatically append rows when using shift. So let's say I have a dataframe with 100 rows and three columns: a, b, and c. 'c' is an average of 'a' and 'b' shifted 30 rows. The following line of code works well (note 'self' represents the dataframe and is used because I have my own class inherited from DataFrame):

self['c'] = ((self['a'] + self['b']) / 2).shift(30)

The above seems to work well but it stops at row 100 (the end of the dataframe). I need it to keep going to a row 130 (100 + the shifted 30). Now I suppose I could have a for loop to append 30 rows to the dataframe before running the above, but as I learn pandas I am finding it does a lot of amazing stuff. So I thought I would check to see if there was a more elegant way.

Answer 1

You need to reindex your dataframe first to extend its range, else pandas won't know what to do with the extra rows. I assumed here that the index is a range, but you can easily adapt for other cases.

Here is a generic example:

df = pd.DataFrame({'col1': list('ABCD'),
                   'col2': range(4)})
df = df.reindex(range(len(df)+3))
df['col2'] = df['col2'].shift(3)

Input:

  col1  col2
0    A     0
1    B     1
2    C     2
3    D     3

Output:

  col1  col2
0    A   NaN
1    B   NaN
2    C   NaN
3    D   0.0
4  NaN   1.0
5  NaN   2.0
6  NaN   3.0

Here is another example with a non range index:

df = pd.DataFrame({'col1': list('ABCD'),
                   'col2': range(4)}, 
                  index=list('wxyz'))
df = df.reindex(list(df.index)+['new_%d'%i for i in range(3)])
df['col2'] = df['col2'].shift(3)

Output:

      col1  col2
w        A   NaN
x        B   NaN
y        C   NaN
z        D   0.0
new_0  NaN   1.0
new_1  NaN   2.0
new_2  NaN   3.0

NB. There are several other ways to initially extend the dataframe (e.g., append, concat). Note that all options (including reindex) will create a new object.