如何使用Pandas找出不同大小DataFrame中df1独有的元素？

Hey there! Let's tackle this problem step by step. First, let's recap your dataframes for clarity so we're all on the same page:

import pandas as pd
import numpy as np

df1 = pd.DataFrame({'A':[np.nan, np.nan, np.nan, 'AAA','SSS','DDD'], 
                    'B':[np.nan,np.nan,'ciao',np.nan,np.nan,np.nan]})
df2 = pd.DataFrame({'C':[np.nan, np.nan, np.nan, 'SSS','FFF','KKK','AAA'], 
                    'D':[np.nan,np.nan,np.nan,1,np.nan,np.nan,np.nan]})

Your goal is to find all elements in df1 that don't exist anywhere in df2 (ignoring NaN values, since those are missing and not meaningful to compare). I'll share two approaches: one that completes your iterative code, and a far more efficient vectorized method for larger datasets.

Option 1: Completing your iterative approach #

If you want to stick with the loop you started, here's how to finish it—with a key optimization to speed up lookups:

# Pre-collect all non-null unique elements from df2 into a set (fast lookups!)
df2_unique_elements = set(df2.stack().dropna().unique())

# Track elements from df1 that aren't in df2
missing_elements = set()

for _, row in df1.iterrows():
    # Check each value in the row
    for val in row:
        # Skip NaNs, and add the value if it's not in df2's elements
        if pd.notna(val) and val not in df2_unique_elements:
            missing_elements.add(val)

# Convert to your desired DataFrame format
df_missing = pd.DataFrame({'Missing Elements': list(missing_elements)})
print(df_missing)

This will output:

Missing Elements
0              DDD
1             ciao

Using a set for df2_unique_elements is crucial here—set lookups are nearly instant, unlike checking against the dataframe directly every time.

Option 2: Vectorized approach (best for large data) #

Iterating row-by-row gets slow quickly with big datasets. Pandas' vectorized operations handle this in bulk, way more efficiently:

# Flatten both dataframes, drop NaNs, and get unique elements
df1_unique = df1.stack().dropna().unique()
df2_unique = df2.stack().dropna().unique()

# Find elements in df1 that aren't present in df2
missing_elements = np.setdiff1d(df1_unique, df2_unique)

# Convert to DataFrame
df_missing = pd.DataFrame({'Missing Elements': missing_elements})
print(df_missing)

This gives the exact same result but runs orders of magnitude faster for larger data. stack() flattens the dataframe into a single Series, dropna() removes missing values, unique() gets distinct entries, and np.setdiff1d() computes the difference between the two arrays.

Quick notes to keep in mind #

• NaN handling: Both approaches ignore NaN because NaN != NaN in Pandas, so comparing missing values doesn't make sense. If you do need to flag NaN as missing, you can adjust the code to count them explicitly.
• Data type consistency: If your data has mixed types (like the integer 1 in df2 and a string "1" in df1), they'll be treated as different. To fix this, convert all elements to strings first:

  df2_unique_elements = set(df2.stack().dropna().astype(str).unique())
  

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