Load, clean, reshape and explore real data with Python and pandas, then turn the results into clear findings.

Intro to Data Analysis with Python

## Why it matters

"What is the average yield per production line?" "Total revenue by region and month?" "How many samples did each analyst run?" Questions like these are **split-apply-combine**: split the rows into groups, apply a calculation to each group, and combine the answers into a table. It is the single most useful pattern in analysis, and in pandas it is `groupby`. If you know SQL, it is `GROUP BY`.

We will use this table of production batches:

```python
batches = pd.DataFrame({
    "batch_id":  ["B1", "B2", "B3", "B4", "B5", "B6"],
    "line":      ["L1", "L2", "L1", "L3", "L2", "L1"],
    "shift":     ["day", "night", "night", "day", "day", "day"],
    "yield_pct": [94.0, 88.5, 91.0, 96.5, 90.0, 93.0],
    "defects":   [2, 7, 4, 1, 5, 3],
})
```

## One group, one statistic

```python
batches.groupby("line")["yield_pct"].mean()
# line
# L1    92.666667
# L2    89.250000
# L3    96.500000
```

Read it as: group by `line`, take the `yield_pct` column, compute the mean for each group. The result is a Series indexed by the group values. Common aggregations: `sum`, `mean`, `median`, `min`, `max`, `count` (non-missing values), `size` (rows, including missing), `nunique`, `std`.

## Several statistics at once: named aggregation

`.agg` with `new_name=(column, function)` pairs gives a tidy summary table with clear column names:

```python
summary = batches.groupby("line").agg(
    batches=("batch_id", "count"),
    mean_yield=("yield_pct", "mean"),
    total_defects=("defects", "sum"),
)
#       batches  mean_yield  total_defects
# line
# L1          3   92.666667              9
# L2          2   89.250000             12
# L3          1   96.500000              1
```

Now you can sort it, round it or plot it: `summary.sort_values("total_defects", ascending=False).round(1)`.

## Grouping by more than one column

Pass a list of columns:

```python
batches.groupby(["line", "shift"])["defects"].sum()
```

The result has a two-level index (a MultiIndex). Two ways to make it easier to work with:

```python
# back to ordinary columns
by_line_shift = batches.groupby(["line", "shift"], as_index=False)["defects"].sum()
#   line  shift  defects
# 0   L1    day        5
# 1   L1  night        4
# ...

# or spread one level into columns
batches.groupby(["line", "shift"])["defects"].sum().unstack(fill_value=0)
# shift  day  night
# line
# L1       5      4
# L2       5      7
# L3       1      0
```

`pivot_table` does the same in one call and will feel familiar from spreadsheets:

```python
batches.pivot_table(index="line", columns="shift", values="defects", aggfunc="sum", fill_value=0)
```

## Adding a group statistic to every row

Sometimes you want each row compared with its group, for example "how far is this batch from its line's average?" `transform` returns a result the same length as the original, aligned row by row:

```python
batches["line_mean"] = batches.groupby("line")["yield_pct"].transform("mean")
batches["vs_line"] = batches["yield_pct"] - batches["line_mean"]
```

## Finding the top group

```python
totals = batches.groupby("line")["defects"].sum()
totals.idxmax()   # 'L2', the label of the largest value
totals.max()      # 12
```

## Common mistakes

- **`count` vs `size`.** `count` skips missing values; `size` counts rows. Pick the one you mean.
- **Averaging averages.** The mean of per-line means is not the overall mean when groups have different sizes. Compute from the rows.
- **Forgetting that the group column becomes the index.** Use `as_index=False` or `.reset_index()` when you need it as a column.
- **Grouping by a messy text column.** `"L1"` and `"l1 "` are different groups; clean first.
- **Reporting a group mean from one or two rows** without saying so. Show the count next to the mean.

## Quick recap

- `df.groupby("col")["value"].agg_function()` = split, apply, combine.
- Named aggregation (`.agg(new=("col", "func"))`) builds clear summary tables.
- Group by a list for several levels; `as_index=False`, `unstack` or `pivot_table` to reshape.
- `transform` puts a group statistic on every row; `idxmax` finds the top group.

## Try it

In the Do tab you will summarise production batches by line: mean yield, a named-aggregation summary, defects by line and shift, and the line with the most defects.


Summarise data by one or more groups with groupby, named aggregation, pivot tables and transform.

groupby and aggregation

Python Pandas Tutorial (Part 8): Grouping and Aggregating - Analyzing and Exploring Your Data

Group By and Aggregate Functions in Pandas | Python Pandas Tutorials

## Why it matters

