Search code examples
pythonpandasgroup-by

Pandas complaining about key error, even when key exists

I have a dataframe that looks like this:

Dataframe

I want to count the instances of specific 'Event' in each 'Run'. I have the following Pandas code

all_run_counts=all_data_df.sort_values('Event',ascending=False).groupby('Run')
    result=all_run_counts["Event"].value_counts()

It gives me a data frame that counts each 'Event' in each 'Run', so far so good.

How I want to just filter specific event type and count instances of that event in each run, but when i do filter with following script, it doesnt work and gives me 'Event' key error when clearly 'Event' is already there as a column:

    result.loc[result['Event'] == 'J']

What is the reason for this?

I was expecting each Run filtered by specific event so I can compare corresponding value_counts of same event type with various runs.

Solution

  • By applying groupby and value_counts, your result ends up being a Series, not a dataframe, and thus Event is not a column but a MultiIndex level.

    You need to reset_index:

    result = all_run_counts["Event"].value_counts().reset_index()

    Note that the groupby is not really needed here, you can directly use value_counts:

    result = all_data_df.value_counts().reset_index()