Analyze Titanic Data with Pandas and SQL

Load, filter, and summarize Titanic passenger data using pandas and DuckDB.

Introduction

30 Second Summary

Every dataset tells a story. The Titanic passenger list is one of the most famous datasets in data science, packed with patterns about who survived and who didn't.

In this project, you will analyze the Titanic passenger dataset using two different approaches. First you'll use pandas for Python-based data manipulation, then you'll use DuckDB to run SQL queries directly on a CSV file.

What You'll Build

You will build two Python scripts that load, explore, filter, group, and summarize 891 rows of Titanic passenger data using both pandas and SQL.

By the end of this project, you'll have:

A pandas analysis script that explores, filters, groups, and sorts passenger data across 12 columns.
A DuckDB SQL script with 5 queries that produce the same results as your pandas code.
A summary CSV report with survival statistics broken down by passenger class and gender.
Secret Mission: Handle missing data, engineer a new feature, and build a pivot table that reveals hidden survival patterns.

Are there any prerequisites?

This project assumes you have basic Python knowledge (data types, loops, functions, lists, and dictionaries) and basic SQL knowledge (SELECT, WHERE, GROUP BY, ORDER BY). You should also have Python and VS Code (or Jupyter Notebook) already installed on your machine.

Set Up Your Project

Before you can analyze any data, you need a workspace with the right tools and files in place.

In this step, you will install DuckDB (a lightweight SQL engine), create a project folder with two Python script files, and grab the Titanic passenger dataset.

In this step, get ready to:

Install the DuckDB Python package.
Create a project folder with two Python script files.
Download the Titanic passenger dataset.

Install DuckDB

DuckDB lets you run SQL queries directly on CSV files without setting up a database server.

Install DuckDB by running this command:

pip install duckdb

Verify the installation by running:

python -c "import duckdb; print(duckdb.__version__)"

✔️ I see a version number

You should see something like 1.5.2. DuckDB is ready to go.

ⓧ Command not found or an error

If you see pip: command not found, try using pip3 instead:

pip3 install duckdb

If the install command runs but the verification still fails, make sure you are using the same Python installation for both commands.

Try running the verification with python3 instead:

python3 -c "import duckdb; print(duckdb.__version__)"

What is DuckDB?

DuckDB is an in-process SQL database engine. It runs entirely inside your Python script with no separate server to install or manage.

The feature you will use most is its ability to query CSV files directly with SQL. Instead of importing data into a database first, you point DuckDB at a CSV file and start writing SELECT statements immediately.

Create your project folder and files

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.

Move to your Desktop by running this command:

cd ~/Desktop

Create your project folder and move into it by running these commands:

mkdir titanic-analysis
cd titanic-analysis

Create two empty Python files by running:

macOS

touch titanic_pandas.py titanic_sql.py

Windows

New-Item titanic_pandas.py -ItemType File
New-Item titanic_sql.py -ItemType File

Confirm the files exist by running:

ls

You should see titanic_pandas.py and titanic_sql.py in the output.

Don't see your files?

Make sure you ran cd titanic-analysis before creating the files. If you created them in the wrong folder, delete them and start again from the cd ~/Desktop step.

Why aren't my project files showing up in the right folder?

Prefer Jupyter Notebook?

If you prefer working in Jupyter, create a single notebook called titanic_analysis.ipynb instead of the two Python files. You can use separate sections in the notebook for the pandas code and the DuckDB code.

Download the Titanic dataset

The Titanic dataset is a CSV file with 891 passenger records and 12 columns. It is hosted publicly on GitHub as part of the pandas repository, so no login or API key is needed.

Make sure you are inside your titanic-analysis folder, then download the CSV file by running this command:

curl -o titanic.csv https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/titanic.csv

Confirm the download by running:

ls

You should now see three files: titanic.csv, titanic_pandas.py, and titanic_sql.py.

Do I need this local file for both scripts?

The pandas script you will write in Step 2 loads data directly from the GitHub URL, so it does not strictly need a local file. The DuckDB script queries a local CSV file on disk.

Having the file downloaded now means both scripts will work without any extra setup later.

Seeing a curl error?

If you see curl: command not found, you can download the file manually instead. Open this URL in your browser:

https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/titanic.csv

Save the page as titanic.csv inside your titanic-analysis folder on the Desktop.

Help me download the Titanic CSV file into my project folder.

Your workspace is ready. Next up, you will load the Titanic dataset into pandas and start exploring what the data looks like.

Load and Explore the Dataset with Pandas

Your project folder is ready and DuckDB is installed. Now it's time to start analyzing real data.

The Titanic dataset is a classic beginner dataset with 891 passenger records covering who survived and who didn't. Before you can filter, group, or summarize anything, you need to load the data and understand what you're working with.

