Calculating pandas column until the end

Question

import pandas
pandas.set_option('display.max_columns', None)
pandas.set_option('display.max_rows', None)
pandas.set_option('display.width', None)
df1 = pandas.read_json("""{"A":{"1062374400000":0.004270709,"1064966400000":0.010686493,"1067644800000":0.006375888,"1070236800000":0.006175395,"1072915200000":0.007879588,"1075593600000":0.01078674,"1078099200000":0.006375888,"1080777600000":0.008180328,"1083369600000":0.006576382,"1086048000000":0.006476135},"B":{"1062374400000":0.0590058044,"1064966400000":0.4393213977,"1067644800000":0.4708762019,"1070236800000":0.6544813354,"1072915200000":1.154254136,"1075593600000":null,"1078099200000":null,"1080777600000":null,"1083369600000":null,"1086048000000":null},"C":{"1062374400000":-0.0309941956,"1064966400000":0.1993213977,"1067644800000":0.0508762019,"1070236800000":0.2044813354,"1072915200000":0.514254136,"1075593600000":null,"1078099200000":null,"1080777600000":null,"1083369600000":null,"1086048000000":null},"D":{"1062374400000":null,"1064966400000":null,"1067644800000":null,"1070236800000":null,"1072915200000":null,"1075593600000":null,"1078099200000":null,"1080777600000":null,"1083369600000":null,"1086048000000":null},"E":{"1062374400000":null,"1064966400000":null,"1067644800000":null,"1070236800000":null,"1072915200000":null,"1075593600000":null,"1078099200000":null,"1080777600000":null,"1083369600000":null,"1086048000000":null}}
""")
print(df1)

I have the above algo, which translates into the following DF:

                   A         B         C   D   E
2003-09-01  0.004271  0.059006 -0.030994 NaN NaN
2003-10-01  0.010686  0.439321  0.199321 NaN NaN
2003-11-01  0.006376  0.470876  0.050876 NaN NaN
2003-12-01  0.006175  0.654481  0.204481 NaN NaN
2004-01-01  0.007880  1.154254  0.514254 NaN NaN
2004-02-01  0.010787       NaN       NaN NaN NaN
2004-03-01  0.006376       NaN       NaN NaN NaN
2004-04-01  0.008180       NaN       NaN NaN NaN
2004-05-01  0.006576       NaN       NaN NaN NaN
2004-06-01  0.006476       NaN       NaN NaN NaN

I'm trying to use the following math to calculate D and E values:

Dn = E(n-1) * An

En = E(n-1) + Cn + Dn or En = E(n-1) + Cn + (E(n-1) * An)

For the equation to work, the first value of E should be manually set. E[0] = C[0].

I'm using the following algorithm:

df1['E'].iloc[0] = df1['C'].iloc[0] # Giving E[0] it's first value (C[0])
for i in range(1, len(df1)):
    df1['D'].iloc[i] = df1['E'].iloc[i - 1] * df1['A'].iloc[i]
    df1['E'].iloc[i] = df1['E'].iloc[i - 1] + df1['C'].iloc[i] + df1['D'].iloc[i]

However, the result is calculating only few rows of D and E.

I thought it could be because somewhen there will be no C or D to calculate E, so I also tried this algo:

df1['E'].iloc[0] = df1['C'].iloc[0] # Giving E[0] it's first value (C[0])
for i in range(1, len(df1)):
    df1['D'].iloc[i] = df1['E'].iloc[i - 1] * df1['A'].iloc[i]
    if df1['C'].iloc[i] == numpy.nan:
        df1['C'].iloc[i] = 0
    if df1['D'].iloc[i] == numpy.nan:
        df1['D'].iloc[i] = 0
    df1['E'].iloc[i] = df1['E'].iloc[i - 1] + df1['C'].iloc[i] + df1['D'].iloc[i]

However, the result is still the same:

                   A         B         C         D         E
