如何按多列分组计算DataFrame中某列特定值的占比？

Hey, let's sort out that KeyError you're running into! The root issue is how you're trying to group by two columns in Pandas—your syntax for selecting multiple columns isn't right, which is why the error pops up. Let's break this down step by step with your example data, then apply it to your actual code.

First, Let's Test with Your Sample Data #

Here's your sample DataFrame for reference:

import pandas as pd

data =[['North Shields','UK','Y'],['North Shields','Foreign','N']]
df = pd.DataFrame(data, columns = ['Port','Type','Shellfish Licence licence (Y/N)'])

The Correct Approach #

To calculate the percentage of rows where Shellfish Licence licence (Y/N) equals 'Y', grouped by both Port and Type, follow these steps:

1. Create a boolean column (optional but makes the logic clearer) to flag rows with 'Y'
2. Group by both columns using the proper syntax, then calculate the mean of the boolean column (since True = 1 and False = 0, the mean gives you the percentage of 'Y's)

Here's the code:

# Step 1: Flag rows with a Shellfish Licence
df['has_shellfish_licence'] = df['Shellfish Licence licence (Y/N)'].eq('Y')

# Step 2: Group by Port + Type and calculate the percentage
port_shel_df = df.groupby(['Port', 'Type'])['has_shellfish_licence'].mean().reset_index(name='Shellfish license percentage')

# Optional: Convert the decimal to a percentage for readability
port_shel_df['Shellfish license percentage'] *= 100

# Set Port as the index like you wanted
port_shel_df = port_shel_df.set_index('Port')

Running this will give you the expected result:

Type  Shellfish license percentage
Port                                             
North Shields    UK                          100.0
North Shields Foreign                            0.0

Fixing Your Original Code #

If you want to stick closer to your original one-liner, here's the corrected version (the key fix is how we specify the grouping columns):

port_shel_df = df['Shellfish Licence licence (Y/N)'].eq('Y')\
    .groupby([df['Port'], df['Type']])\
    .mean()\
    .reset_index(name='Shellfish license percentage')

port_shel_df = port_shel_df.set_index('Port')

Why Your Original Code Threw a KeyError #

When you wrote port_merge_lic_df['Port','Type'], Pandas interpreted that as trying to access a single column named ('Port','Type')—which doesn't exist in your DataFrame. To select multiple columns, you need to use double brackets [['Port','Type']] (to get a sub-DataFrame) or pass a list of column names directly to groupby() like we did above.

内容的提问来源于stack exchange，提问作者john doe