I'm trying to find an easier way to run aggregate functions with my dataframe instead of extracting data manually and running the functions separately from the Data Frame itself. I have soccer statistics from a team, and I want to run analysis and statistics based off of Age. I want to bin the ages and from that, run statistics based off of these age groups. More concretely, I have a df:
df = pd.DataFrame({'Age':[20,30,22,27,35,33,22,28,29,21,28,33,29,27,31,20,25,26,31,33,29,18],
'Goals':np.random.randint(1,6,22),
'Shots on Goals':np.random.randint(5,20,22),
'Yellow Cards':np.random.randint(1,6,22),
'Assists':np.random.randint(0,16,22)})
df['Age Grps'] = pd.cut(df.Age, bins =[17,24,28,32,36])
df.set_index(['Age Grps'], inplace = True)
df.head(8)
Which outputs the following Data frame with the index set as the binned age groups:
| Age Grps | Age | Assists | Goals | Shot on Goals | Yellow Cards |
|----------|-----|---------|-------|---------------|--------------|
| (17,24] | 20 | 3 | 3 | 13 | 2 |
| (28, 32] | 30 | 2 | 3 | 11 | 3 |
| (17,24] | 22 | 10 | 3 | 14 | 5 |
| (24,28] | 27 | 3 | 1 | 16 | 3 |
| (32,36] | 35 | 1 | 4 | 5 | 1 |
| (32,36] | 33 | 5 | 4 | 17 | 1 |
| (17,24] | 22 | 14 | 5 | 13 | 3 |
| (24,28] | 28 | 14 | 2 | 7 | 4 |
Is it possible to groupby the current index (Age Grps) to produce the following result:
╔══════════╦═════╦═════════╦═══════╦═══════════════╦══════════════╗
║ Age Grps ║ Age ║ Assists ║ Goals ║ Shot on Goals ║ Yellow Cards ║
╠══════════╬═════╬═════════╬═══════╬═══════════════╬══════════════╣
║ (17,24] ║ 20 ║ 3 ║ 3 ║ 13 ║ 2 ║
║ ╠═════╬═════════╬═══════╬═══════════════╬══════════════╣
║ ║ 22 ║ 14 ║ 5 ║ 13 ║ 3 ║
║ ╠═════╬═════════╬═══════╬═══════════════╬══════════════╣
║ ║ 22 ║ 10 ║ 3 ║ 14 ║ 5 ║
╠══════════╬═════╬═════════╬═══════╬═══════════════╬══════════════╣
║ (24,28] ║ 27 ║ 3 ║ 1 ║ 16 ║ 3 ║
║ ╠═════╬═════════╬═══════╬═══════════════╬══════════════╣
║ ║ 28 ║ 14 ║ 2 ║ 7 ║ 4 ║
╠══════════╬═════╬═════════╬═══════╬═══════════════╬══════════════╣
║ (28,32] ║ 28 ║ 14 ║ 2 ║ 7 ║ 4 ║
╠══════════╬═════╬═════════╬═══════╬═══════════════╬══════════════╣
║ (32,36] ║ 35 ║ 1 ║ 4 ║ 5 ║ 1 ║
║ ╠═════╬═════════╬═══════╬═══════════════╬══════════════╣
║ ║ 33 ║ 5 ║ 4 ║ 17 ║ 4 ║
╚══════════╩═════╩═════════╩═══════╩═══════════════╩══════════════╝
What I want to be able to do is run aggregate statistics for each age group, such as Average Assists for each age group, Average Goals, Average Shot on Goals, etc. etc. Something like:
df['Average Goals'] = df.groupby('bucket')['Goals'].mean()
df['Average Assists'] = df.groupby('bucket')['Assists'].mean()
In order to generate a table like this:
╔══════════╦═════╦═════════╦═════════════════╦═══════╦═══════════════╦═══════════════╦══════════════╗
║ Index ║ Age ║ Assists ║ Average Assists ║ Goals ║ Average Goals ║ Shot on Goals ║ Yellow Cards ║
╠══════════╬═════╬═════════╬═════════════════╬═══════╬═══════════════╬═══════════════╬══════════════╣
║ (17,24] ║ 20 ║ 3 ║ 9 ║ 3 ║ 3.67 ║ 13 ║ 2 ║
║ ╠═════╬═════════╣ ╬═══════╬ ╬═══════════════╬══════════════╣
║ ║ 22 ║ 14 ║ ║ 5 ║ ║ 13 ║ 3 ║
║ ╠═════╬═════════╣ ╬═══════╬ ╬═══════════════╬══════════════╣
║ ║ 22 ║ 10 ║ ║ 3 ║ ║ 14 ║ 5 ║
╠══════════╬═════╬═════════╬═════════════════╬═══════╬═══════════════╬═══════════════╬══════════════╣
║ (24,28] ║ 27 ║ 3 ║ 8.5 ║ 1 ║ 1.5 ║ 16 ║ 3 ║
║ ╠═════╬═════════╣ ╬═══════╬ ╬═══════════════╬══════════════╣
║ ║ 28 ║ 14 ║ ║ 2 ║ ║ 7 ║ 4 ║
╠══════════╬═════╬═════════╬═════════════════╬═══════╬═══════════════╬═══════════════╬══════════════╣
║ (28,32] ║ 28 ║ 14 ║ 14 ║ 2 ║ 2 ║ 7 ║ 4 ║
╠══════════╬═════╬═════════╬═════════════════╬═══════╬═══════════════╬═══════════════╬══════════════╣
║ (32,36] ║ 35 ║ 1 ║ 3 ║ 4 ║ 4 ║ 5 ║ 1 ║
║ ╠═════╬═════════╣ ╬═══════╬ ╬═══════════════╬══════════════╣
║ ║ 33 ║ 5 ║ ║ 4 ║ ║ 17 ║ 4 ║
╚══════════╩═════╩═════════╩═════════════════╩═══════╩═══════════════╩═══════════════╩══════════════╝
I know I can extract the data in the form of lists and perform the statistics I need, but I'm trying to do things in a "pandorable" manner. Also I'm going to be plotting this data with matplotlib and I want to use the easy method of pandas and matplotlib API df.plot().
Thanks in advance for the help