Demos and Recipes LSE ME204 · Data Engineering Principles for the Social Sciences
🖥️ Week 03 Day 02 Lecture

SQL and Joining Data

Dr Jon Cardoso-Silva

LSE Data Science Institute

🗓️ 28 Jul 2026

🛠️ Final Project Support

10:00 – 11:00

I’ll be going around the room to help you. Use this hour to make progress on your project.

What you could be doing right now:

Continuing with your data collection (API, scraping, or both)
Cleaning and reshaping your collected data into a DataFrame
Sketching what your first chart or table might look like
Come talk to me about scope, feasibility, or a technical problem

💡 Deadline reminder: submit your final project by 5pm Friday 31 July.

☕ Coffee Break

11:00 – 11:15

When we come back:

Why SQL is a second language for the same filtering and grouping questions
Querying hourly_readings, side by side in SQL and pandas
In-class demo: INNER JOIN vs LEFT JOIN on geocoded weather data

1️⃣ SQL Is a Different Way to Work with Data

11:15 – 11:35

A list can hold anything

Structure and strictness increase as you move from a list to a SQL table.

A plain list

readings = [15.2, "cloudy", None, 1013]

Any type in any position. Nothing stops you putting a string where a number should be.

A dict of lists

data = {
    "temp": [15.2, 14.8],
    "city": ["london", "edinburgh"],
}

Columns have names, but nothing enforces that every column has the same length or the same type throughout.

A DataFrame

df = pd.DataFrame(data)
df.dtypes
# temp    float64
# city     object

One type per column, enforced the moment the DataFrame is built.

A SQL table

CREATE TABLE hourly_readings (
    temp    DECIMAL(5,2),
    city    VARCHAR(20)
);

Types are declared before any data goes in, and the table can also declare which columns identify a row.

Where the data comes from

DataFrames: you build them yourself

In Weeks 01 and 02 you called an API, got JSON back, and built a DataFrame from the response. You controlled what went in.

response = requests.get(url, params=params)
df = pd.json_normalize(response.json())

SQL tables: someone else built them

At most organisations, the data already exists in a database. Your job is to work out what tables are there and what each column means.

SELECT name
FROM sqlite_master
WHERE type = 'table';

Same questions, two languages

Once data exists in either form, the same questions come up: filter rows, group and aggregate, sort, join two tables. SQL and pandas are two ways to ask them.

pandas	SQL
`df.head(5)`	`SELECT * FROM table LIMIT 5`
`df[df["col"] > val]`	`SELECT * FROM table WHERE col > val`
`df.groupby("col").size()`	`SELECT col, COUNT(*) FROM table GROUP BY col`
`df.sort_values("col", ascending=False)`	`SELECT * FROM table ORDER BY col DESC`
`pd.merge(df1, df2, on="key")`	`SELECT ... FROM t1 JOIN t2 ON t1.key = t2.key`

Section 2 builds each of these pairs on real data.

Connecting to a database

Python’s built-in sqlite3 module. No installation needed.

import sqlite3

conn = sqlite3.connect("data/my_weather.db")

sqlite3 is part of Python’s standard library. connect opens an existing database file or creates a new one if the file does not exist yet. That file does not exist yet, so this call creates it.

Everything you do with this database goes through conn. Queries, inserts, schema changes: they all start from the connection object.

💡 Let me show you in the notebook. Open the lecture solutions notebook and run the sqlite3.connect cell.

Declaring the schema before any data goes in

A DataFrame gets its types from the data you load. A SQL table declares them first, then accepts data.

DROP TABLE IF EXISTS hourly_readings;

CREATE TABLE hourly_readings (
    dt         BIGINT,
    temp       DECIMAL(5,2),
    feels_like DECIMAL(5,2),
    pressure   INTEGER,
    humidity   INTEGER,
    city       VARCHAR(20)
);

DROP TABLE IF EXISTS removes any previous version. CREATE TABLE declares the contract: column names, types, and constraints. No data has been inserted yet.

Note the filename: my_weather.db. You are building your own database here just to avoid overwriting the one we will be using in the afternoon class.

⚠️ Loading data: append, not replace

df_hourly_readings.to_sql(
    "hourly_readings",
    conn,
    if_exists="append",
    index=False,
)

if_exists='append' inserts rows into the table you just created, keeping the declared schema.

if_exists='replace' would drop that table and let pandas guess a schema from scratch. Always use append when you have created the schema yourself.

Inspecting a database like a DataFrame

PRAGMA table_info is SQLite’s equivalent of df.info().

Which tables exist?

