I am trying to apply a lambda function to a dataframe by referencing three columns. I want to update one of the columns, Cumulative Total, based on the following logic:
If it's on the first row, then Cumulative Total should equal the value in Total. If it's not the first row, then apply the following formula that references the prior row:
x.shift()['Cumulative Total']-(x.shift()['Total']*(x.shift()['Annualized Rate']/1200))
I want the Cumulative Total column to look like so:
Total Annualized Rate Cumulative Total
869 11.04718067 869
868 5.529953917 861
871 8.266360505 857
873 6.872852234 851
873 8.24742268 846
874 9.610983982 840
870 5.517241379 833
871 8.266360505 829
868 2.764976959 823
What is throwing me off is how I can determine whether or not I'm on the first row. This sounds rather trivial, but I'm very new to Pandas and am totally stumped. iloc doesn't seem to work, as it seems to only be used for grabbing a row of a given index.
The code is currently as follows:
df['Cumulative Total'] = df.apply(lambda x: x['Total'] if x.iloc[0] else x.shift()['Cumulative Total']-(x.shift()['Total']*(x.shift()['Annualized Rate']/1200)),axis=1)
The statement if x.iloc[0] is wrong. Any idea on how I can determine if it's the first row?
Aucun commentaire:
Enregistrer un commentaire