如何用Pandas比较两个DataFrame并标记新增/删除/变更值至新列

Got it, let's break down how to get the status labels you need. The key here is to first merge on the identifying columns (userid, first_name, last_name) instead of including title—this lets us spot when a user's title has changed between the two DataFrames.

Step 1: Set Up Your Sample DataFrames #

First, let's recreate the DataFrames you provided:

import pandas as pd
import numpy as np

df1 = pd.DataFrame({
    'userid': ['u1', 'u2', 'u4'],
    'first_name': ['user1', 'user2', 'user4'],
    'last_name': ['test', 'test', 'test'],
    'title': ['manager', 'creator', 'Analysts']
})

df2 = pd.DataFrame({
    'userid': ['u1', 'u2', 'u3'],
    'first_name': ['user1', 'user2', 'user2'],
    'last_name': ['test', 'test', 'test'],
    'title': ['designer', 'creator', 'creator']
})

Step 2: Perform Outer Merge with Indicator #

We'll do an outer join on the user identifiers, keep both title columns (with suffixes to tell them apart), and add the merge indicator column to track which DataFrame each row comes from:

merged_df = pd.merge(
    df1,
    df2,
    how='outer',
    on=['userid', 'first_name', 'last_name'],
    suffixes=('_df1', '_df2'),
    indicator=True
)

Step 3: Calculate Status Labels #

Now we can use the merge indicator and title columns to categorize each row:

• Deleted: Rows only present in df1 (left_only)
• Added: Rows only present in df2 (right_only)
• Changed: Rows present in both, but titles differ
• Unchanged: Rows present in both, titles are identical

We can use vectorized operations (faster than apply for large datasets) to compute this:

merged_df['status'] = np.where(
    merged_df['_merge'] == 'left_only',
    'Deleted',
    np.where(
        merged_df['_merge'] == 'right_only',
        'Added',
        np.where(
            merged_df['title_df1'] != merged_df['title_df2'],
            'Changed',
            'Unchanged'
        )
    )
)

Final Result #

If you print merged_df, you'll get:

userid first_name last_name title_df1 title_df2      _merge    status
0     u1      user1       test    manager   designer        both    Changed
1     u2      user2       test    creator    creator        both  Unchanged
2     u4      user4       test   Analysts       NaN   left_only    Deleted
3     u3      user2       test       NaN    creator  right_only      Added

You can drop the _merge column if you don't need it anymore with merged_df.drop('_merge', axis=1, inplace=True).

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