Aggregation and Exploratory Data Analysis in Jupyter

Aggregation and Exploratory Data Analysis in Jupyter

After this lesson you will be able to: aggregate data by group, read quick summary statistics, and work in a Jupyter Notebook to explore a new dataset.

1. Jupyter Notebook in 1 minute

A notebook is made of cells: code cells run Python, markdown cells hold notes. The result of the last line in a cell is displayed right below it, which makes notebooks ideal for trying things out and observing data.

• Shift + Enter: run the current cell and move to the next one.
• Variables created in one cell can be used in later cells; if you run cells out of order you may get confusing results. When in doubt, choose Restart Kernel and Run All Cells.

2. Grouping and aggregating: groupby

The example dataset:

import pandas as pd

sales = pd.DataFrame({
    "region":  ["N", "S", "N", "S", "N", "C"],
    "product": ["A", "A", "B", "B", "A", "B"],
    "revenue": [100, 150, 200, 50, 300, 120],
})

groupby splits the table into groups and then applies an aggregation to each group:

print(sales.groupby("region")["revenue"].sum())
# region
# C    120
# N    600
# S    200

print(sales.groupby("product")["revenue"].mean())
# A    183.333333
# B    123.333333

print(sales.groupby("region").size()["S"])     # 2   (number of rows in group S)

Several aggregations at once:

r = sales.groupby("region")["revenue"].agg(["count", "max"])
print(r.loc["N", "count"])   # 3
print(r.loc["N", "max"])     # 300

3. Quick counts and descriptive statistics

print(sales["region"].value_counts())            # count how often each value appears; N is the most frequent (3)
print(sales["region"].value_counts().idxmax())   # N
print(sales["revenue"].describe())               # count 6, mean 153.33, min, max, quartiles

4. Cross-tabulation: pivot_table

pv = sales.pivot_table(index="region", columns="product",
                       values="revenue", aggfunc="sum")
print(pv)
# product      A      B
# region
# C          NaN  120.0
# N        400.0  200.0
# S        150.0   50.0

A cell with no data for that combination (for example, region C has not sold product A) is NaN.

5. Correlation

xy = pd.DataFrame({"x": [1, 2, 3, 4], "y": [2, 4, 6, 8], "z": [8, 6, 4, 2]})
print(xy["x"].corr(xy["y"]))   # 1.0   (both increase)
print(xy["x"].corr(xy["z"]))   # -1.0  (when x increases, z decreases)

The correlation coefficient lies between −1 and 1: close to 1 means the variables move in the same direction, close to −1 means opposite directions, and close to 0 means little linear relationship. Correlation does not prove causation.

6. Quick plotting

sales.groupby("region")["revenue"].sum().plot(kind="bar") draws a bar chart right in the notebook (you need to install matplotlib).

Workflow for exploring a new dataset

1. df.shape, df.head(), df.info(): what does the data contain?
2. df.isna().sum(): where are values missing?
3. df.describe() and value_counts(): what does the distribution look like, and are there odd values?
4. groupby / pivot_table: compare between groups.
5. corr() and charts: how are the variables related?

Key Points

• groupby(column)[numeric_column].aggregation() is the most commonly used pattern.
• value_counts() to count, describe() to summarize.
• pivot_table for cross-tabulations; a cell with no data is NaN.
• Correlation measures how much variables move together, not what causes what.

Reference

McKinney, W. (2022). Python for Data Analysis: Data Wrangling with Pandas, NumPy, and Jupyter (3rd ed.). O'Reilly Media.