Define recursive function in Pandas dataframe

Question

I can't seem to find the answer to my question so I'm trying my luck on here. Would very much appreciate your help.

I've got a Pandas dataframe with values in Col1 and Col2. Instead of the np.nan values in Col2, I'd like to calculate the following: today's Col2 value = previous day's Col2 value multiplied by today's Col1 value.

This should be some form of recursive function. I've tried several answers, including a for loop here below, but none seem to work:

df = pd.read_excel('/Users/fhggshgf/Desktop/test.xlsx')
df.index = df.date
df.drop(['date'], axis=1, inplace=True)

for i in range(1, len(df)):
    fill_value = df['Col2'].iloc[i - 1]
    finaldf['Col2'].fillna(fill_value, inplace=True)

screenshot

you says previous day's Col2 value multiplied by today's Col1 value, but your codes don't show any multiplication. It is really a multiplication? could you add the expected output? — Andy L.
– Andy L., Commented Nov 24, 2019 at 22:19
For future reference, it's helpful if you format the output of your dataframe (at least part of it) and include it in your question. See here for an explanation on formatting. — Stealing
– Stealing, Commented Nov 24, 2019 at 22:57

Matthew Borish · Accepted Answer · 2019-11-24 22:50:40Z

1

You could try something like this.

import pandas as pd
import numpy as np
df = pd.DataFrame({'date': [1,2,3,4,5,6],
               'col_1': [951, 909, 867, 844, 824, 826],
               'col_2': [179, 170, 164, 159, 153, 149]})

col_2_update_list = []

for i, row in df.iterrows():

    if i != 0:

        today_col_1 = df.at[i,'col_1']
        prev_day_col_2 = df.at[i-1,'col_2'] 

        new_col_2_val = prev_day_col_2 * today_col_1

        col_2_update_list.append(new_col_2_val)

    else:
        col_2_update_list.append(np.nan)


df['updated_col_2'] = col_2_update_list

answered Nov 24, 2019 at 22:50

Matthew Borish

3,1262 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Olivier44 Over a year ago

Thanks a tonne, much appreciated.

Matthew Borish Over a year ago

Glad to help @Olivier44. I'd really appreciate it if you could mark this as the accepted answer if it solved your problem. Thanks!

Stealing · Accepted Answer · 2019-11-25 02:10:49Z

1

This avoids the use of loops but you need to create 2 new columns:

import pandas as pd
import numpy as np
import sys

df = pd.DataFrame({'date': [1,2,3,4,5,6],
               'col_1': [951, 909, 867, 844, 824, 826],
               'col_2': [179, np.nan, 164, 159, np.nan, 149]})
print(df)

# Compare 2 columns
df['col_4'] = df['col_2'].fillna(method='ffill')*df['col_1']
df['col_3'] = df['col_2'].fillna(sys.maxsize)
df['col_2'] = df[['col_4','col_3']].min(axis=1).astype(int)

df = df.drop(['col_4', 'col_3'], axis = 1)
print(df)

answered Nov 25, 2019 at 2:10

Stealing

1871 silver badge13 bronze badges

Collectives™ on Stack Overflow

Define recursive function in Pandas dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related