A Full Method To Describe Time Series Windows, Resampler OHLC

A step by step guide to use pandas in exploratory data analysis


If you want to organize a time series data into windows and see the boundary values (open, high, low and close) of every window in one place, then Resample OHLC is a good choice.

It’s similar to how you see the stock prices at the stock exchange.

Developer Notes

Resample ohlc function reorganizes the time series data into specified intervals. Each window contains a list of records. The ohlc function identifies the open & close values by looking the timestamp of the records. And identifies the high and low values by looking at the value of the records. Finally, it describes each window using four standard keys – open, high, low & close.

Let’s see it in detail with an example. Implemented this module for a retail client who runs in-store and e-commerce.

Business Case

Our sales database contains the total number of products sold on a day. The business analysts study the sales pattern in order to understand how the inventory is moving. Accordingly they will advise the pricing and procurement teams.

This study involves looking at the sales data from the perspective of various frequencies. For example, how the perishable goods are moving on a scale of 3 day window. Likewise, what is the price bandwidth for groceries week on week? Similarly, how is the price fluctuating for personal care products on a fortnight window, etc.

For that, the business analysts would need a function that instantly reorganizes the data into time intervals (groups) and tells the open/close and high/low value during that period.

Let’s see how elegantly we can solve it using the Resampler OHLC method.

Solution

  1. Firstly, read the source dataset. In summary, the dataset contains the sales details for 20 days. Each record showing the day of transaction and the total number of Liquid Handwash & Moisturizer products sold on that day.
  1. Now that, we have to resample this dataset at a frequency of 5 days and call the ohlc function.

That’s it, without delay we got the required table instantly. This table clearly shows how the inventory is moving for each product on the specified scale.

Analysis: On the first day 200 units of Liquid Handwash were sold and that’s the lowest count of the week as well. The highest count was 346 and on the last day of the week, 294 units were sold. The demand was growing and started declining in the next week. Every week, we were able to meet the 300 units milestone for at least one day.

Result

We have reorganized the time series dataset into a specified frequency. Have described the distribution of values inside each window in a more readable format. Supported the business analysts in making more informed decisions related to the inventory movement like pricing and procurement.

What Next?

If you would like to share your business case on how you’ve used this method, please send the details to pub@additionalsheet.com. Our editorial team shall get back to you in adding it to this publication. Please contribute to the integration of knowledge towards building a new generation of fast learners. An example speaks a thousand paragraphs.

Also Read

An Excellent Method for Instant Data Transformation, DF GroupBy Transform

How is GroupBy Rank used in Pricing Intelligence?

Write a Comment

Comment