Master Pandas From Scratch

Learn Pandas fundamentals, Series, DataFrames, data selection, filtering, cleaning, sorting, grouping, merging, reshaping, string operations, time series, data analysis, performance optimization, visualization, and practical projects through structured lessons.

COURSE SYLLABUS
1. Pandas Introduction and Environment Setup
Introduction to Pandas Installation and Environment Setup
2. Pandas Core Data Structures
Data Structures Overview Pandas Series DataFrames Indexes, Rows and Columns Shape, Size and Dimensions
3. Pandas Data Input and Output
Data Input and Output Overview Reading CSV, Excel and JSON Files Writing and Exporting Files
4. Initial Data Inspection
Data Inspection Overview Head and Tail Data Info and Types Descriptive Statistics
5. Selection and Indexing
Selection and Indexing Overview Column Selection loc and iloc Index Management
6. Filtering and Conditional Selection
Filtering Overview Boolean Filtering Multiple Conditions isin, between and str.contains
7. Basic Data Cleaning
Data Cleaning Overview Missing Values Duplicate Data Rename and Replace Data Type Conversion
8. Vectorized Operations and Sorting
Operations and Sorting Overview Column Operations Sorting Values and Indexes Sum, Mean, Max and Min
9. GroupBy and Aggregation
GroupBy Overview Grouping Data Aggregation Functions Group Filtering Group Transform
10. Merging, Joining and Concatenating
Merge, Join and Concat Overview Concatenation Merging DataFrames Joining by Index
11. Reshaping and Pivoting
Reshaping Overview Pivot Tables Melting Data Stack and Unstack Cross Tabulation
12. String and Categorical Data
String and Categorical Overview String Operations Categorical Data One-Hot Encoding
13. Time Series Analysis
Time Series Overview DateTime Operations Time-Based Indexes Resampling Data Shift and Rolling Windows
14. Custom Functions and Mapping
Apply and Mapping Overview Apply and Lambda Functions Map Operations Element-Wise Operations
15. Performance and Large Datasets
Performance Overview Memory Optimization Processing Files in Chunks PyArrow, Dask and Polars
16. Pandas Data Visualization
Visualization Overview Basic Plotting Histograms, Lines, Scatter and Box Plots Matplotlib and Seaborn

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.

Data Files students.csv students.xlsx Pandas read_csv() read_excel() DataFrame Rows Columns Read files into a structured table for analysis.

Figure 1: Pandas reads supported files and converts their data into usable objects.

Remember: Reading a file does not normally modify the original file. It loads data into Python for further processing.

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=";"
)
Tip: Use 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.

Important: Reading modern Excel files generally requires an Excel engine such as 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
Remember: JSON files can have different structures. The appropriate reading approach depends on how the JSON data is organized.

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.

Practical use: Chunking can help process large CSV files without loading the entire dataset into memory at once.

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.
Important: Always verify the file path, file format, and separator when a file fails to load.

11. Practical Exercise

CIIT TRAINING INSTITUTE: Student Records

Practice reading a CSV file containing student details for Samadhan, Pradnya, and Samiksha.

Complete these tasks:

  1. Create a file named students.csv.
  2. Read the CSV file into a DataFrame.
  3. Display the first two records.
  4. Read only the Name and Marks columns.
  5. Read only the first two data rows.
  6. Save the data to an Excel file and read it again.
  7. 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

  1. Which function reads CSV files in Pandas?
  2. What does the usecols parameter do?
  3. How can you read a particular Excel worksheet?
  4. How can you read a CSV file without headers?
  5. What is the purpose of index_col?
  6. How can you read a file using a pipe separator?
  7. 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.