在Pandas中对比df1与df2：匹配集装箱号及指定金额字段

I'll walk you through how to solve this problem where you need to match entries in df1 and df2 based on two key rules:

1. The Cntr No must be an exact match between both DataFrames.
2. df1's Total value must match any of three columns in df2: Labour Cost, Material Cost, or Amount in Estimate Currency.

Step-by-Step Solution #

1. Merge the DataFrames on Cntr No

First, we'll combine the two DataFrames using an inner join on Cntr No—this keeps only rows where the container number exists in both tables.

import pandas as pd

# Sample data (based on your example)
df1 = pd.DataFrame({
    'Cntr No': ['OOLU 3868088', 'OOLU 3868088', 'TRIU 0625840', 'TRIU 1234567'],
    'Total': [28, 50, 100, 75]
})

df2 = pd.DataFrame({
    'Cntr No': ['OOLU 3868088', 'TRIU 0625840'],
    'Labour Cost': [28, 90],
    'Material Cost': [35, 100],
    'Amount in Estimate Currency': [40, 110]
})

# Merge on Cntr No
merged_df = df1.merge(df2, on='Cntr No', how='inner')

2. Apply the Value Match Condition

Next, we'll create a boolean condition to check if Total matches any of the three cost columns in df2. We use the | operator (logical OR) to combine the checks.

# Define the match condition
match_condition = (
    merged_df['Total'] == merged_df['Labour Cost']
) | (
    merged_df['Total'] == merged_df['Material Cost']
) | (
    merged_df['Total'] == merged_df['Amount in Estimate Currency']
)

# Filter to get only matching rows
matched_rows = merged_df[match_condition]

3. (Optional) Get Matching Rows from df1 Only

If you just want the rows from df1 that have a valid match in df2, you can use this approach instead:

df1_matched = df1[
    df1.apply(
        lambda row: df2[df2['Cntr No'] == row['Cntr No']].apply(
            lambda df2_row: row['Total'] in [df2_row['Labour Cost'], df2_row['Material Cost'], df2_row['Amount in Estimate Currency']],
            axis=1
        ).any(),
        axis=1
    )
]

Example Output #

For the sample data above, matched_rows will include:

• The first row of df1 (OOLU 3868088, Total=28) because it matches Labour Cost in df2.
• The third row of df1 (TRIU 0625840, Total=100) because it matches Material Cost in df2.

Notes #

• If your data has NaN values in the cost columns, they won't interfere with the match (since NaN == any_value returns False).
• For large datasets, the merge approach is more efficient than using apply with lambda functions, as it leverages pandas' vectorized operations.

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