如何使用Pandas对CSV数据按URL分组并将Label统计结果转为列格式

Problem Scenario #

I have the following CSV data (and I don't need to focus on the sub_url field):

url, sub_url, label
first.com, first.com/1, label1
first.com, first.com/2, label1
first.com, first.com/3, label2
first.com, first.com/4, label2
first.com, first.com/5, label1
second.com, second.com/1, label2
second.com, second.com/2, label2
second.com, second.com/3, label1
third.com, third.com/1, label2
third.com, third.com/2, label2

I want to transform this into a wide-format output where each URL has counts for label1 and label2 as separate columns:

url, count_label1, count_label2
first.com, 3, 2
second.com, 1, 2
third.com, 0, 2

I tried this code:

df = pd.read_csv(path_to_csv, sep=,)
df.groupby( ["url"] )["label"].value_counts()

But this returns each label count as a separate row per URL instead of columns. How can I adjust this to get the desired structure?

Solution #

You can achieve this in two clean, straightforward ways with Pandas:

Method 1: Use pd.crosstab (Simplest Approach)

crosstab is built exactly for this kind of frequency tabulation between two columns. It directly outputs the wide format you need:

import pandas as pd

# Fix the syntax error in sep parameter and read the CSV
df = pd.read_csv(path_to_csv, sep=',')

# Create cross-tabulation and rename columns to match your desired output
result = pd.crosstab(df['url'], df['label']).reset_index()
result.columns = ['url', 'count_label1', 'count_label2']

print(result)

Method 2: Build on Your Original groupby Code

If you want to expand on your initial approach, use unstack to pivot the label counts into columns, then fill missing values and rename columns:

import pandas as pd

df = pd.read_csv(path_to_csv, sep=',')

# Group, count, pivot to columns, fill missing values, and clean up
result = (
    df.groupby(['url', 'label'])
      .size()  # Same as value_counts() but returns a size-based Series
      .unstack(fill_value=0)  # Pivot labels into columns, fill missing counts with 0
      .reset_index()  # Move url from index to a regular column
      .rename(columns={'label1': 'count_label1', 'label2': 'count_label2'})
)

print(result)

Explanation #

• pd.crosstab: This function automatically calculates how many times each label appears per url, then returns a wide-format DataFrame. We just rename the columns to match your requested naming convention.
• groupby + unstack:
  • groupby(['url', 'label']).size() computes the count of each label per URL (similar to your original value_counts but with a multi-indexed Series).
  • unstack() pivots the label index level into columns, converting the long-format Series into a wide DataFrame.
  • fill_value=0 ensures URLs with no occurrences of a label (like third.com for label1) show a 0 instead of NaN.
  • reset_index() and rename(columns=...) clean up the structure to match your desired output.

Both methods will give you exactly the formatted data you're looking for!

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