Merged two csv file in pandas coming as NAN values

1
votes

I have two CSV files, I want to merge its based on the common column Date using pandas data frame, Below is the code what I am using for this, but after merging, CSV2 column values coming as a NAN, I don't understand the problem, please can anyone explain the issue with the code or file, Thanks

NB: Row no for both of CSV are not the same (Row no For C02 = 85 and SG_Data: 115) CSV1

import pandas as pd

df1 = pd.read_csv('C02B.csv')
df2 = pd.read_csv('S2_Imprint.csv')

cd = df1.merge(df2, how='left', on='Date')

enter image description here

CSV2 enter image description here

Outputs

output

1
pythonpandascsv

1 Answers

2
votes

There is different format of datetimes, so first parse both columns to datetimes by parse_dates parameter:

df1 = pd.read_csv('C02B.csv', parse_dates=['Date'])
df2 = pd.read_csv('S2_Imprint.csv', parse_dates=['Date'], dayfirst=True)

And then remove times by Series.dt.floor to new column used for merging:

df1['Date1'] = df1['Date'].dt.floor('d')
d = df1.merge(df2, how='left', left_on='Date1', right_on='Date')