Pandas to_csv(): Export Files and Verify CSV Data
Use to_csv() to export selected records with the intended fields, identifiers and formatting. Choose index and missing-value rules, then reload the CSV to check its contents.
Create a table and return CSV text 🔝
With no destination, to_csv() returns CSV text. By default it includes the row index. Use index=False when the index is only a disposable row number.
import pandas as pd
import csv, sqlite3, tempfile
from pathlib import Path
from io import StringIO, BytesIO
df = pd.DataFrame({"NAME": ["Ravi", "Raju", "Alex"], "ID": [1, 2, 3],
"MATH": [30, 40, 50], "ENGLISH": [20, 30, 40]})
print("Default index included:")
print(df.to_csv().rstrip())
print("No index:")
print(df.to_csv(index=False).rstrip())Expected output
Default index included:
,NAME,ID,MATH,ENGLISH
0,Ravi,1,30,20
1,Raju,2,40,30
2,Alex,3,50,40
No index:
NAME,ID,MATH,ENGLISH
Ravi,1,30,20
Raju,2,40,30
Alex,3,50,40Write a file and choose its path 🔝
A file destination returns None. A relative path is resolved from the current working directory. Create a required parent folder first. Windows paths can use raw strings such as r"D:\data\my_file.csv"; Colab uses Linux paths.
with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
target = Path(folder) / "data" / "my_file.csv"
target.parent.mkdir(parents=True, exist_ok=True)
returned = df.to_csv(target, index=False, encoding="utf-8")
loaded = pd.read_csv(target)
print("File created:", target.exists(), "Return:", returned)
print(loaded)
assert returned is None and loaded.equals(df)
# Optional Colab output:
# df.to_csv("student.csv", index=False, encoding="utf-8")Expected output
File created: True Return: None
NAME ID MATH ENGLISH
0 Ravi 1 30 20
1 Raju 2 40 30
2 Alex 3 50 40Use a custom separator 🔝
sep is a one-character delimiter. Specify the same separator when importing the file.
text = df.to_csv(sep="#", index=False)
print(text.rstrip())
loaded = pd.read_csv(StringIO(text), sep="#")
assert loaded.equals(df)Expected output
NAME#ID#MATH#ENGLISH
Ravi#1#30#20
Raju#2#40#30
Alex#3#50#40Choose an explicit missing marker 🔝
na_rep changes missing-value representation, not stored data. Pick a marker that cannot collide with a valid value and pass matching reader options. Default blank fields may be ambiguous when empty strings are valid.
missing = pd.DataFrame({"name": ["Ravi", "Raju"], "mark": [30.0, None]})
text = missing.to_csv(index=False, na_rep="*", float_format="%.1f")
print(text.rstrip())
loaded = pd.read_csv(StringIO(text), na_values=["*"], keep_default_na=False)
assert loaded["mark"].isna().sum() == 1Expected output
name,mark
Ravi,30.0
Raju,*Choose columns and header labels 🔝
columns selects output fields and order. header=False omits headings; header can also provide aliases. Read a headerless file with header=None and explicit names.
print(df.to_csv(columns=["NAME", "ID"], index=False).rstrip())
text = df.to_csv(index=False, header=False)
print("No header:")
print(text.rstrip())
loaded = pd.read_csv(StringIO(text), header=None, names=df.columns.tolist())
print("Aliases:")
print(df.to_csv(columns=["NAME", "ID"], header=["student", "student_id"], index=False).rstrip())
assert loaded.equals(df)Expected output
NAME,ID
Ravi,1
Raju,2
Alex,3
No header:
Ravi,1,30,20
Raju,2,40,30
Alex,3,50,40
Aliases:
student,student_id
Ravi,1
Raju,2
Alex,3Preserve a meaningful index deliberately 🔝
index=False would discard IDs stored only as index labels. Export with index=True and index_label, or restore the ID column with reset_index().
indexed = df.set_index("ID")
text = indexed.to_csv(index=True, index_label="student_id")
print(text.rstrip())
loaded = pd.read_csv(StringIO(text), index_col="student_id")
assert loaded.index.tolist() == [1, 2, 3]Expected output
student_id,NAME,MATH,ENGLISH
1,Ravi,30,20
2,Raju,40,30
3,Alex,50,40Export a selected subset 🔝
Filter rows and select columns before exporting. Here the student class/name example is embedded so it needs no external test.csv file. See loc and row filtering.
students = pd.DataFrame({"class": ["Four", "Three", "Four"], "name": ["John Deo", "Max Ruin", "Alex John"], "mark": [75, 85, 55]})
fields = students.loc[:, ["class", "name"]]
print(fields.to_csv(index=False).rstrip())
selected = students.loc[students["mark"].ge(70), ["class", "name"]]
print("Selected rows:")
print(selected.to_csv(index=False).rstrip())
assert len(selected) == 2Expected output
class,name
Four,John Deo
Three,Max Ruin
Four,Alex John
Selected rows:
class,name
Four,John Deo
Three,Max RuinPreserve commas, quotes and line breaks 🔝
Default minimal quoting protects fields containing delimiters, quotes or newlines. QUOTE_NONE needs a compatible escapechar and reader settings; removing quotes indiscriminately can break a CSV.
notes = pd.DataFrame({"id": [1, 2], "note": ['Pen, blue', 'Customer said "yes"']})
text = notes.to_csv(index=False)
print(text.rstrip())
assert pd.read_csv(StringIO(text)).equals(notes)
unquoted = notes.to_csv(index=False, quoting=csv.QUOTE_NONE, escapechar="\\")
print("Escaped unquoted CSV:")
print(unquoted.rstrip())
assert pd.read_csv(StringIO(unquoted), quoting=csv.QUOTE_NONE, escapechar="\\").equals(notes)Expected output
id,note
1,"Pen, blue"
2,"Customer said ""yes"""
Escaped unquoted CSV:
id,note
1,Pen\, blue
2,Customer said \"yes\"Choose encoding for the receiving application 🔝
UTF-8 supports international text. utf-8-sig adds a byte-order mark that some spreadsheet import workflows prefer. Formatting as CSV does not force a spreadsheet to preserve leading-zero identifiers; import ID columns as text.
names = pd.DataFrame({"id": ["001", "002"], "name": ["José", "Ravi"]})
binary = BytesIO()
names.to_csv(binary, index=False, encoding="utf-8-sig")
print("UTF-8 BOM present:", binary.getvalue().startswith(b"\xef\xbb\xbf"))
binary.seek(0)
loaded = pd.read_csv(binary, encoding="utf-8-sig", dtype={"id": "string"})
print(loaded)
assert loaded["id"].tolist() == ["001", "002"]Expected output
UTF-8 BOM present: True
id name
0 001 José
1 002 RaviFormat amounts and dates under a defined policy 🔝
float_format rounds the exported representation. date_format controls datetime values inside the CSV; a date in the filename is constructed separately. Use decimal and sep consistently with the receiving reader.
report = pd.DataFrame({"date": pd.to_datetime(["2026-09-01", "2026-09-02"]), "amount": [5.256, 20.0]})
text = report.to_csv(index=False, float_format="%.2f", date_format="%Y-%m-%d")
print(text.rstrip())
filename = "sales_" + pd.Timestamp("2026-09-02").strftime("%Y%m%d") + ".csv"
print("Filename:", filename)
assert report.loc[0, "amount"] == 5.256Expected output
date,amount
2026-09-01,5.26
2026-09-02,20.00
Filename: sales_20260902.csvAppend only with a consistent schema 🔝
mode="a" appends file content. Write the header once and keep the same field order. Rerunning an append cell against a persistent file can duplicate records; these temporary files reset on each run.
with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
target = Path(folder) / "student.csv"
df.iloc[:2].to_csv(target, index=False)
df.iloc[2:].to_csv(target, mode="a", header=False, index=False)
loaded = pd.read_csv(target)
print(loaded)
assert loaded.equals(df)Expected output
NAME ID MATH ENGLISH
0 Ravi 1 30 20
1 Raju 2 40 30
2 Alex 3 50 40Export a database result 🔝
SQLite makes this database example runnable without credentials. For MySQL, use a configured SQLAlchemy connection; see connection setup and database reading. Keep credentials outside a shared notebook.
with sqlite3.connect(":memory:") as connection:
df.to_sql("student", connection, index=False)
from_sql = pd.read_sql_query("SELECT NAME, ID, MATH, ENGLISH FROM student ORDER BY ID", connection)
print(from_sql.to_csv(index=False).rstrip())
assert from_sql.equals(df)
# Optional MySQL adaptation with your configured SQLAlchemy engine:
# with engine.connect() as connection:
# from_sql = pd.read_sql_query("SELECT * FROM student ORDER BY id", connection)
# from_sql.to_csv("student_database.csv", index=False)
# For an existing SQLite file, connect with sqlite3.connect("my_db.db").Expected output
NAME,ID,MATH,ENGLISH
Ravi,1,30,20
Raju,2,40,30
Alex,3,50,40Practical example: export accepted orders and keep review records 🔝
The synthetic amounts include an invalid value. Parse them, retain the source ID as text and do not turn an unknown amount into zero. See the Data Cleaning hub.
raw = pd.DataFrame({"order_id": pd.Series(["001", "002", "003", "004"], dtype="string"),
"product": ["Pen", "Book", "Bag", "Pencil"], "amount": ["5.25", "20", "bad", "8.5"]})
amount = pd.to_numeric(raw["amount"], errors="coerce")
review = raw.loc[amount.isna()].copy()
accepted = raw.loc[amount.notna()].copy()
accepted["amount"] = amount.loc[accepted.index]
text = accepted.to_csv(index=False, float_format="%.2f")
print(text.rstrip())
print("Review IDs:", review["order_id"].tolist())
assert len(accepted) + len(review) == len(raw)Expected output
order_id,product,amount
001,Pen,5.25
002,Book,20.00
004,Pencil,8.50
Review IDs: ['003']Read the exported CSV back and check its scope 🔝
CSV does not store all Pandas dtype metadata. Specify ID types and missing-value rules when reloading. Check record count, field names, identifiers and totals under the selected formatting precision.
loaded = pd.read_csv(StringIO(text), dtype={"order_id": "string"})
print(loaded)
print("Exported orders:", len(loaded), "Revenue:", loaded["amount"].sum())
assert loaded.columns.tolist() == accepted.columns.tolist()
assert loaded["order_id"].tolist() == ["001", "002", "004"]
assert loaded["amount"].sum() == 33.75
# Optional Colab downloads:
# accepted.to_csv("accepted_orders.csv", index=False, float_format="%.2f")
# review.to_csv("review_orders.csv", index=False)
# from google.colab import files
# files.download("accepted_orders.csv")Expected output
order_id product amount
0 001 Pen 5.25
1 002 Book 20.00
2 004 Pencil 8.50
Exported orders: 3 Revenue: 33.75Common questions 🔝
Why is there an extra unnamed column? The index was exported; use index=False for disposable labels. Does a relative path mean the script folder? It means the current working directory. Does CSV preserve dtypes? No; specify important types when reading. Does mode=w replace a file? Yes; mode=x fails if a file exists, and mode=a appends. Does a missing marker change stored values? No. Can a spreadsheet treat text as a formula? It can interpret certain text fields as formulas; apply a documented text-import or sanitization policy when exporting untrusted text for spreadsheet use. Can a file be compressed? A filename such as report.csv.gz allows compression inference. Continue with read_excel(), to_excel() and the student SQL sample.
Exercise: export only ID and Maths marks 🔝
Select students with Maths marks at least 40, export ID and MATH without the index, and reload the result. Predict IDs and row count.
Exercise solution 🔝
IDs 2 and 3 meet the rule. The output has two fields and two rows.
selected = df.loc[df["MATH"].ge(40), ["ID", "MATH"]]
answer_csv = selected.to_csv(index=False)
print(answer_csv.rstrip())
answer = pd.read_csv(StringIO(answer_csv))
assert answer["ID"].tolist() == [2, 3]
assert answer.shape == (2, 2)Expected output
ID,MATH
2,40
3,50Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change export options and sales values, then inspect CSV text and reloaded records. Save your own copy to keep edits. All sample tables are included in the notebook.
Reference: Pandas DataFrame.to_csv documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials