How To Resample and Interpolate Your Time Series Data With Python

You may have observations at the wrong frequency.

Maybe they are too granular or not granular enough. The Pandas library in Python provides the capability to change the frequency of your time series data.

In this tutorial, you will discover how to use Pandas in Python to both increase and decrease the sampling frequency of time series data.

After completing this tutorial, you will know:

  • About time series resampling, the two types of resampling, and the 2 main reasons why you need to use them.
  • How to use Pandas to upsample time series data to a higher frequency and interpolate the new observations.
  • How to use Pandas to downsample time series data to a lower frequency and summarize the higher frequency observations.

Let’s get started.

Update Dec/2016: Fixed definitions of upsample and downsample.

How To Resample and Interpolate Your Time Series Data With Python

How To Resample and Interpolate Your Time Series Data With Python
Photo by sung ming whang, some rights reserved.

Resampling

Resampling involves changing the frequency of your time series observations.

Two types of resampling are:

  1. Upsampling: Where you increase the frequency of the samples, such as from minutes to seconds.
  2. Downsampling: Where you decrease the frequency of the samples, such as from days to months.

In both cases, data must be invented.

In the case of upsampling, care may be needed in determining how the fine-grained observations are calculated using interpolation. In the case of downsampling, care may be needed in selecting the summary statistics used to calculate the new aggregated values.

There are perhaps two main reasons why you may be interested in resampling your time series data:

  1. Problem Framing: Resampling may be required if your data is available at the same frequency that you want to make predictions.
  2. Feature Engineering: Resampling can also be used to provide additional structure or insight into the learning problem for supervised learning models.

There is a lot of overlap between these two cases.

For example, you may have daily data and want to predict a monthly problem. You could use the daily data directly or you could downsample it to monthly data and develop your model.

A feature engineering perspective may use observations and summaries of observations from both time scales and more in developing a model.

Let’s make resampling more concrete by looking at a real dataset and some examples.

Stop learning Time Series Forecasting the slow way!

Take my free 7-day email course and discover data prep, modeling and more (with sample code).

Click to sign-up and also get a free PDF Ebook version of the course.

Start Your FREE Mini-Course Now!

Shampoo Sales Dataset

This dataset describes the monthly number of sales of shampoo over a 3 year period.

The units are a sales count and there are 36 observations. The original dataset is credited to Makridakis, Wheelwright, and Hyndman (1998).

Below is a sample of the first 5 rows of data, including the header row.

Below is a plot of the entire dataset taken from Data Market.

Shampoo Sales Dataset

Shampoo Sales Dataset

The dataset shows an increasing trend and possibly some seasonal components.

Download and learn more about the dataset here.

Load the Shampoo Sales Dataset

Download the dataset and place it in the current working directory with the filename “shampoo-sales.csv“.

The timestamps in the dataset do not have an absolute year, but do have a month. We can write a custom date parsing function to load this dataset and pick an arbitrary year, such as 1900, to baseline the years from.

Below is a snippet of code to load the Shampoo Sales dataset using the custom date parsing function from read_csv().

Running this example loads the dataset and prints the first 5 rows. This shows the correct handling of the dates, baselined from 1900.

We also get a plot of the dataset, showing the rising trend in sales from month to month.

Plot of the Shamoo Sales Dataset

Plot of the Shampoo Sales Dataset

Upsample Shampoo Sales

The observations in the Shampoo Sales are monthly.

Imagine we wanted daily sales information. We would have to upsample the frequency from monthly to daily and use an interpolation scheme to fill in the new daily frequency.

The Pandas library provides a function called resample() on the Series and DataFrame objects. This can be used to group records when downsampling and making space for new observations when upsampling.

We can use this function to transform our monthly dataset into a daily dataset by calling resampling and specifying the preferred frequency of calendar day frequency or “D”.

Pandas is clever and you could just as easily specify the frequency as “1D” or even something domain specific, such as “5D.” See the further reading section at the end of the tutorial for the list of aliases that you can use.

Running this example prints the first 32 rows of the upsampled dataset, showing each day of January and the first day of February.

We can see that the resample() function has created the rows by putting NaN values in the new values. We can see we still have the sales volume on the first of January and February from the original data.

Next, we can interpolate the missing values at this new frequency.

The Series Pandas object provides an interpolate() function to interpolate missing values, and there is a nice selection of simple and more complex interpolation functions. You may have domain knowledge to help choose how values are to be interpolated.

A good starting point is to use a linear interpolation. This draws a straight line between available data, in this case on the first of the month, and fills in values at the chosen frequency from this line.

