如何在Pandas中按ID分组统计行数及转换代理产品计数宽表？

Hey there! As someone who made the switch from R to Pandas myself, I totally get how you're hunting for familiar equivalents to your go-to R workflows. Let's break down your two questions clearly, with direct comparisons to the R code you're used to:

1. Group by ID and Count Rows per Group #

First, I’m guessing your R code for this looks something like this (using dplyr):

library(dplyr)
df %>% group_by(ID) %>% summarise(row_count = n())

In Pandas, you’ve got two straightforward options, each with a small but important difference:

• size(): Counts every row in each group, including those with missing values (NA/NaN)

  # Returns a Series with group IDs as index and counts as values
  df.groupby('ID').size()
  
  # Convert to a full DataFrame with named columns if needed
  df.groupby('ID').size().reset_index(name='row_count')
  

• count(): Only counts non-missing values for the column you specify (great if you want to exclude rows with empty entries in a specific field)

  # Count non-NA entries in EVT_ID for each ID group
  df.groupby('ID')['EVT_ID'].count().reset_index(name='valid_entry_count')
  

2. Reshape to Wide Format (AGT_KEY as Rows, product_group as Columns) #

For this task, your R code probably uses tidyr’s pivot_wider, like:

library(tidyr)
df %>% 
  pivot_wider(
    id_cols = AGT_KEY,
    names_from = product_group,
    values_from = EVT_ID,
    values_fn = length,
    values_fill = 0
  )

In Pandas, you can use either of two methods to get the same result:

Option 1: pivot_table() (Flexible for Custom Logic) #

This method gives you full control over aggregation rules:

import pandas as pd

wide_table = pd.pivot_table(
    df,
    index='AGT_KEY',       # Set AGT_KEY as row index
    columns='product_group',# Use product_group values as column names
    values='EVT_ID',       # Column to use for counting occurrences
    aggfunc='count',       # Aggregation: count how many times each combo appears
    fill_value=0           # Fill missing product groups for an agent with 0
)

Option 2: pd.crosstab() (Shorter for Frequency Counts) #

This is a purpose-built function for cross-tabulating two columns—perfect for this exact use case:

wide_table = pd.crosstab(df['AGT_KEY'], df['product_group'])

Example Output

If your raw data looks like this:

Both methods will produce this clean wide table:

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