Translate SQL Grouping Sets to Python

How to handle the multi group by of PostgreSQL 9.5 in Pandas

Image generated by author with Dall-E

After understanding how grouping sets exactly works, we will see how to write a Python function to do it, using Pandas

SQL : Grouping set

The super useful multi grouping of Postgres 9.5. It creates the union of different grouping. Let’s create a table for our example :

— create a table
CREATE TABLE students (
 name Text Not null,
 class TEXT NOT NULL,
 gender TEXT NOT NULL,
 grade INTEGER NOT NULL
 
);
 — insert some values
INSERT INTO students VALUES ( 'Pierre',1,1,15);
INSERT INTO students VALUES ( 'Paul',2,1,15);
INSERT INTO students VALUES ( 'Jack',1,1,14);
INSERT INTO students VALUES ( 'Marie',1,2,12);
INSERT INTO students VALUES ( 'Lea',2,2,18);
INSERT INTO students VALUES ( 'Nath',2,2,10);

by author

Objective: Calculate the average per class, and the average per gender.

If we want to calculate in the same table, the average of the grades per class and per gender, we could calculate the average per class in one table, the average per gender in another one, then join both…