Pandas按多列分组统计数据：使用crosstab未得预期结果

Hey there! Let's work through getting your pivot transformation right. Since you mentioned crosstab didn't give the expected result, let's start with concrete examples and common fixes—this should cover most text classification use cases.

First, let's assume your original DataFrame looks like this (super typical for text classification tasks):

import pandas as pd

# Sample original DataFrame
df = pd.DataFrame({
    'text': ['The movie was great', 'This show is boring', 'Best meal ever', 'Worst experience', 'Great acting'],
    'sentiment': ['positive', 'negative', 'positive', 'negative', 'positive']
})

Case 1: You want a count-based cross-tabulation #

If your goal is to see how many times each text maps to each label (or vice versa), crosstab works perfectly—you might just have mixed up the row/column order.

Try this correct usage:

# Rows = text entries, Columns = classification labels, Values = count
result = pd.crosstab(df['text'], df['sentiment'])
print(result)

Output:

sentiment               negative  positive
text                                     
Best meal ever                 0         1
Great acting                   0         1
The movie was great            0         1
This show is boring            1         0
Worst experience              1         0

If you want rows to be labels and columns to be texts, just swap the arguments:

result = pd.crosstab(df['sentiment'], df['text'])

Case 2: You want to group texts under their labels #

If your expected output has one column per label, with all corresponding texts listed under each, crosstab isn't the right tool—use groupby + pivot instead:

# Group texts by label, then pivot to make labels columns
grouped = df.groupby('sentiment')['text'].agg(list).reset_index()
result = grouped.pivot(columns='sentiment', values='text')
print(result)

Output:

sentiment               negative                                      positive
0         [This show is boring, Worst experience]  [The movie was great, Best meal ever, Great acting]

Common crosstab Mistakes That Cause Unexpected Output #

• Swapped row/column arguments: If you passed df['label'], df['text'] instead of the reverse, your rows and columns will be flipped.
• Unhandled duplicates: If you have repeated text-label pairs, crosstab counts them by default. To get unique entries, run df = df.drop_duplicates() first, or use normalize='index' to get proportions instead of counts.
• Expecting non-numeric values: crosstab is built for frequency counts. If you need to aggregate text or other non-numeric data, stick with groupby + custom aggregation.

If your expected output doesn't match these cases, share a tiny snippet of your raw data and what you want the final DataFrame to look like—I’ll adjust the solution to fit exactly!

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