I2 · Working with Data: NumPy, pandas, SQL and Visualisation

Reading Data from Files

pandas reads data from many formats into DataFrames: read_csv for comma-separated text, read_excel for spreadsheets, read_json for JSON, read_sql for databases and read_parquet for efficient columnar files. Options control the separator, encoding, header row, which columns to load, how missing values are written and which columns are dates, and matching writer methods such as to_csv save results.

8 min Intermediate · Sublevel Beginner Jump to the activity

What you need: The earlier topics of this module.

Definition

You download a rainfall data set from a government portal. It opens with odd characters where the district names should be, every number is read as text, and the dates look like plain strings. The data is fine; the reading was not.

pandas reads data from many formats into DataFrames: read_csv for comma-separated text, read_excel for spreadsheets, read_json for JSON, read_sql for databases and read_parquet for efficient columnar files. Options control the separator, encoding, header row, which columns to load, how missing values are written and which columns are dates, and matching writer methods such as to_csv save results.

In simple words

Reading a file is like receiving a parcel: you need to know how it was packed. Is it separated by commas or semicolons? Which language encoding? Is the first row headings? Tell pandas, and it unpacks the table correctly.

Overview

  • pandas reads data with read_* functions and writes with to_* methods.
  • read_csv is the most used; it guesses types, but you can set sep, header, usecols, dtype and parse_dates.
  • encoding="utf-8" handles Hindi and other scripts; some government files use other encodings.
  • na_values tells pandas which markers, such as "NA" or "-", mean missing.
  • Large files can be read in chunks with chunksize to save memory.
  • Always check the result with head(), info() and shape straight after loading.

Why it matters

Every project starts by loading data, and many hours are lost to badly read files: wrong types, mangled text, shifted columns. Reading data correctly, and checking it immediately, prevents errors that would otherwise appear much later.

Key terms

CSV
Comma-separated values: a plain-text table, one row per line.
Delimiter
The character separating values, such as a comma, semicolon or tab.
Encoding
How text is stored as bytes; UTF-8 handles every language.
Header
The row containing column names.
parse_dates
An option telling pandas which columns to convert to dates.
Parquet
A compressed, typed columnar file format, much faster than CSV for large data.

Types

Data arrives in many file formats and sources. These are the main types and the pandas function for each.

1. CSV and other delimited text

Plain-text tables where commas (or tabs, semicolons) separate values.

  • How it works: pd.read_csv("file.csv", sep=",") reads it; df.to_csv("out.csv", index=False) writes it.
  • Example: Downloading rainfall data from data.gov.in as CSV.
  • When to use it: Use it as the default exchange format: simple, universal and readable by any tool, from Excel to pandas; but it stores no data types, so dates and codes must be parsed again.

2. Excel workbooks

.xlsx files with one or more sheets.

  • How it works: pd.read_excel("file.xlsx", sheet_name="Term1") reads a sheet (it needs the openpyxl package); sheet_name=None reads all sheets into a dictionary.
  • Example: A school's marks register with one sheet per section.
  • When to use it: Use it for data from offices and teachers, then save cleaned data as CSV or Parquet.

3. JSON

Text with nested objects and lists, common from web APIs.

  • How it works: pd.read_json reads simple JSON; pd.json_normalize flattens nested records into columns.
  • Example: Weather API results with a nested "main": {"temp": 31} field.
  • When to use it: Use it for data from APIs and apps; flatten it before analysis. Nested lists inside records may need to become separate tables.

4. Databases (SQL)

Tables stored in a database such as SQLite, MySQL or PostgreSQL.

  • How it works: pd.read_sql("SELECT * FROM students", connection) runs a query and returns a DataFrame; df.to_sql writes back.
  • Example: Reading this month's orders from a shop's sales database.
  • When to use it: Use it when data lives in a database; filter in SQL to avoid loading more rows than needed.

5. Fast binary formats: Parquet and Feather

Column-based binary files that store types and compress well.

  • How it works: df.to_parquet and pd.read_parquet (with pyarrow) load large tables many times faster than CSV and keep dtypes such as dates.
  • Example: A 2 GB CSV of trips saved as a few hundred megabytes of Parquet.
  • When to use it: Use them for large data and for saving cleaned data between steps of a project.

6. Web tables and URLs

Reading files directly from a web address, or HTML tables from a page.

  • How it works: read_csv accepts a URL; pd.read_html(url) returns a list of every table found on the page (it needs lxml).
  • Example: Reading a Wikipedia table of Indian states and their populations.
  • When to use it: Use it for public data; save a local copy, since web pages change, and respect the site's terms.

Explained in detail

Formats you will meet

