CODEWITHSUNDEEP
pythonpandas
Ala Głowacka
Ala Głowacka

Reputation: 343

Join column from other dataframe in pandas

I have got 2 dataframes: d1 columns: ['Text', 'Date', 'Date_with_affect'] - with Id as index:

89, 'Hello', 2018-03-05, 2018-03-06

d2 columns: ['Price', 'Open', 'High', 'Low', 'Vol.', 'Change'] - with Date set as index

2018-03-06, 12, 13, 14, 11, 0, 0.5

I would like to add new column to the d1 dataframe that will be a 'Change' column from d2 by "Date_with_affect" matching the "Date" from d2. So the result would be:

89, 'Hello', 2018-03-05, 2018-03-06, 0.5

And if date is not in d2 then should pick the next day. What is the easiest way to that? I tried to do that but I guess that it is completly wrong.

d1["Change"] = d2[d1["Date_with_affect"]].Change

Upvotes: 1

Views: 162

Answers (1)

BENY
BENY

Reputation: 323226

For this case I think you can use merge_asof (also, I assuming your date is datetime format. If not, try to converting it using pd.to_datetime):

pd.merge_asof(
   d1,
   d2.reset_index()[['Date','Change']],
   left_on='Date_with_affect',
   right_on='Date'
)

Upvotes: 4

Related Questions