Create next rows in dataframe based on values from previous

Question

My df looks like below

   id    number 
   123      1   
   256      2  
   879      3   
   132      4  
   3215     5   
   216      6  

Output should be like this:

   id    number 
   123      1   
   256      2  
   879      3   
   132      4  
   3215     5   
   216      6  
   NaN      7
   NaN      8
   NaN      9
   NaN      10

So basically I need add 1 into previous row in column number and in column id there shouldn't be any values. I need 30 new rows. I tried with this:

n = 30  
for i in range(n):
       df = df.append(df.tail(1).add(1))

but result was not correct. Do youhave any ideas? Thanks for help. Regards Tomasz

Answer 1

You can set_index, reindex and reset_index:

df.set_index('number').reindex(range(1, 11)).reset_index()

output:

   number      id
0       1   123.0
1       2   256.0
2       3   879.0
3       4   132.0
4       5  3215.0
5       6   216.0
6       7     NaN
7       8     NaN
8       9     NaN
9      10     NaN

If you want to keep the column order:

cols = df.columns
df.set_index('number').reindex(range(1, 11)).reset_index()[cols]

       id  number
0   123.0       1
1   256.0       2
2   879.0       3
3   132.0       4
4  3215.0       5
5   216.0       6
6     NaN       7
7     NaN       8
8     NaN       9
9     NaN      10

Answer 2

Try set_index and reindex:

>>> df.set_index('number').reindex(range(11)).reset_index()
    number      id
0        0     NaN
1        1   123.0
2        2   256.0
3        3   879.0
4        4   132.0
5        5  3215.0
6        6   216.0
7        7     NaN
8        8     NaN
9        9     NaN
10      10     NaN
>>> 

Answer 3

A merge is another efficient option, and maintains column order:

df.merge(pd.Series(range(1,11), name = 'number'),how = 'right')
 
       id  number
0   123.0       1
1   256.0       2
2   879.0       3
3   132.0       4
4  3215.0       5
5   216.0       6
6     NaN       7
7     NaN       8
8     NaN       9
9     NaN      10