Importing Data in Python: 10 Code-Along Examples

The importing guide covers the full territory: plain text, flat files, CSVs with problems, Excel, pickle, JSON, SQL databases, and data fetched straight from the web. This workbook runs one format per example. The pattern to notice is how the target keeps changing while the destination stays the same: almost everything funnels into a pandas DataFrame, because that is where analysis happens. By the end you will have imported from ten sources and met the arguments that rescue you when a file refuses to load cleanly.

1. Text files: open, read, and the context manager

The most basic import is a plain text file. The with statement is the context manager that opens the file and guarantees it closes, even if something fails mid-read.

# create a sample file to work with
sample = "Call me a data analyst.\nEvery dataset has a story.\nThis one has three lines."
with open("notes.txt", "w") as file:
    file.write(sample)
# read the whole file at once
with open("notes.txt", "r") as file:      # "r" = read text; "rb" would be binary
    content = file.read()
print(content)
# or read line by line
with open("notes.txt", "r") as file:
    print(file.readline(), end="")        # first line only
    print(file.readline(), end="")        # the next one

The with block is the habit to build: the file closes itself the moment the block ends, so there is no dangling handle to forget. read() swallows everything in one string, while readline() advances one line per call, which is how you sip from files too large to hold in memory at once.

2. NumPy flat files: loadtxt

When a file is purely numeric, NumPy’s loadtxt imports it straight into an array. Its arguments handle the usual obstacles: a different delimiter, a header row to skip, and columns to select.

import numpy as np
# create a tab-separated sensor log with a header row
with open("sensor_log.txt", "w") as f:
    f.write("time\ttemp\thumidity\n")
    f.write("1\t21.5\t40\n2\t21.8\t42\n3\t22.4\t41\n")
readings = np.loadtxt(
    "sensor_log.txt",
    delimiter="\t",       # tab-separated, not the default whitespace
    skiprows=1,           # jump over the header row
    usecols=[0, 1],       # keep only time and temp
    dtype=float
)
print(readings)
print(readings.shape)     # (3, 2): three readings, two columns

The result is a pure array: fast, numeric, and ideal for maths, but with no column names attached. That trade-off is the decision rule: loadtxt for homogeneous numbers headed into calculations, pandas (next example) the moment you have mixed types or want named columns.

3. Pandas read_csv: taming a messy file

read_csv is the most used import function in data work, and its optional arguments exist because real files misbehave: odd separators, comment lines, missing-value codes, and no header. Here is a deliberately ugly file, tamed.

import pandas as pd
# create a messy tab-separated file: comments, no header, "Nothing" for missing
with open("voyage_log.txt", "w") as f:
    f.write("# exported from the legacy system\n")
    f.write("Aurora\t2024-05-01\t340\n")
    f.write("Meridian\t2024-05-03\tNothing\n")
    f.write("# do not edit below this line\n")
    f.write("Aurora\t2024-05-07\t512\n")
log = pd.read_csv(
    "voyage_log.txt",
    sep="\t",                              # tabs, not commas
    header=None,                           # no header row in the file
    names=["ship", "date", "cargo_kg"],    # supply our own column names
    comment="#",                           # ignore lines starting with #
    na_values="Nothing"                    # treat this string as missing
)
print(log)
print(log["cargo_kg"].isna().sum(), "missing value(s) detected")

Every argument neutralises one defect: sep for the tabs, header=None plus names for the missing header, comment for the junk lines, and na_values for the legacy system’s “Nothing”. When a CSV refuses to load, the answer is almost always one of these four, plus nrows=5 to preview a huge file before committing to the full read.

4. Excel workbooks and their sheets

Excel files hold multiple sheets, so importing has two steps: open the workbook, then parse the sheet you want by name or position. The read_excel shortcut collapses both when you already know the sheet.

import pandas as pd
# create a two-sheet workbook
with pd.ExcelWriter("sales.xlsx") as writer:
    pd.DataFrame({"region": ["North", "South"], "revenue": [1200, 950]}
                 ).to_excel(writer, sheet_name="2024", index=False)
    pd.DataFrame({"region": ["North", "South"], "revenue": [1450, 1100]}
                 ).to_excel(writer, sheet_name="2025", index=False)
