How to merge two time-series DataFrames with different time intervals
Land Your First Data Science Job
A proven roadmap to prepare for $75K+ entry-level data roles. Perfect for Data Scientist ready to level up their career.
To merge two time-series DataFrames with different time intervals in Pandas, you can use the resample
method to resample the time-series with the lower frequency to match the time-series with the higher frequency. Then, you can use the merge
method to combine the two DataFrames based on the time index. The exact method to merge depends on the type of merging you want, for example left join, right join, inner join, etc. You can also use the concat
method to concatenate the two DataFrames along the time axis.
Here's an example of how to merge two time-series DataFrames with different time intervals in Pandas:
import pandas as pd
# create the first DataFrame with daily data
df1 = pd.DataFrame({'date': ['2021-01-01', '2021-01-02', '2021-01-03'],
'data1': [10, 20, 30]})
# set the date column as the index and convert to a datetime index
df1['date'] = pd.to_datetime(df1['date'])
df1 = df1.set_index('date')
# create the second DataFrame with monthly data
df2 = pd.DataFrame({'date': ['2021-01-01', '2021-02-01', '2021-03-01'],
'data2': [100, 200, 300]})
# set the date column as the index and convert to a datetime index
df2['date'] = pd.to_datetime(df2['date'])
df2 = df2.set_index('date')
# resample the second DataFrame to daily data using the mean
df2 = df2.resample('D').mean()
# merge the two DataFrames on the date index using an inner join
merged = pd.merge(df1, df2, left_index=True, right_index=True, how='inner')
# check the result
print(merged)
This code will produce the following output:
data1 data2
date
2021-01-01 10 100.0
2021-01-02 20 NaN
2021-01-03 30 NaN
Land Your First Data Science Job
A proven roadmap to prepare for $75K+ entry-level data roles. Perfect for Data Scientist ready to level up their career.
Related Articles
Continue your learning journey with these related topics
Master Data Science in Days, Not Months 🚀
Skip the theoretical rabbit holes. Get practical data science skills delivered in bite-sized lessons – Approach used by real data scientist. Not bookworms. 📚