Working with Time Series Data in Python

Transactional data, especially financial trading data, most often than not is identified by a timestamp. The format of timestamp varies with system that generates the data.

In this post, we discuss how to import a time series data into a python pandas data frame, parse the time stamp into a python understandable Datetime object, assign the Datetime object as index , and perform complex computations using Datetime index.

Let’s get started !

The raw data may be downloaded from the link . This is hourly data for cryptocurrency trades

Import the csv file into a python Data frame as below

By default, the Date column gets imported as a string type, we transform date from string type to Pandas datetime object .

Once Date column has been transformed, we set the Date column (now a Datetime object) as index of the Data frame

It is always good to know what you’re working with . Check .

Subset data based on datetime index (It can’t get easier than this)

Descriptive statistics — Specify the date range, the column and the function that needs to be applied. All descriptive functions available in Pandas Data Frame may be applied here. In this example, I use min and max functions.

Resample -The frequency of sample data can be tuned using the dataframe.resample(‘frequency’) method. Pandas provides a variety of resampling frequency. The most commonly used are monthly, weekly, yearly, quarterly and hourly . In the below example I resample the frequency from hourly to weekly (‘W’) . I further find the weekly mean of closing price.

Apply a variety of aggregate functions to the columns in a Data Frame . In the below example I resample the frequency of data to monthly , then pass the column and the associated aggregate function as a key value pair (dictionary).

Below, at a monthly frequency- I derive the mean of the closing price, sum of volume traded, minimum of lowest price and the maximum of opening price. All it takes is a one liner !

Rolling values — Dataframe.rolling(‘timeframe’) along with the aggregate function

Below, I derive 180 days rolling sum of the volume traded, followed by 180 days rolling mean of all the values in the Data frame - df.

There are a lot many features that makes working with timestamp data in python anything but fun !

I encourage you to read about datetime library, datetime.strptime and datetime.strftime methods. I plan to come up with these topics in details in the subsequent posts.