I have a table (example below)
kick_result kick_yards kicker
50 MADE 28.0 X1
64 MADE 30.0 X2
75 MADE 27.0 X2
158 MADE 32.0 X2
259 MISS 46.0 X3
For each value of kicker -
I want to count how many field goals were made and missed (with %)
For each value of kicker -
I want to find the amount of field goals made and missed @ each range of yards <20, 21-30, 31-40, 41-50, 51+
As your requirement has 2 parts:
let's solve it one by one.
We can use df.groupby() together with .value_counts(normalize=True) to get it:
(df.groupby('kicker')['kick_result']
.value_counts(normalize=True).mul(100).round(2)
.sort_index()
.to_frame(name='Result_%')
).reset_index()
Test Data Construction:
To have a complete testing of the various requirements, I have added test data as follows:
kick_result kick_yards kicker
49 MADE 18.0 X1
50 MADE 28.0 X1
51 MADE 38.0 X1
52 MISS 48.0 X1
53 MISS 58.0 X1
64 MADE 30.0 X2
75 MADE 27.0 X2
158 MADE 32.0 X2
159 MISS 32.0 X2
160 MISS 42.0 X2
259 MISS 46.0 X3
260 MISS 26.0 X3
261 MADE 56.0 X3
Run code:
(df.groupby('kicker')['kick_result']
.value_counts(normalize=True).mul(100).round(2)
.sort_index()
.to_frame(name='Result_%')
).reset_index()
Result:
kicker kick_result Result_%
0 X1 MADE 60.00
1 X1 MISS 40.00
2 X2 MADE 60.00
3 X2 MISS 40.00
4 X3 MADE 33.33
5 X3 MISS 66.67
We can use pd.crosstab() together with pd.cut() to build a table with the yard ranges.
Total attempts for all ranges are also included.
pd.crosstab(index=[df['kicker'], pd.cut(df['kick_yards'],[0, 20, 30, 40, 50, np.inf])],
columns=df['kick_result'],
margins=True, margins_name='Total_Attempts')
Result (using the enriched test data):
kick_result MADE MISS Total_Attempts
kicker kick_yards
X1 (0.0, 20.0] 1 0 1
(20.0, 30.0] 1 0 1
(30.0, 40.0] 1 0 1
(40.0, 50.0] 0 1 1
(50.0, inf] 0 1 1
X2 (20.0, 30.0] 2 0 2
(30.0, 40.0] 1 1 2
(40.0, 50.0] 0 1 1
X3 (20.0, 30.0] 0 1 1
(40.0, 50.0] 0 1 1
(50.0, inf] 1 0 1
Total_Attempts 7 6 13
Let us chain with cut and crosstab
out = pd.crosstab([df.kicker,pd.cut(df.kick_yards,[20,30,40,50,np.Inf],include_lowest=True)]
,df.kick_result,normalize='index')
out
Out[228]:
kick_result MADE MISS
kicker kick_yards
X1 (19.999, 30.0] 1.0 0.0
X2 (19.999, 30.0] 1.0 0.0
(30.0, 40.0] 1.0 0.0
X3 (40.0, 50.0] 0.0 1.0
Leverage get_dummies, cut and build a resulting DataFrame:
df['Att'] = 1
dfmm = pd.get_dummies(df['kick_result'])
cols_A = ['A20','A21-30','A31-40','A41-50','A51+']
cols_M = [x.replace('A','M') for x in cols_A]
df_att = pd.DataFrame(pd.get_dummies(pd.cut(df.kick_yards,[0,20,30,40,50,np.Inf],include_lowest=True)))
df_att.columns = df_att.columns.to_list()
df_att.columns = cols_A
df_made = df_att.multiply(dfmm['MADE'], axis=0)
df_made.columns=cols_M
dff = pd.concat([df,dfmm,df_att,df_made], axis=1).drop(['kick_result','kick_yards'], axis=1)
Resulting DataFrame:
kicker Att MADE MISS A20 A21-30 A31-40 A41-50 A51+ M20 M21-30 \
0 X1 1 1 0 0 1 0 0 0 0 1
1 X2 1 1 0 0 1 0 0 0 0 1
2 X2 1 1 0 0 1 0 0 0 0 1
3 X2 1 1 0 0 0 1 0 0 0 0
4 X3 1 0 1 0 0 0 1 0 0 0
M31-40 M41-50 M51+
0 0 0 0
1 0 0 0
2 0 0 0
3 1 0 0
4 0 0 0
Aggregation from that DataFrame:
dff.groupby('kicker').agg(['sum'])
Att MADE MISS A20 A21-30 A31-40 A41-50 A51+ M20 M21-30 M31-40 M41-50 \
sum sum sum sum sum sum sum sum sum sum sum sum
kicker
X1 1 1 0 0 1 0 0 0 0 1 0 0
X2 3 3 0 0 2 1 0 0 0 2 1 0
X3 1 0 1 0 0 0 1 0 0 0 0 0
M51+
sum
kicker
X1 0
X2 0
X3 0