calculating values in python using formula for a dataframe

I have an excel sheet with the data below, here we need to calculate 2 columns based on other column values but I am getting NaN values

import pandas as pd

# Define the initial data
data = {'statMonthName': ['Mar', 'Mar', 'Mar', 'Mar', 'Apr', 'Apr', 'Apr', 'Apr', 'Apr'],
        'statWeek': [1, 2, 3, 4, 5, 6, 7, 8, 9],
        'p_organic': [3646049.56, 3867696.284, 4051128.056, 4095508.5, 2778538.164, 2789640.51, 2736064.373, 3105200.772, 3112694.166],
        'f_organic': [2289567, None, None, None, None, None, None, None, None],
        'pct_diff': [None, None, None, None, None, None, None, None, None]}

# Create a Pandas dataframe to store the data
df = pd.DataFrame(data)

# Print the dataframe
print(df)

in excel I am using the formula

f_organic = D2 =C2*(1+E1)
which means f_organic of x week  = p_organic of x week * (1+ pct_diff of (x-1) week)

and pct_diff is given as

pct_diff = =D2/C2-1
pct_diff = f_organic row2/ p_organic row 2 - 1

when I am trying to achieve this via python, I am failing and getting only null values

my code is below

# fill null values in f_organic and pct_diff columns

for i in range(1, len(df)):
    df.at[i, 'pct_diff'] = df.at[i, 'f_organic'] / df.at[i, 'p_organic'] - 1
    df.at[i, 'f_organic'] = df.at[i, 'p_organic'] * (1 + df.at[i-1, 'pct_diff'])
    

# print final dataframe
df

any help or suggestion is appreciated

Your DataFrame:

  statMonthName  statWeek    p_organic  f_organic pct_diff
0           Mar         1  3646049.560  2289567.0     None
1           Mar         2  3867696.284        NaN     None
2           Mar         3  4051128.056        NaN     None
3           Mar         4  4095508.500        NaN     None
4           Apr         5  2778538.164        NaN     None
5           Apr         6  2789640.510        NaN     None
6           Apr         7  2736064.373        NaN     None
7           Apr         8  3105200.772        NaN     None
8           Apr         9  3112694.166        NaN     None

Your formula (in array notation):

f_organic[i] = p_organic[i] * (1 + pct_diff[i-1])

pct_diff[i]  = (f_organic[i] / p_organic[i]) - 1

Correctly filling in the missing values

From your code that tries to fill in the missing values, the problem lies not in the formula, but in the way the for loop was constructed.

Since it starts with index i=1 (the second row), it misses the value of pct_diff[0] (the first row). So all the calculations will involve null values, resulting only in NaNs.

A solution would be to first add a line outside the loop that fills in pct_diff[0], and then reorder the code in the loop to reflect this:

# Fill in the missing value of pct_diff on the first row.
df.at[0, 'pct_diff'] = df.at[0, 'f_organic'] / df.at[0, 'p_organic'] - 1

# Fill in the remaining values of f_organic and pct_diff.
for i in range(1, len(df)):

    # f_organic[i] = p_organic[i] * (1 + pct_diff[i-1])
    df.at[i, 'f_organic'] = df.at[i, 'p_organic'] * (1 + df.at[i-1, 'pct_diff'])
    
    # pct_diff[i] = (f_organic[i] / p_organic[i]) - 1
    df.at[i, 'pct_diff'] = df.at[i, 'f_organic'] / df.at[i, 'p_organic'] - 1


print(df)

Output:

  statMonthName  statWeek    p_organic     f_organic  pct_diff
0           Mar         1  3646049.560  2.289567e+06 -0.372042
1           Mar         2  3867696.284  2.428752e+06 -0.372042
2           Mar         3  4051128.056  2.543939e+06 -0.372042
3           Mar         4  4095508.500  2.571808e+06 -0.372042
4           Apr         5  2778538.164  1.744806e+06 -0.372042
5           Apr         6  2789640.510  1.751778e+06 -0.372042
6           Apr         7  2736064.373  1.718134e+06 -0.372042
7           Apr         8  3105200.772  1.949937e+06 -0.372042
8           Apr         9  3112694.166  1.954642e+06 -0.372042