In this step, get ready to:

Load the Titanic CSV from a URL into a pandas DataFrame.
Explore the dataset's shape, data types, and missing values.
Generate summary statistics for numeric columns.

Import pandas and load the dataset

Pandas can read a CSV file directly from a URL. The Titanic dataset is hosted on GitHub as part of the pandas repository, so no download or login is needed.

Open titanic_pandas.py in your editor and add the following code to import pandas and preview the data:

import pandas as pd

# ============================================================
# LOAD THE DATASET
# ============================================================
url = "https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/titanic.csv"
df = pd.read_csv(url)

# ============================================================
# EXPLORE THE DATA
# ============================================================
# See the first 5 rows
print("=== First 5 Rows ===")
print(df.head())
print()

What does this code do?

import pandas as pd loads the pandas library and gives it the shorthand name pd. This is the standard convention you'll see in almost every pandas project.
pd.read_csv(url) reads the CSV file from the GitHub URL and stores it in a DataFrame called df. A DataFrame is a table-like structure with rows and columns.
df.head() prints the first 5 rows so you can preview the data before doing anything else.

Save titanic_pandas.py.
Run the script by running this command:

python titanic_pandas.py

You should see a table with 5 rows and columns like PassengerId, Survived, Pclass, Name, Sex, and Age.

Seeing an error instead of a table?

If you see ModuleNotFoundError: No module named 'pandas', pandas is not installed. Run pip install pandas and try again.
If the script hangs or shows a connection error, check your internet connection. The script downloads the CSV from GitHub each time it runs.
If you see command not found: python, try using python3 instead.

Help me debug why my pandas script isn't loading the Titanic CSV.

Explore the dataset structure

Seeing 5 rows is a good start, but you need to understand the full picture before analyzing anything. How many rows and columns are there? What types of data does each column hold? Are there missing values that could skew your results?

Add this code below your existing code in titanic_pandas.py to check the dataset's shape, data types, and missing values:

# Check the shape (rows, columns)
print(f"=== Shape: {df.shape[0]} rows, {df.shape[1]} columns ===")
print()

# Check column names and data types
print("=== Column Data Types ===")
print(df.dtypes)
print()

# Check for missing values
print("=== Missing Values ===")
print(df.isnull().sum())
print()

What does this code do?

df.shape returns the number of rows and columns as a tuple. The Titanic dataset has 891 rows and 12 columns.
df.dtypes shows each column's data type. You'll see int64 and float64 for numbers, and object for text columns like Name and Sex.
df.isnull().sum() counts how many values are missing in each column. Missing data is common in real-world datasets and can affect your analysis if you don't account for it.

Save titanic_pandas.py.
Rerun the script by running this command:

python titanic_pandas.py

In the Missing Values section of your output, you should see Age with 177 missing, Cabin with 687 missing, and Embarked with 2 missing.

Missing values don't match those numbers?

Make sure the URL in your code matches the one shown above exactly. A different version of the Titanic dataset may have different missing value counts.
Check that you haven't accidentally modified the df variable between loading and checking. The exploration code should come right after pd.read_csv().

Help me figure out why my missing value counts look wrong.

Generate summary statistics

You know the shape of the data and where the gaps are. Now you can get a quick statistical overview of every numeric column in a single command.

Add this code below your missing values check in titanic_pandas.py:

# Basic statistics for numeric columns
print("=== Summary Statistics ===")
print(df.describe())
print()

What does this code do?

df.describe() calculates count, mean, standard deviation, min, max, and percentiles for every numeric column.

Look at the Survived column. Its mean is about 0.38. Since Survived uses 1 for survived and 0 for did not survive, this mean tells you the overall survival rate was roughly 38%.

Save titanic_pandas.py and rerun the script.

python titanic_pandas.py

You should see a statistics table with rows like count, mean, std, min, 25%, 50%, 75%, and max for columns including Survived, Pclass, Age, and Fare.

✔️ Awesome, I've got everything!

Double check you've saved titanic_pandas.py and your output shows all five sections: first 5 rows, shape, data types, missing values, and summary statistics.

ⓧ I'd like to double check the full code

Here is the full titanic_pandas.py so far. Make sure your file matches this exactly.

import pandas as pd

# ============================================================
# LOAD THE DATASET
# ============================================================
url = "https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/titanic.csv"
df = pd.read_csv(url)

# ============================================================
# EXPLORE THE DATA
# ============================================================
# See the first 5 rows
print("=== First 5 Rows ===")
print(df.head())
print()

# Check the shape (rows, columns)
print(f"=== Shape: {df.shape[0]} rows, {df.shape[1]} columns ===")
print()

