How to Use Pandas Groupby for Calculating Rolling Sum Over the Next n-Days

Learn how to compute the rolling sum of sales over the next `n-days` with `Pandas` using groupby and rolling functions efficiently.
---
This video is based on the question https://stackoverflow.com/q/66586889/ asked by the user 'Alessandro Ceccarelli' ( https://stackoverflow.com/u/8618380/ ) and on the answer https://stackoverflow.com/a/66589802/ provided by the user 'piterbarg' ( https://stackoverflow.com/u/14551426/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Pandas groupby to compute rolling sum over the next n-days

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Unlocking the Power of Pandas: Computing Rolling Sums Over the Next n-Days

If you're working with sales data in Python, you might find yourself needing to analyze trends over certain periods. One common operation is calculating the rolling sum of sales over the next n-days. When using the popular Pandas library, this can be a bit tricky—especially when you're looking to assess future sales rather than historical data. In this post, we’ll address how to compute a rolling sum of sales over the next seven days, using an example DataFrame to illustrate the solution step by step.

The Problem

Imagine you have a DataFrame that contains sales data for your products across different business IDs. Below is a simplified version of what the DataFrame looks like:

[[See Video to Reveal this Text or Code Snippet]]

For each combination of business ID (ID_FAR) and product ID (cod_id), you want to compute the sum of sales (quantity) over the next 7 days. When you attempt this with the standard rolling functions in Pandas, you may find that it calculates rolling sums for past days instead.

The Solution

Fortunately, with a small adjustment, you can flip the index of your DataFrame in descending order and then apply the rolling function. Here is how you can achieve that:

Step 1: Setting the Index and Sorting

First, you need to set the date column as the index and then sort it in descending order. This way, when you apply the rolling function, it will look forward rather than backward.

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Applying GroupBy and Rolling Functions

Next, group the DataFrame by cod_id and ID_FAR, and apply the rolling function specifying the window as '7d'. The min_periods argument ensures that you receive values even if there aren’t enough records in that time period.

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Sorting and Reviewing Results

Finally, sort the indexed result back to its original order to make it easier to read. The output will show the rolling sum of quantity over the next 7 days for each combination of cod_id and ID_FAR.

Here's what the final output looks like:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

In this guide, we’ve successfully computed the rolling sum of sales over the next n-days using Pandas. The key takeaway is to flip the index to descending order before using the rolling function, allowing you to look ahead rather than behind. Knowing how to manipulate your data effectively will empower you to draw valuable insights from it. Happy coding!

Видео How to Use Pandas Groupby for Calculating Rolling Sum Over the Next n-Days канала vlogize