My data is related to "Cricket", A sports which is popular in India. It has 20 overs for each inning max and each over has approx 6 balls (can vary).
dataframe has these cols,
season match_id inning batting_team bowling_team total_runs_per_ball sum_total_runs sum_total_wickets over/ball runs_last_5 wickets_last_5
sum_total_runs is a cumsum() result of total_runs_per_ball.
sum_total_wickets is also cumsum() result of another query.
runs_last_5 and wickets_last_5 are the newly created columns with 0 as preset value. I want to update both of these by determining their sum in last 5 overs.
First of all, my data looks like this (not showing some less important columns),
Lots of columns to show, so I am placing images instead.
df.head:
df.tail:
I don't know how to determine the last 5 overs by code. I am not showing rows before 5.1 (over/ball < 5.1). But runs_last_5 and wickets_last_5 will take data from there till over/ball 9.6, for 10.1 last 5 overs will be till 5.1. I mean, logically and verbally I know that if current over is 8.1, then the runs_last_5 will be sum_total_runs from 3.1 to 8.1 over/ball or if the current over/ball is 11.3 then the runs_last_5 will be the sum_total_runs from 6.3 over/ball to 9.3 over/ball. Both runs_last_5 and wickets_last_5 will take this data from, sum_total_runs and sum_total_wickets.
runs_last_5 and wickets_last_5 should look like,
In[]: df.head(10)
out[]:
sum_total_runs sum_total_wickets runs_last_5 wickets_last_5
32 61 0 59 0
33 61 1 59 1
34 61 1 59 1
35 61 1 59 1
36 61 1 58 1
37 61 1 58 1
38 62 1 55 1
39 63 1 52 1
40 64 1 47 1
41 66 1 45 1
In[]: df.tail(10)
out[]:
sum_total_runs sum_total_wickets runs_last_5 wickets_last_5
75879 102 9 31 4
75880 106 9 35 4
75881 106 9 35 4
75882 106 9 31 4
75883 106 9 30 4
75884 106 9 29 4
75885 107 9 29 4
75886 107 9 28 4
75887 107 9 24 4
75888 107 10 23 5