Module 7 Lesson: Combining Datasets Safely

Let us begin

Many useful analyses need more than one table.

You may have:

• one table for learners;
• one table for courses;
• one table for enrolments;
• one table for activity;
• one table for support tickets.

Combining tables can answer richer questions. It can also quietly create wrong results.

So every join needs checks.

Stacking with concat

Use concat when tables have the same structure and you want to stack rows.

all_feedback = pd.concat(
    [feedback_january, feedback_february],
    ignore_index=True
)

This is useful for monthly files with the same columns.

Check:

len(all_feedback) == len(feedback_january) + len(feedback_february)

Joining with merge

Use merge when you want to add columns from one table to another using a key.

enriched = enrolments.merge(
    courses,
    on="course_id",
    how="left"
)

Here, enrolments is the main table. Courses adds course details.

Join types

Common join types:

• inner: keep only matching rows from both tables;
• left: keep all rows from the left table;
• right: keep all rows from the right table;
• outer: keep all rows from both tables.

For analysis, a left join is often safer when the left table is your main table. It keeps the main records and shows missing matches.

Key checks

Before joining, check the key.

courses["course_id"].isna().sum()
courses["course_id"].duplicated().sum()

If courses should have one row per course, duplicate course_id values are a problem.

Check both sides:

enrolments["course_id"].isna().sum()
courses["course_id"].isna().sum()

Missing matches

After a left join, missing values from the right table may show unmatched keys.

enriched["course_name"].isna().sum()

Investigate missing matches before continuing.

Row-count reconciliation

Record row counts before and after.

before_rows = len(enrolments)

enriched = enrolments.merge(courses, on="course_id", how="left")

after_rows = len(enriched)

If a left join creates more rows than the left table, the right table likely has duplicate keys.

That may be correct in a one-to-many relationship, but it must be intentional.

Many-to-many joins

A many-to-many join can multiply rows.

Do not use it casually. Ask:

• Is this expected?
• Does one row still mean what I think it means?
• Should I aggregate one table before joining?

Often the safer answer is to summarize first, then join.

Mini worked example

Question:

> Which course level has the most support tickets per enrolment?

Plan:

1. Count tickets by enrolment.
2. Join ticket counts to enrolments.
3. Join course level from courses.
4. Group by course level.

This avoids joining every ticket row directly to every analysis row when a count is enough.

Takeaway

A join is more than a command. It is a claim that two tables belong together in a specific way. In the next module, we work with dates, resampling, and rolling summaries.