Group By Part I: Data Aggregation

Long data re-crossing the road?

Question: Long data re-crossing the road?

Answer: As we saw in the previous section, long data present a ‘nested structure’ since values cluster around cases.

So what?

Question: So what?

Answer: The nested structure of the data plays a key role in aggregating the data. For example, one may want to aggregate daily sales data into monthly or quarterly sales data. In so doing, one trades off the granularity of the data (e.g., daily sales records) for the possibility to best appreciate a general trend in the data (e.g., comparing the sales of two contiguous quarters).

Got it. How can I do this in Pandas?

Question: Got it. How can I do this in Pandas?

Answer: Pandas has a class to help with this task, namely, .groupby. Mainly, this class captures the nested structure of the data and allows to aggregate or transform the data (see next section) according to the nested structure.

The code examples in this section show how to use .groupby for aggregation purposes. The sample DataFrame contains monthly sales data for three products, each observed for three months. First, we must tell Pandas what grouping data to consider. We do that by passing the column name used to group the data. In this example, we use the product column. That means we will pass from product-time data points (i.e., monthly data) to product data points (e.g., the total sales for each product over the three months). Since the grouping structure is defined by one column only, the input is a string with the column’s name. An array of strings must be passed if the grouping structure is based on multiple columns.

Calling the groupby object alone does not affect the data. Instead, the signature of the object is displayed. To aggregate the data, we must deploy the groupby object with aggregation functions. The string between brackets is the target column whose values we want to consider; .aggregate() defines the operation to carry out based on the grouping; the array of NumPy functions is the set of mathematical operations to perform as the data are aggregated. In examples, we might ask for the total sales for each product and the average monthly sales over the three months.

This section includes detailed code examples showing how to group data by one or more columns and apply various aggregation functions like sum, mean, count, etc.