pd.read_sql("""
    SELECT name
    FROM sqlite_master
    WHERE type = 'table'
    ORDER BY name;
""", conn)

	name
0	geocoding_matches
1	hourly_readings
2	weather_conditions

What columns does a table have?

pd.read_sql(
    "PRAGMA table_info(hourly_readings)",
    conn,
)

	cid	name	type	dflt_value
0	0	dt	BIGINT	None
1	1	temp	DECIMAL(5,2)	None
2	2	feels_like	DECIMAL(5,2)	None
3	3	pressure	INTEGER	None
4	4	humidity	INTEGER	None
5	5	city	VARCHAR(20)	None

💡 Let me show you in the notebook. Run the sqlite_master and PRAGMA cells now.

2️⃣ Asking Questions in SQL

11:35 – 12:05

SELECT and FROM: pick columns and name the table

Every SQL query starts here: which columns, from which table.

SQL

SELECT *
FROM hourly_readings
LIMIT 5;

SELECT * means every column. FROM names the table. LIMIT 5 stops after five rows, so you can see what the data looks like without printing thousands of lines.

pandas equivalent

df_hourly_readings.head(5)

Same idea: show the first five rows so you know what you are working with.

💡 Let me show you. Run the LIMIT 5 cell in the notebook now.

Picking specific columns

SELECT city, temp, humidity
FROM hourly_readings
LIMIT 5;

Replace * with the column names you want, separated by commas.

(
    df_hourly_readings
    [["city", "temp", "humidity"]]
    .head(5)
)

WHERE: filter rows

Keep only the rows that match a condition.

SQL

SELECT *
FROM hourly_readings
WHERE city = 'london'
  AND temp > 15;

WHERE filters rows before they reach the result. AND combines two conditions: the city must be 'london' and the temperature must be above 15.

pandas: .query()

(
  df_hourly_readings
  .query("city == 'london' and temp > 15")
)

pandas: .loc[]

df_hourly_readings.loc[
    (df_hourly_readings["city"] == "london")
    & (df_hourly_readings["temp"] > 15)
]

.query() reads like the SQL WHERE. .loc[] does the same thing with boolean indexing. Notice == for equality in Python, = in SQL.

💡 Run the WHERE cell in the notebook.

GROUP BY and ORDER BY: summarise and sort

For every city, what is the average temperature among rows above 15°C?

SQL

SELECT city,
       AVG(temp) AS avg_temp
FROM hourly_readings
WHERE temp > 15
GROUP BY city
ORDER BY avg_temp DESC;

GROUP BY city collapses rows into one row per city. AVG(temp) computes the mean inside each group. AS avg_temp gives the result column a name. ORDER BY ... DESC sorts from highest to lowest.

pandas equivalent

(
    df_hourly_readings
    .query("temp > 15")
    .groupby("city", as_index=False)
    ["temp"].mean()
    .rename(columns={"temp": "avg_temp"})
    .sort_values("avg_temp", ascending=False)
)

Each line does one job. .query() filters. .groupby() groups. .mean() aggregates. .sort_values() orders.

💡 Run the GROUP BY cell in the notebook. The SQL and pandas outputs should match.

`pd.read_sql` returns a DataFrame

Every query result comes back as a DataFrame you already know how to work with.

result = pd.read_sql("""
    SELECT city, AVG(temp) AS avg_temp
    FROM hourly_readings
    WHERE temp > 15
    GROUP BY city
    ORDER BY avg_temp DESC
""", conn)

type(result)
# <class 'pandas.core.frame.DataFrame'>

SQL gets you the rows you need from the database. The result comes back as a DataFrame, and from there you can use every pandas method you already know.

So far every question used one table. What happens when the answer needs columns from two tables? That is what a join does ⏭️

How to read a SQL query

You write the clauses in one order. The database runs them in another.

Written order

SELECT city, AVG(temp)
FROM hourly_readings
WHERE temp > 15
GROUP BY city
ORDER BY AVG(temp) DESC;

Execution order

FROM: pick the table
WHERE: filter rows
GROUP BY: collapse into groups
SELECT: pick columns and compute
ORDER BY: sort the result

This is why you cannot use a column alias from SELECT inside WHERE: the database has not run SELECT yet when it filters.

💡 SQLite and types. SQLite is more forgiving than most databases. It uses “type affinity”, which means it tries to convert values to the declared type but does not always reject a mismatch. Other databases (PostgreSQL, MySQL) are stricter.

References

3️⃣ Joining Two Tables

12:05 – 12:35

Two tables, one question

