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

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() and to_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.

Input Files CSV / Excel / JSON Pandas Read and Process Output Files Save Results Read → Process → Export

Figure 1: Pandas reads data, processes it, and writes the results to files.

Remember: Pandas provides functions such as 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.

Tip: Use 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.

Tip: The file must be available at the specified path. Otherwise, Pandas may raise a 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=";"
)
Important: Choose the correct separator and column names for your file. The examples assume the files contain the shown data.

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.

Note: Reading Excel files generally requires an appropriate engine, such as 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
)
Remember: If you want to preserve the index in the CSV file, omit 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.

Important: Writing to a path may create or overwrite a file. Check the destination before saving important data.

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.
Tip: Start by checking the file path and file format when Pandas cannot load a dataset.

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:

  1. Create a DataFrame with the four columns.
  2. Save the DataFrame to students.csv.
  3. Read the CSV file back into a DataFrame.
  4. Display the first two records.
  5. Export only the Name and Marks columns to another CSV file.
  6. Save the complete DataFrame as an Excel file.
  7. 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

  1. What is the difference between data input and data output?
  2. Which function reads a CSV file?
  3. What is the purpose of index=False in to_csv()?
  4. How can you read a particular Excel worksheet?
  5. Which function reads JSON data into a DataFrame?
  6. What can cause a FileNotFoundError?
  7. 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.