How to merge two CSV files having same value for two column fields using python?

Question

The first CSV file.

    DATE            TIME      ENG-1  ENG-2    ENG-3  ENG-4   ENG-5   ENG-6
'01 10 2016'    '06:35:00'  0.28596 0.29029 0.28756 0.28571 0.30868 0.14109
'01 10 2016'    '06:40:00'  0.44193 0.45012 0.44324 0.44423 0.46907 0.21463
'01 10 2016'    '06:45:00'  0.62864 0.64037 0.62642 0.63543 0.66269 0.31124
'01 10 2016'    '06:50:00'  0.80956 0.83893 0.80395 0.83088 0.85561 0.39706
'01 10 2016'    '06:55:00'  1.03745 1.06965 1.03274 1.06828 1.09404 0.51961
'01 10 2016'    '07:00:00'  1.27753 1.32139 1.27205 1.31855 1.3468  0.64307
'01 10 2016'    '07:05:00'  1.47166 1.52537 1.45165 1.52041 1.54714 0.74423

The Second CSV file

   DATE            TIME     A    B  C   D
'01 10 2016'    '06:00:00'  27  74  0   4
'01 10 2016'    '06:30:00'  27  74  1.9 4
'01 10 2016'    '07:00:00'  27  74  0   4
'01 10 2016'    '07:30:00'  28  70  7.4 4
'01 10 2016'    '08:00:00'  28  70  9.3 4

I want to combine these two CSV into the third CSV file only taking those value for which the Date and Time value matches.

The Resultant combined CSV file should be like this.

    DATE            TIME      ENG-1    ENG-2    ENG-3  ENG-4   ENG-5   ENG-6   A    B   C   D
 '01 10 2016'    '07:00:00'  1.27753 1.32139 1.27205 1.31855 1.3468  0.64307  27   74   0   4

jezrael · Answer 1 · 2017-08-07T06:51:48.083

You need merge with default inner join with parameter on:

df = pd.merge(df1, df2, on=['DATE','TIME'])
print (df)
           DATE        TIME    ENG-1    ENG-2    ENG-3    ENG-4   ENG-5  \
0  '01 10 2016'  '07:00:00'  1.27753  1.32139  1.27205  1.31855  1.3468   

     ENG-6   A   B    C  D  
0  0.64307  27  74  0.0  4

If only DATE and TIME columns (for join) are in both DataFrames and all another columns names are different, on can be omit too:

df = pd.merge(df1, df2)
print (df)
           DATE        TIME    ENG-1    ENG-2    ENG-3    ENG-4   ENG-5  \
0  '01 10 2016'  '07:00:00'  1.27753  1.32139  1.27205  1.31855  1.3468   

     ENG-6   A   B    C  D  
0  0.64307  27  74  0.0  4

Also is necessary same dtypes of columns for join, else no match.

How to merge two CSV files having same value for two column fields using python?

1 Answers1