CODEWITHSUNDEEP
pandasdataframe
Alex
Alex

Reputation: 1131

pandas, only calculate the last n rows

I am looking for a solution to calculate the result for the last n rows, like the result in the last row.

import pandas as pd
d = {'col1': [20, 23, 40, 41, 46, 47, 48, 49, 50, 50, 52, 55, 56, 69, 70]}
df = pd.DataFrame(data=d)


df["calc"] = ""  #create new column

# my solution to calculate the value for the last 3 rows
x = df.iloc[-1, df.columns.get_loc('col1')] / df.iloc[-2, df.columns.get_loc('col1')] - 1
df.iloc[-1, df.columns.get_loc('calc')] = x

x = df.iloc[-2, df.columns.get_loc('col1')] / df.iloc[-3, df.columns.get_loc('col1')] - 1
df.iloc[-2, df.columns.get_loc('calc')] = x

x = df.iloc[-3, df.columns.get_loc('col1')] / df.iloc[-4, df.columns.get_loc('col1')] - 1
df.iloc[-3, df.columns.get_loc('calc')] = x

I obtain a more elegant solution, than copy the code and change the shifts. Thanks!

Upvotes: 1

Views: 290

Answers (1)

Michael Szczesny
Michael Szczesny

Reputation: 5036

To calculate the last N rows

N = 3
df['calc1'] = df.col1.iloc[-N:] / df.col1.iloc[-(N+1):].shift() - 1
df

Out:

    col1       calc     calc1
0     20                  NaN
1     23                  NaN
2     40                  NaN
3     41                  NaN
4     46                  NaN
5     47                  NaN
6     48                  NaN
7     49                  NaN
8     50                  NaN
9     50                  NaN
10    52                  NaN
11    55                  NaN
12    56  0.0181818  0.018182
13    69   0.232143  0.232143
14    70  0.0144928  0.014493

Upvotes: 2

Related Questions