Clean, Group & Combine Data

Handle missing values and types, summarize groups and combine tables using explicit keys.

By the end of this lesson

You will be able to

Measure missingness and duplicates before changing data.
Convert columns to appropriate dtypes.
Group rows and calculate multiple summaries.
Combine two tables using an explicit key.

Audit before you clean

Start with row counts, duplicate keys, missingness and data types. Record the original counts so that later transformations can be compared with the source. A missing value may be meaningful; dropping it without understanding the reason can bias the analysis.

missing = df.isna().sum().sort_values(ascending=False)
duplicates = df.duplicated().sum()

print("rows:", len(df))
print("duplicates:", duplicates)
print(missing)

Summarize by groups

GroupBy follows a split-apply-combine pattern: split rows into groups, apply a calculation and combine the results. Use named aggregations so the output columns explain what they measure.

by_hour = (
    df.groupby("hr", as_index=False)
      .agg(
          mean_rentals=("cnt", "mean"),
          median_rentals=("cnt", "median"),
          observations=("cnt", "size"),
      )
)

Join on a key you can explain

A merge matches rows using one or more key columns. Check key uniqueness and the expected join relationship before trusting the result. Use the validate argument where the relationship is known.

joined = left.merge(
    right,
    on="date",
    how="left",
    validate="many_to_one",
    indicator=True,
)

Apply what you learned

Exercise

Create a daily demand summary

Build a grouped table from the hourly records and validate the aggregation before saving it.

Parse dteday as a date and combine it with hr only when you need an hourly timestamp.
Audit missing values, duplicate rows and column dtypes.
Group by weekday and calculate mean, median and observation count for cnt.
Check that group counts sum to the number of included rows.
Save the grouped output as a CSV.

Done when

The summary is traceable to the source, its group counts reconcile, and any removed rows are counted and explained.

Free references

Study resources

3 links

01
Working with missing data Article
Read detection, dropping and filling missing values. Choose a policy only after measuring.

Done
02
Group by: split-apply-combine Article
Focus on grouping by one key and named aggregation.

Done
03
Merge, join and concatenate Article
Use this when combining tables; verify the join key and expected relationship.

Done

Check your understanding

Self-test

2 questions

Question 1

Why record row counts before and after cleaning?
Question 2

What does validate="many_to_one" check in a merge?

Completion check

Move on when

Your cleaning decisions are documented, grouped summaries reconcile with source rows, and merges use validated keys.

Mark passed and continue Back to module