Pandas透视表格式问题求助：按类别分列统计ID对应数量

Hey there! Let's sort out this pivot table issue you're facing. The goal is to get a table where each ID has counts per Category as separate columns, right? That error you're seeing ("grouper for Category...") usually pops up due to missing values in your Category column, or a mismatch in how you're defining the aggregation. Here are two solid solutions:

Solution 1: Use pd.crosstab (Simpler for Cross Counts) #

pd.crosstab is built exactly for this kind of row-column frequency counting, and it handles missing categories by default (filling with 0):

table_channel = pd.crosstab(index=df['ID'], columns=df['Category'], dropna=False).reset_index()

• dropna=False ensures all existing Category values are kept as columns, even if some IDs have no entries for them (they'll show 0).
• reset_index() moves the ID from the index to a regular column, matching your desired output format.

Solution 2: Fix the pivot_table Code #

If you prefer sticking with pivot_table, adjust the parameters to avoid the error and get the right output:

1. First, handle any missing values in Category (this is likely the root of your error):

   # Replace NaN with a placeholder like 'Missing' or drop rows with NaN
   df['Category'] = df['Category'].fillna('Missing')
   # Or drop rows with missing Category: df = df.dropna(subset=['Category'])
   

2. Then use aggfunc='size' instead of count (size counts total rows in the group, while count ignores NaN values) and add fill_value=0:

   table_channel = pd.pivot_table(
       data=df,
       index=['ID'],
       columns=['Category'],
       aggfunc='size',
       fill_value=0
   ).reset_index()
   

This will give you the exact format you want: ID as the first column, each Category as a separate column, with counts (0 for IDs that don't have entries in that Category).

Let me know if either of these works for you! 😊

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