Grouping and aggregating

Awa asks herself a very simple question: how much does she sell on average in each city? She could filter one city, calculate, and start again for the next. With dozens of cities, that would take very long. Exactly, and that is why pandas offers groupby.

Imagine that she sets out one basket per city, then puts each ticket in it. That is what groupby of city does: it splits the table into groups. The square brackets then say which column to look at, here the amount. Careful, at this moment nothing has been calculated yet: the tickets are only sorted.

The mean method then calculates the average of each basket. Finally, pandas brings these results together in a single answer, with one row per city. Let's take Douala: 2300 plus 4100 plus 800 makes 7200, and 7200 divided by 3 gives 2400.

Douala comes first with an average of 2400 francs per sale. Remember these three steps, because they are always the same: split, calculate, combine. And if Awa wanted the total rather than the average, she would only have to replace mean with sum.

And if she wants the number of sales and the total at the same time? She uses the agg method. Each line gives a name to the result, then between parentheses the column and the calculation to do. For example, number equals amount, count means: count the amounts, and call this result number.

She gets a small table, with one row per city and one column per calculation. Douala has 3 sales for a total of 7200 francs. Notice that the city names have moved to the left: they now serve as the index.

To cross two questions, the pivot_table method presents the result as a grid: the cities in rows, the items in columns, and the total sales in each cell. In the code, index says what goes in the rows, columns what goes in the columns, values the column to calculate, and aggfunc the calculation to do. An empty cell here means that this item was never sold in this city: Garoua only sold soap.

Sometimes, the information is in two files. The first says how many items were sold, and the second, the catalog, gives the price of each item.

The merge method joins them. With on equals item, pandas looks for the same item in the catalog for each row, and it adds its price on the same row. The item column is the link between the two tables, like a name that appears on two different lists.

All that is left is to multiply the quantity by the price. For the soap, 3 times 800 gives 2400.

In the lesson, ask your own questions of a real dataset, without watching the video again. And if you hesitate about the split, think back to the baskets.