Pandas无法生成1/0形式的订单-ASIN关联指标求助

Problem Description #

I have the following input data:

Order_id ASIN
1 abc1
1 abc3
2 cba2
2 abc1
2 bbc3

I want to create an indicator matrix where rows are Order_id, columns are ASIN, with a value of 1 if the ASIN exists in the order, and 0 otherwise. The expected output is:

abc1 abc3 cba2 bbc3
Order id
1 1 1 0 0
2 1 0 1 1

I tried using this Pandas code:

bskt = pd.pivot_table(df,index='Order_Id', columns='ASIN', fill_value = 0)

But didn't get the expected result. Need help fixing this.

Solution #

The issue with your original code comes down to two key points:

1. Typo: Your data uses Order_id (lowercase 'd'), but your code references Order_Id (uppercase 'd').
2. Missing aggregation setup: pivot_table requires a numerical column to aggregate, and you didn't specify one or an aggregation function.

Here are two straightforward ways to get your desired indicator matrix:

Method 1: Use pd.crosstab (Simplest Approach)

pd.crosstab is built for creating cross-tabulations between categorical variables, which fits your use case perfectly. It automatically counts occurrences (resulting in 1 for existing pairs, 0 for missing ones):

import pandas as pd

# Recreate your input DataFrame
data = {
    'Order_id': [1, 1, 2, 2, 2],
    'ASIN': ['abc1', 'abc3', 'cba2', 'abc1', 'bbc3']
}
df = pd.DataFrame(data)

# Create the indicator matrix
indicator_matrix = pd.crosstab(df['Order_id'], df['ASIN'])

# Optional: Reorder columns to match your expected output
desired_columns = ['abc1', 'abc3', 'cba2', 'bbc3']
indicator_matrix = indicator_matrix[desired_columns]

print(indicator_matrix)

Output:

ASIN     abc1  abc3  cba2  bbc3
Order_id                        
1          1     1     0     0
2          1     0     1     1

Method 2: Fix Your pivot_table Code

If you prefer sticking with pivot_table, add a dummy numerical column (all 1s) and specify an aggregation function (like max or count) to ensure we get 1 for existing pairs:

import pandas as pd

# Recreate input DataFrame
data = {
    'Order_id': [1, 1, 2, 2, 2],
    'ASIN': ['abc1', 'abc3', 'cba2', 'abc1', 'bbc3']
}
df = pd.DataFrame(data)

# Add a dummy column with value 1
df['indicator'] = 1

# Use pivot_table with proper aggregation
bskt = pd.pivot_table(
    df,
    index='Order_id',
    columns='ASIN',
    values='indicator',
    aggfunc='max',  # Works since each pair appears at most once
    fill_value=0
)

# Optional: Reorder columns
desired_columns = ['abc1', 'abc3', 'cba2', 'bbc3']
bskt = bskt[desired_columns]

print(bskt)

This will produce the same output as the crosstab method.

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