# Check column names and data types
print("=== Column Data Types ===")
print(df.dtypes)
print()

# Check for missing values
print("=== Missing Values ===")
print(df.isnull().sum())
print()

# Basic statistics for numeric columns
print("=== Summary Statistics ===")
print(df.describe())
print()

You now have a clear picture of the Titanic dataset: 891 passengers, 12 columns, and some notable gaps in the data. Next up, you'll filter and group this data to uncover patterns in who survived.

Filter and Query Data with Pandas

In the last step, you explored the Titanic dataset and discovered key patterns like 177 missing Age values and a 38% overall survival rate.

Now it's time to ask specific questions of your data. You'll filter, group, and sort the dataset using pandas operations that map directly to SQL's WHERE, GROUP BY, and ORDER BY.

In this step, get ready to:

Filter the dataset to find survivors in 1st class.
Group passengers by class and gender to calculate survival rates and average fares.
Sort passengers by fare to find the most expensive tickets.

Filter passengers who survived in 1st class

SQL uses WHERE to filter rows that match a condition. In pandas, you achieve the same thing with boolean indexing.

In titanic_pandas.py, add the following filtering code below the last print() statement in your file:

# ============================================================
# FILTERING (SQL WHERE equivalent)
# ============================================================
# Filter passengers who survived and were in 1st class
survivors_first_class = df[(df["Survived"] == 1) & (df["Pclass"] == 1)]
print("=== Survivors in 1st Class ===")
print(f"Count: {len(survivors_first_class)}")
print(survivors_first_class[["Name", "Sex", "Age", "Fare"]].head(10))
print()

What does this code do?

df["Survived"] == 1 creates a True/False mask for rows where the passenger survived.
df["Pclass"] == 1 creates another mask for 1st class passengers.
The & operator combines both masks so only rows matching both conditions pass through.
.head(10) limits the output to the first 10 matching rows, showing just the Name, Sex, Age, and Fare columns.

Save titanic_pandas.py.
Run the script by running this command:

python titanic_pandas.py

Scroll to the bottom of the output. You should see === Survivors in 1st Class === followed by a count and a table of 10 rows showing passenger names, sex, age, and fare.

Not seeing the filtered output?

Make sure you added the code below the existing exploration section, not inside it.
Check that you used & (not and) to combine the two conditions. Python's and keyword does not work with pandas boolean masks.
Why isn't my pandas boolean filter working?

Group passengers by class and gender

Grouping lets you split your data into categories and calculate summary statistics for each one. This is the pandas equivalent of SQL's GROUP BY clause.

Add the first grouping calculation below your filtering code:

# ============================================================
# GROUPING (SQL GROUP BY equivalent)
# ============================================================
# Survival rate by passenger class
survival_by_class = df.groupby("Pclass")["Survived"].mean()
print("=== Survival Rate by Class ===")
print(survival_by_class)
print()

What does this code do?

df.groupby("Pclass") splits the DataFrame into three groups, one for each passenger class (1, 2, and 3).
["Survived"].mean() calculates the average of the Survived column (0s and 1s) for each group. Because Survived is binary, the mean is the survival rate as a decimal.

Save titanic_pandas.py and run the script:

python titanic_pandas.py

You should see === Survival Rate by Class === with three values. 1st class has the highest survival rate (around 0.63) and 3rd class the lowest (around 0.24).

Seeing an error on groupby?

Make sure you typed groupby as one word (not group_by).
Check that the column names "Pclass" and "Survived" are capitalized exactly as shown. Column names are case-sensitive.
Why is my pandas groupby throwing an error?

Add the second grouping calculation below the survival rate code:

# Average fare by class and sex
avg_fare = df.groupby(["Pclass", "Sex"])["Fare"].mean()
print("=== Average Fare by Class and Sex ===")
print(avg_fare)
print()

What does this code do?

Passing a list ["Pclass", "Sex"] to groupby() groups the data by both columns, creating six groups (3 classes x 2 genders).
["Fare"].mean() calculates the average ticket price for each of those six groups.

Save titanic_pandas.py and run the script:

python titanic_pandas.py

You should see === Average Fare by Class and Sex === with six rows. 1st class females paid the most on average.

Seeing a KeyError?

Double-check that "Sex" and "Fare" are capitalized exactly as shown. Pandas column names are case-sensitive.
Make sure the column names are wrapped in a list with square brackets: ["Pclass", "Sex"].
Why am I getting a KeyError in my pandas groupby?

Sort passengers by ticket fare

Sorting lets you rank rows by a column's values. This is the pandas equivalent of SQL's ORDER BY clause.

Add the sorting code below your grouping section:

