Merge Pandas Dataframes on two columns

Question

I have two data frames I am trying to merge using pandas.

lu_3
Site    SurveyDate  TripDate
003L    1990-09-28  1990-06-10
065r    2008-04-03  2008-03-28

data
Site    SurveyDate  
003L    1990-09-28  
065r    2008-04-03

I am trying to merge lu_3 to populate data with a TripDate. data has been subsetted for ease of reading, but contains more columns than shown. Both data frames are using Site as the index.

I have tried:

tmp = data.merge(lu_3,left_index=True,right_index=True,how='left', on=['SurveyDate'])

But that returns a NaT for the trip date.

Checking the data types yields:

lu_3.dtypes
Out[111]: 
SurveyDate    datetime64[ns]
TripDate      datetime64[ns]
dtype: object

data.dtypes
Out[114]: 
SurveyDate        datetime64[ns]

EDIT

I have reset the indicies on data and lu_3 and tried to merge using:

tmp = tmp_data.merge(lu_3,on=['Site','SurveyDate'], how='left').set_index('Site')

But I am still getting NaT for TripDate on tmp.

check the date type of `SurveyDate` in both dataframes to ensure that they are consistent, eg. `Type(data.SurveryDate)` — Alexander, Apr 27 '16 at 23:15
Sorry, `Type(data.SurveryDate.iat[0])` will get the type of the first element in the series. — Alexander, Apr 27 '16 at 23:20
It worked for me. Which version of Pandas are you using? 0.18.0 is latest. — Alexander, Apr 27 '16 at 23:25

score 0 · Answer 1 · answered Apr 28 '16 at 01:04

I think you dont need to specify on when you are merging on index

tmp = data.merge(lu_3,left_index=True,right_index=True,how='left')

         SurveyDate_x SurveyDate_y    TripDate
Site                                      
003L   1990-09-28   1990-09-28  1990-06-10
065r   2008-04-03   2008-04-03  2008-03-28

Merge Pandas Dataframes on two columns

1 Answers1