Running this example, we can see interpolated values.

Looking at a line plot, we see no difference from plotting the original data as the plot already interpolated the values between points to draw the line.

Shamoo Sales Interpolated Linear

Shampoo Sales Interpolated Linear

Another common interpolation method is to use a polynomial or a spline to connect the values.

This creates more curves and can look more natural on many datasets. Using a spline interpolation requires you specify the order (number of terms in the polynomial); in this case, an order of 2 is just fine.

Running the example, we can first review the raw interpolated values.

Reviewing the line plot, we can see more natural curves on the interpolated values.

Shamoo Sales Interpolated Spline

Shampoo Sales Interpolated Spline

Generally, interpolation is a useful tool when you have missing observations.

Next, we will consider resampling in the other direction and decreasing the frequency of observations.

Downsample Shampoo Sales

The sales data is monthly, but perhaps we would prefer the data to be quarterly.

The year can be divided into 4 business quarters, 3 months a piece.

Instead of creating new rows between existing observations, the resample() function in Pandas will group all observations by the new frequency.

We could use an alias like “3M” to create groups of 3 months, but this might have trouble if our observations did not start in January, April, July, or October. Pandas does have a quarter-aware alias of “Q” that we can use for this purpose.

We must now decide how to create a new quarterly value from each group of 3 records. A good starting point is to calculate the average monthly sales numbers for the quarter. For this, we can use the mean() function.

Putting this all together, we get the following code example.

Running the example prints the first 5 rows of the quarterly data.

We also plot the quarterly data, showing Q1-Q4 across the 3 years of original observations.

Shamoo Sales Upsampled Quarterly

Shampoo Sales Downsampled Quarterly

Perhaps we want to go further and turn the monthly data into yearly data, and perhaps later use that to model the following year.

We can downsample the data using the alias “A” for year-end frequency and this time use sum to calculate the total sales each year.

Running the example shows the 3 records for the 3 years of observations.

We also get a plot, correctly showing the year along the x-axis and the total number of sales per year along the y-axis.

Shamoo Sales Upsampled Yearly Sum

Shampoo Sales Downsampled Yearly Sum

Further Reading

This section provides links and further reading for the Pandas functions used in this tutorial.

Summary

In this tutorial, you discovered how to resample your time series data using Pandas in Python.

Specifically, you learned:

  • About time series resampling and the difference and reasons between downsampling and upsampling observation frequencies.
  • How to upsample time series data using Pandas and how to use different interpolation schemes.
  • How to downsample time series data using Pandas and how to summarize grouped data.

Do you have any questions about resampling or interpolating time series data or about this tutorial?
Ask your questions in the comments and I will do my best to answer them.

Want to Develop Time Series Forecasts with Python?

Introduction to Time Series Forecasting With Python

Develop Your Own Forecasts in Minutes

...with just a few lines of python code

Discover how in my new Ebook:
Introduction to Time Series Forecasting With Python

It covers self-study tutorials and end-to-end projects on topics like:
Loading data, visualization, modeling, algorithm tuning, and much more...

Finally Bring Time Series Forecasting to
Your Own Projects

Skip the Academics. Just Results.

Click to learn more.

