I want to merge two csv files. However I want to select few columns of one csv file and merge it with another csv. My csv's are as follow
one.csv
Id,StationName,Date,Time,Temperature
1,Abc,01-01-2019,00:00:00,11.1
1,Abc,01-01-2019,00:15:00,12.1
1,Abc,01-01-2019,00:30:00,13
two.csv
Id,StationName,Date,Time,Wind,RH,Temperature
1,Abc,01-01-2019,00:00:00,2,7,11.1
1,Abc,01-01-2019,00:30:00,4,8,13
I want to merge both files based on Date and Time values.Also select few columns of two.csv file. Output file should be like this
output.csv
Id,StationName,Date,Time,Temperature,RH
1,Abc,01-01-2019,00:00:00,11.1,7
1,Abc,01-01-2019,00:15:00,12.1,NaN
1,Abc,01-01-2019,00:30:00,13,8
My python code is as follow:
import pandas as pd
import csv
# reading csv files
fields=['Date','Time','RH']
data1 = pd.read_csv('one.csv')
data2 = pd.read_csv('two.csv', usecols=fields)
# using merge function by setting how='outer'
output = pd.merge(data1, data2,on=['Date','Time'],how='outer')
output.to_csv('Final.csv')
# displaying result
print('Done')
I am not getting my desired output. Thanks for any help.
output = data1.merge(data2, on=['Date','Time'], how='outer')
and it looks to be your desired output?output=pd.merge(data1, data2[['Date','Time','RH']],on=['Date','Time'],how='left')