Practical Guide to GroupBy Aggregate in Pandas: Solving a Retail Problem

In this article, you will understand two things:

  • How important is an Assortment Strategy to the Retail business and
  • How does Group By – Aggregate method solves the price range problem of assortment.

Assortment strategies refer to the plans that retailers (in-store and e-commerce) use to display the optimal product mix of their daily inventory. They are very important to maximize sales as the customers directly interact with the product mix on display and make purchase decisions based on what they see.

Photo by Christina on Unsplash

One such strategy is price range. This strategy aims to ensure that the products assorted in a particular display will all be inside a certain price range.

Customer has more chance of deciding to buy another product, if it is in the same price range of the previous product added to the cart.

Assortment strategies work hand in hand with the pricing strategies.


The client has assorted the products which are found to be sold together by the business analysts. To further increase the sales, the client wants to ensure that the products in a display will always fall into the same product range. The maximum price difference should not exceed more than 25%.

If the price exceeds the limit, the manager reduces the price. It may decrease the profit margin of that product but increase the store’s overall profitability, as the sales increase.


Our approach to the solution

  1. Get the product details including the display id and the product price.
  2. Group the products by the display id.
  3. Within each display:
    1. Get the min product price
    2. Get the max product price
    3. Calculate the price range
    4. If the price range is less than 10%, say the display is good.
    5. If the price range is above 10%, say the display needs some changes.

Technology evaluation

The Aggregate method of a Pandas GroupBy object can aggregate the values over a specified axis using some inbuilt operations. The inbuilt operations include min & max.

This technique is found to be suitable to calculate the required price range.


Read the inventory details along with the planned display unit id.

Display Unit’ Plan

Group By the Display Unit and calculate the min and max price at each display unit level.

Product Range Analysis Using Group By Aggregate

Calculate the price range from the min and max


This shows the products in the first display unit are in the required product range. The price difference is less than 25%.

But the spectrum of prices in the second display unit is too wide. Now the recommendation from this analysis would be: Reduce the price of Liquid Hand Wash B6 by $2 so that it has more chances of selling.

Also Read

Write a Comment