CODEWITHSUNDEEP
pythonpandasmergetimestamptime-series
D.Banerjee
D.Banerjee

Reputation: 91

How to merge two different dataframe with a slight difference in timestamp

I have calculated the moving average of 15 minutes from 10 second recorded data. Now I wanted to merge two timeseries data (15 minutes average and 15 minutes moving average) from different files into a new file based on the nearest timestamp.

The 15 minutes moving average data is as below. As I have calculated the moving average, the first few rows are NaN:

RecTime NO2_RAW NO2 Ox_RAW  Ox  CO_RAW  CO  SO2_RAW SO2
2019-06-03 00:00:08 NaN NaN NaN NaN NaN NaN NaN NaN
2019-06-03 00:00:18 NaN NaN NaN NaN NaN NaN NaN NaN
2019-06-03 00:00:28 NaN NaN NaN NaN NaN NaN NaN NaN
2019-06-03 00:00:38 NaN NaN NaN NaN NaN NaN NaN NaN

The 15 minute average data is shown below:

    Site    Species ReadingDateTime   Value Units   Provisional or Ratified
0   CR9       NO2   2019-03-06 00:00:00 8.2 ug m-3  P
1   CR9       NO2   2019-03-06 00:15:00 7.6 ug m-3  P
2   CR9       NO2   2019-03-06 00:30:00 5.9 ug m-3  P
3   CR9       NO2   2019-03-06 00:45:00 5.1 ug m-3  P
4   CR9       NO2   2019-03-06 01:00:00 5.2 ug m-3  P

I want a table like this:

ReadingDateTime Value   NO2_Raw NO2
2019-06-03 00:00:00         
2019-06-03 00:15:00         
2019-06-03 00:30:00         
2019-06-03 00:45:00         
2019-06-03 01:00:00

I tried to match the two dataframes with nearest time

df3 = pd.merge_asof(df1, df2, left_on = 'RecTime', right_on = 'ReadingDateTime', tolerance=pd.Timedelta('59s'), allow_exact_matches=False)

I got a new dataframe

    RecTime NO2_RAW NO2 Ox_RAW  Ox  CO_RAW  CO  SO2_RAW SO2 Site    Species ReadingDateTime Value   Units   Provisional or Ratified
0   2019-06-03 00:14:58 1.271111    21.557111   65.188889   170.011111  152.944444  294.478000  -124.600000 -50.129444  NaN NaN NaT NaN NaN NaN
1   2019-06-03 00:15:08 1.294444    21.601778   65.161111   169.955667  152.844444  294.361556  -124.595556 -50.117556  NaN NaN NaT NaN NaN NaN
2   2019-06-03 00:15:18 1.318889    21.648556   65.104444   169.842556  152.750000  294.251556  -124.593333 -50.111667  NaN NaN NaT NaN NaN NaN

But the values of df2 became NaN. Can someone please help?

Upvotes: 2

Views: 223

Answers (1)

Matts
Matts

Reputation: 1341

Assuming the minutes are correct, you could remove the seconds, and then you would be able to merge.

df.RecTime.map(lambda x: x.replace(second=0)).

You could either create a new column or replace the existing one to merge.

Upvotes: 1

Related Questions