如何将DataFrame B的Y列按日期匹配添加至DataFrame A，无匹配项填0

To solve this problem, we'll use pandas to align DataFrame B's Y values with DataFrame A based on shared date columns, then fill any missing entries with 0. Here's a straightforward, step-by-step solution:

Step 1: Set Up Your DataFrames #

First, let's replicate your sample data in pandas (skip this if you already have your DataFrames loaded):

import pandas as pd

# DataFrame A
df_a = pd.DataFrame({
    'Year': [1990, 1991, 1992, 1993, 1994],
    'Month': ['01', '03', '04', '06', '08'],
    'Day': ['01', '02', '11', '07', '12'],
    'X': [55, 324, 56, 4, 5]
})

# DataFrame B
df_b = pd.DataFrame({
    'Year': [1990, 1991, 1992],
    'Month': ['01', '03', '04'],
    'Day': ['01', '02', '11'],
    'Y': [1, 2, 3]
})

Step 2: Perform a Left Merge #

Use a left merge to keep all rows from DataFrame A, and match rows from DataFrame B where the Year, Month, and Day columns exactly align. This will leave NaN values in the Y column for dates that don't exist in B:

merged_df = df_a.merge(df_b, on=['Year', 'Month', 'Day'], how='left')

Step 3: Fill Missing Values with 0 #

Replace any NaN entries in the Y column with 0 using fillna():

merged_df['Y'] = merged_df['Y'].fillna(0)

Optional: Convert Y to Integer Type #

If you want Y to be an integer instead of a float (since NaN converts columns to float), add this line:

merged_df['Y'] = merged_df['Y'].astype(int)

Final Result #

Printing merged_df will give you exactly the output you requested:

Year Month Day    X  Y
0  1990    01  01   55  1
1  1991    03  02  324  2
2  1992    04  11   56  3
3  1993    06  07    4  0
4  1994    08  12    5  0

内容的提问来源于stack exchange，提问作者Felipe Rincón