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
| Format | Read with | Notes |
|---|---|---|
| CSV / TSV | pd.read_csv(path, sep=",") | Universal and readable, but stores no types; sep="\t" for tab-separated |
| Excel | pd.read_excel(path, sheet_name=0) | Needs the openpyxl package; watch for merged cells and notes above the table |
| JSON | pd.read_json or pd.json_normalize | Common from web APIs; nested fields need flattening |
| SQL database | pd.read_sql(query, connection) | Let the database filter before loading |
| Parquet | pd.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
- Open the file in a text editor first to see the separator, header and any title lines.
- Read it with the right options: sep, encoding, skiprows, na_values, parse_dates.
- Check shape, head, tail, dtypes and missing counts.
- Fix types that loaded wrongly.
- Save the cleaned result, preferably without the index.
In code
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,stationsTry 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
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