2003-09-01  0.004271  0.059006 -0.030994       NaN -0.030994
2003-10-01  0.010686  0.439321  0.199321 -0.000331  0.167996
2003-11-01  0.006376  0.470876  0.050876  0.001071  0.219943
2003-12-01  0.006175  0.654481  0.204481  0.001358  0.425783
2004-01-01  0.007880  1.154254  0.514254  0.003355  0.943392
2004-02-01  0.010787       NaN       NaN  0.010176       NaN
2004-03-01  0.006376       NaN       NaN       NaN       NaN
2004-04-01  0.008180       NaN       NaN       NaN       NaN
2004-05-01  0.006576       NaN       NaN       NaN       NaN
2004-06-01  0.006476       NaN       NaN       NaN       NaN

What do I need to make sure D and E are calculated untill the end of the DF?

Expected results (asked in comments):

                    A         B          C            D          E
2003-09-01 0,00427071 0,0590058 -0,0309942          NaN -0,0309942
2003-10-01  0,0106865  0,439321   0,199321 -0,000331219   0,167996
2003-11-01 0,00637589  0,470876  0,0508762   0,00107112   0,219943
2003-12-01  0,0061754  0,654481   0,204481   0,00135824   0,425783
2004-01-01 0,00787959   1,15425   0,514254   0,00335499   0,943392
2004-02-01  0,0107867       NaN          0    0,0101761   0,953568
2004-03-01 0,00637589       NaN          0   0,00607984   0,959648
2004-04-01 0,00818033       NaN          0   0,00785024   0,967498
2004-05-01 0,00657638       NaN          0   0,00636264   0,973861
2004-06-01 0,00647614       NaN          0   0,00630685   0,980168

user_10451126 · Answer

maybe slightly cleaner using fillna() to set all your NaN values to 0 so you don't have to worry about them in the loop. Should be a little bit faster

df1.fillna(0, inplace = True)
df1['E'].iloc[0] = df1['C'].iloc[0] # Giving E[0] it's first value (C[0])
for i in range(1, len(df1)):
    df1['D'].iloc[i] = df1['E'].iloc[i - 1] * df1['A'].iloc[i]
    df1['E'].iloc[i] = df1['E'].iloc[i - 1] + df1['C'].iloc[i] + df1['D'].iloc[i]

            A           B           C           D           E
2003-09-01  0.004271    0.059006    -0.030994   0.000000    -0.030994
2003-10-01  0.010686    0.439321    0.199321    -0.000331   0.167996
2003-11-01  0.006376    0.470876    0.050876    0.001071    0.219943
2003-12-01  0.006175    0.654481    0.204481    0.001358    0.425783
2004-01-01  0.007880    1.154254    0.514254    0.003355    0.943392
2004-02-01  0.010787    0.000000    0.000000    0.010176    0.953568
2004-03-01  0.006376    0.000000    0.000000    0.006080    0.959648
2004-04-01  0.008180    0.000000    0.000000    0.007850    0.967498
2004-05-01  0.006576    0.000000    0.000000    0.006363    0.973861
2004-06-01  0.006476    0.000000    0.000000    0.006307    0.980168

user_10343917 · Answer

I was using the following algorithm to make the calcules:

df1['E'].iloc[0] = df1['C'].iloc[0] # Giving E[0] it's first value (C[0])
for i in range(1, len(df1)):
    df1['D'].iloc[i] = df1['E'].iloc[i - 1] * df1['A'].iloc[i]
    if df1['C'].iloc[i] == numpy.nan:
        df1['C'].iloc[i] = 0
    if df1['D'].iloc[i] == numpy.nan:
        df1['D'].iloc[i] = 0
    df1['E'].iloc[i] = df1['E'].iloc[i - 1] + df1['C'].iloc[i] + df1['D'].iloc[i]

However, what apparently had to be done was:

df1['E'].iloc[0] = df1['C'].iloc[0]
for i in range(1, len(df1)):
    df1['D'].iloc[i] = df1['E'].iloc[i - 1] * df1['A'].iloc[i]
    if numpy.isnan(df1['C'].iloc[i]):
        df1['C'].iloc[i] = 0
    if numpy.isnan(df1['D'].iloc[i]):
        df1['D'].iloc[i] = 0
    df1['E'].iloc[i] = df1['E'].iloc[i - 1] + df1['C'].iloc[i] + df1['D'].iloc[i]

People let me know if someone has a better solution. For now, this is working.

Calculating pandas column until the end

2 Answers