hourly_readings has weather data. geocoding_matches has country and state. To answer “which UK cities were warmest?” you need columns from both.

geocoding_matches (sample)

query_name	name	country
London	London	GB
London	City of London	GB
London	Chelsea	GB
London	London	CA
London	London	US
Edinburgh	City of Edinburgh	GB
Edinburgh	Old Town	GB
Edinburgh	Edinburgh	US
Cardiff	Cardiff	GB
Belfast	Belfast	GB

Each city has its own colour. Saturated rows are GB, pale rows are the same name in another country.

Four searches, twenty rows, and seven of them are in GB. London alone returns three.

hourly_readings (sample)

city	temp	humidity	dt
london	16.03	78	1754006400
edinburgh	12.89	82	1754006400
cardiff	15.13	83	1754006400
belfast	13.94	79	1754006400

Notice: city is lowercase (london), but query_name is capitalised (London). A join has to account for that.

What a join does

A join combines rows from two tables where a condition matches.

geocoding_matches

query_name	country
London	GB
London	CA
London	US
Edinburgh	GB
Edinburgh	US

hourly_readings

city	temp
london	16.03
edinburgh	12.89

→

result

query_name	country	temp
London	GB	16.03
Edinburgh	GB	12.89

The result has columns from both tables. Rows that share a colour matched on the city name.

Two questions decide what the result looks like:

Which rows match? The ON clause sets the condition.
What happens to rows that do not match? That is the difference between INNER JOIN and LEFT JOIN.

A join is only as good as its key

The obvious query looks right and triples the data.

SELECT g.query_name,
       COUNT(*) AS rows_returned
FROM geocoding_matches g
JOIN hourly_readings r
  ON LOWER(g.query_name) = r.city
WHERE g.country = 'GB'
GROUP BY g.query_name;

LOWER() handles London against london, and country = 'GB' drops the foreign matches. Nothing here is a syntax error.

query_name	rows_returned
London	180
Edinburgh	120
Cardiff	60
Belfast	60

60 readings per city went in. London came back 180 times, Edinburgh 120.

⚠️ London has three GB matches, so each of its 60 readings is paired three times. An average over this result gives the same number as the correct query, because every value repeats equally. COUNT(*) is what catches it.

INNER JOIN: only the rows that match

Join on a column that identifies one row, and each reading matches once.

SELECT c.name,
       c.state,
       ROUND(AVG(r.temp), 2) AS avg_temp
FROM hourly_readings r
JOIN cities c
  ON r.city = c.city
GROUP BY c.name, c.state
ORDER BY avg_temp DESC;

cities has one row per city, and hourly_readings.city points at it. Each of the 240 readings matches exactly one city row, so the average is over 60 readings per city.

On the previous slide, joining on query_name gave London 180 rows. Here it gives 60.

Result (sample)

name	state	avg_temp
London	England	17.09
Belfast	Northern Ireland	16.56
Cardiff	Wales	16.27
City of Edinburgh	Scotland	15.66

Four rows, one per city, each an average over that city’s 60 readings.

cities stores City of Edinburgh, the name the Geocoding API returned, not the name you searched for.

💡 Run the INNER JOIN cell in the notebook.

LEFT JOIN: every row from the left, NULLs where absent

Keep every row from the left table. Fill in NULLs where the right table has no match.

SELECT g.query_name,
       g.name,
       g.country,
       c.state AS uk_region
FROM geocoding_matches g
LEFT JOIN cities c
  ON g.name = c.name
  AND g.country = 'GB'
ORDER BY g.query_name, g.country, g.name;

country = 'GB' goes in ON, not WHERE. A WHERE filter would drop every row outside GB after the join, which defeats the point of keeping all 20.

In ON it decides which rows find a match, not which rows survive.

Result (sample)

query_name	name	country	uk_region
Belfast	Belfast	GB	Northern Ireland
Belfast	Belfast	NZ	NULL
London	London	GB	England
London	Chelsea	GB	NULL
London	City of London	GB	NULL
London	London	CA	NULL

All 20 rows come back. 4 matched, 16 are NULL.

Look at Chelsea and City of London. Both are in GB, and both are NULL, because neither is the row cities stores for London. Being in the right country is not the same as being the right row.

NULL in SQL is NaN in pandas. Check for it with .isna().

Other join types

INNER and LEFT are the two you will use most. Two others exist:

FULL OUTER JOIN keeps all rows from both tables, with NULLs on whichever side has no match. SQLite does not support it directly.
CROSS JOIN pairs every row from one table with every row from the other. Rarely useful on large tables.

