CODEWITHSUNDEEP
pythonpandas
W.R
W.R

Reputation: 187

Apply function to each n rows pandas

I have a pandas df col which looks like the following:

0           0.286
1           0.240
2           0.335
3           0.397
2430       38.580
2431       38.650
2432       38.630
2433       38.170
6007       72.960
6008       71.250
6009       70.370
6010       70.460 ...

I would like to output a new_col with the % change from the initial value, resetting every fourth value, then a final 4 line output which takes the average of every fourth value in the new_col.

Expected output new_col:

0.00
-16.08
17.13
38.81
0.00
0.18
0.13
-1.06
0.00
-2.34
-3.55
-3.43

avg_col

0.00
-6.08
4.57
11.44

Upvotes: 1

Views: 1883

Answers (1)

ALollz
ALollz

Reputation: 59549

You can get the new_col by grouping every 4 lines:

df['new_col'] = df.groupby(df.index//4)[1].apply(lambda x: (x-x.iloc[0])/x.iloc[0]*100).reset_index(0, drop=True)

Or to avoid the .groupby.apply perhaps transform and then do the calculation (might be faster for large Frames)

df['new_col'] = df.groupby(df.index//4)[1].transform('first')
df['new_col'] = (df[1] - df.new_col)/df.new_col*100

Output df:

       0       1    new_col
0      0   0.286   0.000000
1      1   0.240 -16.083916
2      2   0.335  17.132867
3      3   0.397  38.811189
4   2430  38.580   0.000000
5   2431  38.650   0.181441
6   2432  38.630   0.129601
7   2433  38.170  -1.062727
8   6007  72.960   0.000000
9   6008  71.250  -2.343750
10  6009  70.370  -3.549890
11  6010  70.460  -3.426535

Get the average by grouping by the division remainder:

df.groupby(df.index%4).new_col.mean()

0     0.000000
1    -6.082075
2     4.570859
3    11.440642
Name: new_col, dtype: float64

Upvotes: 2

Related Questions