Combine the data based on common index with dates

Question

I have two data frame, first named df1 having data as:

                 Date  Exact   Latitude  Longitude
0 1993-01-01 00:00:00    0.0  29.456137  85.506958
1 2017-10-01 05:00:00    0.0  27.694225  85.291702
2 2017-10-01 06:00:00    0.0  28.962729  80.912323
3 2017-10-02 05:00:00    0.0  27.699097  85.299431
4 2017-10-03 04:00:00    0.0  27.700438  85.329933

and df2 as

               Date (LT)  Raw Conc.
6551 2017-10-01 00:00:00       10.0
6552 2017-10-01 01:00:00        7.0
6553 2017-10-01 02:00:00       11.0
6554 2017-10-01 03:00:00       11.0
6555 2017-10-01 04:00:00       12.0
6556 2017-10-01 05:00:00        9.0
6557 2017-10-01 06:00:00        7.0
6558 2017-10-01 07:00:00        7.0

I want to merge the two dataframe based on common date and time in columns column Date and Date(LT) so that the final output is like:

Date                  Exact            Raw Conc.
2017-10-02 05:00:00    0.0               9.0

I tried to merge it by using the date of both data frame as an index by using df1.set_index('Date'). But, when I tried to check the index by using df1.index, it had not been used as an index. I also tried using :

newfile=df1.loc[(df1.Date==df2.Date)]

It gives me error:

ValueError: Can only compare identically-labeled Series objects

What is it I am missing ?

You will find the answer to your question by clicking on the duplicate target link and doing a ctrl+F search for "Avoiding duplicate key column in output". — cs95, Dec 11 '18 at 01:29

score 1 · Accepted Answer · answered Dec 11 '18 at 00:32

1

You can merge using the aptly named merge method:

>>> df1[['Date','Exact']].merge(df2.rename(columns={'Date (LT)':'Date'}))
                  Date  Exact  Raw Conc.
0  2017-10-01 05:00:00    0.0        9.0
1  2017-10-01 06:00:00    0.0        7.0

I selected only Date and Exact from your df1, and renamed the column Date (LT) to Date in df2 to replicate your desired output

answered Dec 11 '18 at 00:32

sacuL

49,704
8
81
106

1

_Most_ questions involving merge (including this one) can now be hammered as duplicate of [Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101). Encouraging you and everyone else to do so ;-) – cs95 Dec 11 '18 at 01:30

Combine the data based on common index with dates

1 Answers1