For a full reference: W3Schools SQL Joins

The same joins in pandas: `pd.merge`

pd.merge is the pandas equivalent of a SQL JOIN.

INNER (readings to cities, on the key)

df_inner = (
    pd.merge(
        df_hourly_readings,
        df_cities,
        on="city",
        how="inner",
    )
    .groupby(["name", "state"], as_index=False)
    .agg(avg_temp=("temp", "mean"))
    .sort_values("avg_temp", ascending=False)
)

how=“inner” keeps only matched rows, the same as JOIN in SQL.

on="city" is the ON clause. Both frames name the column the same way, so one argument covers it.

LEFT (every geocoded match, region where known)

df_uk = (
    df_cities
    .query("country == 'GB'")
    [["name", "state"]]
    .rename(columns={"state": "uk_region"})
)

df_left = pd.merge(
    df_geocoding_matches,
    df_uk,
    on="name",
    how="left",
)

how=“left” keeps every row from the left DataFrame, with NaN where there is no match.

SQL puts the GB condition in ON. pandas has no ON to put it in, so filter the right frame before merging.

`.pipe()`: a readable pipeline in pandas

If you liked how a SQL query reads as a sequence of named steps, .pipe() gives you the same thing in pandas.

Define each step as a function

def keep_warm_readings(df):
    result = df.query("temp > 15")
    return result

def average_by_city(df):
    result = (
        df.groupby("city", as_index=False)
        .agg(avg_temp=("temp", "mean"))
    )
    return result

def warmest_first(df):
    result = df.sort_values(
        "avg_temp", ascending=False
    )
    return result

Chain them with .pipe()

(
    df_hourly_readings
    .pipe(keep_warm_readings)
    .pipe(average_by_city)
    .pipe(warmest_first)
)

This answers the Section 2 question again, and the name of each function stands in for the explanation you would otherwise write as a comment.

Compare with the version that reassigns to a new variable at every step:

df_warm = df_hourly_readings.query(...)
df_avg = df_warm.groupby(...)
df_result = df_avg.sort_values(...)

Both produce the same result. The .pipe() version reads as one pipeline.

`.pipe()` for loading and reshaping data

.pipe() is especially useful when one of the steps is not a DataFrame method. Loading a JSON file and normalising it cannot be chained with dot syntax, but you can wrap it in a function:

def load_readings(path, city):
    with open(path) as f:
        raw = json.load(f)
    result = (
        pd.json_normalize(raw, record_path="data")
        .assign(city=city)
    )
    return result

record_path="data" tells json_normalize which nested list holds the records you want, so it returns one row per hourly reading.

Then the full pipeline reads:

(
    load_readings("data/raw/london_0.json", "london")
    .pipe(keep_warm_readings)
    .pipe(average_by_city)
    .pipe(warmest_first)
)

From raw JSON file to final table in four named steps. You can read what happens without opening any of the functions.

🏁 Thanks!

12:35 – 13:00

This afternoon

You will practise querying and joining data in SQLite, and your class teacher can also help you with your final project.

💻 Today’s Lab

Where you want to be by tonight

You can connect to a SQLite database and list its tables
You can write a SELECT/WHERE/GROUP BY/ORDER BY query and its pandas equivalent
You understand what an INNER JOIN keeps and what a LEFT JOIN keeps
Rough code is fine. Polish it tomorrow. The important thing is that the queries run.

⚠️ Submit by 5pm Friday 31 July.

LSE Summer School 2026 | ME204 Week 03 Day 02

SQL and Joining Data

🛠️ Final Project Support

☕ Coffee Break

1️⃣ SQL Is a Different Way to Work with Data

A list can hold anything

Where the data comes from

Same questions, two languages

Connecting to a database

Declaring the schema before any data goes in

Inspecting a database like a DataFrame

2️⃣ Asking Questions in SQL

SELECT and FROM: pick columns and name the table

Picking specific columns

WHERE: filter rows

GROUP BY and ORDER BY: summarise and sort

pd.read_sql returns a DataFrame

How to read a SQL query

3️⃣ Joining Two Tables

Two tables, one question

What a join does

A join is only as good as its key

INNER JOIN: only the rows that match

LEFT JOIN: every row from the left, NULLs where absent

Other join types

The same joins in pandas: pd.merge

.pipe(): a readable pipeline in pandas

.pipe() for loading and reshaping data

🏁 Thanks!

`pd.read_sql` returns a DataFrame

The same joins in pandas: `pd.merge`

`.pipe()`: a readable pipeline in pandas

`.pipe()` for loading and reshaping data