Reading Files in Pandas
Learn how to load CSV, Excel, JSON, and other data files into Pandas DataFrames, configure reading options, and handle common file-loading errors.
1. Learning Objectives
After completing this lesson, you will be able to:
- Understand how Pandas reads external data files.
- Read CSV files using
read_csv(). - Read Excel files using
read_excel(). - Load JSON data using
read_json(). - Choose specific columns and rows while reading data.
- Specify file paths, separators, and worksheet names.
- Recognize common file-reading errors.
2. Understanding File Reading
Data analysis often begins with information stored in files. Pandas provides reading functions that load this information into a DataFrame for analysis and manipulation.
Figure 1: Pandas reads supported files and converts their data into usable objects.
3. Reading CSV Files
CSV stands for Comma-Separated Values. It is a widely used format for storing tabular data in plain text.
Suppose students.csv contains:
Name,Age,Marks Samadhan,20,85 Pradnya,21,92 Samiksha,19,88
Using read_csv()
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 uses the first row as column headers by default. The remaining rows become DataFrame records.
4. Reading CSV Files with Options
The read_csv() function supports options that
help control how data is loaded.
Select Specific Columns
df = pd.read_csv(
"students.csv",
usecols=["Name", "Marks"]
)
print(df)
Output:
Name Marks 0 Samadhan 85 1 Pradnya 92 2 Samiksha 88
Read a Limited Number of Rows
df = pd.read_csv(
"students.csv",
nrows=2
)
print(df)
Output:
Name Age Marks 0 Samadhan 20 85 1 Pradnya 21 92
Specify a Separator
If a file uses semicolons instead of commas, specify
the delimiter with the sep parameter.
df = pd.read_csv(
"students.txt",
sep=";"
)
usecols when you only need certain columns.
It can reduce unnecessary data loading.
5. Reading Excel Files
Excel files commonly use the .xlsx extension.
Pandas can load worksheet data using read_excel().
import pandas as pd
df = pd.read_excel("students.xlsx")
print(df.head())
The head() method displays the first five rows
by default, helping you check the loaded data.
Read a Particular Worksheet
df = pd.read_excel(
"students.xlsx",
sheet_name="Students"
)
print(df)
You can provide a worksheet name or a zero-based worksheet
position, such as sheet_name=0.
openpyxl. Install the required
dependency if it is missing.
6. Reading JSON Files
JSON stands for JavaScript Object Notation. It is commonly used for data exchange between applications and APIs.
Example content of students.json:
[
{"Name": "Samadhan", "Marks": 85},
{"Name": "Pradnya", "Marks": 92},
{"Name": "Samiksha", "Marks": 88}
]
Using read_json()
df = pd.read_json("students.json")
print(df)
Output:
Name Marks 0 Samadhan 85 1 Pradnya 92 2 Samiksha 88
7. Reading Text and Other Delimited Files
The read_table() function is useful for
reading text files containing separated values.
Example content of students.txt:
Name|Marks Samadhan|85 Pradnya|92 Samiksha|88
Specify the Separator
df = pd.read_table(
"students.txt",
sep="|"
)
print(df)
Output:
Name Marks 0 Samadhan 85 1 Pradnya 92 2 Samiksha 88
The separator must match the format of the source file. Common separators include commas, tabs, pipes, and semicolons.
8. Handling Headers and Index Columns
Some files do not contain column headers. You can tell Pandas how to interpret these files using reading options.
File Without a Header
Example content:
Samadhan,85 Pradnya,92 Samiksha,88
df = pd.read_csv(
"student_marks.csv",
header=None,
names=["Name", "Marks"]
)
print(df)
Output:
Name Marks 0 Samadhan 85 1 Pradnya 92 2 Samiksha 88
Use a Column as the Index
df = pd.read_csv(
"students.csv",
index_col="Name"
)
print(df)
The index_col parameter uses the specified
column as the DataFrame index.
9. Reading Files in Chunks
Large files may require more memory than is available.
The chunksize parameter lets you process
a CSV file in smaller portions.
import pandas as pd
for chunk in pd.read_csv(
"large_students.csv",
chunksize=1000
):
print(chunk.shape)
Each iteration returns a DataFrame containing up to 1,000 rows. The final chunk may contain fewer rows.
10. File Paths and Common Errors
A file path tells Python where a file is located. A relative path is interpreted from the current working directory, while an absolute path specifies its location.
# Relative path
df = pd.read_csv("students.csv")
# Example absolute Windows path
df = pd.read_csv(r"C:\Data\students.csv")
print(df.head())
| Error | Possible Cause | What to Check |
|---|---|---|
FileNotFoundError |
File path does not exist. | Check the location and file name. |
PermissionError |
Python cannot access the file. | Check permissions and file locks. |
ParserError |
File contents cannot be parsed as expected. | Check delimiters, quotes, and row structure. |
ImportError |
A required file-format dependency is missing. | Install the appropriate dependency. |
11. Practical Exercise
CIIT TRAINING INSTITUTE: Student Records
Practice reading a CSV file containing student details for Samadhan, Pradnya, and Samiksha.
Complete these tasks:
- Create a file named
students.csv. - Read the CSV file into a DataFrame.
- Display the first two records.
- Read only the Name and Marks columns.
- Read only the first two data rows.
- Save the data to an Excel file and read it again.
- Identify and resolve a file path error if one occurs.
Practice Code
import pandas as pd
df = pd.DataFrame({
"Name": ["Samadhan", "Pradnya", "Samiksha"],
"Age": [20, 21, 19],
"Marks": [85, 92, 88]
})
df.to_csv("students.csv", index=False)
loaded_df = pd.read_csv("students.csv")
print("Complete Data:")
print(loaded_df)
print("\nFirst two rows:")
print(loaded_df.head(2))
print("\nSelected columns:")
print(
pd.read_csv(
"students.csv",
usecols=["Name", "Marks"]
)
)
print("\nFirst two data rows:")
print(
pd.read_csv(
"students.csv",
nrows=2
)
)
Revision Questions
- Which function reads CSV files in Pandas?
- What does the
usecolsparameter do? - How can you read a particular Excel worksheet?
- How can you read a CSV file without headers?
- What is the purpose of
index_col? - How can you read a file using a pipe separator?
- What is the benefit of reading a CSV file in chunks?
Summary
In this lesson, we learned how to read CSV, Excel, JSON, and delimited text files into Pandas DataFrames. We explored file paths, worksheet selection, headers, index columns, separators, and selected-column reading.
We also learned how to limit rows with nrows,
read large CSV files in chunks using chunksize,
and identify common file-loading errors.
Reading files is an essential step in data analysis because it allows us to load external datasets into Pandas for cleaning, transformation, and further analysis.