40 Responses to How To Resample and Interpolate Your Time Series Data With Python

  1. Alex December 28, 2016 at 10:03 pm #

    Kinda feel like you inverted upsampling and downsampling.

    https://en.wikipedia.org/wiki/Upsampling
    https://en.wikipedia.org/wiki/Decimation_(signal_processing)

  2. David January 12, 2017 at 1:49 am #

    Hi,

    in the upsample section, why did you write

    upampled = series.resample(‘D’).mean()

    (by the way, I assume it is _upsampled_, not upampled). I don’t understand why you need to put the mean if you are inserting NaNs. Wouldn’t it be sufficient just to write series.resample(‘D’)?

    • Jason Brownlee January 12, 2017 at 9:34 am #

      Hi David,

      You are right, I’ve fixed up the examples.

      • David January 13, 2017 at 3:29 am #

        Hello,

        I think it is necessary to add “asfreq()”, i.e.:

        upsampled = series.resample(‘D’).asfreq()

        because in new versions of pandas resample is just a grouping operation and then you have to aggregate functions.

      • Hamid September 19, 2018 at 8:22 am #

        Hello Jason,

        You didn’t!

  3. Carmen Mardiros January 27, 2017 at 2:08 am #

    Jason, I have what’s hopefully a quick question that was prompted by the interpolation example you’ve given above.

    I’ve been tasked with a monthly forecasting analysis. My original data is daily. If I aggregate it to month-level, this gives me only 24 usable observations so many models may struggle with that. It feels like I should be able to make more use of my richer, daily dataset for my problem.

    I have heard somewhere (but can’t remember where or whether I imagined it!) that a workaround is to create “fake” monthly data by creating rolling sums say from 26th Dec to 26th January. So for December I would have 31 “fake months”, one starting on each day of December and ending on the corresponding day number in January. Is this a valid workaround for artificially increasing sample size in short time series for training models? I can see straight off the bat that autocorrelation is a massive issue but is it worth exploring or have I just dreamt that up.

    Are there any other workarounds for working with short time series?

    Thanks!

    • Jason Brownlee January 27, 2017 at 12:11 pm #

      Hi Carmen,

      Perhaps the 24 obs provide sufficient information for making accurate forecasts.

      I would advise you to develop and evaluate a suite of different models and focus on those representations that produce effective results.

      Your idea of fake months seems useful only if it can expose more or different information to the learning algorithms not available by other means/representations.

      I’d love to hear how you go with your forecast problem.

  4. Priyanka Mehta April 11, 2017 at 4:11 pm #

    I have a timeseries data where I am using resample technique to downsample my data from 15 minute to 1 hour. The data is quite large ( values every 15 minutes for 1 year) so there are more than 30k rows in my original csv file.

    I am using:
    df[‘dt’] = pd.to_datetime(df[‘Date’] + ‘ ‘ + df[‘Time’])
    df = df.set_index(‘dt’).resample(‘1H’)[‘KWH’].first().reset_index()

    but after resampling I only get first day and last day correctly, all the intermediate values are filled with NAN. Can you help point what I might be doing wrong.

    • Jason Brownlee April 12, 2017 at 7:50 am #

      Onse resampled, you need to interpolate the missing data.

  5. Priyanka Mehta April 11, 2017 at 4:50 pm #

    This is how my data looks before resampling :
    24 01/01/16 06:00:04 4749.28 15.1 23.5 369.6 2016-01-01 06:00:04
    25 01/01/16 06:15:04 4749.28 14.7 23.5 369.6 2016-01-01 06:15:04
    26 01/01/16 06:30:04 4749.28 14.9 23.5 369.6 2016-01-01 06:30:04
    27 01/01/16 06:45:04 4749.47 14.9 23.5 373.1 2016-01-01 06:45:04
    28 01/01/16 07:00:04 4749.47 15.1 23.5 373.1 2016-01-01 07:00:04
    29 01/01/16 07:15:04 4749.47 15.2 23.5 373.1 2016-01-01 07:15:04
    … … … … … … …
    2946 31/01/16 16:30:04 4927.18 15.5 24.4 373.1 2016-01-31 16:30:04
    2947 31/01/16 16:45:04 4927.24 15.0 24.4 377.6 2016-01-31 16:45:04
    2948 31/01/16 17:00:04 4927.30 15.2 24.4 370.5 2016-01-31 17:00:04

    and this is how it looks after resampling:

    df[‘dt’] = pd.to_datetime(df[‘Date’] + ‘ ‘ + df[‘Time’])
    df = df.set_index(‘dt’).resample(‘1H’)[‘KWH’,’OCT’,’RAT’,’CO2′].first().reset_index()

    17 2016-01-01 17:00:00 4751.62 15.0 23.8 370.9
    18 2016-01-01 18:00:00 4751.82 15.1 23.6 369.2
    19 2016-01-01 19:00:00 4752.01 15.3 23.6 375.4
    20 2016-01-01 20:00:00 4752.21 14.8 23.6 370.1
    21 2016-01-01 21:00:00 4752.61 15.0 23.8 369.2
    22 2016-01-01 22:00:00 4752.80 15.2 23.7 369.6
    23 2016-01-01 23:00:00 4753.00 15.7 23.5 372.3
    24 2016-01-02 00:00:00 NaN NaN NaN NaN
    25 2016-01-02 01:00:00 NaN NaN NaN NaN
    26 2016-01-02 02:00:00 NaN NaN NaN NaN
    27 2016-01-02 03:00:00 NaN NaN NaN NaN
    28 2016-01-02 04:00:00 NaN NaN NaN NaN
    29 2016-01-02 05:00:00 NaN NaN NaN NaN
    … … … … …
    8034 2016-11-30 18:00:00 NaN NaN NaN NaN
    8035 2016-11-30 19:00:00 NaN NaN NaN NaN
    8036 2016-11-30 20:00:00 NaN NaN NaN NaN
    8037 2016-11-30 21:00:00 NaN NaN NaN NaN
    8038 2016-11-30 22:00:00 NaN NaN NaN NaN
    8039 2016-11-30 23:00:00 NaN NaN NaN NaN
    8040 2016-12-01 00:00:00 4811.96 14.8 24.8 364.3
    8041 2016-12-01 01:00:00 4812.19 15.1 24.8 376.7
    8042 2016-12-01 02:00:00 4812.42 15.1 24.7 373.1
    8043 2016-12-01 03:00:00 4812.66 15.2 24.7 372.7
    8044 2016-12-01 04:00:00 4812.89 14.9 24.7 370.9

  6. Geoff S April 26, 2017 at 7:43 pm #

    Do you really think it makes sense to take monthly sales in January of 266 bottles of shampoo, then resample that to daily intervals and say you had sales of 266 bottles on the 1st Jan, 262.125806 bottles on the 2nd Jan ?

    • Jason Brownlee April 27, 2017 at 8:40 am #

      No, it is just an example of how to use the API.

      The domain/domain experts may indicate suitable resampling and interpolation schemes.

      • Emilia July 19, 2017 at 2:35 am #

        Instead of interpolating when resampling monthly sales to the daily interval, is there a function that would instead fill the daily values with the daily average sales for the month? This would be useful for data that represent aggregated values, where the sum of the dataset should remain constant regardless of the frequency… For example, if I need to upsample rainfall data, then the total rainfall needs to remain the same. Are there built-in functions that can do this?

        • Jason Brownlee July 19, 2017 at 8:29 am #

          Sure, you can do this. You will have to write some code though.

          • thrillbo October 10, 2017 at 8:19 am #

            we just had an intern do this with rainfall data. it’s not too hard! thanks Jason for the helpful guide, this was just was i was searching for!

          • Jason Brownlee October 10, 2017 at 4:41 pm #

            Glad to hear it!

  7. Aman Garg November 18, 2017 at 5:03 am #

    Hello Jason,

    Thanks for a nice post. In my time series data, I have two feature columns i.e. Latitude and Longitude and index is datetime.

    Since these GPS coordinates are captured at infrequent time intervals, I want to resample my data in the fixed time interval bin, for example: one GPS coordinate in every 5sec time interval.

    Is there a way to do it?

    • Jason Brownlee November 18, 2017 at 10:25 am #

      Sounds like you could use a linear interpolation for time and something like linear for the spatial coordinates.

  8. Elham January 20, 2018 at 11:52 am #

    Hello Jason,

    I have a question regarding down sampling data from daily to weekly or monthly data,
    If my data is multivariate time series for example it has a categorical variables and numeric variables, how can I do the down sampling for each column automatically, is there a simple way of doing this?

    Thanks in advance

  9. Vijay V February 25, 2018 at 3:46 pm #

    Hi Jason,

    Can we use (if so, how) resampling to balance 2 unequal classes in the data? Example, in predicting stock price direction, the majority class will be “1” (price going up) and minority class will be “-1” (price going down). Problem is that the classifier may predict most or all labels as “1” and still have a high accuracy, thereby showing a bias towards the majority class.

    Is there a way to fix this?

  10. debby March 12, 2018 at 7:26 pm #

    hi im using the code below is this correct my data is a signal stored in a single row

    from scipy import signal

    resample_signal=scipy.signal.resample(x,256)
    plt.plot(resample_signal)

    • Jason Brownlee March 13, 2018 at 6:24 am #

      I don’t know. If the plot looks good to you, then yes.

  11. Adam April 7, 2018 at 6:41 am #

    Thanks! That was really helpful, but my problem is a bit different. I have data recorded at random time intervals and I need to interpolate values at 5-min timesteps, as shown below:

    Input:
    ——-
    2018-01-01 00:04 | 10.00
    2018-01-01 00:09 | 12.00
    2018-01-01 00:12 | 10.00
    2018-01-01 00:14 | 15.00
    2018-01-01 00:18 | 20.00

    The needed output:
    ————————
    2018-01-01 00:00 | 08.40
    2018-01-01 00:05 | 10.40
    2018-01-01 00:10 | 11.90
    2018-01-01 00:15 | 16.10
    2018-01-01 00:20 | 21.50

    Hope that is clear enough!
    Really appreciate your help!

    • Jason Brownlee April 7, 2018 at 6:42 am #

      You might need to read up on the resample/interpolate API in order to customize the tool for this specific case.

  12. Olivia April 12, 2018 at 5:58 am #

    Hi ! I’m trying to get a percentual comparison of CPI between two years. In this particular case, I have data with columns:
    ‘Date’ (one date per week of year, for three years)
    ‘CPI’
    and others that for this are not important.
    The thing is I have to divide each CPI by its year-ago-value. For example, if I have the CPI of week 5 year 2010, I have to divide it by CPI of week 5 year 2009.
    I’ve already managed to get the week of the year and year of each observation, but I can’t figure out how to get the observation needed, as they are both observations from the same data frame. Any help will be really appreciated.

    • Jason Brownlee April 12, 2018 at 8:50 am #

      Sorry, I’m not intimately familiar with your dataset. I don’t know how I can help exactly.

      • Olivia April 12, 2018 at 9:20 am #

        So sorry. I thought I attached a part. This is a header of the data (not sure if it will do for “intimately familiarization” but hope it does clarify):

        Date CPI
        05-02-2010 211.0963582
        12-02-2010 211.2421698
        19-02-2010 211.2891429
        26-02-2010 211.3196429
        05-03-2010 211.3501429
        12-03-2010 211.3806429
        19-03-2010 211.215635
        26-03-2010 211.0180424
        02-04-2010 210.8204499
        09-04-2010 210.6228574
        16-04-2010 210.4887
        23-04-2010 210.4391228
        30-04-2010 210.3895456

  13. Nicole April 20, 2018 at 10:30 am #

    Hello Jason,

    Thanks you for the helpful guide. I am currently working to interpolate daily stock returns from weekly returns. I know I have to keep the total cumulative return constant but I am still confused about the procedure. Could you give me some hints on how to write my function?

    Thanks

    • Jason Brownlee April 20, 2018 at 2:20 pm #

      What problem are you having exactly? Do the examples not help?

  14. Tu Nguyen May 9, 2018 at 12:43 am #

    can i solve this problem with LSTMs? and how to do that?

    • Jason Brownlee May 9, 2018 at 6:26 am #

      The LSTM can interpolate. You can train the model as a generator and use it to generate the next point given the prior input sequence.

  15. Mark June 19, 2018 at 5:37 am #

    Hi ,

    How to take care of categorical variables while re-sampling.

  16. Annie John July 26, 2018 at 5:18 pm #

    Sir, I’m regularly following your posts.It’s very informative.I really appreciate your efforts.
    Now I’m working on a dataset having 6 months of daily fuel sale data from Feb 2018 to July 2018. In that dataset one complete month data for MAY is missing. I want to forecast daily fuel sale for august month.I have no idea how to deal with 1 missing month.Shall I do analysis with feb,mar,april data only or need to interpolate data for 1 month May.
    It would be grateful if you give any suggestion on this problem.
    Thanking you in advance sir..!!

    • Jason Brownlee July 27, 2018 at 5:47 am #

      Perhaps try modeling using on one or two prior months?

      Perhaps try imputing?

      Perhaps try methods that can handle missing data, e.g. Masking in LSTMs?

  17. pridya August 1, 2018 at 6:30 am #

    Hi Jason,
    Thanks a lot for the post!. Very helpful.
    Had a question for you – I am trying to do a resampling by week for number of employees quitting the job. I have used mean() to aggregate the samples at the week level. And I am not sure how the mean is calculated in this case and why it would give me negative values. Any help here is much appreciated:

    Data before Resampling: (Index = date_series)
    date_series company year first_day_of_week date_of_attendance attrition_count week
    1/1/2018 2018 0 1
    1/2/2018 AAA 2018 12/31/2017 1/2/2018 2 1
    1/3/2018 AAA 2018 12/31/2017 1/3/2018 0 1
    1/4/2018 AAA 2018 12/31/2017 1/4/2018 0 1
    1/5/2018 AAA 2018 12/31/2017 1/5/2018 1 1
    1/6/2018 AAA 2018 12/31/2017 1/6/2018 1 1
    1/7/2018 AAA 2018 1/7/2018 1/7/2018 0 1

    Code used for Resampling:
    # Resampling to weekly frequency
    df_week = df_test.resample(‘W’).mean()

    Data after resampling:
    week year attrition_count
    1 2018 -0.554218343

    • Jason Brownlee August 1, 2018 at 7:50 am #

      That is odd, perhaps inspect the groups of data before calculating the mean to see exactly what is contributing?

Leave a Reply