Reading & Writing Data (CSV, Excel, JSON)
Published by Brayan K
Reading and writing data in Pandas means loading external files — CSV, Excel, or JSON — into a DataFrame with the pd.read_* functions, and saving a DataFrame back out with the matching df.to_* methods.
Part of the free Pandas course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
Reading CSV Files
CSV (comma-separated values) stores tabular data as text. To read a local file that already exists, import pandas and pass its filename to pd.read_csv:
import pandas as pd
df = pd.read_csv("data.csv")Pandas uses the first row as the column headers automatically. To try this without a real file, wrap a CSV string in io.StringIO — Pandas treats the in-memory text exactly like a file:
import pandas as pd
import io
# A CSV as a string — StringIO lets us read it like a real file
csv_text = """name,age,city
Alice,25,London
Bob,30,Paris
Carol,35,Berlin"""
df = pd.read_csv(io.StringIO(csv_text))
print(df)
# ✅ Expected output:
# name age city
# 0 Alice 25 London
# 1 Bob 30 Paris
# 2 Carol 35 BerlinBefore you trust the import: identifiers are not numbers
A successful read can still change your data. In an inventory file, 0012 may be a product identifier and NA a real region code. By default, pandas infers a number for the first and treats the second as a missing-value token.
import io
import pandas as pd
text = """product_id,region,units
0012,NA,3
0040,EU,0"""
df = pd.read_csv(io.StringIO(text))
print(df["product_id"].tolist())
print(df["region"].isna().tolist())
# ✅ Expected output:
# [12, 40]
# [True, False]Choose the data contract before parsing: dtype keeps identifiers as text, and keep_default_na=False preserves literal tokens such as NA. Neither option substitutes for the other. Once leading zeroes have been discarded, converting the numbers to strings cannot tell you how many zeroes were present.
import io
import pandas as pd
text = """product_id,region,units
0012,NA,3
0040,EU,0"""
df = pd.read_csv(io.StringIO(text),
dtype={"product_id": str, "region": str},
keep_default_na=False)
print(df["product_id"].tolist())
print(df["region"].tolist())
print(df["units"].tolist())
# ✅ Expected output:
# ['0012', '0040']
# ['NA', 'EU']
# [3, 0]Disabling default missing-value tokens also preserves empty fields as empty strings. If an empty units field really means missing, declare that for that column with na_values. This is an import decision; for later replacement or removal, see Handling Missing Data (NaN, fillna, dropna).
import io
import pandas as pd
text = """product_id,region,units
0012,NA,3
0040,EU,
0007,,0"""
df = pd.read_csv(io.StringIO(text),
dtype={"product_id": str, "region": str},
keep_default_na=False,
na_values={"units": [""]})
print(df["region"].tolist())
print(df["units"].isna().tolist())
print(df.loc[2, "units"] == 0)
# ✅ Expected output:
# ['NA', 'EU', '']
# [False, True, False]
# TrueA missing column may be a delimiter mistake
A semicolon-separated file is still text. Using the default comma separator reads the whole header as one column. Inspect df.columns before blaming the column name.
import io
import pandas as pd
text = "item;amount\nPen;2\nBook;5\n"
df = pd.read_csv(io.StringIO(text))
print(df.columns.tolist())
print(df["amount"].sum())
# ✅ Expected output:
# ['item;amount']
# ✅ Expected error:
# KeyError: 'amount'Set sep=";" to match this file. Guessing a delimiter from a filename is unreliable; check the actual header and a few rows.
import io
import pandas as pd
text = "item;amount\nPen;2\nBook;5\n"
df = pd.read_csv(io.StringIO(text), sep=";")
print(df.columns.tolist())
print(df["amount"].sum())
# ✅ Expected output:
# ['item', 'amount']
# 7Reading Excel and JSON
Excel files use pd.read_excel. The local .xlsx examples here explicitly select the openpyxl engine, which must be installed in the Python environment running them. Pandas also supports other engines for particular formats, such as calamine for reading and xlsxwriter for writing. Run the installation command in a terminal, then run the Python line against a file you have. This browser editor does not bundle openpyxl:
python -m pip install openpyxlimport pandas as pd
df = pd.read_excel("data.xlsx", sheet_name="Sheet1", engine="openpyxl")JSON data uses pd.read_json, which accepts a file path or a file-like object. Wrap JSON text in io.StringIO; passing literal JSON directly is deprecated in pandas 2.x:
df = pd.read_json("data.json")import pandas as pd
import io
# JSON as a string — a list of records, one object per row
json_text = '''
[
{"name": "Alice", "age": 25},
{"name": "Bob", "age": 30},
{"name": "Carol", "age": 35}
]
'''
df = pd.read_json(io.StringIO(json_text))
print(df)
# ✅ Expected output:
# name age
# 0 Alice 25
# 1 Bob 30
# 2 Carol 35Writing Data Back Out
Each reader has a matching writer method you call on the DataFrame:
df.to_csv("out.csv", index=False) # CSV
df.to_excel("out.xlsx", index=False, engine="openpyxl") # Excel using this engine
df.to_json("out.json", orient="records") # JSON list of recordsCalling df.to_csv() with no filename returns the CSV as a string — handy for previewing exactly what would be written. The default line ending depends on the operating system. These examples set lineterminator="\n" so the exported text is the same on Windows, macOS and Linux; end="" avoids adding another newline when printing it.
import pandas as pd
df = pd.DataFrame({
"name": ["Alice", "Bob"],
"age": [25, 30],
})
# Passing no path returns the CSV text instead of writing a file
print(df.to_csv(index=False, lineterminator="\n"), end="")
# ✅ Expected output:
# name,age
# Alice,25
# Bob,30import io
import pandas as pd
text = """product_id,region,units
0012,NA,3
0040,US,
0007,NA,0"""
# Keep identifiers as text, keep the region code NA as text (it is Namibia's code,
# not a missing value), and treat only an empty units field as missing.
# 👉 replace ___ with str
# 👉 replace ___ with False
# 👉 replace ___ with {"units": [""]}
df = pd.read_csv(io.StringIO(text),
dtype={"product_id": ___},
keep_default_na=___,
na_values=___)
print(df["product_id"].tolist())
print(df["region"].tolist())
print(df["units"].isna().tolist())
# ✅ Expected output:
# ['0012', '0040', '0007']
# ['NA', 'US', 'NA']
# [False, True, False]Match each format to its reader and writer
| Format | Read | Write |
|---|---|---|
| CSV | pd.read_csv() | df.to_csv() |
| Excel | pd.read_excel() | df.to_excel() |
| JSON | pd.read_json() | df.to_json() |
Install the engine selected for your Excel operation; these local examples select openpyxl.
Preserve an inventory CSV round trip
An inventory export must preserve product identifiers and the literal region code NA. Starting with the CSV below, write a program that reads it without changing those values, exports it without the row index using explicit LF line endings, and reads it back with the same contract. Compare the two DataFrames and print the exported CSV.
product_id,region,units
0012,NA,3
0040,EU,0An export preserves values in text, but CSV does not carry pandas column types or missing-value rules. The reader needs the contract again. Write your version before opening the solution.
import io
import pandas as pd
text = """product_id,region,units
0012,NA,3
0040,EU,0"""
types = {"product_id": str, "region": str}
df = pd.read_csv(io.StringIO(text), dtype=types, keep_default_na=False)
exported = df.to_csv(index=False, lineterminator="\n")
restored = pd.read_csv(io.StringIO(exported), dtype=types, keep_default_na=False)
print(df.equals(restored))
print(exported, end="")
# ✅ Expected output:
# True
# product_id,region,units
# 0012,NA,3
# 0040,EU,0
Practice quiz
A CSV has product_id values 0012 and 0040. These are identifiers. Which choice preserves their leading zeroes when reading?
- Convert the inferred integers back with astype(str)
- Set dtype={"product_id": str} when calling read_csv
- Set index=False on read_csv
- Sort the rows first
Answer: Set dtype={"product_id": str} when calling read_csv. Choose the type while reading. Once inference turns 0012 into 12, converting back to text cannot recover the original number of zeroes.
You set dtype={"region": str}, but the real region code NA is still treated as missing. What also needs to change?
- The row index
- The column order
- The output separator
- Set keep_default_na=False to disable the default missing-value tokens
Answer: Set keep_default_na=False to disable the default missing-value tokens. dtype controls the column type. Missing-value recognition is a separate parsing decision.
With keep_default_na=False, only an empty units field should count as missing. Which option expresses that rule?
- na_values={"units": [""]}
- na_values=["NA"]
- dtype={"units": str}
- index_col="units"
Answer: na_values={"units": [""]}. A dictionary applies the missing token to the named column. Empty strings in other columns remain strings.
After reading a file, df.columns.tolist() is ["item;amount"]. Accessing df["amount"] raises KeyError. Which correction matches that header?
- Use header=None
- Change amount to Amount
- Read the text with sep=";"
- Add index=False
Answer: Read the text with sep=";". The default comma separator never split the semicolon header. Fix the delimiter instead of changing the intended column name.
A CSV export has an extra unnamed first column containing 0, 1, 2. Those numbers are not data you want to export. What should change?
- Set header=False
- Pass index=False to to_csv
- Remove the first data column
- Use keep_default_na=False
Answer: Pass index=False to to_csv. to_csv writes the DataFrame index by default. index=False omits it while retaining the named data columns and header.
json_text holds a JSON array of row objects. Which pandas 2.x call avoids the deprecated literal-JSON argument?
- pd.read_json(open(json_text))
- pd.read_csv(json_text)
- pd.read_json(str(json_text))
- pd.read_json(io.StringIO(json_text))
Answer: pd.read_json(io.StringIO(json_text)). StringIO supplies a file-like object containing the JSON. A string is otherwise ambiguous with a filename, and direct literal JSON is deprecated.
Why do these examples specify lineterminator=" " when exporting CSV?
- To use the same row ending across operating systems
- To convert every column to a number
- To remove all newlines inside quoted fields
- To remove the index automatically
Answer: To use the same row ending across operating systems. The default row ending depends on the operating system. An explicit terminator makes the exported text portable; it does not change data types or remove the index.
Which statement is true about the Excel snippet on this page?
- It runs without an Excel file because pandas generates one
- Installing openpyxl in a terminal also installs it in this browser editor
- It needs a local file and an Excel engine such as openpyxl in that Python environment
- sheet_name installs the required engine
Answer: It needs a local file and an Excel engine such as openpyxl in that Python environment. The browser editor does not bundle openpyxl. The local example requires both the file and the engine in the Python environment running it.
Continue this course
- Previous: Creating DataFrames
- Next: Inspecting Data: head, tail, info, describe — Get a fast overview of any dataset with the core inspection methods
- Quick reference: Pandas cheat sheet › Creating & Loading