Join column from other dataframe in pandas

Question

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

could you paste the output of d1.head() and d2.head() to illustrate the data contained in the two dataframes — Haleemur Ali
– Haleemur Ali, Commented Mar 8, 2018 at 15:34

cs95 · Accepted Answer · 2018-03-08 15:39:51Z

4

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'
)

edited Mar 8, 2018 at 15:39

cs95

406k106 gold badges744 silver badges797 bronze badges

answered Mar 8, 2018 at 15:37

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

cs95 Over a year ago

All about that aesthetic.

Ala Głowacka Over a year ago

I get error: "ValueError: left keys must be sorted".Both dates are datatimes

BENY Over a year ago

@AlaGłowacka using sort_values before merge

Collectives™ on Stack Overflow

Join column from other dataframe in pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related