Working with JSON and SQL Data using Pandas
Lecture 16 · Descendants of Machine Learning
1. Introduction
This lecture covers two important non-CSV data formats frequently encountered in real-world data science
work:
• JSON — JavaScript Object Notation
• SQL — Structured Query Language / Database data
Both are extremely common in industry data pipelines and APIs.
2. JSON Format
2.1 What is JSON?
• JSON stands for JavaScript Object Notation
• It is a universal data format — almost all programming languages can read it (Python, Java,
JavaScript, etc.)
• One of the most widely used formats for data exchange on the internet
2.2 Why is JSON Important?
• Whenever you call an API (Application Programming Interface), the response is almost always in JSON
format
• Example: a request to a weather API or a currency exchange API returns data as JSON
• This JSON data can then be easily converted into a Pandas DataFrame for analysis
Note: APIs will be covered in detail in a dedicated video after this data collection series.
3. Loading JSON into a Pandas DataFrame
3.1 Dataset Used — Recipes Dataset
• Contains approximately 3,900 dishes
• Each dish has: id, cuisine (e.g., Jamaican, Mexican, Italian), and ingredients (a list)
• This is essentially a classification problem: given the ingredients, predict the type of cuisine
3.2 Method: pd.read_json()
Just like pd.read_csv() reads CSV files, pd.read_json() reads JSON files:
import pandas as pd
df = pd.read_json('[Link]')
• The file should be in the same folder as your notebook
• Output: a DataFrame with columns like cuisine and ingredients
3.3 Loading JSON from a URL
You can also load JSON data directly from a URL — no need to download the file first:
df = pd.read_json('[Link]
Example used in lecture: A Currency Exchange Rate dataset — 160 rows, 7 columns, with different parent
currencies and their exchange values.
3.4 Key Parameters of pd.read_json()
The parameters are very similar to pd.read_csv(). Some useful ones:
Parameter Purpose
encoding Change encoding if characters aren't displaying correctly
chunksize Load large files in chunks to save RAM
dtype Specify column data types
Tip: Once you are comfortable with pd.read_csv(), picking up pd.read_json() is very easy — the pattern is
identical.
4. SQL Data — Loading from a Database
4.1 What is SQL?
• SQL stands for Structured Query Language
• Used to directly query and retrieve data from relational databases
• Very common in industry — many Kaggle datasets come in .sql format
4.2 The Problem — Why Do We Need XAMPP?
When you download a .sql file (like the World Cities dataset), it is not a simple file you can open like a CSV. It
is a database dump — the data is structured to live inside a database server. To use it, you need:
• A running database server (MySQL) on your machine
• A way to import the .sql file into that server
• Then connect Python to that server to read the data
This is exactly where XAMPP comes in.
4.3 What is XAMPP?
• XAMPP is a free, easy-to-install software that sets up a local server environment on your machine
• It bundles: Apache (web server) + MySQL (database server)
• After setup, access the database via browser at: localhost/phpmyadmin
• Import your .sql file there — the data is now live in tables, ready for Python to read
Analogy: XAMPP is like setting up a mini office server in your room. The .sql file is the raw data, phpMyAdmin is
the file manager, and Python connects to it through the connector library.
4.4 Dataset Used — World Cities Dataset
Contains 3 tables:
• city — city name, country code, district, population
• country — country details, surface area, independence year, population, life expectancy
• countrylanguage — languages spoken in each country
5. Connecting Python to MySQL
5.1 Step 1 — Install the Connector Library
A bridge library is needed to allow Python/Pandas to communicate with MySQL. Without this, Python has no
way to talk to the database server:
!pip install mysql-connector-python
5.2 Step 2 — Import and Create a Connection
import [Link]
conn = [Link](
host='localhost', # your machine (use remote IP for AWS etc.)
user='root', # default username in XAMPP
password='', # empty by default in XAMPP
database='world' # name of the database to connect to
)
• This returns a MySQL connection object stored in the variable conn
• Use localhost for your own machine; use a remote IP (e.g., AWS) for cloud databases
6. Loading SQL Data into a Pandas DataFrame
6.1 Method: pd.read_sql_query()
df = pd.read_sql_query("SELECT * FROM city", conn)
Two things are required:
• SQL query — written as a string
• conn — the connection object created in the previous step
Pandas automatically creates a DataFrame from the query result.
7. Using SQL Queries to Filter Data
Since you are writing actual SQL queries, you can filter and manipulate data before it even loads into the
DataFrame — making it very efficient:
Example 1 — Load the entire city table
df = pd.read_sql_query("SELECT * FROM city", conn)
# Returns 4079 rows x 5 columns
Example 2 — Filter by country (India)
df = pd.read_sql_query("SELECT * FROM city WHERE CountryCode = 'IND'", conn)
# Returns 341 rows — all cities in India
Example 3 — Filter by country (USA)
df = pd.read_sql_query("SELECT * FROM city WHERE CountryCode = 'USA'", conn)
# Returns 274 rows — all cities in USA
Example 4 — Filter by Life Expectancy
df = pd.read_sql_query("SELECT * FROM country WHERE LifeExpectancy > 60", conn)
# Returns only countries where life expectancy is greater than 60
Example 5 — Load the country table
df = pd.read_sql_query("SELECT * FROM country", conn)
Key Insight: If you know SQL, you can write powerful queries to filter, join, and aggregate data before bringing it
into Python — making your analysis workflow much more efficient.
8. Key Parameters of pd.read_sql_query()
Parameter Purpose
sql The SQL query string
con The connection object (conn)
index_col Set a specific column as the DataFrame index
chunksize Load large tables in chunks to avoid RAM overload
columns Choose specific columns to load
9. Summary
Format Pandas Function Common Use Case
JSON pd.read_json() API responses, web data
SQL DB pd.read_sql_query() Relational databases (MySQL, PostgreSQL, SQLite, etc.)
CSV pd.read_csv() Flat files, Kaggle datasets (covered in Lecture 15)
• Both pd.read_json() and pd.read_sql_query() follow the same pattern as pd.read_csv() — once you
know one, the others are straightforward
• Next Lecture: Fetching data from a real API and converting it into a DataFrame
Action Item: Download the documentation links from the video description and experiment with different
datasets on Kaggle using both read_json() and read_sql_query().