合并DataFrame时保留分组首尾极值的实现方法求助

安全阿岳

2026-5-29

Solution to Combine DataFrames While Retaining Group Extremes

Hey there! Let's figure out how to get your desired output. The key here is that we need to combine the intersection of df1 and df2 (which merge gives us) with the extreme values (min/max, or first/last depending on your actual need) of each group in A from df1, even if those extremes aren't in df2.

Step 1: Prepare Sample Data

First, let's replicate your sample DataFrames for clarity:

import pandas as pd

df1 = pd.DataFrame({
    'A': [1,1,1,1,1,1,2,2,3,3,3,3,3,4,4,4,4],
    'B': [11,2,32,42,54,66,16,23,13,24,35,46,51,12,28,39,49]
})

df2 = pd.DataFrame({'B': [32,42,13,24,35,39,49]})

Step 2: Map `A` Values to df2

Since df2's B values all exist in df1, we can merge to add the corresponding A column to df2:

# Merge to get A values for df2's B entries, remove duplicates
df2_with_A = df2.merge(df1[['A', 'B']], on='B', how='left').drop_duplicates()

Step 3: Extract Group-Wise Extremes from df1

We'll start with getting the minimum and maximum B values for each A group (this matches the "极值" definition of min/max):

# Get min and max B for each A group, reshape into a standard DataFrame
group_extremes = df1.groupby('A')['B'].agg(['min', 'max']).stack().reset_index(name='B').drop('level_1', axis=1)

Step 4: Combine and Clean the Data

Now concatenate the df2 data (with A) and group extremes, remove duplicates, then sort to match your desired output structure:

# Combine the two datasets
combined = pd.concat([df2_with_A, group_extremes]).drop_duplicates()

# Sort by A and B, then reset the index
df3 = combined.sort_values(by=['A', 'B']).reset_index(drop=True)

Result from This Code:

Adjustments for Different "Extreme" Definitions

If "首尾极值" Means First/Last Entries in Each Group

If you want the first and last values of each A group in df1's original order (not min/max), replace the group_extremes step with:

# Get first and last B values for each A group in df1's original order
group_first_last = df1.groupby('A').agg(first_B=('B', 'first'), last_B=('B', 'last')).stack().reset_index(name='B').drop('level_1', axis=1)

If You Want Extremes Excluding df2's Values

Looking at your sample output, you might want extremes that aren't already present in df2. We can filter df1 first to exclude df2's B values, then extract extremes:

# Filter df1 to remove B values that are in df2
df1_filtered = df1[~df1['B'].isin(df2['B'])]

# Get min and max from the filtered DataFrame
filtered_extremes = df1_filtered.groupby('A')['B'].agg(['min', 'max']).stack().reset_index(name='B').drop('level_1', axis=1)

# Combine with df2_with_A and sort
combined = pd.concat([df2_with_A, filtered_extremes]).drop_duplicates()
df3 = combined.sort_values(by=['A', 'B']).reset_index(drop=True)

Final Notes

If you need to narrow down to only one extreme per side (e.g., the smallest value below df2's minimum and largest value above df2's maximum for each group), we can add additional filtering steps, but the core approach of combining intersection data with group-specific extremes remains the same.

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

合并DataFrame时保留分组首尾极值的实现方法求助

Step 1: Prepare Sample Data #

Step 2: Map A Values to df2 #

Step 3: Extract Group-Wise Extremes from df1 #

Step 4: Combine and Clean the Data #