多Pandas数据框关联与字典调用：实现car_id_type函数

Got it, let's break down how to build this function to get the desired result. Here's a step-by-step approach with code:

Step 1: Understand the Data Flow #

We need to link the three DataFrames through their shared keys:

• df1 ↔ df2 via plane_id
• df2 ↔ df3 via bike_id
  Once linked, we can map the car_brand from df3 to the corresponding car_id using the given dictionary, then attach this to df1.

Step 2: Implement the Function #

Here's the complete code for car_id_type:

import pandas as pd

def car_id_type(df1, df2, df3):
    # Define the car brand to ID mapping
    Car_Dictionary = {'Toyota': 4, 'Nissan': 11, 'Ford': 6, 'Honda': 2}
    
    # Merge df1 with df2 on plane_id (inner join for exact matches)
    merged_df = pd.merge(df1, df2, on='plane_id', how='inner')
    # Merge the result with df3 on bike_id
    merged_df = pd.merge(merged_df, df3, on='bike_id', how='inner')
    
    # Map car_brand to car_id using the dictionary
    merged_df['car_id'] = merged_df['car_brand'].map(Car_Dictionary)
    
    # Keep only the original columns from df1 plus car_id
    result_df = merged_df[['house_id', 'plane_id', 'car_id']]
    
    return result_df

Step 3: Test with Sample Data #

Let's verify with your sample data:

# Sample DataFrames
df1 = pd.DataFrame({'house_id': [1122], 'plane_id': [7771]})
df2 = pd.DataFrame({'plane_id': [7771], 'bike_id': [457]})
df3 = pd.DataFrame({'bike_id': [457], 'car_brand': ['Nissan']})

# Run the function
df4 = car_id_type(df1, df2, df3)
print(df4)

This will output exactly the df4 you described:

house_id  plane_id  car_id
0      1122      7771      11

Notes on Edge Cases #

• If you need to keep all rows from df1 even when there's no matching plane_id/bike_id/car_brand, change the how parameter in pd.merge to 'left'. You can then handle missing car_id values (e.g., fill with 0 or NaN) using fillna().
• If car_brand has values not in Car_Dictionary, map() will return NaN for those entries. You can add a default value using merged_df['car_id'] = merged_df['car_brand'].map(Car_Dictionary).fillna(-1) (replace -1 with your preferred default).

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