如何用Pandas基于键对比两个DataFrame并实现指定更新逻辑？

Got it, let's translate your dictionary-based logic into clean Pandas code that behaves exactly the same way. Here's how to do it step by step:

Step 1: Read Your Data (Same as Before) #

First, we'll read the CSV and Excel files just like you did:

import pandas as pd

# Adjust file paths as needed
temp = pd.read_csv('../../test.csv')
temp2 = pd.read_excel('../../main.xlsx')

Step 2: Replicate the Dictionary Behavior with Pandas #

Your original code converts both DataFrames to dictionaries, then updates dict1 using keys from dict2 (or sets values to "Not in dict2" for missing keys). We can replicate this directly with Pandas' map() and fillna() methods, which are designed for exactly this kind of key-based lookup and replacement:

# Convert temp2 into a Series where the index is the key column (first column) and values are the second column
temp2_lookup = temp2.set_index(temp2.columns[0])[temp2.columns[1]]

# Update temp's second column:
# - For each key in temp's first column, pull the matching value from temp2_lookup
# - If the key doesn't exist in temp2, replace with "Not in dict2"
temp[temp.columns[1]] = temp[temp.columns[0]].map(temp2_lookup).fillna("Not in dict2")

How This Matches Your Original Logic #

Let's break down the correspondence to your dictionary code:

• temp2_lookup is the Pandas equivalent of your dict2: it stores all key-value pairs from temp2 in a structure optimized for fast lookups.
• The map() method replaces your loop over dict1: it iterates over every key in temp's first column, checks for a match in temp2_lookup, and returns the corresponding value (or NaN if no match exists).
• fillna("Not in dict2") replicates your else clause: it replaces all NaN values (keys missing from temp2) with the specified string.

Verify the Result #

If you want to confirm the output matches your original dict1, you can convert the updated temp back to a dictionary:

updated_dict = temp.set_index(temp.columns[0])[temp.columns[1]].to_dict()

This will be identical to the dict1 you get from your original code.

Example Test Case #

Suppose:

• temp (test.csv) has rows: A,1, B,2, C,3
• temp2 (main.xlsx) has rows: A,10, C,30, D,40

Your original code would produce dict1 = {"A":10, "B":"Not in dict2", "C":30}. The Pandas code above will update temp's second column to [10, "Not in dict2", 30], and updated_dict will match exactly.

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