Pandas合并不同列名数据集：指定列映射并累加对应值

Solution Code #

Use pandas' vectorized operations to avoid slow row-wise loops, which is critical for large datasets:

import pandas as pd

# Sample DataFrames
df1 = pd.DataFrame({
    'DTime': ['2023-02-21 00:00:01', '2023-02-21 00:00:02', '2023-02-21 00:00:03', '2023-02-21 00:00:04'],
    'A': [0, 0, 0, 4],
    'B': [0, 1, 0, 2],
    'C': [0, 0, 2, 0]
})

df2 = pd.DataFrame({
    'DTime': ['2023-02-21 00:00:01', '2023-02-21 00:00:02', '2023-02-21 00:00:03', '2023-02-21 00:00:04'],
    'AAA': [0, 0, 0, 1],
    'BBB': [0, 1, 0, 0],
    'CC': [0, 0, 2, 0],
    'DDD': [1, 0, 0, 0],
    'EE': [0, 0, 1, 0]
})

# Step 1: Define explicit column mapping
col_mapping = {'A': 'AAA', 'B': 'BBB', 'C': 'CC'}

# Step 2: Set DTime as index to ensure automatic row alignment
df1 = df1.set_index('DTime')
df2 = df2.set_index('DTime')

# Step 3: Rename df1 columns to match df2's target columns
df1_renamed = df1[col_mapping.keys()].rename(columns=col_mapping)

# Step 4: Perform vectorized addition (fill_value=0 handles missing indices safely)
df2[col_mapping.values()] = df2[col_mapping.values()].add(df1_renamed, fill_value=0)

# Optional: Reset index to move DTime back to a column
df2 = df2.reset_index()

print(df2)

Output #

DTime  AAA  BBB  CC  DDD  EE
0  2023-02-21 00:00:01    0    0   0    1   0
1  2023-02-21 00:00:02    0    2   0    0   0
2  2023-02-21 00:00:03    0    0   4    0   1
3  2023-02-21 00:00:04    5    2   0    0   0

Key Efficiency Benefits #

• Vectorized Operations: Pandas uses optimized C-backed functions for column-level calculations, which is orders of magnitude faster than Python loops for large datasets.
• Automatic Alignment: Using DTime as the index ensures rows are matched correctly without manual row checks.
• Scalability: The approach scales linearly with dataset size, making it suitable for thousands of rows and hundreds of columns.

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