Timeseries Merging

Learn how to perform timeseries friendly merging of data.

Introduction

Timeseries friendly merging refers to combining DataFrames based on time-related columns to preserve the temporal relationships between data points, which often carry essential information.

In traditional merging, the focus is on exact matches between the keys in the DataFrames. However, timeseries friendly merging allows flexible merging strategies, such as matching based on the nearest time or filling in missing values with the most recent available data.

There are two main pandas methods for this purpose which we’ll cover, namely merge_ordered() and merge_asof().

Ordered merge

The merge_ordered() method is used to perform an ordered merge of two DataFrames based on one or more columns. It’s designed for ordered data like time series data and is particularly useful when we want to merge data with irregular time intervals in a sorted manner.

Suppose we have two DataFrames storing the closing share price for the two stocks of Amazon (df_amzn) and Apple (df_aapl) but with misaligned time stamps, as illustrated below:

Press + to interact
Illustration of two sample DataFrames with different orders of elements in the DatetimeIndex
Illustration of two sample DataFrames with different orders of elements in the DatetimeIndex

We can use merge_ordered() to merge both DataFrames, as shown below:

Press + to interact
# Perform ordered merge with merge_ordered()

merged_df = pd.merge_ordered(left=df_amzn, 

                             right=df_aapl, 

                             on='index') # Merge on DatetimeIndex name (i.e., 'index')



# Reassign DatetimeIndex as index after merge

# This is because the merge_ordered() method generates a new index column

merged_df = merged_df.set_index('index')



# View output

print(merged_df)

print('=' * 60)



# View index type

print(merged_df.index)

We can see from the example above that even though the original indexes for both DataFrames weren’t aligned initially, the output DataFrame has an ordered DatetimeIndex ...

Get hands-on with 1400+ tech skills courses.