Writing Files in Pandas
Learn how to export Pandas Series and DataFrames into CSV, Excel, JSON, and other file formats.
1. Learning Objectives
After completing this lesson, you will be able to:
- Save a DataFrame to a CSV file.
- Control headers, indexes, columns, and missing values during export.
- Write data to Excel and JSON formats.
- Understand common file-writing errors.
- Export cleaned and processed data for future use.
2. Create a Sample DataFrame
Before saving data, let us create a DataFrame containing student information.
import pandas as pd
data = {
"Name": ["Samadhan", "Pradnya", "Samiksha"],
"Marks": [85, 92, 88],
"City": ["Pune", "Mumbai", "Nashik"]
}
df = pd.DataFrame(data)
print(df)
Output
Name Marks City 0 Samadhan 85 Pune 1 Pradnya 92 Mumbai 2 Samiksha 88 Nashik
3. Writing a DataFrame to CSV
The to_csv() method saves a DataFrame into a comma-separated values file.
df.to_csv("students.csv", index=False)
This creates a file named students.csv in the current working directory.
Example CSV content
Name,Marks,City Samadhan,85,Pune Pradnya,92,Mumbai Samiksha,88,Nashik
index=False when you do not want Pandas to write the DataFrame's row index as an extra column.
4. Important CSV Export Options
| Parameter | Purpose | Example |
|---|---|---|
index |
Controls whether row indexes are saved. | index=False |
header |
Controls whether column names are saved. | header=True |
columns |
Selects which columns to export. | columns=["Name", "Marks"] |
sep |
Sets the field separator. | sep=";" |
na_rep |
Text used for missing values. | na_rep="NA" |
encoding |
Sets the file encoding. | encoding="utf-8" |
mode |
Controls how the file is opened. | mode="a" |
Example: Export selected columns
df.to_csv(
"student_marks.csv",
columns=["Name", "Marks"],
index=False
)
Example: Use a different separator
df.to_csv("students_semicolon.csv", sep=";", index=False)
Example: Replace missing values in the output
df.to_csv("students.csv", index=False, na_rep="Not Available")
5. Saving to Excel
Use to_excel() to export a DataFrame to an Excel workbook.
df.to_excel("students.xlsx", index=False)
To choose a worksheet name, use the sheet_name parameter.
df.to_excel(
"students.xlsx",
sheet_name="StudentDetails",
index=False
)
openpyxl. If Pandas reports that no Excel writer engine is available, install the required package in your Python environment.
Writing multiple DataFrames to one workbook
marks = df[["Name", "Marks"]]
cities = df[["Name", "City"]]
with pd.ExcelWriter("student_report.xlsx") as writer:
marks.to_excel(writer, sheet_name="Marks", index=False)
cities.to_excel(writer, sheet_name="Cities", index=False)
6. Saving to JSON
JSON is a common format for exchanging structured data between applications and web APIs.
df.to_json("students.json", orient="records", indent=4)
The orient="records" option stores each DataFrame row as a separate JSON object.
Example output
[
{
"Name": "Samadhan",
"Marks": 85,
"City": "Pune"
},
{
"Name": "Pradnya",
"Marks": 92,
"City": "Mumbai"
},
{
"Name": "Samiksha",
"Marks": 88,
"City": "Nashik"
}
]
7. Other Export Formats
Pandas supports several other export formats, depending on your installed dependencies and the data you are working with.
| Format | Method | Example |
|---|---|---|
| CSV | to_csv() |
df.to_csv("data.csv", index=False) |
| Excel | to_excel() |
df.to_excel("data.xlsx", index=False) |
| JSON | to_json() |
df.to_json("data.json", orient="records") |
| Parquet | to_parquet() |
df.to_parquet("data.parquet", index=False) |
| HTML | to_html() |
df.to_html("data.html", index=False) |
| Clipboard | to_clipboard() |
df.to_clipboard(index=False) |
8. Appending Rows to a CSV File
When you need to add new rows to an existing CSV file, use append mode carefully.
new_student = pd.DataFrame({
"Name": ["Aarav"],
"Marks": [90],
"City": ["Pune"]
})
new_student.to_csv(
"students.csv",
mode="a",
header=False,
index=False
)
mode="a"opens the file for appending.header=Falseavoids writing the column names again.index=Falseavoids adding the DataFrame index.
9. Exporting Data After Cleaning
A common workflow is to clean a dataset and save the processed result for reporting or later analysis.
data = {
"Name": ["Samadhan", "Pradnya", "Samiksha"],
"Marks": [85, None, 88],
"City": ["Pune", "Mumbai", "Nashik"]
}
df = pd.DataFrame(data)
df["Marks"] = df["Marks"].fillna(df["Marks"].mean())
df.to_csv("cleaned_students.csv", index=False)
print("File exported successfully.")
Output
File exported successfully.
The resulting file contains the processed DataFrame. The output location depends on the current working directory unless you provide a different path.
10. File Paths and Common Errors
Use a clear output path
df.to_csv("output/students.csv", index=False)
The output directory must already exist. Pandas does not automatically create missing parent directories for this path.
Common issues
- PermissionError: The application may not have permission to write to the destination.
- FileNotFoundError: A parent directory may be missing or the path may be incorrect.
- Missing Excel engine: Install a compatible Excel-writing dependency.
- Unexpected index column: Set
index=Falseif you do not need the index in the exported file. - Duplicate headers while appending: Use
header=Falsewhen appending to a file that already contains headers.
11. Practical Exercise
Task: Samadhan has a DataFrame containing student names, marks, and cities. Export it into three different formats.
import pandas as pd
df = pd.DataFrame({
"Name": ["Samadhan", "Pradnya", "Samiksha"],
"Marks": [85, 92, 88],
"City": ["Pune", "Mumbai", "Nashik"]
})
# Task 1: Save to CSV without the index
# Task 2: Save to Excel with sheet name "Students"
# Task 3: Save to JSON using records orientation
Suggested solution
df.to_csv("students.csv", index=False)
df.to_excel(
"students.xlsx",
sheet_name="Students",
index=False
)
df.to_json(
"students.json",
orient="records",
indent=4
)
12. Revision Questions
- Which method exports a DataFrame to CSV?
- Why is
index=Falsecommonly used? - Which parameter sets the worksheet name during Excel export?
- What does
orient="records"mean when writing JSON? - Which mode is commonly used to append data to a file?
- Why should you use
header=Falsewhen appending rows to an existing CSV? - What should you check if an Excel export reports a missing writer engine?
Summary
Pandas provides convenient methods for exporting Series and DataFrames into common file formats. Use to_csv() for CSV files, to_excel() for Excel workbooks, and to_json() for JSON data. Choose options such as index, header, columns, and orient according to the needs of your output.
Always verify the destination path, required optional dependencies, and the structure of existing files before writing or appending data.