Reading & Writing Data (CSV, Excel, JSON)

Reviewed & 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.

Real analysis starts with real data. This is the bridge between files on disk and the DataFrames you've been building by hand.

What You'll Learn in This Lesson

• Read CSV files with pd.read_csv
• Read Excel files with pd.read_excel (openpyxl)
• Read JSON with pd.read_json
• Save data with to_csv, to_excel, to_json
• Why index=False matters when writing
• Test read_csv using io.StringIO

1 Reading CSV Files

CSV (comma-separated values) is the most common data format. Read one in a single line with pd.read_csv:

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)

# Output:
#     name  age    city
# 0  Alice   25  London
# 1    Bob   30   Paris
# 2  Carol   35  Berlin

# ✅ Expected output:
#     name  age    city
# 0  Alice   25  London
# 1    Bob   30   Paris
# 2  Carol   35  Berlin

2 Reading Excel and JSON

Excel files use pd.read_excel. For modern .xlsx files you need the openpyxl engine installed:

pip install openpyxl

df = pd.read_excel("data.xlsx", sheet_name="Sheet1")

JSON data uses pd.read_json, which accepts a file path or a JSON string:

df = pd.read_json("data.json")

import pandas as pd

# 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(json_text)
print(df)

# Output:
#     name  age
# 0  Alice   25
# 1    Bob   30
# 2  Carol   35

# ✅ Expected output:
#     name  age
# 0  Alice   25
# 1    Bob   30
# 2  Carol   35

3 Writing 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)     # Excel (needs openpyxl)
df.to_json("out.json", orient="records") # JSON list of records

Calling df.to_csv() with no filename returns the CSV as a string — handy for previewing exactly what would be written.

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))

# Output:
# name,age
# Alice,25
# Bob,30

# ✅ Expected output:
# name,age
# Alice,25
# Bob,30

import pandas as pd
import io

csv_text = """product,price
Pen,2
Notebook,5
Eraser,1"""

# 🎯 YOUR TURN: replace ___ with the function that reads CSV text
df = pd.___(io.StringIO(csv_text))
print(df)

# Expected output:
#     product  price
# 0       Pen      2
# 1  Notebook      5
# 2    Eraser      1

📋 Quick Reference

Excel needs openpyxl installed for .xlsx files.

🎯 Mini Challenge: Round Trip

Read CSV text in, then write it straight back out:

• Read the CSV string into a DataFrame with io.StringIO
• Print the DataFrame
• Convert it back to CSV text with to_csv(index=False) and print it

import pandas as pd
import io

csv_text = """city,temp
London,15
Paris,18"""

df = pd.read_csv(io.StringIO(csv_text))
print(df)
print("--- as CSV ---")
print(df.to_csv(index=False))

# Output:
#      city  temp
# 0  London    15
# 1   Paris    18
# --- as CSV ---
# city,temp
# London,15
# Paris,18

# ✅ Expected output:
#      city  temp
# 0  London    15
# 1   Paris    18
# --- as CSV ---
# city,temp
# London,15
# Paris,18

❓ Frequently Asked Questions

Lesson complete — you can load and save real data!

You can read CSV, Excel, and JSON into DataFrames, write them back out with the to_* methods, use index=False correctly, and test reads with io.StringIO.

Practice quiz

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

Frequently asked questions

How do I read a CSV file in Pandas?

Use pd.read_csv('file.csv'). It returns a DataFrame, automatically using the first row as column headers. You can pass options like sep, header, and index_col to control parsing.

How do I read an Excel file?

Use pd.read_excel('file.xlsx'). For .xlsx files you need the openpyxl engine installed (pip install openpyxl). Use the sheet_name argument to choose a specific sheet.

How do I save a DataFrame without the index column?

Pass index=False, e.g. df.to_csv('out.csv', index=False). Otherwise Pandas writes the row index as an extra unnamed first column, which is usually not what you want in a CSV.

How can I test read_csv without a real file?

Wrap a CSV string in io.StringIO and pass that to pd.read_csv. Pandas treats the in-memory text exactly like a file, which is perfect for examples, tests, and learning.