Timeseries Merging

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: