Calculate DataFrame values recursively

Question

I'm trying to calculate the column values of a pandas data frame "recursively".

Suppose there are data for two different days each having 10 observations and you want to calculate some variable r where only the first value of r is given (on each day) and you want to calculate the remaining 2*9 entries while every subsequent value depends on the previous entry of r and one additional 'contemporaneous' variable 'x'.

The first problem is that I want to perform the calculations for each day individually i.e. I'd like to use the pandas.groupby() function for all my calculations... but when I try to subset the data and use the shift(1) function, I only get "NaN" entries

data.groupby(data.index)['r'] =   ( (1+data.groupby(data.index)['x']*0.25) * (1+data.groupby(data.index)['r'].shift(1)))

For my second approach, I used a for loop to iterate through the index (dates):

for i in range(2,21):
    data[data['rank'] == i]['r'] =  ( (1+data[data['rank'] == i]['x']*0.25) * (1+data[data['rank'] == i]['r'].shift(1))

but still, that doesn't work for me. Is there a way to perform such a calculation on DataFrames? Maybe something like rolling apply?

Data:

df = pd.DataFrame({
  'rank' : [1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10],
  'x' : [0.00275,0.00285,0.0031,0.0036,0.0043,0.0052,0.0063,0.00755,0.00895,0.0105,0.0027,0.00285,0.0031,0.00355,0.00425,0.0051,0.00615,0.00735,0.00875,0.0103],
  'r' : [0.00158,'NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN',0.001485,'NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN','NaN']
  },index=['2014-01-02', '2014-01-02', '2014-01-02', '2014-01-02',
           '2014-01-02', '2014-01-02', '2014-01-02', '2014-01-02',
           '2014-01-02', '2014-01-02', '2014-01-03', '2014-01-03',
           '2014-01-03', '2014-01-03', '2014-01-03', '2014-01-03',
           '2014-01-03', '2014-01-03', '2014-01-03', '2014-01-03'])

There have been a number of questions about this, but there is currently no quick way to do these kinds of recurrence-relation computations in pandas. You have to loop. There is [an open issue](https://github.com/pandas-dev/pandas/issues/4567) about it. — BrenBarn, Apr 30 '17 at 19:17

Stephen Rauch · Accepted Answer · 2017-05-01T19:43:26.060

To do your rolling apply, you can use pandas.groupby().apply(). Inside the apply you can use a loop to do the calculations per group. The inner loop could also potentially be done with scipy.lfilter, but I couldn't understand the exact formula you are after, so I just winged that part.

Code:

def rolling_apply(group):
    r = [group.r.iloc[0]]
    for x in group.x:
        r.append((1 + r[-1]) * (1 + x * 0.25))
    group.r = r[1:]
    return group

df['R'] = df.groupby(df.index).apply(rolling_apply).r

Results:

                   r  rank        x          R
2014-01-02   0.00158     1  0.00275   1.002269
2014-01-02       NaN     2  0.00285   2.003695
2014-01-02       NaN     3  0.00310   3.006023
2014-01-02       NaN     4  0.00360   4.009628
2014-01-02       NaN     5  0.00430   5.015014
2014-01-02       NaN     6  0.00520   6.022833
2014-01-02       NaN     7  0.00630   7.033894
2014-01-02       NaN     8  0.00755   8.049058
2014-01-02       NaN     9  0.00895   9.069306
2014-01-02       NaN    10  0.01050  10.095737
2014-01-03  0.001485     1  0.00270   1.002161
2014-01-03       NaN     2  0.00285   2.003588
2014-01-03       NaN     3  0.00310   3.005915
2014-01-03       NaN     4  0.00355   4.009471
2014-01-03       NaN     5  0.00425   5.014793
2014-01-03       NaN     6  0.00510   6.022462
2014-01-03       NaN     7  0.00615   7.033259
2014-01-03       NaN     8  0.00735   8.048020
2014-01-03       NaN     9  0.00875   9.067813
2014-01-03       NaN    10  0.01030  10.093737

Test Data:

df = pd.DataFrame({
    'rank': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    'x': [0.00275, 0.00285, 0.0031, 0.0036, 0.0043, 0.0052, 0.0063, 0.00755,
          0.00895, 0.0105, 0.0027, 0.00285, 0.0031, 0.00355, 0.00425,
          0.0051, 0.00615, 0.00735, 0.00875, 0.0103],
    'r': [0.00158, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN',
          'NaN', 0.001485, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN',
          'NaN', 'NaN']
}, index=['2014-01-02', '2014-01-02', '2014-01-02', '2014-01-02',
          '2014-01-02', '2014-01-02', '2014-01-02', '2014-01-02',
          '2014-01-02', '2014-01-02', '2014-01-03', '2014-01-03',
          '2014-01-03', '2014-01-03', '2014-01-03', '2014-01-03',
          '2014-01-03', '2014-01-03', '2014-01-03', '2014-01-03'])

Update:

Now that the actual recursive equation desired is known, here is an update for the apply function:

def rolling_apply(group):
    r = [group.r.iloc[0]]
    for x in group.x[:-1]:
        r.append((1 + r[-1]) * (1 + x * 0.25) - 1)
    group.r = r
    return group

df.r = df.groupby(df.index).apply(rolling_apply).r

Would it also be possible do this as a lambda function and apply? I'm just curious. — Moondra, May 01 '17 at 17:01
@moondra, the OP's calculation is a bit involved, and thus even if expressible in a lambda, I would suggest it would be poor coding style. — Stephen Rauch, May 01 '17 at 17:24
How does this compare to a for loop in terms of execution time? — SuperCodeBrah, Mar 15 '21 at 18:26
@SuperCodeBrah. Not exactly sure what you are asking, but .apply is essentially a Python for loop at its core. — Stephen Rauch, Mar 15 '21 at 20:21
Okay, thanks for clarifying. I figured that was the case, but just checking since numpy uses C under the hood from what I understand. I guess it's a little more elegant to use apply with a single function (rather than a `for` loop), but I was hoping there might be a speed benefit as well. — SuperCodeBrah, Mar 15 '21 at 22:23

score 0 · Answer 2 · answered May 01 '17 at 16:21

Stephen Rauch's answer was very helpful. Since I was looking for a column "r" where only the consecutive values of each day are calculated while the initial values (0.00158, 0.001485) remain unchanged, I will post the final solution additionally (just in case someone has a similar problem). In Stephen Rauch's solution, the value of R[0] belongs to r[1] etc. therefore one has to shift the data for all "ranks" with the exception of 1.

Test Data

df = pd.DataFrame({
'rank': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
'x': [0.00275, 0.00285, 0.0031, 0.0036, 0.0043, 0.0052, 0.0063, 0.00755,
      0.00895, 0.0105, 0.0027, 0.00285, 0.0031, 0.00355, 0.00425,
      0.0051, 0.00615, 0.00735, 0.00875, 0.0103],
'r': [0.00158, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN',
      'NaN', 0.001485, 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN',
      'NaN', 'NaN'] }, index=['2014-01-02', '2014-01-02', '2014-01-02', '2014-01-02',
      '2014-01-02', '2014-01-02', '2014-01-02', '2014-01-02',
      '2014-01-02', '2014-01-02', '2014-01-03', '2014-01-03',
      '2014-01-03', '2014-01-03', '2014-01-03', '2014-01-03',
      '2014-01-03', '2014-01-03', '2014-01-03', '2014-01-03'])

Code

 def rolling_apply(group):
     r = [group.r.iloc[0]]
     for x in group.x:
         r.append((1 + r[-1]) * (1 + x * 0.25) -1)
     group.r = r[1:]
     return group

df['R'] = df.groupby(df.index).apply(rolling_apply).r

df['r'] = np.where(df['rank']==1,df['r'],df['R'].shift(1) )

df = df.drop('R',1)

Result

                     r  rank        x
2014-01-02     0.00158     1  0.00275
2014-01-02  0.00226859     2  0.00285
2014-01-02   0.0029827     3  0.00310
2014-01-02  0.00376001     4  0.00360
2014-01-02   0.0046634     5  0.00430
2014-01-02  0.00574341     6  0.00520
2014-01-02  0.00705088     7  0.00630
2014-01-02  0.00863698     8  0.00755
2014-01-02   0.0105408     9  0.00895
2014-01-02   0.0128019    10  0.01050
2014-01-03    0.001485     1  0.00270
2014-01-03    0.002161     2  0.00285
2014-01-03  0.00287504     3  0.00310
2014-01-03  0.00365227     4  0.00355
2014-01-03  0.00454301     5  0.00425
2014-01-03  0.00561034     6  0.00510
2014-01-03  0.00689249     7  0.00615
2014-01-03  0.00844059     8  0.00735
2014-01-03   0.0102936     9  0.00875
2014-01-03   0.0125036    10  0.01030

Calculate DataFrame values recursively

2 Answers2

Linked