Pandas: Group time series data

Akshay Chavan
Mar 10 · 4 min read

In this article, we will try our hand to get a big picture view of a huge time series data. Stock market data is a good example of such a data where the data is collected for every second and can be decades long.

To get a good idea of time series data it's better to plot it, which is what I will use as an application for the code discussed in this article.

If you want to see how the stock performed in the last year it is not worth looking at data collected every hour. May be not every minute and definitely not every second. One data point per day will help us understand the highs and lows in the year.

To get a data point per day from a dataset that has points collected every second, we need to group the points collected for a day into one using a min, max or average operation.

Pandas package in python provides such a functionality to group time series data with just one parameter called frequency. Which is what we will explore in this article.

Here we will use data collected for Tesla’s stock which has been around for about 10 year. Below is the code showing, how I downloaded the data.

Python code to download historical stock data

The package used is yfinance and you can find the details here.

I will use this data to create plots similar to the ones we see on websites showing stock market trends. The plots will be for the data in the past 1 day, 5 days, 1 month, 6 months, year-to-data, 1 year, 5 years and the maximum data available.

First lets look at the Grouper class from Pandas that I will use in this article. You can find the whole documentation for this class here, however, we will focus on the parameter called freq (frequency) that will help group a column of type Datetime using specified frequencies, also known as offset aliases.

output = input.groupby(pd.Grouper(key='', freq='')).mean()

The groupby function takes an instance of class Grouper which in turn takes the name of the column key to group-by and the frequency by which to group. The freq parameter can range from nanoseconds to a year. The subset of the frequencies that we will use in this article are listed in the table below.

Data summary

The column that you group-by becomes the index. The frequency parameter can take just the character that defines the frequency or prefix it by a quantity to scale it. Example: To group day wise, you can either use D or 1D. They both are equivalent. To group by week or 7 days you can use either W or 1W or 7D.

subsetLastNDays function below is a helper function to create a subset of the after grouping.

There are 4 simple steps that I follow in the code that you will see below:
1. Read the original input data
2. Group-by and average
3. Subset the data as required
4. Save the output data

In the next part of the article you will find the code written to group the input data as per the frequencies mentioned in the table and the charts generated from the output data using Datawrapper.

One Day, One minute

The original data downloaded had data for every minute. So here I just subset the data for the last day.

One Day, One minute
One Day, One minute

Five Days, Thirty Minutes

For this case, I use the T parameter for grouping by the minutes and 30T to group 30 minutes of data into one data point.

Five Days, Thirty Minutes
Five Days, Thirty Minutes

One Month, One Day

Here we start with data collected every 30 minutes. To group by day, I used D value. You don’t need to specify 1 to group by the unit frequency.

One Month, One Day
One Month, One Day

Six Months, One Day

Here the data was collected every 1 hour, but that does not need any code change to group a day’s data.

Six Months, One Day
Six Months, One Day

Year to date, One Day

You can accomplish the one-day grouping with 24 hours as well. Here’s how.

Year to date, One Day
Year to date, One Day

One Year, One Day

And just for kicks another one day grouping using B, business days which is Monday to Friday by default.

One Year, One Day

If you happen to be located in the UAE where a typical business week is from Sunday to Thursday. You might want to use CB the custom business day option, which can be set as:

pd.offsets.CustomBusinessDay(weekmask='Sun Mon Tue Wed Thu')
One year, One Day

Five Years, One Week

One week groups can be done using 1W option.

Five Years, One Week
Five Years, One Week

Max, One Month

Lastly for a month-long group use the option M, that sets the grouped data to the last day of the month. It makes sense to set the combined data to the last day of the month to denote the average of all the days of the month.

To set the combined data to the first of the month you can use MS offset alias.

Max, One Month
Max, One Month

There are a lot of other offsets that I haven’t discussed here. I hope that this article makes it easier to work with the other frequencies.

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data…

Sign up for Analytics Vidhya News Bytes

By Analytics Vidhya

Latest news from Analytics Vidhya on our Hackathons and some of our best articles! Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Akshay Chavan

Written by

Computer Vision | Machine Learning | Deep Learning https://arccoder.github.io/

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Akshay Chavan

Written by

Computer Vision | Machine Learning | Deep Learning https://arccoder.github.io/

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store