# ============================================================
# SORTING (SQL ORDER BY equivalent)
# ============================================================
# Top 10 passengers by fare (most expensive tickets)
top_fares = df.sort_values("Fare", ascending=False).head(10)
print("=== Top 10 Most Expensive Tickets ===")
print(top_fares[["Name", "Pclass", "Fare", "Survived"]])
print()

What does this code do?

sort_values("Fare", ascending=False) sorts the entire DataFrame by the Fare column in descending order, putting the most expensive tickets first.
.head(10) takes only the top 10 rows from that sorted result.
The column selection [["Name", "Pclass", "Fare", "Survived"]] keeps the output focused on the most relevant columns.

Save titanic_pandas.py and run the script:

python titanic_pandas.py

You should see === Top 10 Most Expensive Tickets === with a table of 10 rows. The most expensive fares are over $500.

Output not sorted correctly?

Make sure ascending=False is set. Without it, pandas sorts in ascending order (cheapest first).
Check that you're calling .head(10) after sort_values(), not before it. The order of chaining matters.
Why isn't my pandas sort_values working as expected?

✔️ Awesome, I've got everything!

Great. Double check you've saved titanic_pandas.py.

ⓧ I'd like to double check the full code

import pandas as pd

# ============================================================
# LOAD THE DATASET
# ============================================================
url = "https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/titanic.csv"
df = pd.read_csv(url)

# ============================================================
# EXPLORE THE DATA
# ============================================================
# See the first 5 rows
print("=== First 5 Rows ===")
print(df.head())
print()

# Check the shape (rows, columns)
print(f"=== Shape: {df.shape[0]} rows, {df.shape[1]} columns ===")
print()

# Check column names and data types
print("=== Column Data Types ===")
print(df.dtypes)
print()

# Check for missing values
print("=== Missing Values ===")
print(df.isnull().sum())
print()

# Basic statistics for numeric columns
print("=== Summary Statistics ===")
print(df.describe())
print()

# ============================================================
# FILTERING (SQL WHERE equivalent)
# ============================================================
# Filter passengers who survived and were in 1st class
survivors_first_class = df[(df["Survived"] == 1) & (df["Pclass"] == 1)]
print("=== Survivors in 1st Class ===")
print(f"Count: {len(survivors_first_class)}")
print(survivors_first_class[["Name", "Sex", "Age", "Fare"]].head(10))
print()

# ============================================================
# GROUPING (SQL GROUP BY equivalent)
# ============================================================
# Survival rate by passenger class
survival_by_class = df.groupby("Pclass")["Survived"].mean()
print("=== Survival Rate by Class ===")
print(survival_by_class)
print()

# Average fare by class and sex
avg_fare = df.groupby(["Pclass", "Sex"])["Fare"].mean()
print("=== Average Fare by Class and Sex ===")
print(avg_fare)
print()

# ============================================================
# SORTING (SQL ORDER BY equivalent)
# ============================================================
# Top 10 passengers by fare (most expensive tickets)
top_fares = df.sort_values("Fare", ascending=False).head(10)
print("=== Top 10 Most Expensive Tickets ===")
print(top_fares[["Name", "Pclass", "Fare", "Survived"]])
print()

Your pandas analysis script can now filter, group, and sort the Titanic data. Next up, you'll build a summary report and save it as a CSV file.

Build a Summary Report and Save to CSV

In the last step, you filtered, grouped, and sorted Titanic passenger data using pandas. Each of those operations answered a specific question about the dataset.

Now it is time to combine multiple statistics into a single summary table. You will group passengers by class and gender, calculate five different metrics for each group, and save the results to a CSV file that you could share with anyone.

In this step, get ready to:

Build a grouped summary table with five statistics per group.
Save the summary to a CSV file you can open and share.

Build a grouped summary table

So far, each of your groupby calls has calculated a single statistic. The .agg() method lets you calculate multiple statistics in one call and give each result column a clean name.

In titanic_pandas.py, scroll to the bottom of the file and find the last print() line in the sorting section.
Add the following code below it:

# ============================================================
# BUILD SUMMARY AND SAVE TO CSV
# ============================================================
# Summary table: survival stats by class and sex
summary = df.groupby(["Pclass", "Sex"]).agg(
    total_passengers=("PassengerId", "count"),
    survivors=("Survived", "sum"),
    survival_rate=("Survived", "mean"),
    avg_age=("Age", "mean"),
    avg_fare=("Fare", "mean")
).round(2)

print("=== Survival Summary by Class and Sex ===")
print(summary)
print()

What does this code do?

groupby(["Pclass", "Sex"]) splits the data into groups for every combination of class and gender. That gives you six groups (3 classes x 2 genders).
.agg() with named arguments like total_passengers=("PassengerId", "count") calculates a statistic and gives the result column a clean name in one step.
.round(2) trims all decimal values to two places so the output is easy to read.

