python - join two dataframe on date column and add new column

Question

I have two dataframes.

Frame

df_stock

I want to join both on date column. So that where there is same date in df_stock, the innovationscore column value should be populated. Like in df_stock, where Date is 2020-10-19, 1 should be populated in InnovationScore column. What I need after joining is Date, Open, Close and InnovationScore columns. What I have done is.

df_stock.join(frame, lsuffix='Date', rsuffix='Date')

which results in this which is not intended. I don't want DateDate column and NAN. if not value in InnovationScore, it should be 0.

Answer 1

Use DataFrame.merge with left join and DataFrame.fillna:

df = df_stock.merge(frame, on='Date', how='left').fillna({'InnovationScore':0})

Or with Series.fillna:

df = df_stock.merge(frame, on='Date', how='left')
df['InnovationScore'] = df['InnovationScore'].fillna(0).astype(int)