lundi 18 décembre 2017

Pandas - lambda function with conditional based on row index

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