Beyond the CSV: other file formats, rows pulled from a SQL database, and live data fetched from a web API.
Programming for Data Science · University of the Philippines Cebu
CSV, JSON, Excel — each with a one-line pandas reader.
Ask a database a question and load the answer.
Fetch fresh data over the web as JSON.
Real projects rarely start with a clean CSV. Knowing where data lives — and how to reach it — is half the job.
A file is a book on your own shelf. A database is a library: you ask the librarian for exactly the pages you need. An API is ordering by phone: you ask from a menu, and the answer is delivered to you.
Load data from a database and from a live API into a DataFrame.
The agreed layout of the data inside a file; it decides which reader you use.
e.g. CSV, JSON, Excel, Parquet → read_csv, read_json, …
Text written like Python lists and dictionaries; the usual format of web replies.
e.g. [{"city": "Cebu", "pop": 964000}]
The codebook that turns a file's bytes into letters. The wrong one garbles ñ.
e.g. encoding="utf-8"
A file's address: the folders to walk through, then its name.
e.g. data/raw/flood.csv
A store of many named tables you ask questions of, instead of opening a file.
e.g. the cities table inside cities.db
One question to a database, written in SQL: which columns, from which table, which rows.
e.g. SELECT city FROM cities WHERE pop > 900000
An open line between your program and a database; queries travel through it.
e.g. conn = sqlite3.connect("cities.db")
A web address built for programs; each endpoint is one address that serves one kind of data.
e.g. /api/flood/projects
The message you send to a web server, and the reply it sends back.
e.g. r = requests.get(url) — r is the response
The three-digit number on every response saying whether it worked.
e.g. 200 OK, 404 not found, 429 too many requests
Everything so far started with read_csv. But data also sits in
databases and behind web APIs. Same DataFrame at the end — different way in.
int64, float64, object (text){"city": "Cebu"} (week 3)Once loaded, it's a DataFrame — all your pandas skills apply.
How you get there: a file reader, a SQL query, or an HTTP request.
One reader per format.
You already load CSV files with read_csv. This part adds the other common file formats — the agreed layout of the bytes inside a file — and the two things that trip people up: text encoding and file paths.
A CSV stores only text — types are guessed on load, and nesting is impossible. JSON keeps structure, Excel keeps sheets, and each has its place.
[{"city": "Cebu", "pop": 964000}].xlsx) that can hold several sheetsFlat text. Universal, but loses types.
Nested, self-describing. The web's format.
Multiple sheets. Common from offices.
JSON (JavaScript Object Notation) writes data as plain
text using two shapes you know from week 3: [ ] for a list and
{ } for a dictionary of key: value pairs. Almost every web API replies
in JSON.
A CSV is a grid on paper. JSON is a set of labelled index cards: every value carries its own label, and a card can hold smaller cards inside it.
txt = '[{…}, {…}]'JSON is just text (a string): a list holding two recordsjson.loads(txt)"load string": turn the JSON text into real Python objects<class 'list'> <class 'dict'>the outside is a list; each item in it is a dictionarypd.DataFrame(data)one row per dictionary, one column per keyThe pattern is identical: read_ plus the format. Excel files can
hold many sheets, so you name the one you want.
The text in quotes is a file path:
the file's address on your computer. A bare name like "data.csv" means "in the
same folder as this notebook".
pd.read_json("data.json")read a JSON file whose records become rowspd.read_excel(…, sheet_name="2024")read one sheet (tab) of a workbook, the one named 2024| Format | Best for | Reader |
|---|---|---|
| CSV | simple, shareable tables | read_csv |
| JSON | nested data, web responses | read_json |
| Excel | multi-sheet office files | read_excel |
| Parquet | big data, fast + typed | read_parquet |
For anything large or reused, parquet (a compact file format made for data tools) keeps types and loads far faster than CSV — worth knowing exists.
Every reader has a writer: df.to_csv, to_json, and so on.
| Format | Size | Write | Read | Keeps dtypes? |
|---|---|---|---|---|
| CSV | 11.44 MB | 104 ms | 59 ms | No — everything is text |
| CSV.gz | 2.84 MB | 319 ms | 65 ms | No |
| JSON | 18.61 MB | 167 ms | 103 ms | Partly |
| Parquet | 3.37 MB | 72 ms | 84 ms | Yes |
| SQLite | 12.13 MB | 89 ms | 77 ms | Yes |
Parquet is a file format built for data tools: stored in columns, compressed, and with each column's type written inside. SQLite is a whole database kept in one file. .gz means the file is zipped (compressed).
Parquet is 3.4× smaller than CSV and the fastest to write, and it remembers that budget is a float. JSON is the largest and slowest at everything — worth knowing, since it is most people’s reflex for "just save this somewhere".
A CSV is characters and commas. Everything you learned in week 6 about pd.to_numeric exists because the format has no slot for "this column is a float".
The schema is the list of columns and their types. It travels with the data, so the read-back needs no conversion step and cannot guess wrong.
df.to_csv("f.csv")write the table to a CSV file (every reader has a matching writer).budget.dtyperead it back and ask what type the budget column hasfloat64decimal numbers — the same answer both times, but CSV got there by guessing from the text, Parquet by reading the type it storedThere is no best format, only a best fit. These questions pick it for you almost every time.
Like choosing between a printed letter, a PDF and a shared spreadsheet: it depends who will open it and what they will do with it.
The flood dataset ships as gzipped CSV, not Parquet — because the production server has no pyarrow (the extra library pandas needs to read and write Parquet). Deployment constraints beat benchmarks.
A hardcoded "C:\\Users\\me\\data.csv" is the single most common reason a classmate cannot run your notebook.
A file path is a file's address: folders separated by / (Mac, Linux) or \ (Windows). A relative path (data/raw/flood.csv) starts from the current folder; an absolute one (C:\Users\…) starts from the top of one particular computer.
The cwd (current working directory) is the folder your program happens to be running in. Notebooks are run from wherever the person happened to be. Anchor paths to a known point in the project instead.
Path("data") / "raw" / "flood.csv"build the path piece by piece; the / joins folder names with the right separator for your computerDATA.exists() → Truethe file really is thereDATA.stat().st_size / 1e6the file's size in bytes, divided by a million: 11.44 MBHalf of encoding errors crash. The other half just quietly change your data.
A file stores bytes (numbers), not letters. An encoding is the codebook that turns bytes into letters; utf-8 and latin-1 are two common codebooks. Read with the wrong one and ñ turns into ñ.
This matters here more than most places: Filipino names and place names are full of ñ, and ñ is exactly where the two common encodings disagree.
A crash sends you to look. Mojibake passes every test you would think to write and lands in a published table.
UnicodeDecodeError is the error type — "these bytes are not valid in the codebook you named"byte 0xf1 in position 2the culprit is byte 0xf1 (ñ in latin-1), third character in (counting from 0): "Cañete".encode("utf-8").decode("latin-1")turn the text into utf-8 bytes, then read them with the wrong codebook'Cañete'mojibake: garbled letters from the wrong encoding, with no error at allBe explicit on read. If you do not know what the file is, look at the bytes rather than trying encodings until one stops complaining.
Excel writes a byte-order mark. Plain utf-8 leaves it in your first column name as an invisible character that breaks every lookup.
encoding="utf-8-sig"utf-8 that also skips the invisible byte-order mark (BOM) Excel puts at the startopen(path, "rb")open the file in "read bytes" mode, without decoding anythingf.read(4)look at the first four raw bytes; b'\xef\xbb\xbf' is the BOMMost analyses touch three or four columns. Loading fifteen and ignoring eleven costs memory and time for nothing.
Two columns: 24 ms and 2.6 MB. All fifteen: 63 ms and 28.5 MB (memory measured with memory_usage(deep=True)). The saving grows with the file.
usecols=["region", "budget"]load only these two columns and skip the other thirteendtype={"year": "category"}tell pandas a column's type up front instead of letting it guessparse_dates=["start_date"]read that column as real dates, not textPast a certain size you cannot hold the table in memory at all. Process it in pieces and keep only the summary.
A chunk is one piece of the file, here 5,000 rows. An iterator hands you the pieces one at a time, in a loop.
Against 28.5 MB for the whole frame. At 34k rows this is a demonstration; at 34M rows it is the only way it runs.
for chunk in pd.read_csv(path, chunksize=5000)read 5,000 rows, run the loop body, forget them, read the next 5,000total += chunk.budget.sum()keep a running total of the budgets (+= adds to what is already there)(34079, 1.60008878239283)all 34,079 rows counted; total budget about 1.60 trillion pesos (1e12 is a trillion)APIs return records with objects inside them. pd.DataFrame leaves those as dicts in a cell; json_normalize spreads them into columns.
loc.region came from {"loc": {"region": ...}}. You can rename afterwards, but the default is honest about where it came from.
nested = [ {…}, {…} ]a list of two records; each has a loc dictionary inside itpd.json_normalize(nested)flatten: each inner key becomes its own columnloc.regionthe dot in the column name says "region, from inside loc"You need to store a cleaned 34,079-row table that only your code will read, and you care about size and load time. Which format?
to_numeric step is needed on read. JSON was the worst on every axis (18.61 MB, slowest read). Choose CSV only when a human needs to open it — which was not the requirement here.Ask a database; load the answer.
You can now read data from files. This part reads it from a database instead, by sending it a question written in SQL — and you will recognise every idea from pandas.
Millions of rows, many people writing at once, related tables — a CSV can't do this. A database can, and SQL is the language you ask it questions in.
SELECT city FROM citiesA database is a library with a librarian. Instead of carrying every book home (loading the whole CSV), you hand over a request slip (a query) and get back only the pages you asked for.
Queries stay fast on data too big to open in memory.
Many users, one consistent source of truth.
SELECT what, FROM where, WHERE whichYou name the columns, the table, and a condition. It reads almost like English — and it's the same filter-and-select logic you know from pandas.
SQL keywords are written in CAPITALS by habit (SQL does not
mind); text values go in single quotes; the ; marks the end of the query.
SELECT city, popwhich columns you want back (SELECT * means all of them)FROM citieswhich table to look inWHERE region = 'Visayas'keep only rows where the condition is true, like a pandas maskORDER BY pop DESCsort by population, biggest first (DESC = descending)read_sql hands back a DataFrameConnect to the database, pass your query as a string, and the result arrives as a DataFrame — ready for every pandas move from the last three weeks.
A connection is an open line between your program and the database: you open it once, send queries through it, and close it when you are done.
import sqlite3SQLite support, built into Python: a database that lives in a single filesqlite3.connect("cities.db")open a connection to the database file cities.dbpd.read_sql(query, conn)send the query through the connection and turn the rows that come back into a DataFrame| Task | SQL | pandas |
|---|---|---|
| Pick columns | SELECT a, b | df[["a","b"]] |
| Filter rows | WHERE x > 5 | df[df.x > 5] |
| Sort | ORDER BY x | sort_values("x") |
| Summarise per group | GROUP BY r | groupby("r") |
Push heavy filtering into the SQL query so less data crosses the wire; do the final shaping in pandas.
Ask the librarian for the three books you need rather than borrowing the whole shelf and sorting it at home.
Let the database do the big cuts; let pandas do the polishing.
You already know what this query does — you wrote it in pandas in week 7. Recognising the equivalence is most of learning SQL.
COUNT(*) AS ncount the rows in each group and call the column nGROUP BY regionone pile per region, exactly like groupby("region")ORDER BY total DESC LIMIT 3biggest total first, keep only the top 3With a 34,079-row table it makes little difference. At 34 million it is the difference between a query and an out-of-memory error.
The boundary between the two worlds is one function. Query in SQL, analyse in pandas, and never write a loop over a cursor (the low-level object that hands back database rows one at a time).
Never build a query by string concatenation (gluing text together). Pass parameters — it is both safer and faster, because the database can reuse its plan (its worked-out way of running that query).
A parameter here is a ? placeholder in the query whose value you pass separately; the database always treats it as data, never as SQL. SQL injection is the attack where typed-in text sneaks extra SQL into a query.
WHERE region = ?a placeholder: "a value goes here"params=["Region III"]the value for the ?, sent separately and safelyf"…'{user_input}'"pasting text straight into the query: a name like O'Brien breaks it, and malicious text can change itA cleaned table is worth storing. SQLite is a single file with no server to run, which makes it the easiest durable option you have.
replace drops and rebuilds; append adds. Choosing wrong duplicates every row or destroys the table.
cleaned.to_sql("projects_clean", con, …)save the DataFrame as a table called projects_clean inside the databaseindex=Falsedo not store pandas' row numbers as an extra columnSELECT COUNT(*) … → 34079ask the database how many rows the new table has: all of them arrivedA JOIN is SQL's word for merge. You learned this in week 7 as merge. The join types map one to one, and so do the traps — a duplicated key multiplies rows here too.
Same reasoning as how="left": it keeps your main table intact and makes unmatched keys visible as NULL.
FROM projects pp is a short nickname for the projects table, used as p.budgetLEFT JOIN regions r ON p.region = r.regionkeep every project and bring in the matching region row; the key is the region columnNULLSQL's word for a missing value (pandas shows it as NaN)What does pd.read_sql(query, conn)
return?
B — a DataFrame.
read_sql runs the query on the
connection and loads the rows straight into a DataFrame, so your pandas skills carry on
from there.
SQL gets the rows; pandas takes it from there. One bridge, two tools.
read_sql → DataFrame.
Back to pull data live off the web.
Fetch fresh data over the web.
Files and databases usually sit on your machine or your organisation's network. This part fetches data from someone else's server over the internet, through an API, and turns the JSON reply into a DataFrame.
An API ("application programming interface") is a web address built for programs, not people. You send a request; it sends structured data back.
/api/flood/projectsThe reply is JSON — which you already know how to turn into a DataFrame.
An API is a restaurant counter: the menu lists what you may order (the endpoints), you place an order (the request), and a tray comes back (the response). You never see the kitchen.
requests.get, then check the statusSend the request, confirm it worked (200 means OK), then parse the
body with .json(). Always check the status before trusting the data.
A status code is the three-digit number at the top of every response. 2xx means it worked, 4xx means your request was wrong (404 = not found, 429 = too many requests), 5xx means the server failed.
Like a courier's tracking status: "delivered", "address not found", "try again later". Read it before you open the parcel.
import requeststhe standard Python library for HTTP (install it with pip if missing)requests.get(url)send a GET request to that address and wait for the response, stored in rr.status_codethe number the server replied with; 200 = OKr.json()read the body as JSON: JSON lists become Python lists, JSON objects become dictionaries| Code | Name | What it means for you | What to check first |
|---|---|---|---|
200 | OK | it worked; the body holds your data | go ahead: r.json() |
400 | Bad Request | the server did not understand what you asked | the parameters in the address |
401 / 403 | Unauthorized / Forbidden | you are not allowed in | your API key: missing, mistyped, expired |
404 | Not Found | nothing lives at that address | the spelling of the endpoint |
429 | Too Many Requests | you hit the rate limit | wait (Retry-After), then cache |
500 | Internal Server Error | the server broke, not you | try again later |
The first digit tells you whose problem it is: 2 = success, 4 = something about your request, 5 = the server's side.
Like the reply from a shop counter: "here you go" (200), "we don't have that" (404), "one at a time, please" (429), "our system is down" (500).
A list of records drops straight into a DataFrame. From here it's ordinary pandas — filter, group, plot — exactly as if you'd loaded a CSV.
pd.DataFrame(r.json())a list of dictionaries becomes a table: one row per dictionary, one column per key[{"city": "Cebu", "pop": 964000}, {"city": "Davao", "pop": 1777000}] becomes 2 rows with columns city and popThis portal serves the flood dataset at /api/flood — an endpoint. Same data you have been loading from disk — now through the door a real service would use.
Hit the root and it tells you its endpoints, its licence, and its data-quality warnings. That is what a good API does.
r.status_code → 200the request workedr.json()["rows"]the reply is a dictionary; look up its "rows" key: 34,079 projects available["data_quality_warning"]another key: the API warns you that one column is unreliableNo sensible API returns 34,079 records in one response. It gives you a window and a link to the next one. This is called paging (or pagination), like the pages of search results.
Do not compute page numbers yourself. The server knows how many there are; your arithmetic can drift.
?limit=100a query parameter added to the address: "100 records per page"while url:keep looping as long as there is a next page (None counts as false and ends the loop)rows.extend(r["results"])add this page's records to the growing listurl = r["next"]the server tells you the address of the next pageA rate limit is the most requests a server accepts from you per minute. An API key is a password-like code that identifies you to the API. The flood API allows 20 requests a minute anonymously and 200 with the demo key. Exceed it and you get a 429 with a Retry-After header telling you exactly how long to wait.
Pull once, save to disk, then work from the file. Re-downloading the same data on every notebook run is the most common way students get blocked.
HEAD = {"X-API-Key": …}a header is extra information sent with the request; this one carries your keyif r.status_code == 429:429 = "too many requests": you hit the rate limittime.sleep(wait)pause for the number of seconds the server asked for (time is a built-in module: import time first; fetch_all() stands for your own function wrapping the paging loop from the last slide)if not Path(…).exists():a cache: only download if you do not already have a saved copyA timeout is how long to wait for a reply before giving up. A file read fails immediately or not at all. A network call can hang, time out, return HTML instead of JSON, or succeed with an error body.
Without one, requests waits forever. A hung cell is worse than a failed one, because it looks like it is working.
try: … except …:week 4: try the risky lines; if one of the named errors happens, run that except block instead of crashingtimeout=10give up after 10 seconds without a replyr.raise_for_status()turn a 4xx or 5xx status code into an error (HTTPError) you can catchexcept ValueError:the body was not valid JSON — often an HTML error page; print the start of it to seeEvery API has rules for what you can request and how often.
Don't hammer it in a loop. Pause between calls; cache what you fetch.
An API key is a password. Never commit it to git (the version-control tool of week 11) or a notebook.
The echo from DS 227 (the companion course): your choices have consequences — here, on infrastructure you don't own and people who rely on it.
Store keys in an environment variable (a named setting kept by your computer, outside your code), not in code.
An API returns 429 with a Retry-After: 37 header. What should your code do?
A request returns
r.status_code == 200. What does that mean?
B — the request succeeded.
200 means OK. 404 is
not found, 429 is too many requests, and 500 is a server
error. Check before calling .json().
Status first, data second. 200 good; 4xx/5xx means
stop and look.
200 = OK. Check it every time.
Load the flood CSV. Save it as CSV, JSON and SQLite. Compare file sizes with os.path.getsize — do you reproduce the benchmark table from Part A?
Write it to SQLite. Get total budget per region in SQL, then the same in pandas. Confirm the numbers match.
Pull 3 pages from /api/flood/projects with the demo key. Build a DataFrame from the results.
Request /api/flood and print data_quality_warning. What wrong headline would someone write if they trusted that column?
Step 4 is the point (DS 227, the companion course, meets this trap in its week 4): the API documents its own worst column, because a good source tells you where it is unreliable.
| Source | Read it with | Watch out for |
|---|---|---|
| CSV | pd.read_csv | No dtypes; encoding; use usecols |
| Parquet | pd.read_parquet | Needs pyarrow installed — not on every server |
| Excel | pd.read_excel | sheet_name; BOM → utf-8-sig |
| SQL | pd.read_sql | Parameterise — never f-string a query |
| API | requests + pd.DataFrame | Timeout, 429s, paging, and cache the result |
All five end in the same object. Once the data is a DataFrame, everything you learned in weeks 6 to 8 applies unchanged — which is the whole reason to standardise on one.
You'll read a JSON and an Excel file, query a small SQLite database, and fetch data from a public API — landing each as a DataFrame you summarise. ~45 minutes.
read_json/read_excel, read_sql, and
requests.get().json().
Add a WHERE clause so the database, not pandas, does the filtering.
read_csv/json/excel/parquet — one reader per format.
SELECT ... WHERE, loaded with read_sql.
requests.get, check status, .json() into a DataFrame.
One sentence: whatever the source, get it into a DataFrame — then every pandas skill you have applies.
Start a project from wherever the data actually lives.
SELECT columns FROM table WHERE condition?)Finish the Week 9 lab and submit it. Be able to fetch an API and load its JSON from memory.
The requests quickstart — requests.readthedocs.io/en/latest/user/quickstart.
Everything here is linked on the course page beside this deck.
Next week: taming messy text with regular expressions.
Cleaning, searching, and extracting from messy text — phone numbers, dates, codes — with Python's string tools and regex.
DS 208 · Programming for Data Science