# step 1: open the workbook and list its sheets
workbook = pd.ExcelFile("sales.xlsx")
print(workbook.sheet_names)                 # ['2024', '2025']
# step 2: parse a sheet by name or by position
sales_2025 = workbook.parse("2025")
first_sheet = workbook.parse(0)
# or the one-line shortcut when you know what you want
sales_2024 = pd.read_excel("sales.xlsx", sheet_name="2024")
print(sales_2024)

ExcelFile earns its extra step when the workbook is unfamiliar: sheet_names shows you what exists before you commit. Once you know the layout, read_excel(sheet_name=...) is the everyday call. Both routes end in an ordinary DataFrame, ready for the same analysis as any CSV.

5. Pickle: saving and loading Python objects

Pickle serialises arbitrary Python objects, not just tables, so a dictionary, a fitted model, or any structure can be frozen to disk and thawed later, exactly as it was.

import pickle
# an object that isn't a table: a nested dict of settings and results
experiment = {
    "model": "gradient_boost",
    "params": {"depth": 6, "learning_rate": 0.1},
    "scores": [0.81, 0.84, 0.83],
}
# freeze it: note "wb", write binary
with open("experiment.pkl", "wb") as file:
    pickle.dump(experiment, file)
# thaw it: "rb", read binary
with open("experiment.pkl", "rb") as file:
    restored = pickle.load(file)
print(restored["params"]["depth"])     # 6: structure fully intact
print(restored == experiment)          # True

The binary modes matter: pickles are not text, so wb and rb are required. And the guide’s warning deserves repeating verbatim: never load a pickle file from a source you do not trust, because unpickling can execute arbitrary code. Pickle is for round-tripping your own objects, not for receiving files from strangers.

6. JSON: files, strings, and nested navigation

JSON is the language of configuration files and APIs. json.load reads from a file, json.loads parses a string, and the result is ordinary dictionaries and lists you navigate with chained keys.

import json
# create a JSON file with nesting, as APIs typically return
record = {
    "customer": "Ava Chen",
    "orders": [
        {"id": 1, "total": 84.50, "items": ["keyboard", "cable"]},
        {"id": 2, "total": 300.00, "items": ["monitor"]},
    ],
    "address": {"city": "London", "country": "UK"},
}
with open("customer.json", "w") as file:
    json.dump(record, file)
# load it back from the file
with open("customer.json", "r") as file:
    data = json.load(file)
# navigate the nesting with chained lookups
print(data["address"]["city"])            # London
print(data["orders"][1]["total"])         # 300.0
print(len(data["orders"][0]["items"]))    # 2
# loads (with an s) parses a STRING instead of a file
parsed = json.loads('{"status": "ok", "count": 3}')
print(parsed["count"])                    # 3

Once loaded, JSON stops being special: it is dictionaries holding lists holding dictionaries, and chained square brackets walk down the levels. The load versus loads distinction is worth pinning: file handle versus string, and Example 9 shows the third form, where an API response parses itself.

7. SQL databases with SQLAlchemy and pandas

Relational databases need a connection before a query. SQLAlchemy’s create_engine builds the connection, and pd.read_sql_query runs your SQL and hands back a DataFrame in one call.

import pandas as pd
from sqlalchemy import create_engine, text
# create a small SQLite database to query
engine = create_engine("sqlite:///media_store.sqlite")
with engine.connect() as conn:
    conn.execute(text("DROP TABLE IF EXISTS albums"))
    conn.execute(text(
        "CREATE TABLE albums (title TEXT, artist TEXT, year INTEGER)"))
    conn.execute(text(
        "INSERT INTO albums VALUES "
        "('Night Signals', 'The Meridians', 2019), "
        "('Paper Maps', 'Ada Vale', 2021), "
        "('Slow Circuits', 'The Meridians', 2023)"))
    conn.commit()
