Pandas groupby().agg() with lambdas in a list comprehension

user_13954860 0
Viewed 269

I have a dataframe called pitches with every pitch from the 2019 MLB season with columns for pitcher_id and pitch type.

I want to group by pitcher_id and count instances of each pitch type, and I can do so with a groupby().agg() method such as the following:

pitches.groupby('pitcher_id')['pitchType'].agg(
    [('Four-Seam Fastball',lambda pitchType: (pitchType=='Four-Seam Fastball').sum()),
    ('Curveball',lambda pitchType: (pitchType=='Curveball').sum())]
)

I'd like to be able to do this with a list comprehension, but when I try it the resulting columns return all zeros. Here's what I'm trying:

pitch_types = ['Four-Seam Fastball', 'Slider', 'Curveball', 'Changeup', 'Sinker']

pitches.groupby('pitcher_id')['pitchType'].agg(
    [(x,lambda pitchType: (pitchType==x).sum()) for x in pitch_types]
)

The first example works flawlessly. The second returns a dataframe with all the pitch types as columns but all zeros for values. Can anyone tell what I'm doing wrong or suggest an alternate approach?

pandas-groupby pandas list-comprehension lambda python
1 Answers

Let's try value_counts here:

(pitches.query("pitchType in @pitch_types")
        .groupby('pitcher_id')['pitchType']
        .value_counts())

The idea is to filter your DataFrame to only keep values you want to count (convince yourself that this doesn't need to be done inside the groupby so it'd be much faster), then you can simply count what you have left inside each group.

user_49090870
Related