{"id":2946,"date":"2022-06-24T12:12:41","date_gmt":"2022-06-24T16:12:41","guid":{"rendered":"https:\/\/www.alpharithms.com\/?p=2946"},"modified":"2022-06-24T16:26:20","modified_gmt":"2022-06-24T20:26:20","slug":"aggregating-time-series-data-with-pandas-resampling","status":"publish","type":"post","link":"https:\/\/www.alpharithms.com\/aggregating-time-series-data-with-pandas-resampling-411212\/","title":{"rendered":"Aggregating Time Series Data with Pandas Resampling"},"content":{"rendered":"

Resampling time-series data into lower-resolution intervals is easy when using Pandas and Python. The resample<\/code> function, combined with the agg<\/code> function, allows developers to specify how data is resampled and to what resolution.<\/p>\n

This is useful when backtesting trading algorithms on different time periods, such as 1-minute, 5-minute, 15-minute, 4-hour, or daily periods. Aggregating time series data with pandas in this way allows multiple time resolutions to be derived from a single source. In many cases, that is the only option found in APIs or historical data archives.<\/p>\n

Highlights<\/h2>\n

In this tutorial, we will cover how to resample an example data set containing 1-minute-resolution historical pricing data for ETH courtesy of the Binance platform. After reading this article you will learn the following:<\/p>\n

    \n
  1. Use the .read_csv<\/code> function to load CSV data as a DataFrame<\/code>, while removing un-wanted columns and dropping un-wanted rows.<\/li>\n
  2. Setting the index to the date<\/code> value and sorting the index.<\/li>\n
  3. Inspecting for missing dates and replacing them with NaN values.<\/li>\n
  4. Defining a mapping for the agg<\/code> function to determine which values are to be resampled for each period and how they are to be interpreted in the process.<\/li>\n
  5. Resample the data into 5-minute, 15-minute, and 4-hour resolutions while.<\/li>\n<\/ol>\n

    This application of sampling would be useful in cases where one was backtesting a trading algorithm on different resolutions of data but only had a single resolution source from which to work.<\/p>\n

    TL;DR<\/h2>\n

    Resampling time series data can be used to turn e.g. 1-minute resolution pricing data into e.g. 4-hour pricing data. Such resampling can be done using pandas as follows:<\/p>\n

    # Example Dataframe with 1-minute OHLC pricing data for ETH via Binance\r\n                       symbol     open     high      low    close\r\ndate                                                             \r\n2021-01-01 00:01:00  ETH\/USDT   737.12   739.00   737.06   738.74\r\n2021-01-01 00:02:00  ETH\/USDT   738.78   738.81   737.35   737.82\r\n2021-01-01 00:03:00  ETH\/USDT   737.74   738.20   737.28   737.43\r\n2021-01-01 00:04:00  ETH\/USDT   737.38   737.80   736.80   736.89\r\n2021-01-01 00:05:00  ETH\/USDT   737.06   737.11   735.46   735.69\r\n...                       ...      ...      ...      ...      ...\r\n2021-12-31 23:55:00  ETH\/USDT  3679.36  3679.36  3676.28  3676.65\r\n2021-12-31 23:56:00  ETH\/USDT  3676.79  3678.54  3676.32  3676.94\r\n2021-12-31 23:57:00  ETH\/USDT  3676.94  3679.01  3676.83  3677.60\r\n2021-12-31 23:58:00  ETH\/USDT  3677.56  3677.97  3672.22  3675.10\r\n2021-12-31 23:59:00  ETH\/USDT  3675.10  3678.63  3675.09  3676.23\r\n\r\n[520542 rows x 5 columns]\r\n\r\n# Resampling data to 4-hour intervals\r\ndf = df.resample(\"4H\").agg({\r\n        \"open\": \"first\",\r\n        \"high\": \"max\",\r\n        \"low\": \"min\",\r\n        \"close\": \"last\"\r\n    })\r\n\r\n# print result\r\nprint(df)\r\n                        open     high      low    close\r\ndate                                                   \r\n2021-01-01 00:00:00   737.12   749.00   729.33   744.82\r\n2021-01-01 04:00:00   744.87   747.09   734.40   737.38\r\n2021-01-01 08:00:00   737.37   741.76   725.10   738.85\r\n2021-01-01 12:00:00   738.85   743.33   732.12   735.39\r\n2021-01-01 16:00:00   735.39   737.73   714.29   725.34\r\n...                      ...      ...      ...      ...\r\n2021-12-31 04:00:00  3742.77  3761.50  3687.00  3721.88\r\n2021-12-31 08:00:00  3721.88  3815.00  3714.00  3780.92\r\n2021-12-31 12:00:00  3780.92  3813.14  3773.44  3784.64\r\n2021-12-31 16:00:00  3784.64  3788.45  3623.00  3626.27\r\n2021-12-31 20:00:00  3626.21  3712.50  3622.29  3676.23\r\n\r\n[2190 rows x 4 columns]<\/pre>\n
    Note the agg<\/code> function requires a mapping of columns and functions to define how the resample<\/code> function is applied.<\/p>\n
    Quick Intro: Time Series Data & Resampling<\/h2>\n
    Time series data is used across many industries to record non-stationary data. Examples include weather data, eCommerce sales data, and stock market pricing data. These are all data that have values that change over time in ways that warrant analysis.<\/p>\n
    In cases where time-series data is used, there is often a stationary “period” during which data is representative of change. For weather, the period might be hourly, daily, or monthly while other industries like securities trading records may record price history at 15-minute, 5-minute, 3-minute, or even 1-minute intervals.<\/p>\n
    Time series data is often provided in raw form, as the most granular format possible. That is; if second-resolution data is available that is the source from which minute-wise data will derive. To achieve this we use resampling<\/strong> which aggregates the data from multiple periods into a longer period. For example, turning 1-minute pricing history into a 15-minute pricing history.<\/p>\n
    Project Setup: Getting Price Data & Loading as DataFrame<\/h2>\n
    The Pandas library for Python is a powerful data analysis package that makes working with spreadsheet data fast and efficient. Among its many functions, the resample method<\/a> available to the DataFrame object will help to aggregate data. To illustrate this method’s functionality we’ll load in some minute-resolution pricing data for ETH (the transactional asset of the Ethereum network<\/a>). To get started, the following steps are required:<\/p>\n
      \n
    1. Have Python installed and create a new project folder with a virtual environment.<\/li>\n
    2. Install pandas via pip install pandas<\/code><\/li>\n
    3. Download 2021 ETH minute-resolution data from CryptoDataDownload’s Asia Binance<\/a> portal (note: free account required for minute data)<\/li>\n<\/ol>\n
      With the above steps complete, we have the following project structure:<\/p>\n
      .\r\n\u2514\u2500\u2500 sample_project\/\r\n    \u2514\u2500\u2500 Binance_ETHUSDT_2021_minute.csv<\/pre>\n
      Inspection of the Binance_ETHUSDT_2021_minute.csv<\/code> file reveals the following structure:<\/p>\n
      https:\/\/www.CryptoDataDownload.com\r\nunix,date,symbol,open,high,low,close,Volume ETH,Volume USDT,tradecount\r\n1640995140000,2021-12-31 23:59:00,ETH\/USDT,3675.10000000,3678.63000000,3675.09000000,3676.23000000,128.71610000,473269.51792100,295\r\n1640995080000,2021-12-31 23:58:00,ETH\/USDT,3677.56000000,3677.97000000,3672.22000000,3675.10000000,129.70340000,476610.50063200,539\r\n1640995020000,2021-12-31 23:57:00,ETH\/USDT,3676.94000000,3679.01000000,3676.83000000,3677.60000000,95.80140000,352361.42737700,230\r\n...\r\n1609459380000,2021-01-01 00:03:00,ETH\/USDT,737.74000000,738.20000000,737.28000000,737.43000000,305.98424000,225752.74457330,251\r\n1609459320000,2021-01-01 00:02:00,ETH\/USDT,738.78000000,738.81000000,737.35000000,737.82000000,418.49987000,308922.65783000,283\r\n1609459260000,2021-01-01 00:01:00,ETH\/USDT,737.12000000,739.00000000,737.06000000,738.74000000,1122.95415000,828662.13405390,643\r\n<\/pre>\n
      Three important things to note here are:<\/p>\n
        \n
      1. There’s a URL on the first line of the file that has to be dealt with.<\/li>\n
      2. The pricing history is in descending order from newest to oldest.<\/li>\n
      3. Each row has an Open, High, Low, Close, and Date value present in 1-minute increments.<\/li>\n<\/ol>\n
        With these steps complete, and a broad awareness of our data’s format, we’ll start the process of resampling with Pandas.<\/p>\n
        Loading, Inspecting, & Fixing the Data<\/h2>\n
        Pandas offers the read_csv<\/code> function to make loading and preprocessing CSV files easy. Using this function, the following actions are taken to load the Binance_ETHUSDT_2021_minute.csv<\/code> file:<\/p>\n
          \n
        1. Specify the file.<\/li>\n
        2. Specify that the date should be parsed into a DateTime column.<\/li>\n
        3. Specify the first row (the URL) should be skipped.<\/li>\n
        4. Specify that the Date column should be used to index the data.<\/li>\n
        5. Specify which columns are needed (optional)<\/li>\n<\/ol>\n
          Loading Data<\/h3>\n
          All these steps can be done in one line as such:<\/p>\n
          import pandas as pd\r\n\r\n# Load CSV data into DataFrame\r\ndf = pd.read_csv(\r\n        'Binance_ETHUSDT_2021_minute.csv',\r\n        skiprows=1,\r\n        parse_dates=['date'],\r\n        index_col=['date'],\r\n        usecols=['date', 'open', 'high', 'low', 'close', 'symbol']\r\n    )<\/pre>\n
          At this point, we can check that our data has been ingested and converted to a DataFrame object as intended:<\/p>\n
          # print a representation of the DataFrame to the console\r\nprint(df)\r\n\r\n# Resulting output\r\n                       symbol     open     high      low    close\r\ndate                                                             \r\n2021-12-31 23:59:00  ETH\/USDT  3675.10  3678.63  3675.09  3676.23\r\n2021-12-31 23:58:00  ETH\/USDT  3677.56  3677.97  3672.22  3675.10\r\n2021-12-31 23:57:00  ETH\/USDT  3676.94  3679.01  3676.83  3677.60\r\n2021-12-31 23:56:00  ETH\/USDT  3676.79  3678.54  3676.32  3676.94\r\n2021-12-31 23:55:00  ETH\/USDT  3679.36  3679.36  3676.28  3676.65\r\n...                       ...      ...      ...      ...      ...\r\n2021-01-01 00:05:00  ETH\/USDT   737.06   737.11   735.46   735.69\r\n2021-01-01 00:04:00  ETH\/USDT   737.38   737.80   736.80   736.89\r\n2021-01-01 00:03:00  ETH\/USDT   737.74   738.20   737.28   737.43\r\n2021-01-01 00:02:00  ETH\/USDT   738.78   738.81   737.35   737.82\r\n2021-01-01 00:01:00  ETH\/USDT   737.12   739.00   737.06   738.74\r\n\r\n[520542 rows x 5 columns]<\/pre>\n
          Inspecting Data<\/h3>\n
          The only thing of note here is that our dates seem to be in reverse order<\/strong> from that which one might expect. In other words, the later<\/em> dates are ordered first. This is because our DataFrame’s index is currently\u00a0unsorted<\/em> as seen by Pandas. We can remedy this by loading our data with the sort_index()<\/code> function initially as such:<\/p>\n
          # Load data with sorted index\r\ndf = pd.read_csv(\r\n    'Binance_ETHUSDT_2021_minute.csv',\r\n    skiprows=1,\r\n    parse_dates=['date'],\r\n    index_col=['date'],\r\n    usecols=['date', 'open', 'high', 'low', 'close', 'symbol']\r\n).sort_index()\r\n\r\n# View dataframe\r\nprint(df)\r\n\r\n# Results\r\n                       symbol     open     high      low    close\r\ndate                                                             \r\n2021-01-01 00:01:00  ETH\/USDT   737.12   739.00   737.06   738.74\r\n2021-01-01 00:02:00  ETH\/USDT   738.78   738.81   737.35   737.82\r\n2021-01-01 00:03:00  ETH\/USDT   737.74   738.20   737.28   737.43\r\n2021-01-01 00:04:00  ETH\/USDT   737.38   737.80   736.80   736.89\r\n2021-01-01 00:05:00  ETH\/USDT   737.06   737.11   735.46   735.69\r\n...                       ...      ...      ...      ...      ...\r\n2021-12-31 23:55:00  ETH\/USDT  3679.36  3679.36  3676.28  3676.65\r\n2021-12-31 23:56:00  ETH\/USDT  3676.79  3678.54  3676.32  3676.94\r\n2021-12-31 23:57:00  ETH\/USDT  3676.94  3679.01  3676.83  3677.60\r\n2021-12-31 23:58:00  ETH\/USDT  3677.56  3677.97  3672.22  3675.10\r\n2021-12-31 23:59:00  ETH\/USDT  3675.10  3678.63  3675.09  3676.23\r\n\r\n[520542 rows x 5 columns]<\/pre>\n
          With the data sorted we can now inspect — which reveals all but the specified columns have been dropped, the date is now the index and is sorted, and there are 520542<\/code> entries representing 520,542 \/ 24 \/ 60 = 361.4875<\/code> days of 2021. That’s roughly 3.5 days’ worth of missing data — something of concern<\/strong>.<\/p>\n
          To check our Pandas DataFrame Timeseires for missing dates we can make use of the date_range<\/code> function combined with the difference<\/code> function as such:<\/p>\n
          # Get a list of missing dates and specify 1min frequency\r\nmissing = pd.date_range(df.index.min(), df.index.max(), freq='1min').difference(df.index)\r\n\r\n# View results\r\nprint(missing)\r\n\r\nDatetimeIndex(['2021-02-11 03:41:00', '2021-02-11 03:42:00',\r\n               '2021-02-11 03:43:00', '2021-02-11 03:44:00',\r\n               '2021-02-11 03:45:00', '2021-02-11 03:46:00',\r\n               '2021-02-11 03:47:00', '2021-02-11 03:48:00',\r\n               '2021-02-11 03:49:00', '2021-02-11 03:50:00',\r\n               ...\r\n               '2021-09-29 08:50:00', '2021-09-29 08:51:00',\r\n               '2021-09-29 08:52:00', '2021-09-29 08:53:00',\r\n               '2021-09-29 08:54:00', '2021-09-29 08:55:00',\r\n               '2021-09-29 08:56:00', '2021-09-29 08:57:00',\r\n               '2021-09-29 08:58:00', '2021-09-29 08:59:00'],\r\n              dtype='datetime64[ns]', length=5057, freq=None)<\/pre>\n
          That’s 5057<\/code> seconds of missing data or 5057 \/ 60 \/ 24 = 3.5118<\/code> days — roughly our suspected missing amount! With that knowledge, a little Googling turns up a press release from Binance<\/a> indicating that trading on September 29th, 2021 was halted due to system maintenance. Another announcement<\/a> indicates that a similar issue happened on February 11th, 2021 as well. This\u00a0excuses<\/em> the missing data but does nothing to help reconcile the missing entries in the DataFrame<\/code>.<\/p>\n
          Dealing With Missing Data<\/h3>\n
          There are many ways of dealing with missing data in Pandas<\/a>. In this case, we don’t want to interpolate values or replace any values explicitly, but it would be nice to insert\u00a0something<\/em> into the missing trade periods to indicate missing data. In this case, we’ll use the resample<\/code> function again to fill any missing values as such:<\/p>\n
          # resample data, filling the missing dates with NaN values\r\ndf = df.resample('1T').fillna(method=None)\r\n\r\n# Print result\r\n                       symbol     open     high      low    close\r\ndate                                                             \r\n2021-01-01 00:01:00  ETH\/USDT   737.12   739.00   737.06   738.74\r\n2021-01-01 00:02:00  ETH\/USDT   738.78   738.81   737.35   737.82\r\n2021-01-01 00:03:00  ETH\/USDT   737.74   738.20   737.28   737.43\r\n2021-01-01 00:04:00  ETH\/USDT   737.38   737.80   736.80   736.89\r\n2021-01-01 00:05:00  ETH\/USDT   737.06   737.11   735.46   735.69\r\n...                       ...      ...      ...      ...      ...\r\n2021-12-31 23:55:00  ETH\/USDT  3679.36  3679.36  3676.28  3676.65\r\n2021-12-31 23:56:00  ETH\/USDT  3676.79  3678.54  3676.32  3676.94\r\n2021-12-31 23:57:00  ETH\/USDT  3676.94  3679.01  3676.83  3677.60\r\n2021-12-31 23:58:00  ETH\/USDT  3677.56  3677.97  3672.22  3675.10\r\n2021-12-31 23:59:00  ETH\/USDT  3675.10  3678.63  3675.09  3676.23\r\n\r\n[525599 rows x 5 columns]\r\n\r\n# spot check dates from 'missing' to ensure NaN values added\r\nprint(df.loc['2021-02-11 03:41:00':'2021-02-11 03:50:00'])\r\n\r\n\r\n# Result\r\n                    symbol  open  high  low  close\r\ndate                                              \r\n2021-02-11 03:41:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:42:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:43:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:44:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:45:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:46:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:47:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:48:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:49:00    NaN   NaN   NaN  NaN    NaN\r\n2021-02-11 03:50:00    NaN   NaN   NaN  NaN    NaN\r\n<\/pre>\n
          Here we see a total of 525,559<\/code> values which account for 364.99 days of 2021 — much more complete than before. Then, using values taken from our missing<\/code> list, we spot-check to confirm appropriate values were added for missing data. These NaN<\/code> values would conceptually be enough for a backtesting program to indicate trading had halted whereas leaving them as missing would be much less clear.<\/p>\n
          Resampling the Data<\/h2>\n
          The data is in 1-minute resolution — there’s nothing that can be done about that. However, there are many cases such that 5-minute, 15-minute, hourly, 4-hour, daily, or even weekly data may need to be derived from greater resolution data. In this case, we will resample the 1-minute resolution data into a 5-minute, 4-hour, and daily resolution. The process will be the same for each:<\/p>\n
            \n
          1. load the 1-minute data<\/li>\n
          2. Specify a resample mapping.<\/li>\n
          3. Specify a resample interval.<\/li>\n
          4. Resample using the agg<\/code> function.<\/li>\n<\/ol>\n
            The resample mapping tells Pandas which columns should be used in our data aggregation and the functions that should be used in consideration of each. This is achieved as follows, and will be the same for each resample operation regardless of time frame:<\/p>\n
            # Defines how to aggregate pricing when downsampling OHLC\r\n    # data into a broader time period. e.g. 1-min data -> 5 min data.\r\n    price_mapping = {\r\n        \"open\": \"first\",\r\n        \"high\": \"max\",\r\n        \"low\": \"min\",\r\n        \"close\": \"last\"\r\n    }<\/pre>\n
            Our data have a measure for the open<\/code>, high<\/code>, low<\/code>, and close<\/code> price (OHLC Data<\/a>). This represents a “candlestick” for each trading period. Below is a short explanation of each:<\/p>\n