Save titanic_pandas.py.
Run the script in your terminal by running:

python titanic_pandas.py

Near the bottom of the output, you should see a table titled === Survival Summary by Class and Sex === with six rows (one for each class and gender combination) and five statistic columns.

Seeing a syntax error?

Check that every opening parenthesis inside .agg() has a matching closing parenthesis. Each named argument like total_passengers=("PassengerId", "count") needs its own set of parentheses.
Make sure each argument inside .agg() is separated by a comma. A missing comma between lines causes Python to throw an error.
Verify that the column names in quotes match the original dataset exactly. PassengerId and Survived are case-sensitive.

Help me fix a syntax error in my pandas .agg() call.

Save the summary to a CSV file

A printed table is useful for quick checks, but saving it to a file means you can share it, open it in a spreadsheet, or feed it into another script later.

In titanic_pandas.py, add the following code at the very bottom of the file, below the print() line you just added:

# Save the summary to a new CSV file
summary.to_csv("titanic_summary.csv")
print("Summary saved to titanic_summary.csv")

What does this code do?

.to_csv("titanic_summary.csv") writes the summary DataFrame to a new CSV file in your project folder. The Pclass and Sex groupings are included as index columns so the file is ready to open in any spreadsheet tool.

Save titanic_pandas.py.
Run the script again in your terminal by running:

python titanic_pandas.py

At the very end of the output, you should see Summary saved to titanic_summary.csv.

Confirm the file was created by running:

ls

You should see titanic_summary.csv listed alongside titanic_pandas.py in the output.

Open titanic_summary.csv in VS Code or a text editor to verify it has six rows of data with columns for total passengers, survivors, survival rate, average age, and average fare.

Don't see the CSV file?

Make sure you ran the script from inside your titanic-analysis folder. The CSV file is saved relative to where you run the command, not where the script lives.
Check the terminal output for any error messages. If you see PermissionError, close the CSV file if it is open in another program and try again.

Help me figure out why my CSV file was not created.

✔️ Awesome, I've got everything!

Great. Double check you have saved titanic_pandas.py and confirmed that titanic_summary.csv exists in your project folder.

ⓧ I'd like to double check the full code

Here is the complete titanic_pandas.py file. Compare your code against this to make sure everything matches.

import pandas as pd

# ============================================================
# LOAD THE DATASET
# ============================================================
url = "https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/titanic.csv"
df = pd.read_csv(url)

# ============================================================
# EXPLORE THE DATA
# ============================================================
# See the first 5 rows
print("=== First 5 Rows ===")
print(df.head())
print()

# Check the shape (rows, columns)
print(f"=== Shape: {df.shape[0]} rows, {df.shape[1]} columns ===")
print()

# Check column names and data types
print("=== Column Data Types ===")
print(df.dtypes)
print()

# Check for missing values
print("=== Missing Values ===")
print(df.isnull().sum())
print()

# Basic statistics for numeric columns
print("=== Summary Statistics ===")
print(df.describe())
print()

# ============================================================
# FILTERING (SQL WHERE equivalent)
# ============================================================
# Filter passengers who survived and were in 1st class
survivors_first_class = df[(df["Survived"] == 1) & (df["Pclass"] == 1)]
print("=== Survivors in 1st Class ===")
print(f"Count: {len(survivors_first_class)}")
print(survivors_first_class[["Name", "Sex", "Age", "Fare"]].head(10))
print()

# ============================================================
# GROUPING (SQL GROUP BY equivalent)
# ============================================================
# Survival rate by passenger class
survival_by_class = df.groupby("Pclass")["Survived"].mean()
print("=== Survival Rate by Class ===")
print(survival_by_class)
print()

# Average fare by class and sex
avg_fare = df.groupby(["Pclass", "Sex"])["Fare"].mean()
print("=== Average Fare by Class and Sex ===")
print(avg_fare)
print()

# ============================================================
# SORTING (SQL ORDER BY equivalent)
# ============================================================
# Top 10 passengers by fare (most expensive tickets)
top_fares = df.sort_values("Fare", ascending=False).head(10)
print("=== Top 10 Most Expensive Tickets ===")
print(top_fares[["Name", "Pclass", "Fare", "Survived"]])
print()

# ============================================================
# BUILD SUMMARY AND SAVE TO CSV
# ============================================================
# Summary table: survival stats by class and sex
summary = df.groupby(["Pclass", "Sex"]).agg(
    total_passengers=("PassengerId", "count"),
    survivors=("Survived", "sum"),
    survival_rate=("Survived", "mean"),
    avg_age=("Age", "mean"),
    avg_fare=("Fare", "mean")
).round(2)

