Read from and Write to CSV with Pandas

1. Why CSV matters

A CSV file is a plain-text table. Each line normally represents one row and commas normally separate the fields. CSV is common because spreadsheets, websites, databases, government portals and business systems can all exchange it.

Name,City,Marks,Course,Joined
Asha,Varanasi,88,Python,2026-09-01
Ravi,Ranchi,42,Python,2026-09-03
Meera,Varanasi,76,AI-ML,2026-09-05
Kabir,Patna,91,AI-ML,2026-09-08
Nisha,Ranchi,,Python,2026-09-10

Download the lesson's students.csv file

Mental model: read_csv() is an input operation. to_csv() is an output operation. Pandas sits between the file and your analysis.

2. Your first read_csv()

import pandas as pd

df = pd.read_csv("students.csv")
print(df)

If the file is in the current working directory, its filename is enough. Pandas reads the first row as column names by default and infers data types for the other columns.

print(df.head())
print(df.shape)
print(df.columns)
print(df.dtypes)
df.info()

3. File paths: the most common beginner problem

Python resolves a relative path from the program's current working directory, which is not always the folder containing your script.

from pathlib import Path

print(Path.cwd())

For reliable project code, build the path relative to the script:

from pathlib import Path
import pandas as pd

HERE = Path(__file__).resolve().parent
csv_path = HERE / "students.csv"

df = pd.read_csv(csv_path)
print(df.head())

You can also read a public HTTP(S) CSV URL directly when the server exposes an actual CSV resource:

url = "https://example.com/data.csv"
df = pd.read_csv(url)

4. Understand the main read_csv() parameters

5. Different separators

Despite its name, read_csv() can read many delimited text files.

df = pd.read_csv("students_semicolon.csv", sep=";")

df = pd.read_csv("students.tsv", sep="\t")

If you genuinely do not know the separator, sep=None can ask the Python parsing engine to detect it:

df = pd.read_csv("unknown.txt", sep=None, engine="python")

6. CSV files without headers

Suppose a file contains:

Asha,Varanasi,88
Ravi,Ranchi,42

If you read it normally, Pandas may treat the first record as headings. Use header=None:

df = pd.read_csv("students_no_header.csv", header=None)
print(df)

Better still, name the columns while reading:

df = pd.read_csv(
    "students_no_header.csv",
    header=None,
    names=["Name", "City", "Marks"]
)

7. Read only what you need with usecols

Large CSV files often contain columns that your program does not need. Restricting columns can reduce memory usage and simplify later work.

df = pd.read_csv(
    "students.csv",
    usecols=["Name", "City", "Marks"]
)
print(df)

You may also pass column positions, but names are usually clearer and safer for maintainable code.

8. Control data types with dtype

Automatic type inference is useful, but identifiers such as phone numbers, pin codes and account-like codes often need to remain strings. A value such as 012345 should not silently become the integer 12345.

df = pd.read_csv(
    "customers.csv",
    dtype={
        "CustomerId": "string",
        "Pincode": "string",
        "Phone": "string"
    }
)

9. Read dates correctly

df = pd.read_csv(
    "students.csv",
    parse_dates=["Joined"]
)

print(df.dtypes)
print(df["Joined"].dt.year)

Once a column is a datetime type, Pandas date operations become available through .dt.

For a known consistent format you can also specify date_format in current Pandas versions:

df = pd.read_csv(
    "students.csv",
    parse_dates=["Joined"],
    date_format="%Y-%m-%d"
)

10. Missing values during import

Pandas recognises many common missing-value representations automatically. Real files, however, may use custom values such as missing, -, or not given.

df = pd.read_csv(
    "students.csv",
    na_values=["missing", "-", "not given"]
)

print(df.isna().sum())

You can also configure missing markers per column with a dictionary.

11. index_col: when a CSV column should become the index

df = pd.read_csv(
    "students_with_id.csv",
    index_col="StudentId"
)

Do this only when the field genuinely behaves like a row label. Do not turn an ordinary analytical column into the index merely to hide it.

12. Skipping rows, comments and limited previews

# Read only the first 100 data rows
preview = pd.read_csv("large.csv", nrows=100)

# Skip two metadata lines before the real table
df = pd.read_csv("report.csv", skiprows=2)

# Ignore lines beginning with #
df = pd.read_csv("report.csv", comment="#")

These are useful for exported reports that contain titles or explanatory lines before the actual data.

13. Encoding problems

CSV is text, so the character encoding matters. UTF-8 is the safest modern default when you control the file. Hindi text, names with accents and symbols require an encoding capable of representing them.

df = pd.read_csv("students.csv", encoding="utf-8")

If a legacy file fails with UnicodeDecodeError, first determine its actual encoding rather than randomly trying encodings. A common Windows-origin file may use a Windows code page, but the correct choice depends on the source.

14. Malformed rows and on_bad_lines

A damaged row can contain too many separators. By default Pandas raises an error, which is often exactly what you want because silent data loss is dangerous.

df = pd.read_csv(
    "messy.csv",
    on_bad_lines="skip"
)

Use skipping only when you intentionally accept losing malformed records and can audit what happened. For important data, inspect and repair the source instead.

15. Large CSV files: chunksize

low_memory=True does not mean the final DataFrame remains tiny. If the full file is too large, process chunks.

