如何用Pandas实现无重复cross join并计算客户新订重订订单比例

Hey there! Let's tackle your two Pandas questions one by one, step by step:

Your DataFrame tracks each user's orders, where reordered=0 means a new order and reordered=1 means a repeat order. Here's how to compute the ratio for each user:

Step-by-Step Implementation #

• First, group the data by user_id and count the number of new vs reordered orders
• Then calculate the ratio (you can choose between new orders / reordered orders or new orders as a percentage of total orders)

import pandas as pd

# Sample data matching your structure
data = {
    'user_id': [128305]*8 + [144548]*3,
    'reordered': [1,1,1,0,0,0,0,0,1,1,1]
}
df = pd.DataFrame(data)

# 1. Count new and reordered orders per user
order_summary = df.groupby('user_id')['reordered'].value_counts().unstack(fill_value=0)
# Rename columns for clarity (0 = new orders, 1 = reordered)
order_summary.columns = ['new_orders', 'reordered_orders']

# 2. Calculate desired ratios
# Option A: New orders divided by reordered orders
order_summary['new_to_reordered_ratio'] = order_summary['new_orders'] / order_summary['reordered_orders']

# Option B: New orders as a percentage of total orders
order_summary['new_order_pct_of_total'] = order_summary['new_orders'] / (order_summary['new_orders'] + order_summary['reordered_orders'])

print(order_summary)

Output Excerpt #

new_orders  reordered_orders  new_to_reordered_ratio  new_order_pct_of_total
user_id
128305             5                 3                1.666667                0.625000
144548             0                 3                0.000000                0.000000

A cross join (cartesian product) combines every row from one dataset with every row from another. To make it duplicate-free, we first ensure we're working with unique values from the columns we want to join.

Method 1: Using Merge with Dummy Key #

This works for any two DataFrames—we add a temporary dummy column to both, merge on that column, then clean up:

# Get unique user IDs from your original DataFrame
unique_users = df['user_id'].unique()

# Create two DataFrames with unique users (could be different datasets too)
df_a = pd.DataFrame({'user_id_a': unique_users})
df_b = pd.DataFrame({'user_id_b': unique_users})

# Perform cross join
cross_join = df_a.assign(dummy_key=1).merge(df_b.assign(dummy_key=1), on='dummy_key').drop('dummy_key', axis=1)

# Optional: Remove self-pairings (if you don't want user x paired with user x)
cross_join = cross_join[cross_join['user_id_a'] != cross_join['user_id_b']]

print(cross_join)

Method 2: Using MultiIndex.from_product #

Great if you just need to cross join two unique value lists:

# Create cross product of unique users
cross_product_index = pd.MultiIndex.from_product(
    [unique_users, unique_users],
    names=['user_id_a', 'user_id_b']
)

# Convert to DataFrame
cross_join = pd.DataFrame(index=cross_product_index).reset_index()

# Optional: Filter out duplicates/self-pairs
cross_join = cross_join[cross_join['user_id_a'] != cross_join['user_id_b']]

print(cross_join)

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