Useful data is rarely in one table. Orders are in one export and customer details in another; sample results are in the instrument file and sample metadata in the LIMS. To answer "revenue by customer segment" or "mean concentration by site" you must **join** the tables on a shared key. A join is also one of the easiest places to silently lose rows or, worse, duplicate them, so this lesson focuses on doing it safely.

## merge in one line

```python
orders = pd.DataFrame({
    "order_id":    [1, 2, 3, 4],
    "customer_id": [10, 11, 10, 99],
    "amount":      [50.0, 20.0, 35.0, 80.0],
})
customers = pd.DataFrame({
    "customer_id": [10, 11, 12],
    "name":        ["Ada", "Grace", "Alan"],
    "segment":     ["retail", "business", "retail"],
})

merged = orders.merge(customers, on="customer_id", how="left")
#    order_id  customer_id  amount   name   segment
# 0         1           10    50.0    Ada    retail
# 1         2           11    20.0  Grace  business
# 2         3           10    35.0    Ada    retail
# 3         4           99    80.0    NaN       NaN
```

Every order is kept. Order 4 refers to customer 99, who is not in `customers`, so its customer columns are `NaN`.

## The four join types

| `how=` | Keeps | SQL |
|---|---|---|
| `"inner"` (default) | only keys found in both tables | `INNER JOIN` |
| `"left"` | every row of the left table | `LEFT JOIN` |
| `"right"` | every row of the right table | `RIGHT JOIN` |
| `"outer"` | every row of both | `FULL OUTER JOIN` |

With `how="inner"` order 4 would simply disappear, and your revenue total would drop by 80 without any warning. That is why analysts usually start with a **left join from the table they are reporting on**, then look at what did not match.

## Finding what did not match

`indicator=True` adds a `_merge` column saying where each row came from:

```python
check = orders.merge(customers, on="customer_id", how="left", indicator=True)
check["_merge"].value_counts()
# both         3
# left_only    1
check.loc[check["_merge"] == "left_only", "order_id"].tolist()   # [4]
```

The other direction, customers with no orders, is a membership test:

```python
customers[~customers["customer_id"].isin(orders["customer_id"])]["name"].tolist()   # ['Alan']
```

## Guarding against duplicated rows

If the right table has **two rows for the same key**, every matching left row is repeated, and sums double. This happens more than you would think (a customer listed twice with different spellings, a sample re-run).

Protect yourself with `validate`:

```python
orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
```

pandas raises a `MergeError` if `customer_id` is not unique in `customers`. Other options are `"one_to_one"` and `"one_to_many"`. Also compare row counts: after a many-to-one left join, `len(merged)` must equal `len(orders)`.

## Keys with different names, and stacking

```python
orders.merge(clients, left_on="customer_id", right_on="id", how="left")
```

When both tables have a non-key column with the same name, pandas adds suffixes `_x` and `_y`; set your own with `suffixes=("_order", "_customer")`.

To stack tables with the same columns (January's file and February's file) use `concat`, not `merge`:

```python
all_months = pd.concat([jan, feb], ignore_index=True)
```

## A worked example

Revenue by segment, keeping a check on unmatched orders:

```python
merged = orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
assert len(merged) == len(orders)

unmatched = merged["segment"].isna().sum()
by_segment = merged.groupby("segment")["amount"].sum()
# business 20.0, retail 85.0   (groupby skips the NaN segment)
print(f"{unmatched} order(s) worth {merged.loc[merged['segment'].isna(), 'amount'].sum()} had no customer record")
```

Reporting the unmatched amount is the honest part: the segment totals add up to 105, not the 185 in `orders`.

## Common mistakes

- **Using the default inner join without thinking.** Unmatched rows vanish silently.
- **Duplicate keys on the "one" side.** Rows multiply; use `validate` and check `len`.
- **Key type mismatch.** `10` (int) does not match `"10"` (text); convert with `astype` first.
- **Untrimmed keys.** `"C-10 "` does not match `"C-10"`; clean keys like any text column.
- **Using merge to stack files.** Use `pd.concat` for rows with the same columns.

## Quick recap

- `left.merge(right, on="key", how="left")` keeps every row you are reporting on.
- `indicator=True` and `isin` show what did not match; report it.
- `validate="many_to_one"` and a row-count check stop accidental duplication.
- `concat` stacks tables; `merge` joins them side by side.

## Try it

In the Do tab you will join orders to customers, find unmatched orders and customers without orders, and total revenue by segment.


Join two datasets on a key with merge, find unmatched rows and guard against duplicated rows.

Merging DataFrames

Merging DataFrames in Pandas | Python Pandas Tutorials

Python Pandas Tutorial 9. Merge Dataframes

## Why it matters

