Skip to content
Laviyx Learn

Try

Python for Data course menu

Read CSV, Excel and JSON Files with pandas (and Save Them)

Load data into pandas with read_csv, read_excel and read_json: separators, headers, choosing columns, parsing dates, encodings, reading large files in chunks, and saving results with to_csv and to_excel.

Beginner9 min readUpdated
On this page

Real data lives in files. pandas reads almost every format with a read_* function that returns a DataFrame, and writes them back with matching to_* methods.

The examples use this file, saved as orders.csv next to your notebook:

orders.csv (Text)
order_id,date,customer,city,amount
1001,2026-01-05,Asha,Mumbai,250
1002,2026-01-09,Ravi,Delhi,120
1003,2026-02-11,Asha,Mumbai,560
1004,2026-02-20,Meera,Pune,90
1005,2026-03-02,John,Chennai,310

read_csv#

Python
import pandas as pd

orders = pd.read_csv("orders.csv")
print(orders)
print(orders.dtypes)
Output
   order_id        date customer     city  amount
0      1001  2026-01-05     Asha   Mumbai     250
1      1002  2026-01-09     Ravi    Delhi     120
2      1003  2026-02-11     Asha   Mumbai     560
3      1004  2026-02-20    Meera     Pune      90
4      1005  2026-03-02     John  Chennai     310
order_id     int64
date        object
customer    object
city        object
amount       int64
dtype: object

pandas detects the header row and the column types. The dates are read as plain text (object) unless you ask for dates.

Useful options#

Python
import pandas as pd

orders = pd.read_csv(
    "orders.csv",
    usecols=["order_id", "date", "amount"],   # only these columns
    parse_dates=["date"],                     # turn text into real dates
    index_col="order_id",                     # use this column as the index
)
print(orders)
print(orders.dtypes)
print(orders["date"].dt.month_name().tolist())
Output
               date  amount
order_id
1001     2026-01-05     250
1002     2026-01-09     120
1003     2026-02-11     560
1004     2026-02-20      90
1005     2026-03-02     310
date      datetime64[ns]
amount             int64
dtype: object
['January', 'January', 'February', 'February', 'March']

Other options you will meet:

OptionUse it when
sep=";"the file uses semicolons (common in Europe) or tabs (sep="\t")
header=None, names=[...]the file has no header row
skiprows=2there are notes above the header
nrows=1000you only want to peek at a big file
encoding="latin-1"you get a UnicodeDecodeError (the default is UTF-8)
na_values=["-", "N/A"]missing values are written in an unusual way
dtype={"pincode": str}a column must stay text (codes with leading zeros)

Big files in chunks#

For files too large to load at once, read them in pieces and combine the partial results:

Python
import pandas as pd

total = 0
for chunk in pd.read_csv("orders.csv", chunksize=2):
    total += chunk["amount"].sum()
print(total)
Output
1330

Each chunk is a DataFrame of up to 2 rows here; in practice you would use something like 100,000.

Excel files#

read_excel reads .xlsx files (it needs the openpyxl package). This example first writes an Excel file so it can read it back:

Python
import pandas as pd

orders = pd.read_csv("orders.csv")
with pd.ExcelWriter("report.xlsx") as writer:
    orders.to_excel(writer, sheet_name="orders", index=False)
    orders.groupby("city")["amount"].sum().to_excel(writer, sheet_name="by_city")

back = pd.read_excel("report.xlsx", sheet_name="by_city")
print(back)
with pd.ExcelFile("report.xlsx") as workbook:
    print(workbook.sheet_names)
Output
      city  amount
0  Chennai     310
1    Delhi     120
2   Mumbai     810
3     Pune      90
['orders', 'by_city']

sheet_name=None reads every sheet into a dictionary of DataFrames. index=False stops pandas writing the 0, 1, 2… index as an extra column.

JSON#

APIs usually send JSON. A list of records maps straight onto rows; nested objects can be flattened with json_normalize:

Python
import pandas as pd
from io import StringIO

text = '[{"id": 1, "name": "Asha", "address": {"city": "Mumbai"}}, {"id": 2, "name": "Ravi", "address": {"city": "Delhi"}}]'
print(pd.read_json(StringIO(text)))

import json
print(pd.json_normalize(json.loads(text)))
Output
   id  name             address
0   1  Asha  {'city': 'Mumbai'}
1   2  Ravi   {'city': 'Delhi'}
   id  name address.city
0   1  Asha       Mumbai
1   2  Ravi        Delhi

Recent pandas versions want file paths or file-like objects in read_json, not raw JSON strings, which is why the text is wrapped in StringIO.

Saving results#

Python
import pandas as pd

orders = pd.read_csv("orders.csv")
big = orders[orders["amount"] > 200]
big.to_csv("big_orders.csv", index=False)
print(open("big_orders.csv").read())
Output
order_id,date,customer,city,amount
1001,2026-01-05,Asha,Mumbai,250
1003,2026-02-11,Asha,Mumbai,560
1005,2026-03-02,John,Chennai,310

Common mistakes#

Practice#

Practice exercise

Using orders.csv, read only the customer and amount columns, then save a CSV called customer_totals.csv with each customer's total amount (no index column is needed beyond the customer). Print the saved file's text.

Show hint

usecols=[...], then groupby("customer")["amount"].sum(). A Series saved with to_csv writes its index (the customer) and values.

Show solution
Python
import pandas as pd

orders = pd.read_csv("orders.csv", usecols=["customer", "amount"])
totals = orders.groupby("customer")["amount"].sum()
totals.to_csv("customer_totals.csv")
print(open("customer_totals.csv").read())
Output
customer,amount
Asha,810
John,310
Meera,90
Ravi,120

Key takeaway#

Next: Selecting Data with loc and iloc.

Finished this lesson?

Related lessons

Python for Datapandas DataFrame Tutorial: Create, Inspect and Explore TablesLearn the pandas DataFrame: creating one from a dictionary or list of rows, inspecting it with head, info, describe, shape and dtypes, selecting columns, and the index.Beginner11 min readPython for Datapandas Data Types: astype, to_numeric, to_datetime and categoryFix column types in pandas: check dtypes, convert with astype, clean numbers stored as text with to_numeric, parse dates with to_datetime, use the category type to save memory, and nullable integer and string types.Intermediate9 min readPythonPython File Handling: Read, Write and Append Files (with open and pathlib)Learn file handling in Python: open files with the with statement, read whole files or line by line, write and append, file modes, encodings, pathlib paths and folders, CSV files, and handling missing files.Intermediate7 min read