# the recommended route: SQL in, DataFrame out
albums = pd.read_sql_query(
    "SELECT title, year FROM albums "
    "WHERE artist = 'The Meridians' ORDER BY year DESC",
    engine
)
print(albums)

The connection string names the dialect and the file: sqlite:///media_store.sqlite means SQLite in a local file, and swapping the prefix points the same code at Postgres or MySQL. read_sql_query is the recommended route because the full power of SQL, filtering, ordering, joins, runs in the database, and only the finished result crosses into pandas.

8. Importing straight from the web

Files on the web import two ways: download a local copy first with urlretrieve, or let pandas read the URL directly. Direct reading is convenient; downloading gives you a reproducible snapshot.

import pandas as pd
from urllib.request import urlretrieve
url = ("https://raw.githubusercontent.com/mwaskom/"
       "seaborn-data/master/tips.csv")
# route 1: read the URL directly, nothing saved locally
tips = pd.read_csv(url)
print(tips.head(3))
# route 2: download a local copy first, then read the copy
urlretrieve(url, "tips_snapshot.csv")
snapshot = pd.read_csv("tips_snapshot.csv")
print(f"saved and reloaded: {snapshot.shape[0]} rows")

Both routes end in the same DataFrame, so the choice is about durability. Direct reading suits exploration, but the file behind a URL can change or vanish; urlretrieve freezes today’s version to disk, which is what you want when an analysis must be repeatable next month. This example needs an internet connection.

9. HTTP requests and JSON APIs

The requests library is the standard tool for talking to the web. For an API, response.json() parses the reply straight into dictionaries, turning Example 6’s navigation skills into live data access.

import requests
# a free, keyless weather API: current conditions in London
url = ("https://api.open-meteo.com/v1/forecast"
       "?latitude=51.5&longitude=-0.12&current_weather=true")
response = requests.get(url)
print(response.status_code)           # 200 means success
data = response.json()                # parsed JSON -> dicts and lists
weather = data["current_weather"]
print(f"London now: {weather['temperature']}°C, "
      f"wind {weather['windspeed']} km/h")
# .text holds the raw body if you ever need the unparsed version
print(response.text[:80], "...")

Three attributes cover most API work: status_code to confirm the call succeeded, json() for parsed data, and text for the raw body. The navigation after json() is exactly the chained-key walking from Example 6, because an API response is just JSON that never touched a file. This example needs an internet connection.

10. Scraping HTML with BeautifulSoup

When data lives in a web page rather than an API, BeautifulSoup parses the HTML into a searchable structure. This example parses a local HTML string, so it runs offline and never breaks when a website changes.

from bs4 import BeautifulSoup
# an HTML page as a string: in real work this would be requests.get(url).text
html = """
<html><head><title>Course Catalogue</title></head>
<body>
  <h1>Data Courses</h1>
  <p class="intro">Three courses are open for enrolment.</p>
  <a href="/courses/sql">SQL Fundamentals</a>
  <a href="/courses/python">Python for Analysts</a>
  <a href="/courses/stats">Practical Statistics</a>
</body></html>
"""
soup = BeautifulSoup(html, "html.parser")
print(soup.title.get_text())              # Course Catalogue
print(soup.find("p", class_="intro").get_text())
# find_all returns every matching tag; .get reads an attribute
for link in soup.find_all("a"):
    print(f"{link.get_text():22s} -> {link.get('href')}")

find_all("a") collects every link tag, get_text() extracts the visible words, and get("href") reads the attribute holding the destination. Swap the local string for requests.get(url).text and this exact code scrapes a live page. The etiquette the guide implies still applies: prefer an API when one exists, and scrape respectfully when one does not.

Work through these and you have the whole article in practice: text files with context managers, NumPy flat files, read_csvand its rescue arguments, Excel sheets, pickle with its trust warning, JSON in all three forms, SQL through SQLAlchemy, web files by URL and download, live APIs with requests, and HTML with BeautifulSoup. The unifying habit is visible across all ten: whatever the source, aim it at a DataFrame or a plain Python structure as early as possible, because the sooner the data reaches a shape pandas understands, the sooner the actual analysis can begin.

See you soon, Andrei.