A large share of real data has a timestamp: daily sales, hourly sensor readings, weekly case counts, monthly instrument calibrations. Questions about it are about **time**: "How did monthly visits change?", "Which weekday is busiest?", "Is there a trend once the daily noise is smoothed out?" pandas has excellent tools for this, but only once your dates are real dates rather than text.

## Step 1: make dates real

```python
df["date"] = pd.to_datetime(df["date"])                 # "2026-01-05" -> Timestamp
df["date"] = pd.to_datetime(df["date"], format="%d/%m/%Y")  # be explicit for 05/01/2026
df["date"] = pd.to_datetime(df["date"], errors="coerce")    # bad values -> NaT (missing)
```

Always give `format` when dates are written day-first or month-first. `01/02/2026` is 1 February in the UK and January 2 in the US, and pandas cannot know which you mean.

Once a column is `datetime64`, the `.dt` accessor gives its parts:

```python
df["date"].dt.year
df["date"].dt.month
df["date"].dt.day_name()     # "Monday", ...
df["date"].dt.to_period("M") # 2026-01
```

## Step 2: a DatetimeIndex

Most time-series tools work on the index:

```python
daily = df.set_index("date").sort_index()
daily.loc["2026-02"]                 # every row in February 2026
daily.loc["2026-01-10":"2026-01-20"] # a date range (both ends included)
```

## Resampling: change the frequency

`resample` is `groupby` for time. Pick a frequency and an aggregation:

```python
monthly = daily["visits"].resample("MS").sum()     # month start labels
weekly  = daily["visits"].resample("W").mean()      # weeks ending Sunday
```

| Code | Meaning |
|---|---|
| `"D"` | day |
| `"W"` | week (ending Sunday) |
| `"MS"` | month, labelled by its first day |
| `"ME"` | month, labelled by its last day |
| `"QS"` | quarter start |
| `"YS"` | year start |

Choose the aggregation that matches the measure: **sum** for counts and money, **mean** or **median** for levels such as temperature or concentration, **last** for balances.

## Comparing periods

```python
monthly.pct_change() * 100      # % change from the previous month
monthly.diff()                  # absolute change
```

## Smoothing with a rolling window

Daily data is noisy. A **rolling mean** averages each day with the days before it:

```python
daily["visits_7d"] = daily["visits"].rolling(7).mean()
```

The first six values are `NaN` because a full 7-day window is not yet available. A 7-day window also removes the weekly pattern, which makes the underlying trend easier to see.

## Grouping by calendar parts

To find the busiest weekday, group by the day name rather than resampling:

```python
by_weekday = daily.groupby(daily.index.day_name())["visits"].mean()
by_weekday.idxmax()
```

## A worked example

```python
import numpy as np

rng = np.random.default_rng(7)
dates = pd.date_range("2026-01-01", "2026-03-31", freq="D")
daily = pd.DataFrame({"visits": rng.integers(80, 121, size=len(dates))}, index=dates)

monthly = daily["visits"].resample("MS").sum()
print(monthly)                   # three rows: Jan, Feb, Mar
print(monthly.pct_change().round(3))
```

February has 28 days and January 31, so February's **total** will usually be lower even if each day is just as busy. When months differ in length, compare the **daily mean** (`resample("MS").mean()`) as well as the total.

## Common mistakes

- **Dates left as text.** Sorting text dates puts `"10/01"` before `"2/01"`. Convert first.
- **Ambiguous day/month order.** Pass `format=` explicitly.
- **Unsorted index.** Sort before slicing ranges or computing rolling windows.
- **Summing a level.** Monthly "sum of temperatures" is meaningless; use the mean.
- **Ignoring time zones.** Mixed time zones shift events across midnight. Use `tz_localize`/`tz_convert` when data comes from several places.

## Quick recap

- `pd.to_datetime` (with `format` when ambiguous) makes dates real; `.dt` gives their parts.
- Set a sorted DatetimeIndex, then slice with `.loc["2026-02"]`.
- `resample("MS").sum()` changes frequency; choose sum or mean to match the measure.
- `rolling(7).mean()` smooths noise; `pct_change` compares periods; group by `day_name()` for weekday patterns.

## Try it

In the Do tab you will convert a date column, resample daily visits to monthly totals and daily means, and find the busiest weekday.


Parse dates, resample daily data to monthly totals and means, smooth with rolling windows and find weekday patterns.

Dates and time series

Python Pandas Tutorial (Part 10): Working with Dates and Time Series Data

Pandas Time Series Analysis Part 1: DatetimeIndex and Resample

Summarise data by group with groupby, join datasets with merge without losing or duplicating rows, and turn daily records into monthly time series.