total = 0

for chunk in pd.read_csv("sales.csv", chunksize=100_000):
    total += chunk["Amount"].sum()

print("Total sales:", total)

Each chunk is a DataFrame. You can filter, aggregate and discard it before reading the next part.

16. Your first to_csv()

After cleaning or analysing data, save the DataFrame:

df.to_csv("students_output.csv")

By default, Pandas writes the DataFrame index too. For most ordinary interchange files, you do not want that extra column:

df.to_csv("students_output.csv", index=False)

17. Main to_csv() parameters

18. Export selected columns

df.to_csv(
    "public_results.csv",
    columns=["Name", "Course", "Marks"],
    index=False
)

This is useful when your working DataFrame contains internal fields that should not be published.

19. Missing values and number formatting on export

df.to_csv(
    "report.csv",
    index=False,
    na_rep="Not Available",
    float_format="%.2f"
)

This changes the text representation in the CSV; it does not modify the DataFrame itself.

20. Append mode: powerful but dangerous

mode="a" appends instead of replacing a file. If you append a DataFrame with its header every time, column names will be repeated in the middle of the file.

new_rows.to_csv(
    "history.csv",
    mode="a",
    header=False,
    index=False
)

21. CSV quoting: why commas inside text do not necessarily break a file

A field such as Varanasi, Uttar Pradesh contains a comma. Correct CSV writers quote fields when required, for example:

Name,Address
Asha,"Varanasi, Uttar Pradesh"

Pandas uses standard CSV quoting rules. Avoid manually constructing CSV rows with string concatenation.

22. Round trip: write, read again, verify

A reliable export workflow does not stop at “file created”. Read the exported file back and validate it.

output = "students_clean.csv"

df.to_csv(output, index=False)

check = pd.read_csv(output)

print(check.head())
print(check.shape)
print(check.columns.tolist())

assert len(check) == len(df)
assert check.columns.tolist() == df.columns.tolist()

Be aware that CSV does not preserve every Pandas data type perfectly. Dates and nullable types may need explicit parsing when read back.

23. A complete practical workflow

from pathlib import Path
import pandas as pd

HERE = Path(__file__).resolve().parent
source = HERE / "students.csv"
output = HERE / "students_clean.csv"

# 1. Read
df = pd.read_csv(
    source,
    dtype={"Name": "string", "City": "string", "Course": "string"},
    parse_dates=["Joined"]
)

# 2. Inspect
print(df.head())
print(df.info())
print(df.isna().sum())

# 3. Clean
df["Name"] = df["Name"].str.strip()
df["City"] = df["City"].str.strip()
df["Marks"] = pd.to_numeric(df["Marks"], errors="coerce")

valid_marks = df["Marks"].between(0, 100) | df["Marks"].isna()
if not valid_marks.all():
    raise ValueError("Marks outside 0..100 found")

# 4. Analyse
print(df.groupby("City", dropna=False)["Marks"].mean())

# 5. Save
df.to_csv(
    output,
    index=False,
    na_rep="",
    date_format="%Y-%m-%d",
    encoding="utf-8"
)

# 6. Verify
check = pd.read_csv(output, parse_dates=["Joined"])
assert check.shape == df.shape
assert check.columns.tolist() == df.columns.tolist()

print("Saved and verified:", output)

Open the complete runnable Python file

24. Common mistakes and how to diagnose them

25. CSV versus Excel versus database

CSV is excellent for simple portable tables and data exchange. It does not preserve formulas, styles, multiple sheets or rich data types.

Excel is useful when people need formatting, formulas and multiple worksheets. Pandas provides read_excel() and to_excel().

Databases are better when many users need reliable updates, transactions, indexes, relationships and queries. Your existing Pandas + SQL lesson covers that direction.

26. Performance checklist for large files

• Read only needed columns with usecols.
• Specify important types with dtype.
• Use nrows for fast inspection.
• Use chunksize when the entire DataFrame is unnecessary or too large.
• Filter and aggregate as early as practical.
• Do not assume low_memory=True means the final dataset is kept out of memory.
• For repeated large analytical workloads, consider formats designed for typed columnar storage rather than CSV.

27. Practice exercises

1. Read students.csv and display only Name and Marks.
2. Read Joined as a date and print only students who joined in September 2026.
3. Save students scoring 75 or more to high_scorers.csv without the index.
4. Export only Name, City and Marks.
5. Write missing marks as NA in the exported file.
6. Create a semicolon-separated export.
7. Read a headerless CSV and assign your own column names.
8. Create a CSV with a Pincode such as 012345; preserve it as text.
9. Read a large file in chunks and calculate the total of a numeric column.
10. Perform a round-trip test: export, re-read and assert row/column counts.

28. Mini project: Student CSV Report Pipeline

Build a command-line program that accepts an input CSV, validates required columns, parses dates and marks, reports missing/invalid values, prints city/course summaries, writes a cleaned CSV and then verifies the exported file.

Minimum requirements

• Never overwrite the source file.
• Use Path for paths.
• Reject missing required columns.
• Preserve identifiers as strings.
• Validate numeric ranges.
• Write with index=False.
• Re-read the output and verify shape and headings.

This combines Levels 0–5 into one realistic workflow.