如何用Pandas构建产品组合矩阵并计算交叉购买占比

Got it, let's break down how to build that product combination matrix you need—this is a classic retail analytics task, and Pandas makes it straightforward. I'll walk through every step, starting from typical transaction data and working our way to both co-purchase counts and purchase ratios.

1. 先处理你的数据（假设是交易型长格式） #

First, let's assume your data looks like this: each row represents a single product purchase by a customer (if your data is already in a wide format with one customer per row and products as columns, skip to step 2).

示例数据 & 0/1客户-产品矩阵转换 #

We'll start by converting this transactional data into a binary matrix where each row is a customer, each column is a product, and values are 1 (customer bought the product) or 0 (didn't buy).

import pandas as pd

# 示例交易数据（替换成你的真实数据）
transaction_data = {
    'customer_id': ['C1', 'C1', 'C2', 'C2', 'C3', 'C4', 'C4', 'C4'],
    'product': ['A', 'B', 'A', 'C', 'B', 'A', 'B', 'C']
}
df = pd.DataFrame(transaction_data)

# 先去重（避免同一个客户多次买同一件产品干扰结果）
df_unique = df.drop_duplicates(subset=['customer_id', 'product'])

# 构建客户-产品的0/1矩阵
customer_product_matrix = pd.crosstab(df_unique['customer_id'], df_unique['product']).ge(1).astype(int)

The ge(1).astype(int) converts purchase counts (from crosstab) into binary values—since we only care if a customer bought the product, not how many times.

2. 计算同时购买的客户数量矩阵 #

To get the number of customers who bought both product X and Y, we can use matrix multiplication: transpose the customer-product matrix and multiply it by itself. The result will be a square matrix where each cell [X,Y] is the count of customers who bought both X and Y.

# 共同购买客户数矩阵
co_purchase_counts = customer_product_matrix.T.dot(customer_product_matrix)

For example, co_purchase_counts.loc['A', 'B'] will give you the number of customers who bought both A and B (in our sample data, that's 2: C1 and C4).

3. 计算购买A的客户中同时购买B的占比 #

To get the percentage of customers who bought A that also bought B, we divide each row in the co-purchase count matrix by the total number of customers who bought the row product.

# 计算占比矩阵（行产品的购买客户中，购买列产品的比例）
purchase_ratios = co_purchase_counts.div(customer_product_matrix.sum(axis=0), axis=0)

# 转换成百分比格式（可选，更易读）
purchase_ratios_percent = purchase_ratios.applymap(lambda x: f"{x:.1%}")

In our sample, purchase_ratios_percent.loc['A', 'B'] will be 66.7%—exactly the kind of metric you're looking for! The diagonal values will be 100% (since every customer who bought X obviously bought X).

额外注意事项 #

• If your data is already wide (one customer per row, products as columns with 0/1 values), you can skip step 1 and use your existing matrix directly.
• Make sure to handle missing values if any—you can use fillna(0) to replace NaNs with 0 (assuming NaN means the customer didn't buy the product).
• For large datasets, this method is efficient because matrix multiplication in Pandas is optimized under the hood.

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