Pandas Data Input and Output
Learn how to import data from external files into Pandas, inspect datasets, and export processed data to CSV, Excel, and other supported file formats.
1. Learning Objectives
After completing this lesson, you will be able to:
- Understand data input and output in Pandas.
- Read CSV files using
read_csv(). - Read Excel files using
read_excel(). - Read JSON files using
read_json(). - Understand file paths and common reading options.
- Export DataFrames using
to_csv()andto_excel(). - Handle common file-reading errors.
2. What Are Data Input and Output?
Data input means loading information from a file or another data source into a Pandas object. Data output means saving processed data to a file or destination.
Figure 1: Pandas reads data, processes it, and writes the results to files.
read_csv()
for input and to_csv() for output.
3. Importing Pandas
Before reading or writing data, import the Pandas library.
import pandas as pd print(pd.__version__)
The first statement imports Pandas and assigns it the
commonly used alias pd. The second statement
displays the installed Pandas version.
import pandas as pd at the beginning
of your Python script or notebook.
4. Common File Formats
Pandas supports several file formats for loading and saving data. The function you use depends on the file format.
| File Format | Reading Function | Writing Method |
|---|---|---|
| CSV | pd.read_csv() |
df.to_csv() |
| Excel | pd.read_excel() |
df.to_excel() |
| JSON | pd.read_json() |
df.to_json() |
| Parquet | pd.read_parquet() |
df.to_parquet() |
| Text with separators | pd.read_table() |
Format-dependent |
5. Reading a CSV File
CSV stands for Comma-Separated Values. It stores tabular data as text, with values separated by a delimiter.
Suppose a file named students.csv contains
the following data:
Name,Age,Marks Samadhan,20,85 Pradnya,21,92 Samiksha,19,88
Read the File
import pandas as pd
df = pd.read_csv("students.csv")
print(df)
Output:
Name Age Marks 0 Samadhan 20 85 1 Pradnya 21 92 2 Samiksha 19 88
Pandas reads the first row as column headers by default and creates a DataFrame from the remaining records.
FileNotFoundError.
6. Reading CSV Files with Options
The read_csv() function accepts parameters
that let you control how the file is loaded.
Reading Selected Columns
df = pd.read_csv(
"students.csv",
usecols=["Name", "Marks"]
)
print(df)
Output:
Name Marks 0 Samadhan 85 1 Pradnya 92 2 Samiksha 88
Reading a Limited Number of Rows
df = pd.read_csv("students.csv", nrows=2)
print(df)
The nrows=2 argument loads at most two data rows.
Reading a File with a Different Delimiter
If values are separated by semicolons instead of commas, specify the separator.
df = pd.read_csv(
"students.txt",
sep=";"
)
7. Reading Excel Files
Pandas can read Excel spreadsheets using
read_excel().
import pandas as pd
df = pd.read_excel("students.xlsx")
print(df)
By default, Pandas reads the first worksheet and uses its first row as column headers.
Selecting a Worksheet
df = pd.read_excel(
"students.xlsx",
sheet_name="Students"
)
print(df.head())
You can specify a worksheet by its name or position.
For example, sheet_name=0 selects the first worksheet.
openpyxl for modern
.xlsx files.
8. Reading JSON Files
JSON is a common format for storing structured information and exchanging data between applications.
Example content of students.json:
[
{"Name": "Samadhan", "Marks": 85},
{"Name": "Pradnya", "Marks": 92},
{"Name": "Samiksha", "Marks": 88}
]
Read JSON Data
df = pd.read_json("students.json")
print(df)
Output:
Name Marks 0 Samadhan 85 1 Pradnya 92 2 Samiksha 88
9. Exporting Data to CSV
The to_csv() method saves a DataFrame
to a CSV file.
import pandas as pd
df = pd.DataFrame({
"Name": ["Samadhan", "Pradnya", "Samiksha"],
"Marks": [85, 92, 88]
})
df.to_csv("student_results.csv", index=False)
The index=False argument prevents Pandas
from writing the DataFrame index as an extra CSV column.
Export Without Column Headers
df.to_csv(
"student_results.csv",
index=False,
header=False
)
index=False or set index=True.
10. Exporting Data to Excel and JSON
Save as Excel
df.to_excel(
"student_results.xlsx",
index=False
)
This writes the DataFrame to an Excel workbook. An appropriate Excel-writing engine may need to be installed.
Save as JSON
df.to_json(
"student_results.json",
orient="records",
indent=4
)
The orient="records" option stores each row
as a JSON object. The indent=4 option makes
the JSON output easier to read.
11. File Paths and Common Errors
A file path tells Python where to find a file or where to save it.
# Relative path
df = pd.read_csv("students.csv")
# Example absolute path on Windows
df = pd.read_csv(r"C:\Data\students.csv")
# Inspect the loaded data
print(df.head())
| Error | Possible Cause | What to Check |
|---|---|---|
FileNotFoundError |
File or path does not exist. | Verify the file location and spelling. |
PermissionError |
Access to the file is denied. | Check file permissions or whether another application is locking it. |
ParserError |
CSV content cannot be parsed as expected. | Check delimiters, quotes, and inconsistent rows. |
ImportError |
A required optional dependency is missing. | Install the appropriate file-format engine. |
12. Practical Exercise
CIIT TRAINING INSTITUTE: Student Records
Create a DataFrame containing student names, ages, marks, and cities for Samadhan, Pradnya, and Samiksha.
Complete these tasks:
- Create a DataFrame with the four columns.
- Save the DataFrame to
students.csv. - Read the CSV file back into a DataFrame.
- Display the first two records.
- Export only the Name and Marks columns to another CSV file.
- Save the complete DataFrame as an Excel file.
- Save the data as JSON using the records orientation.
Practice Code
import pandas as pd
data = {
"Name": ["Samadhan", "Pradnya", "Samiksha"],
"Age": [20, 21, 19],
"Marks": [85, 92, 88],
"City": ["Pune", "Mumbai", "Nashik"]
}
df = pd.DataFrame(data)
# Export to CSV
df.to_csv("students.csv", index=False)
# Read the CSV file
loaded_df = pd.read_csv("students.csv")
print("Loaded data:")
print(loaded_df)
print("\nFirst two records:")
print(loaded_df.head(2))
# Export selected columns
loaded_df[["Name", "Marks"]].to_csv(
"student_marks.csv",
index=False
)
# Export to Excel
loaded_df.to_excel(
"students.xlsx",
index=False
)
# Export to JSON
loaded_df.to_json(
"students.json",
orient="records",
indent=4
)
Revision Questions
- What is the difference between data input and data output?
- Which function reads a CSV file?
- What is the purpose of
index=Falseinto_csv()? - How can you read a particular Excel worksheet?
- Which function reads JSON data into a DataFrame?
- What can cause a
FileNotFoundError? - How can you export selected DataFrame columns to a CSV file?
Summary
In this lesson, we learned how Pandas reads data from CSV, Excel, and JSON files. We explored common reading options, including selecting columns, limiting rows, and selecting Excel worksheets.
We also learned to export DataFrames using
to_csv(), to_excel(), and
to_json(). File paths, optional dependencies,
and common file-reading errors were discussed.
Data input and output are essential for importing real-world datasets, processing information, and saving results for reports and further analysis.