Pandas按列对分组透视问题求助：双向流量数据合并处理

Got it, let's work through this bidirectional flow data problem—pivoting and melting alone don't quite hit the mark here, but there's a straightforward way to group those reciprocal pairs cleanly.

First, the core idea is to create a standardized identifier for each pair of locations, regardless of their "from" or "to" order. That way, both a→b and b→a get mapped to the same group.

Step 1: Create a Standardized Pair Column #

We can sort the from_location and to_location values for each row, then combine them into a consistent key (either a tuple or a string). Here's how to do it:

import pandas as pd
import numpy as np  # For the faster alternative later

# Your original data
df = pd.DataFrame(
    columns=['from_location','to_location','flow'],
    data =[['a','b',1],['b','a',3]]
)

# Option 1: Using apply (simple, works for small datasets)
df['pair'] = df.apply(lambda row: tuple(sorted([row['from_location'], row['to_location']])), axis=1)

# Option 2: Faster numpy-based method (better for large datasets)
df[['loc1', 'loc2']] = np.sort(df[['from_location', 'to_location']], axis=1)

Step 2: Aggregate Flow by Standardized Pair #

Now that we have a consistent key for each reciprocal pair, we can group by that key and aggregate the flow values (sum, mean, max—whatever makes sense for your use case).

Using the first option (tuple pair):

merged_df = df.groupby('pair').agg(total_flow=('flow', 'sum')).reset_index()

# Optional: Split the tuple into separate columns for readability
merged_df[['location1', 'location2']] = pd.DataFrame(merged_df['pair'].tolist(), index=merged_df.index)
merged_df = merged_df.drop('pair', axis=1)

Using the faster numpy option:

merged_df = df.groupby(['loc1', 'loc2']).agg(total_flow=('flow', 'sum')).reset_index()

Final Result #

Either way, you'll end up with a clean merged dataset where reciprocal flows are combined:

This approach avoids the confusion of manually merging and sorting because we first lock in a consistent identity for each pair before aggregating. No more messy ordering issues!

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