Loading and saving data

The two objects pandas is built on, how to read CSV, Excel, SQL and Parquet into them, and how to write results back out.

DataFrame and Series

Also called df, pd.DataFrame, pd.Series

What: A Series is one labelled column of values with a single dtype. A DataFrame is a table of Series that share one row index, each column with its own name and dtype.
Why: Almost every pandas operation takes or returns one of these two, so knowing which one you are holding explains most error messages.

Python

import pandas as pd

df = pd.DataFrame({"name": ["Ali", "Sara"], "age": [24, 31]})
ages = df["age"]        # Series
subset = df[["age"]]    # DataFrame with one column

Watch out

Expecting df["age"] and df[["age"]] to behave the same: single brackets give a Series, double brackets a DataFrame.

read_csv

Also called pd.read_csv, load CSV

What: pd.read_csv reads a delimited text file into a DataFrame, guessing column types. Options choose columns, set dtypes, parse dates and read only the first rows.
Why: CSV is the most common format you will be handed, and choosing columns and dtypes at load time saves memory and later cleaning.

Python

df = pd.read_csv(
    "sales.csv",
    usecols=["date", "region", "amount"],
    dtype={"region": "category"},
    parse_dates=["date"],
)

Watch out

Loading a huge file with every column when you need three, then running out of memory.

Excel, SQL, JSON and Parquet

Also called read_excel, read_sql, read_parquet, read_json

What: pandas has a reader for each common source: read_excel for workbooks, read_sql for a database query, read_json for JSON and read_parquet for columnar Parquet files.
Why: You can pull data from wherever it lives into the same DataFrame and use one set of tools afterwards.

Python

sheet = pd.read_excel("report.xlsx", sheet_name="Q1")
orders = pd.read_sql("SELECT * FROM orders", engine)
events = pd.read_parquet("events.parquet")

Watch out

Forgetting the optional engine packages: Excel needs openpyxl and Parquet needs pyarrow installed.

Writing data out

Also called to_csv, to_parquet, to_excel

What: Every reader has a writer: to_csv, to_excel, to_parquet, to_sql. index=False leaves the row index out of the file.
Why: Cleaned data is only useful once it is saved in a form the next person or step can read.

Python

df.to_csv("clean.csv", index=False)
df.to_parquet("clean.parquet")

Watch out

Leaving the default index=True in to_csv and getting an extra unnamed column every time the file is read back.

Why Parquet over CSV

What: Parquet is a compressed, columnar file format that stores each column's type alongside the data.
Why: Files are smaller, load faster, keep dates and categories as they were, and let you read only the columns you need.
Example: A 2 GB CSV of events often becomes a few hundred megabytes of Parquet that loads in a fraction of the time with its dates already parsed.

First look at a dataset

Also called head, info, describe, shape

What: head() shows the first rows, shape the row and column count, info() each column's dtype and non-null count, and describe() summary statistics.
Why: Two minutes here tells you which columns are the wrong type, which have missing values and whether the numbers look plausible.

Python

df.shape
df.head()
df.info()
df.describe(include="all")
df["region"].value_counts(dropna=False)

Go deeper