FormatRead withNotes
CSV / TSVpd.read_csv(path, sep=",")Universal and readable, but stores no types; sep="\t" for tab-separated
Excelpd.read_excel(path, sheet_name=0)Needs the openpyxl package; watch for merged cells and notes above the table
JSONpd.read_json or pd.json_normalizeCommon from web APIs; nested fields need flattening
SQL databasepd.read_sql(query, connection)Let the database filter before loading
Parquetpd.read_parquet(path)Keeps types, compresses well, loads selected columns fast

Options that solve common problems

  • encoding="utf-8": the default; if Hindi or Tamil text appears garbled from an older file, try encoding="utf-16" or the file's actual encoding.
  • sep=";": many European-style files use semicolons because commas are decimal separators there.
  • skiprows=3 or header=2: skip title lines that sit above the real table in many official downloads.
  • na_values=["NA", "-", ".."]: tell pandas which strings mean missing.
  • thousands=",": read "1,25,000" as a number.
  • parse_dates=["date"]: convert date columns on load; dayfirst=True for Indian-style dates such as 15/08/2024.
  • usecols and dtype: load only the columns you need, with the types you expect, to save memory.

Check immediately after loading

A file that loads without an error can still be wrong. Straight after reading, look at df.shape: does the number of rows match what the source says? Look at df.head() and df.tail(): are the columns in the right places, or has a title line become the header? Look at df.dtypes: is a numeric column showing as text because of a stray "N/A" or a comma in "1,200"? Look at df.isna().sum(): are there more blanks than expected? Catching these problems at the first step saves hours later.

Saving results

Write a cleaned DataFrame with df.to_csv("clean.csv", index=False); index=False avoids an extra unnamed column of row numbers. For large data you will reuse, df.to_parquet("clean.parquet") is smaller, faster and keeps column types. India's Open Government Data platform, data.gov.in, offers thousands of data sets as CSV, JSON and XML, and they are excellent practice material because they contain exactly the quirks described here.

Very large files need extra care. Reading only the columns you need with usecols, choosing compact types, or reading in chunks with chunksize lets you process files bigger than your computer's memory, one piece at a time. For data you will load again and again, converting it once to Parquet usually pays for itself within a few runs.

How it works

  1. Open the file in a text editor first to see the separator, header and any title lines.
  2. Read it with the right options: sep, encoding, skiprows, na_values, parse_dates.
  3. Check shape, head, tail, dtypes and missing counts.
  4. Fix types that loaded wrongly.
  5. Save the cleaned result, preferably without the index.

In code

read_files.py
import io
import pandas as pd

raw = """Rainfall data (mm), provisional
Source: example
district;date;rainfall_mm;stations
Pune;01/07/2024;1,250;12
Nashik;01/07/2024;-;8
Pune;02/07/2024;980;12
"""
df = pd.read_csv(io.StringIO(raw), sep=";", skiprows=2, na_values=["-"],
                 thousands=",", parse_dates=["date"], dayfirst=True)
print(df)
print(df.dtypes)
print("Shape:", df.shape, " Missing per column:", df.isna().sum().to_dict())

out = io.StringIO()
df.to_csv(out, index=False)
print(out.getvalue().splitlines()[0])

Output

  district       date  rainfall_mm  stations
0     Pune 2024-07-01       1250.0        12
1   Nashik 2024-07-01          NaN         8
2     Pune 2024-07-02        980.0        12
district                  str
date           datetime64[us]
rainfall_mm           float64
stations                int64
dtype: object
Shape: (3, 4)  Missing per column: {'district': 0, 'date': 0, 'rainfall_mm': 1, 'stations': 0}
district,date,rainfall_mm,stations

Try it yourself

The file that lied

A student downloads district rainfall data and loads it with pd.read_csv(path).

Match the problem to the option

Which read_csv option fixes it?

  • Values separated by semicolons
  • Title lines above the table
  • "-" used for missing values
  • Dates loaded as text
  • "1,25,000" read as text

Common mistakes

  • Trusting a file because it loaded without an error.
  • Overwriting the original download with cleaned data.
  • Saving with the index and getting an extra unnamed column.

Check yourself

Question 1

1. A numeric column loads as text. A likely cause is…

Question 2

2. Which option reads 15/08/2024 as 15 August?

Question 3

3. Why prefer Parquet for large, reused data?

Summary

  • pandas reads CSV, Excel, JSON, SQL and Parquet into DataFrames.
  • Options such as sep, encoding, skiprows, na_values and parse_dates fix most reading problems.
  • Check shape, types and missing values immediately, and keep the raw file untouched.

Finished reading?

Sources: pandas: IO tools (text, CSV, Excel, JSON) · Open Government Data Platform India