如何用Python找出两个Pandas DataFrame的列名差异

I need to find the columns that exist in one Pandas DataFrame but are missing from another—kind of like using EXCEPT in SQL to compare column sets. Here's my sample code to illustrate the scenario:

Sample DataFrame 1 (df1):

import pandas as pd

d1 = {
    'row_num': [1, 2, 3, 4, 5],
    'name': ['john', 'tom', 'bob', 'rock', 'jimy'],
    'DoB': ['01/02/2010', '01/02/2012', '11/22/2014', '11/22/2014', '09/25/2016'],
    'Address': ['NY', 'NJ', 'PA', 'NY', 'CA']
}
df1 = pd.DataFrame(data=d1)
df1['month'] = pd.DatetimeIndex(df1['DoB']).month
df1['year'] = pd.DatetimeIndex(df1['DoB']).year

Sample DataFrame 2 (df2):

d2 = {
    'row_num': [1, 2, 3, 4, 5],
    'name': ['john', 'tom', 'bob', 'rock', 'jimy'],
    'DoB': ['01/02/2010', '01/02/2012', '11/22/2014', '11/22/2014', '09/25/2016'],
    'Address': ['NY', 'NJ', 'PA', 'NY', 'CA']
}
df2 = pd.DataFrame(data=d2)

In this case, df2 is missing the month and year columns that are present in df1. How can I replicate the behavior of SQL's EXCEPT to find these missing columns using Pandas/Python?

Method 1: Use Set Operations (Most Intuitive) #

Since DataFrame column names are stored as an Index object (which acts like a set in many ways), you can directly use set subtraction to get the columns in df1 that aren't in df2:

missing_columns = set(df1.columns) - set(df2.columns)
# Convert to a sorted list for better readability
missing_columns_list = sorted(set(df1.columns) - set(df2.columns))
print(missing_columns_list)  # Output: ['month', 'year']

This is clean and straightforward—exactly analogous to how EXCEPT works in SQL for comparing sets of values.

Method 2: Use Pandas' Index.difference() #

Pandas Index objects have a built-in difference() method that does exactly what we need: it returns the elements in the first index that aren't in the second.

missing_columns = df1.columns.difference(df2.columns)
# Convert to a list if you need it in that format
missing_columns_list = list(missing_columns)
print(missing_columns_list)  # Output: ['month', 'year']

This method is Pandas-native, so it plays nicely with other Pandas operations if you need to chain logic later.

Method 3: Filter with a List Comprehension #

If you prefer a more explicit, readable approach, you can filter df1's columns by checking which ones aren't present in df2:

missing_columns = [col for col in df1.columns if col not in df2.columns]
print(missing_columns)  # Output: ['month', 'year']

This works great if you want to add extra conditions down the line—like checking column data types alongside presence.

All three methods will give you the same result here—pick the one that fits your coding style best!

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