CODEWITHSUNDEEP
pythonpandasdataframejoin
shahid hamdam
shahid hamdam

Reputation: 821

join two dataframe on date column and add new column

I have two dataframes.

Frame

enter image description here

df_stock

enter image description here

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.

enter image description here

Upvotes: 0

Views: 245

Answers (1)

jezrael
jezrael

Reputation: 862431

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)

Upvotes: 1

Related Questions