Pandas - lambda function with conditional based on row index

Question

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?

Edit: thank you all for your answers. Alexander's answer is on the right track, but I've noticed that the results strayed somewhat from what was to be expected. These differences became more pronounced the larger the dataframe used.

Alexander - can you address this issue with an edit to your answer? Using vanilla Python, I've arrived at the results below. The differences are largely trivial, but as stated, can get more pronounced with larger datasets.

total=(869,868,871,873,873,874,870,871,868)
rate=(11.047181,5.529954,8.266361,6.872852,8.247423,9.610984,5.517241,8.266361,2.764977)
def f(total,rate):
     cum = []
     for i in range(len(total)):
         if i == 0:
             cum.append(total[i])
         else:
             cum.append(float(cum[i-1])-(float(total[i-1])*(rate[i-1]/1200.0)))
     return cum
f(total, rate)

Returns:

869
860.9999997591667
856.9999996991667
850.99999934
845.9999995100001
839.9999992775
832.9999992641667
828.9999995391668
822.9999991800001

Alexander · Accepted Answer · 2017-12-19 16:25:16Z

3

Perhaps this?

df = df.assign(
    Cumulative_Total=df['Total'].iat[0] 
                     - ((df['Total'] * df['Annualized Rate'].div(1200))
                        .shift()
                        .fillna(0)
                        .cumsum())
)

>>> df
   Total  Annualized Rate  Cumulative_Total
0    869        11.047181               869
1    868         5.529954               861
2    871         8.266361               857
3    873         6.872852               851
4    873         8.247423               846
5    874         9.610984               840
6    870         5.517241               833
7    871         8.266361               829
8    868         2.764977               823

edited Dec 19, 2017 at 16:25

answered Dec 19, 2017 at 1:18

Alexander

111k32 gold badges212 silver badges208 bronze badges

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

7 Comments

baobobs Over a year ago

Both before and after your edit, I'm getting the same error - IndexError: index 0 is out of bounds for axis 0 with size 0. Does axis=1 need to be added? If so, where?

Alexander Over a year ago

df['Total'].iat[0] just accesses the first element of the Total series. Are you sure the data exists? Try df.shape

baobobs Over a year ago

Just noticed - my column actually contains a space - df['Cumulative Total']

Alexander Over a year ago

you could append with .rename(columns={'Cumulative_Total': 'Cumulative Total'})

baobobs Over a year ago

Hey, it worked! Looks like some of the initial results had empty results. Per your suggestion, I included the statement if df.shape[0] > 0: prior to your code. Thanks!

|

Julien Spronck · Accepted Answer · 2017-12-19 00:40:31Z

0

Would this work? In this solution, I used x.name to get the row index.

df['Cumulative Total'] = df.apply(lambda x: x['Total'] if x.name == 0 else x.shift()['Cumulative Total']-(x.shift()['Total']*(x.shift()['Annualized Rate']/1200)),axis=1)

answered Dec 19, 2017 at 0:40

Julien Spronck

15.5k5 gold badges50 silver badges57 bronze badges

1 Comment

baobobs Over a year ago

I'm getting an error ValueError: Cannot set a frame with no defined index and a value that cannot be converted to a Series. I'll have to dig into this..

Collectives™ on Stack Overflow

Pandas - lambda function with conditional based on row index

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related