如何在Python中对两个DataFrame进行并排排序并将非双方共有的行移至原DataFrame底部

Got it, let's tackle this problem step by step. The goal is to align two DataFrames by their shared identifiers (like your WWTxxxx codes), sort those shared rows consistently, and push any rows unique to each DataFrame to the bottom of their respective tables. This will make your side-by-side view clean and eliminate tedious manual work even for large datasets.

Step 1: Confirm Your Matching Key #

First, make sure both DataFrames have a common identifier column (or use the index if that's where your WWTxxxx codes live). For this example, we'll use an ID column holding those codes.

Step 2: Test with Sample Data #

Let's create sample DataFrames to mimic your scenario:

import pandas as pd

# Left DataFrame with a unique row (WWT0117)
df_left = pd.DataFrame({
    'ID': ['WWT0115', 'WWT0117', 'WWT0116', 'WWT0118'],
    'Residual': [0.02, 0.03, 0.01, 0.04]
})

# Right DataFrame with a unique row (WWT0119)
df_right = pd.DataFrame({
    'ID': ['WWT0115', 'WWT0116', 'WWT0119'],
    'Residual': [0.02, 0.01, 0.05]
})

Step 3: Automate the Sorting and Reordering #

Here's the core code to process both DataFrames efficiently:

# Get shared IDs and sort them to ensure consistent alignment across both DataFrames
common_ids = sorted(df_left['ID'].intersection(df_right['ID']))

# Process left DataFrame: sorted shared rows first, then sorted unique rows at the bottom
df_left_common = df_left[df_left['ID'].isin(common_ids)].sort_values('ID')
df_left_unique = df_left[~df_left['ID'].isin(common_ids)].sort_values('ID')
df_left_final = pd.concat([df_left_common, df_left_unique]).reset_index(drop=True)

# Repeat the logic for the right DataFrame
df_right_common = df_right[df_right['ID'].isin(common_ids)].sort_values('ID')
df_right_unique = df_right[~df_right['ID'].isin(common_ids)].sort_values('ID')
df_right_final = pd.concat([df_right_common, df_right_unique]).reset_index(drop=True)

What This Does #

• Aligned Shared Rows: Both DataFrames will have matching ID rows sorted in the same order, so they line up perfectly in a side-by-side view.
• Unique Rows at Bottom: Any row that only exists in one DataFrame (like WWT0117 in your left DataFrame) gets moved to the bottom of that table.
• Scalable: This works seamlessly with large datasets—no manual row-by-row editing required.

If Your ID is the DataFrame Index #

If your WWTxxxx codes are already set as the index of each DataFrame, adjust the code slightly:

# Assume df_left and df_right use ID as their index
common_ids = sorted(df_left.index.intersection(df_right.index))

df_left_final = pd.concat([
    df_left.loc[common_ids], 
    df_left.loc[sorted(df_left.index.difference(df_right.index))]
])
df_right_final = pd.concat([
    df_right.loc[common_ids], 
    df_right.loc[sorted(df_right.index.difference(df_left.index))]
])

Now when you view df_left_final and df_right_final side by side, your aligned rows will be at the top, and unique rows will sit neatly at the bottom of their respective tables.

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