print("=== Survival Summary by Class and Sex ===")
print(summary)
print()

# Save the summary to a new CSV file
summary.to_csv("titanic_summary.csv")
print("Summary saved to titanic_summary.csv")

Your pandas analysis script is complete. You have a saved CSV report that breaks down survival statistics by class and gender. Next up, you will write SQL queries using DuckDB that produce the same results, so you can see both approaches side by side.

Run Equivalent SQL Queries with DuckDB

Your pandas analysis is complete and your summary report is saved to a CSV file.

Now you will write SQL queries that produce the exact same results using DuckDB. DuckDB can query CSV files directly with SQL from inside your Python script, so there is no database server to install or data to import.

In this step, get ready to:

Download the Titanic CSV locally and write your first SQL query with DuckDB.
Write GROUP BY and ORDER BY queries that match your pandas results.
Build a full summary query and compare it to your saved report.

Import DuckDB and download the CSV

In the pandas script, you loaded the CSV directly from a URL. DuckDB queries CSV files by file path, so you need a local copy of the dataset first.

Open titanic_sql.py in your editor and add the imports and CSV download code by copying this:

import duckdb
import urllib.request
import os

# ============================================================
# DOWNLOAD THE CSV FILE (if not already present)
# ============================================================
csv_file = "titanic.csv"
if not os.path.exists(csv_file):
    url = "https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/titanic.csv"
    urllib.request.urlretrieve(url, csv_file)
    print(f"Downloaded {csv_file}")

What does this code do?

duckdb lets you run SQL queries on CSV files directly from Python.
urllib.request and os are Python standard library modules for downloading files and checking if a file already exists.
The if not os.path.exists(csv_file) check prevents re-downloading the file every time you run the script.

Save titanic_sql.py.
Run the script by entering this command in your terminal:

python titanic_sql.py

You should see Downloaded titanic.csv printed in your terminal.

Don't see the download message?

Make sure you are running the command from inside your titanic-analysis folder. DuckDB looks for files relative to where you run the script.
If you see ModuleNotFoundError: No module named 'duckdb', DuckDB is not installed. Run pip install duckdb first.
If you already have a titanic.csv file in the folder (from a previous download), the script skips the download. That is expected behavior.
Why isn't my DuckDB script downloading the CSV file?

Now that the CSV is downloaded locally, you can start writing SQL queries against it.

Add Query 1 below the download code in titanic_sql.py by copying this code:

# ============================================================
# QUERY 1: SELECT with WHERE (filter survivors in 1st class)
# ============================================================
print("=== Query 1: Survivors in 1st Class ===")
duckdb.sql("""
    SELECT Name, Sex, Age, Fare
    FROM 'titanic.csv'
    WHERE Survived = 1 AND Pclass = 1
    LIMIT 10
""").show()

What does this query do?

duckdb.sql() runs a SQL query and returns a result object. Calling .show() prints that result as a formatted table.
FROM 'titanic.csv' tells DuckDB to read the CSV file directly. No table creation or import step is needed.
The WHERE clause filters for passengers who survived (Survived = 1) and were in 1st class (Pclass = 1). This is the SQL equivalent of your pandas boolean indexing from Step 3.

Save titanic_sql.py and run the script again.
You should see a table of 10 first-class survivors with their Name, Sex, Age, and Fare columns.

Seeing a SQL syntax error?

