Lukasz Lukasz - 2 months ago 11
Python Question

Merging two dataframes only at specific times

I have two excel files that I'm trying to merge into one using

pandas
. The first file is a list of times and dates with a subscriber count for that given time and day. The second file has weather information on an hourly basis. I import both files and the data resembles:

df1=

Date Count
2010-01-02 09:00:00 15
2010-01-02 10:00:00 8
2010-01-02 11:00:00 9
2010-01-02 12:00:00 11
2010-01-02 13:00:00 8
2010-01-02 14:00:00 10
2010-01-02 15:00:00 8
2010-01-02 16:00:00 6
...

df2 =

Date Temp Rel_Hum Pressure Weather
2010-01-00 09:00:00 -5 93 100.36 Snow,Fog
2010-01-01 10:00:00 -5 93 100.36 Snow,Fog
2010-01-02 11:00:00 -6.5 91 100 Snow,Fog
2010-01-03 12:00:00 -7 87 89 Snow,Fog
2010-01-04 13:00:00 -7 87 89 Snow,Fog
2010-01-05 14:00:00 -6.7 88 89 Snow,Fog
2010-01-06 15:00:00 -6.5 89 89 Snow,Fog
2010-01-07 16:00:00 -6 88 90 Snow,Fog
2010-01-08 17:00:00 -6 89 89 Snow,Fog
...


I only need to weather info for the times that are specified in
df1
, but
df2
contains weather info for a 24 hour period for everyday of that month.

Since
df1
only contains 2 columns, I've modified
df1
to have a
Temp
Rel_Hum
Pressure
and
Weather
column so that it resembles:

Date Count Temp Rel_Hum Pressure Weather
2010-01-02 09:00:00 15 0 0 0 0
2010-01-02 10:00:00 8 0 0 0 0
2010-01-02 11:00:00 9 0 0 0 0
2010-01-02 12:00:00 11 0 0 0 0
2010-01-02 13:00:00 8 0 0 0 0
2010-01-02 14:00:00 10 0 0 0 0
2010-01-02 15:00:00 8 0 0 0 0
2010-01-02 16:00:00 6 0 0 0 0
...


I've managed to test the code that I've written for a one month period and the problem that I'm encountering is that it is taking a great deal of time to complete the task. I wanted to know if there was a faster way of going about this

import pandas as pd
import numpy as np
from datetime import datetime

location = '/home/lukasz/Documents/BUS/HOURLY_DATA.xlsx'
location2 = '/home/lukasz/Documents/BUS/Weather Data/2010-01.xlsx'

df1 = pd.read_excel(location)
df2 = pd.read_excel(location2)

df.Temp = df.Temp.astype(float)
df.Rel_Hum = df.Rel_Hum.astype(float)
df.Pressure = df.Pressure.astype(float)
df.Weather = df.Weather.astype(str)

n = len(df2) - len(df)

for i in range(len(df)):
print(df['Date'][i])
for j in range(i, i+n):
date_object = datetime.strptime(df2['Date/Time'][j], '%Y-%m-%d %H:%M') # The date column in df2 is a str
if df['Date'][i] == date_object:
df.set_value(i, 'Temp', df2['Temp'][j])
df.set_value(i, 'Dew_Point_Temp', df2['Dew_Point_Temp'][j])
df.set_value(i, 'Rel_Hum', df2['Rel_Hum'][j])
df.set_value(i, 'Pressure', df2['Pressure'][j])
df.set_value(i, 'Weather', df2['Weather'][j])

# print(df[:5])

df.to_excel(location, index=False)

Answer

Use a combination of reindex to get df2 aligned with df1. Make sure to include parameter method='ffill' to forward fill weather information.

Then use join

df1.join(df2.set_index('Date').reindex(df1.Date, method='ffill'), on='Date')

enter image description here