Pandas中合并DataFrame后处理时间不匹配行并上移数据的方法

Got it, let's solve this problem efficiently—since you're working with large datasets, we'll focus on vectorized pandas operations (no slow loops!) to handle the alignment.

First, let's recreate your sample DataFrames to test the solution:

import pandas as pd

# Create DataFrame A
df_a = pd.DataFrame({
    'Time': ['1:20', '1:21', '1:22', '1:23'],
    'Reading 1': [1, 5, 6, 7],
    'R2': [3, 4, 2, 8],
    'R3': [4, 2, 1, 9]
})

# Create DataFrame B
df_b = pd.DataFrame({
    'Time': ['1:20', '1:22', '1:23', '1:24'],
    'Sensor1': [1, 3, 2, 5],
    'S2': [5, 8, 4, 6],
    'S3': [2, 4, 7, 8]
})

Method 1: Filter & Align (Most Efficient) #

This approach avoids creating the initial mismatched merged DataFrame entirely, which is better for large datasets. We'll:

1. Keep only rows from df_a where the Time exists in df_b
2. Align this filtered data with all rows from df_b (filling missing rows with NaN automatically)

# Step 1: Filter df_a to only include times present in df_b
mask = df_a['Time'].isin(df_b['Time'])
filtered_a = df_a[mask].reset_index(drop=True)

# Step 2: Rename df_b's Time column to avoid conflict and align with filtered_a
df_b_renamed = df_b.rename(columns={'Time': 'Time2'})

# Step 3: Concatenate aligned data
df_c = pd.concat([filtered_a, df_b_renamed], axis=1)

Result:

Time  Reading 1  R2  R3 Time2  Sensor1  S2  S3
0  1:20          1   3   4  1:20        1   5   2
1  1:22          6   2   1  1:22        3   8   4
2  1:23          7   8   9  1:23        2   4   7
3   NaN        NaN NaN NaN  1:24        5   6   8

This is the most efficient method because it leverages pandas' fast boolean indexing and avoids modifying existing rows with shifts—ideal for large datasets.

Method 2: Fix Existing Merged DataFrame (Using Shift) #

If you already have the initial merged DataFrame (like your Dataframe C), you can use vectorized shifts to fix mismatched rows without loops:

First, create the initial merged DataFrame:

# Create initial merged DataFrame C
df_b_renamed = df_b.rename(columns={'Time': 'Time2'})
df_c_initial = pd.concat([df_a, df_b_renamed], axis=1)

Then fix the mismatched rows:

import numpy as np

# Identify rows where Time from A doesn't match Time2 from B
mismatch_mask = df_c_initial['Time'] != df_c_initial['Time2']

# Define columns from DataFrame A to fix
left_cols = ['Time', 'Reading 1', 'R2', 'R3']

# Replace mismatched rows with the next row's values (vectorized shift)
df_c_initial[left_cols] = np.where(
    mismatch_mask[:, np.newaxis],  # Reshape mask to match column dimensions
    df_c_initial[left_cols].shift(-1),
    df_c_initial[left_cols]
)

# Drop the last row (since its values were shifted up to the previous mismatched row)
# Skip this if you want to keep the last row with NaNs instead
df_c_final = df_c_initial.iloc[:-1]

Result after dropping the last row:

Time  Reading 1  R2  R3 Time2  Sensor1  S2  S3
0  1:20          1   3   4  1:20        1   5   2
1  1:22          6   2   1  1:22        3   8   4
2  1:23          7   8   9  1:23        2   4   7

If you want to keep the 4th row (with Time2=1:24), skip the iloc[:-1] step—you'll get NaN values in the left columns for that row, which matches the intent of removing mismatched content.

Why This Is Efficient #

Both methods use pandas' built-in vectorized operations, which are implemented in C under the hood—way faster than looping through rows manually, even for large datasets. Method 1 is slightly more efficient because it avoids creating the initial mismatched dataset, but Method 2 works if you already have that merged DataFrame.

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