Arduino Electronics and Robotics Course for Expats in Bukit Timah

1. Introduction: Loading and Saving Data in Pandas

In real-world data science, machine learning, and AI, datasets do not originate inside Python scripts—they live on disk in formats from clients, web APIs, or databases. Pandas provides I/O functions to read external files into DataFrames and to export cleaned data back to disk.

• Import: pd.read_csv(), pd.read_excel(), pd.read_json()
• Export: df.to_csv(), df.to_excel(), df.to_json()

2. Common Data Formats and Public Datasets

1. CSV — plain text delimited by commas; lightweight and standard in ML pipelines
2. Excel (.xlsx, .xls) — business workbooks that often contain multiple sheets
3. JSON — key–value text used by web services, APIs, and cloud platforms

Public practice sources include Kaggle, the UCI Machine Learning Repository, Google Cloud Public Datasets, and regional or government open-data portals.

3. Reading Files into Pandas DataFrames

A. Reading CSV Files (pd.read_csv)

import pandas as pd

# Reading a CSV file with optimization parameters
df = pd.read_csv(
    "retail_sales.csv",
    usecols=["Date", "Category", "Sales", "Quantity"],  # Load only required columns
    parse_dates=["Date"],                               # Parse column as datetime
    nrows=1000                                          # Limit number of rows read
)

• usecols — load only needed columns to save memory
• parse_dates — convert date strings to datetime on import
• nrows — limit rows for inspection of large files
• dtype — set column types explicitly to control memory use

B. Reading Excel Files (pd.read_excel)

Excel requires an extra engine such as openpyxl:

!pip install openpyxl

# Reading a specific worksheet from an Excel workbook
df_excel = pd.read_excel("company_records.xlsx", sheet_name="Q1_Sales")

Use sheet_name to target a sheet by name or index when a workbook has multiple sheets.

C. Reading JSON Files (pd.read_json)

# Reading a JSON file into a DataFrame
df_json = pd.read_json("web_transactions.json")

4. Exporting DataFrames to Disk

By default, export methods write the row index as an extra column. Use index=False to keep only real data columns:

A. Exporting to CSV (df.to_csv)

# Exporting cleaned DataFrame to CSV without row index numbers
df.to_csv("cleaned_sales.csv", index=False)

B. Exporting to Excel (df.to_excel)

# Exporting to Microsoft Excel
df.to_excel("cleaned_sales.xlsx", sheet_name="CleanedData", index=False)

C. Exporting to JSON (df.to_json)

# Exporting to JSON format
df.to_json("cleaned_sales.json")

5. Practical Workflow: Clean and Export

import pandas as pd

# 1. Importing CSV data with date parsing
df = pd.read_csv("retail_sales.csv", parse_dates=["Date"])

# 2. Inspecting initial dataset structure
print("Initial Dataset Shape:", df.shape)

# 3. Data cleaning: filter out rows where Sales are 0
df_cleaned = df[df["Sales"] > 0]
df_cleaned = df_cleaned[["Date", "Category", "Sales", "Quantity"]]
print("Cleaned Dataset Shape:", df_cleaned.shape)

# 4. Exporting the cleaned subset
df_cleaned.to_excel("cleaned_retail_sales.xlsx", index=False)
df_cleaned.to_json("cleaned_retail_sales.json")
print("Files successfully saved to disk!")

Example path: an original file with 1,825 rows and 6 columns; after dropping zero-sales rows and keeping four columns, about 1,780 rows remain and are written to Excel and JSON without an index column.