Check that the file name inside the SQL string is 'titanic.csv' with single quotes. Double quotes around the file name cause a syntax error in DuckDB.
Make sure the triple quotes (""") around the SQL string are present on both sides. Missing one will cause a Python syntax error.
Why is my DuckDB SQL query returning a syntax error?

Why DuckDB instead of an online SQL editor?

DuckDB runs entirely inside your Python script. There is no database server to install, no data to import, and no browser tab to switch to.

You can compare your SQL output to your pandas output in the same terminal window, which makes it easy to see how the two approaches line up.

Write GROUP BY and ORDER BY queries

In your pandas script, you used groupby() and sort_values() to summarize and rank data. Now you will write the SQL equivalents using GROUP BY and ORDER BY.

Add Query 2 below Query 1 in titanic_sql.py by copying this code:

# ============================================================
# QUERY 2: GROUP BY (survival rate by class)
# ============================================================
print("=== Query 2: Survival Rate by Class ===")
duckdb.sql("""
    SELECT Pclass,
           ROUND(AVG(Survived), 2) AS survival_rate
    FROM 'titanic.csv'
    GROUP BY Pclass
    ORDER BY Pclass
""").show()

What does this query do?

GROUP BY Pclass groups all 891 passengers into three buckets (1st, 2nd, 3rd class).
AVG(Survived) calculates the survival rate for each group. Since Survived is 0 or 1, averaging it gives you the proportion who survived.
This is the SQL equivalent of df.groupby("Pclass")["Survived"].mean() from your pandas script.

Save titanic_sql.py and run the script.
You should see a table with three rows. 1st class has a survival rate around 0.63, 2nd class around 0.47, and 3rd class around 0.24.

Numbers don't match your pandas output?

Small rounding differences are normal because of the ROUND(..., 2) in SQL. The overall pattern should be the same: 1st class survives at the highest rate, 3rd class at the lowest.
If you see no output at all, make sure you have .show() at the end of your duckdb.sql() call.
Why are my DuckDB GROUP BY results different from my pandas results?

Add Query 3 below Query 2 by copying this code:

# ============================================================
# QUERY 3: GROUP BY with multiple columns (avg fare by class and sex)
# ============================================================
print("=== Query 3: Average Fare by Class and Sex ===")
duckdb.sql("""
    SELECT Pclass, Sex,
           ROUND(AVG(Fare), 2) AS avg_fare
    FROM 'titanic.csv'
    GROUP BY Pclass, Sex
    ORDER BY Pclass, Sex
""").show()

What's different about this query?

This query groups by two columns (Pclass, Sex) instead of one. That creates six groups (3 classes x 2 genders) so you can see how fare varies by both class and gender at the same time.

Save titanic_sql.py and run the script.
You should see a 6-row table showing the average fare for each class and gender combination.

Seeing a column name error?

Check that Pclass and Sex are capitalized exactly as shown. DuckDB column names from the CSV are case-sensitive.
Why is my DuckDB query saying a column name is not found?

Add Query 4 below Query 3 by copying this code:

# ============================================================
# QUERY 4: ORDER BY with LIMIT (top 10 most expensive tickets)
# ============================================================
print("=== Query 4: Top 10 Most Expensive Tickets ===")
duckdb.sql("""
    SELECT Name, Pclass, Fare, Survived
    FROM 'titanic.csv'
    ORDER BY Fare DESC
    LIMIT 10
""").show()

What does this query do?

ORDER BY Fare DESC sorts all passengers from highest fare to lowest.
LIMIT 10 keeps only the top 10. This is the SQL equivalent of df.sort_values("Fare", ascending=False).head(10) from your pandas script.

Save titanic_sql.py and run the script.
You should see the 10 most expensive tickets. The top fares are over $500.

Output looks wrong or is missing?

If the sort order is reversed (cheapest first), check that you wrote DESC after ORDER BY Fare. Without DESC, SQL defaults to ascending order.
Why is my DuckDB ORDER BY query not sorting correctly?

Write the full summary query

Your final query will calculate the same grouped summary you built with pandas in Step 4. If the numbers match your titanic_summary.csv file, you know both approaches are producing identical results.

Add Query 5 below Query 4 in titanic_sql.py by copying this code:

# ============================================================
# QUERY 5: Full summary (matches the pandas summary table)
# ============================================================
print("=== Query 5: Survival Summary by Class and Sex ===")
duckdb.sql("""
    SELECT Pclass, Sex,
           COUNT(*) AS total_passengers,
           SUM(Survived) AS survivors,
           ROUND(AVG(Survived), 2) AS survival_rate,
           ROUND(AVG(Age), 2) AS avg_age,
           ROUND(AVG(Fare), 2) AS avg_fare
    FROM 'titanic.csv'
    GROUP BY Pclass, Sex
    ORDER BY Pclass, Sex
""").show()

What does this query do?

COUNT(*) counts total passengers in each group.
SUM(Survived) counts how many survived (since the column is 0 or 1, summing gives the count of 1s).
AVG(Survived), AVG(Age), and AVG(Fare) compute the same averages as the pandas .agg() call from Step 4.
ROUND(..., 2) rounds to 2 decimal places, matching the .round(2) you used in pandas.

Save titanic_sql.py and run the script one final time.
Scroll to the bottom of the terminal output. You should see a 6-row table with columns for total_passengers, survivors, survival_rate, avg_age, and avg_fare.
Open titanic_summary.csv in your editor and compare the numbers to the Query 5 output. They should match.

Numbers don't match the CSV?

The avg_age values may differ slightly. The Age column has 177 missing values. Pandas and DuckDB both exclude NaN from averages by default, but tiny floating-point differences can appear. The pattern should be the same.
If your total_passengers counts do not match, check that your pandas script groups by Pclass and Sex (same as your SQL query).
Why are my DuckDB summary numbers different from my pandas CSV?

✔️ Awesome, I've got everything!

Make sure you have saved titanic_sql.py before moving on.

ⓧ I'd like to double check the full code

Here is the complete titanic_sql.py file. Compare it line by line with your version.

import duckdb
import urllib.request
import os

# ============================================================
# DOWNLOAD THE CSV FILE (if not already present)
# ============================================================
csv_file = "titanic.csv"
if not os.path.exists(csv_file):
    url = "https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/titanic.csv"
    urllib.request.urlretrieve(url, csv_file)
    print(f"Downloaded {csv_file}")

# ============================================================
# QUERY 1: SELECT with WHERE (filter survivors in 1st class)
# ============================================================
print("=== Query 1: Survivors in 1st Class ===")
duckdb.sql("""
    SELECT Name, Sex, Age, Fare
    FROM 'titanic.csv'
    WHERE Survived = 1 AND Pclass = 1
    LIMIT 10
""").show()

# ============================================================
# QUERY 2: GROUP BY (survival rate by class)
# ============================================================
print("=== Query 2: Survival Rate by Class ===")
duckdb.sql("""
    SELECT Pclass,
           ROUND(AVG(Survived), 2) AS survival_rate
    FROM 'titanic.csv'
    GROUP BY Pclass
    ORDER BY Pclass
""").show()

# ============================================================
# QUERY 3: GROUP BY with multiple columns (avg fare by class and sex)
# ============================================================
print("=== Query 3: Average Fare by Class and Sex ===")
duckdb.sql("""
    SELECT Pclass, Sex,
           ROUND(AVG(Fare), 2) AS avg_fare
    FROM 'titanic.csv'
    GROUP BY Pclass, Sex
    ORDER BY Pclass, Sex
""").show()

# ============================================================
# QUERY 4: ORDER BY with LIMIT (top 10 most expensive tickets)
# ============================================================
print("=== Query 4: Top 10 Most Expensive Tickets ===")
duckdb.sql("""
    SELECT Name, Pclass, Fare, Survived
    FROM 'titanic.csv'
    ORDER BY Fare DESC
    LIMIT 10
""").show()

# ============================================================
# QUERY 5: Full summary (matches the pandas summary table)
# ============================================================
print("=== Query 5: Survival Summary by Class and Sex ===")
duckdb.sql("""
    SELECT Pclass, Sex,
           COUNT(*) AS total_passengers,
           SUM(Survived) AS survivors,
           ROUND(AVG(Survived), 2) AS survival_rate,
           ROUND(AVG(Age), 2) AS avg_age,
           ROUND(AVG(Fare), 2) AS avg_fare
    FROM 'titanic.csv'
    GROUP BY Pclass, Sex
    ORDER BY Pclass, Sex
""").show()

Secret mission

Handle Missing Data and Reveal Hidden Patterns

Your analysis scripts work, but they skip over a common real-world problem. Real datasets have missing values, hidden relationships, and patterns that only emerge when you reshape the data. In these three challenges, you will handle missing values, engineer a new feature, and build a pivot table.

Before you go

Clean Up Your Resources

This project runs entirely on your local machine, so there are no ongoing costs or cloud resources to worry about.

Decide whether to keep your project files for future reference, or delete them to free up space.

Resources you used:

titanic-analysis project folder.
titanic_pandas.py (pandas analysis script).
titanic_sql.py (DuckDB SQL script).
titanic.csv (downloaded dataset).
titanic_summary.csv (generated summary report).
duckdb Python package (installed via pip).

Keep everything running

No action needed. Choose this if you plan to keep experimenting with the Titanic dataset or want to reference your scripts later.

Your titanic-analysis folder and all files inside it stay on your Desktop.
The duckdb package stays installed in your Python environment.

Pause - I'll come back to this later

Since everything runs locally, there is nothing to pause. Your files will be waiting for you whenever you come back.

Keep your titanic-analysis folder as-is. You can rerun either script anytime.
If you want to free up a small amount of space, delete titanic.csv and titanic_summary.csv. Your scripts will regenerate them when you run them again.

Delete - I don't want to use this again

Remove all project files and the installed package to start fresh.

Delete the entire titanic-analysis folder from your Desktop.
Uninstall the DuckDB package by running:

pip uninstall duckdb

Confirm the uninstall by typing y when prompted.

Mission accomplished

That's a Wrap

Nice work! You just built two Python scripts that analyze the Titanic passenger dataset using pandas and SQL.

What you learned:

Loaded and explored a real-world dataset with 891 rows and 12 columns using pandas.
Filtered, grouped, and sorted data using pandas operations that map directly to SQL WHERE, GROUP BY, and ORDER BY.
Built a grouped summary report, saved it to CSV, and wrote 5 equivalent SQL queries using DuckDB to compare both approaches side by side.
Handled missing data, engineered a new feature, and built a pivot table to reveal hidden survival patterns.

Ready to quiz yourself?