Pandas多DataFrame非合并场景下：统计仅使用单一交易类型的个体数量方案咨询

When dealing with large Pandas DataFrames where merging isn't feasible, we can break the problem into smaller, memory-efficient steps. Here's a step-by-step approach tailored to your use case:

Core Idea #

Instead of merging the entire datasets, we'll:

1. Aggregate transaction data per contract ID
2. Map individuals to their contracts
3. Check if all transactions across an individual's contracts belong to a single type

Step 1: Encode Transactions & Compute Contract-Level Stats #

First, we'll convert transaction types to integers (to save memory) and calculate the min/max transaction code per contract. If a contract uses only one type, its min and max codes will be identical.

import pandas as pd
from pandas.api.types import CategoricalDtype

# Sample data (replace with your actual DataFrames)
df1 = pd.DataFrame({
    'contract id': [1, 2, 3],
    'first name': ['John', 'Rob', 'Rob'],
    'last name': ['Smith', 'Brown', 'Brown']
})

df2 = pd.DataFrame({
    'contract id': [1, 1, 1, 2, 2, 2, 3],
    'transaction': ['cash', 'cash', 'cash', 'bank transfer', 'bank transfer', 'bank transfer', 'cash']
})

# Encode transaction types to integers (reduces memory usage)
trans_cat = CategoricalDtype(df2['transaction'].unique(), ordered=False)
df2['trans_code'] = df2['transaction'].astype(trans_cat).cat.codes

# Get min/max transaction code for each contract
contract_trans_stats = df2.groupby('contract id')['trans_code'].agg(['min', 'max'])

Step 2: Map Individuals to Their Contracts #

Group the contract IDs by individual (using first/last name as the unique identifier):

ind_contracts = df1.groupby(['first name', 'last name'])['contract id'].apply(list)

Step 3: Check for Single Transaction Type per Individual #

We have two efficient options here, depending on your dataset size:

Option A: Dictionary Lookup + Apply (Good for Large Individual Counts)

Use dictionaries for fast contract stat lookups, then check if all transactions across an individual's contracts are the same:

# Create lookup dictionaries for contract min/max codes
contract_min = contract_trans_stats['min'].to_dict()
contract_max = contract_trans_stats['max'].to_dict()

def has_single_transaction(contract_ids):
    # Get all min/max codes for the individual's contracts
    all_mins = [contract_min[cid] for cid in contract_ids]
    all_maxes = [contract_max[cid] for cid in contract_ids]
    # If overall min == max, all transactions are the same type
    return min(all_mins) == max(all_maxes)

# Apply the check to each individual
single_trans_individuals = ind_contracts.apply(has_single_transaction)

# Count qualifying individuals
count = single_trans_individuals.sum()
print(f"Number of individuals using only one transaction type: {count}")  # Output: 1

Option B: Vectorized Explode + Merge (Faster for Vectorized Operations)

If your individual-to-contract mapping is manageable, use pandas vectorization for speed:

# Explode the contract list to create individual-contract pairs
ind_contract_exploded = ind_contracts.explode().reset_index()
ind_contract_exploded.columns = ['first name', 'last name', 'contract id']

# Merge with contract stats (small merge compared to full dataset merge)
ind_trans_stats = ind_contract_exploded.merge(contract_trans_stats, on='contract id', how='left')

# Compute overall min/max transaction codes per individual
overall_stats = ind_trans_stats.groupby(['first name', 'last name']).agg(
    overall_min=('min', 'min'),
    overall_max=('max', 'max')
)

# Count individuals with identical overall min/max
count = (overall_stats['overall_min'] == overall_stats['overall_max']).sum()
print(f"Number of individuals using only one transaction type: {count}")  # Output:1

Key Advantages #

• Memory Efficiency: We avoid merging the full large datasets, working instead with aggregated mappings and stats.
• Speed: Both methods use O(n) time complexity for each DataFrame, which scales well for big data.
• Flexibility: Choose the option that best fits your dataset's size and structure.

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