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

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
Tip: Use 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
)
Note: Excel export commonly requires an additional engine such as 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)
Remember: Parquet is a column-oriented format often useful for analytics workflows. Writing Parquet files requires a supported optional dependency, such as PyArrow or fastparquet.

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=False avoids writing the column names again.
  • index=False avoids adding the DataFrame index.
Important: This example assumes the existing CSV file already has the same columns in the same order. If the file does not exist, or its structure differs, handle that case explicitly.

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=False if you do not need the index in the exported file.
  • Duplicate headers while appending: Use header=False when 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

  1. Which method exports a DataFrame to CSV?
  2. Why is index=False commonly used?
  3. Which parameter sets the worksheet name during Excel export?
  4. What does orient="records" mean when writing JSON?
  5. Which mode is commonly used to append data to a file?
  6. Why should you use header=False when appending rows to an existing CSV?
  7. 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.