如何基于Pandas以更智能的方式对双Excel数据进行分箱排序？

Hey Nicklas, let's refactor your code to make it way more flexible and efficient! The main issues with your current approach are the slow nested loops and hard-coded bin logic. Here's a smarter, scalable way to handle this task:

Step 1: Efficiently Match Timestamps with Merging #

Instead of looping through both datasets manually, use Pandas' optimized merge function to find matching timestamps in one go. We'll also filter out non-positive Power values upfront to clean the data early.

import pandas as pd

# Load your Excel files
dataREF = pd.read_excel('FilterDataREF.xlsx', sheet_name='Sheet1')
dataPCU = pd.read_excel('FilterDataPCU.xlsx', sheet_name='Ark1')

# Merge datasets on Timestamp, keep only rows with positive Power in both
merged_data = pd.merge(
    dataREF[dataREF['Power'] > 0],
    dataPCU[dataPCU['Power'] > 0],
    on='Timestamp',
    suffixes=('_REF', '_PCU')  # Add suffixes to distinguish columns from each file
)

Step 2: Dynamically Generate Bins #

To make binning adaptable to changing sizes or maximum limits, we'll calculate bins dynamically. You have two flexible options:

Option A: Bins based on your actual data's maximum Power

This lets bins automatically adjust to the highest Power value in your merged dataset:

bin_size = 50  # You can tweak this value anytime

# Get the highest Power value across both datasets
max_power = merged_data[['Power_REF', 'Power_PCU']].max().max()
# Round up to the nearest multiple of bin_size for clean upper limit
upper_limit = ((max_power // bin_size) + 1) * bin_size
# Create bin ranges
bins = range(0, upper_limit + bin_size, bin_size)

Option B: User-defined upper limit

If you need to enforce a fixed upper bound (like your original 1500), just set it explicitly:

bin_size = 50
upper_limit = 1500  # Adjust this value as needed
bins = range(0, upper_limit + bin_size, bin_size)

Step 3: Apply Binning with pd.cut #

Use Pandas' built-in pd.cut function to assign each Power value to its corresponding bin. This replaces your hard-coded bin conditions with clean, maintainable code:

# Add bin columns for both REF and PCU Power values
merged_data['Power_REF_bin'] = pd.cut(
    merged_data['Power_REF'],
    bins=bins,
    right=False,  # Creates bins like [0,50), [50,100), etc.
    include_lowest=True  # Ensures the smallest value falls into the first bin
)

merged_data['Power_PCU_bin'] = pd.cut(
    merged_data['Power_PCU'],
    bins=bins,
    right=False,
    include_lowest=True
)

# Calculate Power difference (as you did in your original code)
merged_data['Power_diff'] = merged_data['Power_PCU'] - merged_data['Power_REF']

Step 4: Optional: Analyze Binned Data #

If you want to aggregate stats by bin (like average difference or count of entries), you can do this with a simple groupby:

bin_summary = merged_data.groupby('Power_REF_bin').agg(
    total_entries=('Power_diff', 'count'),
    avg_power_diff=('Power_diff', 'mean'),
    min_power_diff=('Power_diff', 'min'),
    max_power_diff=('Power_diff', 'max')
).reset_index()

print(bin_summary)

Why This Works Better: #

• Speed: Merging replaces nested loops, cutting runtime from O(n*m) to O(n) — critical for large datasets.
• Flexibility: Just change bin_size or upper_limit and the entire binning logic updates automatically.
• Readability: Using Pandas' native functions makes the code shorter, easier to debug, and simpler for others to understand.

内容的提问来源于stack exchange，提问作者Nicklas Koldkjær