Pandas Cheat Sheet

import pandas as pd

The alias pd is the standard convention used throughout Pandas documentation and most Python data projects.

import pandas as pd

print(pd.__version__)

Checking the version helps you compare behavior with the correct API documentation and release notes.

import pandas as pd

sales = pd.Series(
    [1250, 980, 1430],
    index=["East", "North", "West"],
    name="sales"
)

print(sales)

The values form a one-dimensional array, while the region names form its index.

import pandas as pd

sales = pd.DataFrame({
    "region": ["East", "North", "West"],
    "revenue": [1250, 980, 1430],
    "orders": [18, 14, 21]
})

print(sales)

Each dictionary key becomes a column, and Pandas creates a default numbered index.

import pandas as pd

data = {
    "region": ["East", "West"],
    "sales": [1250, 1430]
}

df = pd.DataFrame(
    data,
    index=["order_101", "order_102"]
)

df.index.name = "order_id"

print(df)

A custom index can identify rows with meaningful labels instead of default row numbers.

import pandas as pd

data = {
    "sales": [1250, 1430],
    "region": ["East", "West"],
    "internal_id": [101, 102]
}

df = pd.DataFrame(
    data,
    columns=["region", "sales"]
)

df = df.astype({
    "region": "str",
    "sales": "int64"
})

print(df.dtypes)

The columns argument controls selection and order, while astype() applies explicit data types.

import pandas as pd

df = pd.read_csv(
    "sales.csv",
    usecols=[
        "order_id",
        "region",
        "sales",
        "order_date"
    ],
    dtype={
        "order_id": "int64",
        "region": "str",
        "sales": "float64"
    },
    parse_dates=["order_date"],
    na_values=["", "N/A", "unknown"]
)

Import only required columns and define important data types while reading the file.

import pandas as pd

df = pd.read_excel(
    "sales.xlsx",
    sheet_name="Orders",
    usecols="A:E",
    header=0,
    parse_dates=["order_date"]
)

print(df.head())

Select the worksheet, column range, header row, and date columns during import.

print(df.head())
print(df.tail())
print(df.sample(5, random_state=42))

df.info(memory_usage="deep")

summary = df.describe(
    include="all"
)

print(summary)

Use several views because the beginning or end of a file may not represent the entire dataset.

region_counts = (
    df["region"]
    .value_counts(
        dropna=False,
        normalize=False
    )
)

region_percent = (
    df["region"]
    .value_counts(
        dropna=False,
        normalize=True
    )
    .mul(100)
    .round(1)
)

print(region_counts)
print(region_percent)

Frequency checks reveal dominant categories, unexpected labels, and missing values.

selection = df.loc[
    ["order_101", "order_103"],
    ["product", "region", "sales"]
]

print(selection)

Both lists contain labels: index labels for the rows and names for the columns.

selection = df.loc[
    "order_101":"order_103",
    "region":"sales"
]

print(selection)

Label-based slices include both the starting and ending labels when those labels exist.

selection = df.iloc[
    [0, 2],
    [0, 2]
]

print(selection)

This selects rows 0 and 2 together with columns 0 and 2.

selection = df.iloc[
    0:3,
    1:3
]

print(selection)

Position 3 is excluded, following standard Python slice behavior.

minimum_sales = 500
allowed_regions = ["East", "West"]

mask = (
    df["sales"].ge(minimum_sales)
    & df["region"].isin(allowed_regions)
    & df["active"].eq(True)
)

result = df.loc[
    mask,
    [
        "product",
        "region",
        "sales"
    ]
]

print(result)

Naming the mask makes complex filtering logic easier to read, test, and reuse.

search_term = "pro"

mask = df["product"].str.contains(
    search_term,
    case=False,
    na=False,
    regex=False
)

result = df.loc[
    mask,
    ["product", "sales"]
]

print(result)

Set regex=False when the search term should be treated as literal text rather than a regular expression.

sorted_df = df.sort_values(
    by=[
        "region",
        "sales",
        "order_date"
    ],
    ascending=[
        True,
        False,
        True
    ],
    na_position="last",
    kind="stable"
)

print(sorted_df)

Each value in ascending corresponds to the column in the same position inside by.

sorted_df = (
    df.sort_values(
        "sales",
        ascending=False
    )
    .reset_index(drop=True)
)

print(sorted_df)

Resetting the index after sorting creates consecutive row labels without retaining the previous index as a column.

indexed_df = (
    df.set_index(
        "order_id",
        verify_integrity=True
    )
    .sort_index()
)

print(indexed_df)

verify_integrity=True raises an error when the proposed index contains duplicate values.

restored_df = indexed_df.reset_index()

print(restored_df)

Without drop=True, the existing index becomes a regular DataFrame column.

clean_df = df.fillna({
    "region": "Unknown",
    "discount": 0,
    "active": False
})

median_sales = clean_df[
    "sales"
].median()

clean_df["sales"] = clean_df[
    "sales"
].fillna(median_sales)

Use a replacement that matches the meaning and data type of each column.

required_columns = [
    "order_id",
    "product",
    "sales"
]

clean_df = df.dropna(
    subset=required_columns
).reset_index(drop=True)

print(clean_df)

Drop a row only when the missing fields are essential to the intended analysis.

df = df.sort_values([
    "product",
    "order_date"
])

df["price"] = (
    df.groupby("product")["price"]
    .ffill(limit=1)
)

Sort the data first and fill within the correct group to avoid carrying a value into an unrelated product.

df["temperature"] = (
    df["temperature"]
    .interpolate(
        method="linear",
        limit=2,
        limit_area="inside"
    )
)

This fills up to two internal gaps without extrapolating beyond the known observations.

raw_sales = df["sales"].copy()

df["sales"] = pd.to_numeric(
    df["sales"],
    errors="coerce"
)

invalid_sales = (
    raw_sales.notna()
    & df["sales"].isna()
)

invalid_rows = df.loc[
    invalid_sales
]

print(invalid_rows)

Preserve the original Series so you can identify values that became missing during conversion.

raw_dates = df[
    "order_date"
].copy()

df["order_date"] = pd.to_datetime(
    df["order_date"],
    format="%Y-%m-%d",
    errors="coerce"
)

invalid_dates = (
    raw_dates.notna()
    & df["order_date"].isna()
)

print(df.loc[invalid_dates])

Supplying a known format makes the expected date structure explicit.

invalid_discount = (
    df["discount"].notna()
    & ~df["discount"].between(
        0,
        1,
        inclusive="both"
    )
)

invalid_rows = df.loc[
    invalid_discount,
    ["order_id", "discount"]
]

print(invalid_rows)

Review invalid values before deciding whether to correct, remove, or cap them.

df["discount"] = (
    df["discount"]
    .clip(
        lower=0,
        upper=1
    )
)

Use clipping only when the valid boundaries are known and values outside them are confirmed errors.

df["customer_name"] = (
    df["customer_name"]
    .astype("string")
    .str.strip()
    .str.replace(
        r"\s+",
        " ",
        regex=True
    )
    .str.title()
)

This removes outer whitespace, collapses repeated internal whitespace, and applies consistent capitalization.

df["email"] = (
    df["email"]
    .astype("string")
    .str.strip()
    .str.lower()
)

valid_email_shape = (
    df["email"]
    .str.fullmatch(
        r"[^@\s]+@[^@\s]+\.[^@\s]+",
        na=False
    )
)

invalid_emails = df.loc[
    ~valid_email_shape
    & df["email"].notna()
]

This performs a practical format check, not complete verification that an email account exists.

df[[
    "department",
    "region",
    "team"
]] = df["assignment"].str.split(
    "-",
    n=2,
    expand=True,
    regex=False
)

With expand=True, the resulting pieces become separate DataFrame columns.

parts = df["product_code"].str.extract(
    r"^(?P<category>[A-Z]{2})-"
    r"(?P<number>\d{4})$"
)

df = df.join(parts)

print(df[[
    "product_code",
    "category",
    "number"
]])

Named capture groups become clearly labeled result columns.

start_date = pd.Timestamp(
    "2026-01-01"
)

end_date = pd.Timestamp(
    "2026-04-01"
)

mask = df["order_date"].between(
    start_date,
    end_date,
    inclusive="left"
)

quarter_one = df.loc[mask]

This includes January 1 but excludes April 1, producing a clear half-open reporting interval.

today = pd.Timestamp.now().normalize()

cutoff = today - pd.Timedelta(
    days=30
)

recent_orders = df.loc[
    df["order_date"].between(
        cutoff,
        today,
        inclusive="both"
    )
]

Normalize the current timestamp when the comparison should operate at calendar-day level.

df["created_at"] = pd.to_datetime(
    df["created_at"],
    utc=True
)

df["created_copenhagen"] = (
    df["created_at"]
    .dt.tz_convert(
        "Europe/Copenhagen"
    )
)

Convert timezone-aware UTC timestamps to the timezone required for reporting or display.

df["local_time"] = pd.to_datetime(
    df["local_time"],
    errors="coerce"
)

df["local_time"] = (
    df["local_time"]
    .dt.tz_localize(
        "Europe/Copenhagen",
        ambiguous="NaT",
        nonexistent="shift_forward"
    )
)

Localizing assigns a timezone to timestamps that do not already contain timezone information.

regional_sales = (
    df.groupby("region")["sales"]
    .sum()
    .sort_values(ascending=False)
)

average_sales = (
    df.groupby("region")["sales"]
    .mean()
)

Select a value column after groupby(), then apply the required aggregation.

rows_per_region = (
    df.groupby("region")
    .size()
)

orders_per_region = (
    df.groupby("region")["order_id"]
    .count()
)

customers_per_region = (
    df.groupby("region")["customer_id"]
    .nunique()
)

Use nunique() when you need the number of distinct values inside each group.

region_total = (
    df.groupby("region")["sales"]
    .transform("sum")
)

df = df.assign(
    region_total=region_total,
    region_share=(
        df["sales"]
        .div(region_total)
    )
)

The transformed totals retain the same index as the original DataFrame.

region_average = (
    df.groupby("region")["sales"]
    .transform("mean")
)

df["above_region_average"] = (
    df["sales"] > region_average
)

Use transform() when the calculated group value must remain aligned with every source row.

summary = (
    df.groupby(
        "region",
        dropna=False,
        as_index=False
    )
    .agg(
        total_sales=("sales", "sum")
    )
)

Set dropna=False when rows with missing grouping keys must be included in the result.

observed_summary = (
    df.groupby(
        "category",
        observed=True
    )["sales"]
    .sum()
)

all_categories = (
    df.groupby(
        "category",
        observed=False
    )["sales"]
    .sum()
)

Use observed=False when unobserved categories must also appear in the grouped result.

matched = orders.merge(
    customers,
    on="customer_id",
    how="inner",
    validate="many_to_one"
)

An inner merge excludes keys that appear in only one of the two DataFrames.

all_records = orders.merge(
    customers,
    on="customer_id",
    how="outer",
    indicator=True
)

print(
    all_records["_merge"]
    .value_counts()
)

The indicator column identifies rows originating from the left, right, or both DataFrames.

audit = orders.merge(
    customers,
    on="customer_id",
    how="outer",
    indicator="merge_source"
)

unmatched = audit.loc[
    audit["merge_source"] != "both"
]

The indicator contains left_only, right_only, or both.

unknown_customers = (
    orders.merge(
        customers,
        on="customer_id",
        how="left_anti"
    )
)

A left anti join returns rows whose keys occur only in the left DataFrame.

monthly_frames = [
    january_orders,
    february_orders,
    march_orders
]

quarter_orders = pd.concat(
    monthly_frames,
    ignore_index=True,
    sort=False
)

Use ignore_index=True when the original row indexes should not be preserved.

customer_features = pd.concat(
    [
        customer_totals,
        customer_counts,
        customer_averages
    ],
    axis="columns",
    join="inner"
)

Column-wise concatenation aligns rows by index rather than by their physical position.

sales_summary = df.pivot_table(
    values="sales",
    index="region",
    columns="product",
    aggfunc="sum",
    fill_value=0
)

Missing region and product combinations are replaced with zero.

detailed_summary = df.pivot_table(
    values="sales",
    index="region",
    columns="product",
    aggfunc=["sum", "mean"],
    fill_value=0,
    margins=True,
    margins_name="Total"
)

Multiple aggregation functions create hierarchical column labels.

grouped_sales = (
    df.groupby(
        ["region", "product"]
    )["sales"]
    .sum()
)

wide_summary = grouped_sales.unstack(
    level="product",
    fill_value=0
)

unstack() moves a row-index level to the column axis.

long_summary = (
    wide_summary
    .stack()
    .rename("sales")
    .reset_index()
)

stack() moves a column level into the row index.

status_counts = pd.crosstab(
    index=df["region"],
    columns=df["status"],
    margins=True,
    margins_name="Total"
)

Without values and aggfunc, crosstab() returns frequency counts.

status_percentages = pd.crosstab(
    index=df["region"],
    columns=df["status"],
    normalize="index"
).mul(100).round(1)

normalize="index" makes every row total equal to one before conversion to percentages.

df["sales_label"] = (
    df["sales"]
    .map(
        lambda value:
        f"${value:,.2f}",
        na_action="ignore"
    )
)

The na_action="ignore" argument prevents the formatting function from receiving missing values.

numeric = df[
    [
        "sales",
        "cost",
        "discount"
    ]
]

rounded = numeric.map(
    lambda value: round(value, 2),
    na_action="ignore"
)

DataFrame.map() applies a scalar function to every selected cell.

def calculate_tax(
    amount,
    tax_rate
):
    return amount * tax_rate

df["tax"] = df["sales"].apply(
    calculate_tax,
    tax_rate=0.20
)

Additional keyword arguments are passed directly to the custom function.

tax_rate = 0.20

df["tax"] = (
    df["sales"]
    .mul(tax_rate)
)

Direct vectorized arithmetic is simpler and usually faster for this type of calculation.

df["sales_scaled"] = (
    df["sales"]
    .transform(
        lambda values:
        (
            values - values.min()
        )
        / (
            values.max()
            - values.min()
        )
    )
)

The transformed Series retains the original row index.

regional_mean = (
    df.groupby("region")["sales"]
    .transform("mean")
)

df["difference_from_mean"] = (
    df["sales"]
    - regional_mean
)

Group transformation broadcasts each regional mean back to its original rows.

duplicate_orders = df.loc[
    df.duplicated(
        subset="order_id",
        keep=False
    )
].sort_values(
    "order_id",
    kind="stable"
)

This returns every row connected to a repeated order ID.

key_columns = [
    "customer_id",
    "order_date",
    "product_id"
]

duplicate_purchases = df.loc[
    df.duplicated(
        subset=key_columns,
        keep=False
    )
].sort_values(
    key_columns,
    kind="stable"
)

A composite key identifies duplicates using a combination of fields.

clean_orders = df.drop_duplicates(
    subset="order_id",
    keep="first",
    ignore_index=True
)

The first row for each order ID is retained.

latest_orders = df.drop_duplicates(
    subset="order_id",
    keep="last",
    ignore_index=True
)

The final row in the current DataFrame order is retained.

has_unique_index = df.index.is_unique

duplicate_index_rows = df.loc[
    df.index.duplicated(
        keep=False
    )
]

print(has_unique_index)
print(duplicate_index_rows)

Duplicate index labels are separate from duplicate row values.

unique_index_df = df.loc[
    ~df.index.duplicated(
        keep="first"
    )
].copy()

Negating the duplicate mask keeps the first row for each index label.

df.to_csv(
    "sales-report.csv.gz",
    index=False,
    compression="infer"
)

Pandas infers gzip compression from the .gz filename extension.

compression_options = {
    "method": "zip",
    "archive_name": "sales-report.csv"
}

df.to_csv(
    "sales-report.zip",
    index=False,
    compression=compression_options
)

The archive contains a CSV file named sales-report.csv.

df.to_excel(
    "sales-report.xlsx",
    sheet_name="Sales",
    index=False,
    freeze_panes=(1, 0)
)

The first worksheet row remains visible while the user scrolls.

with pd.ExcelWriter(
    "sales-workbook.xlsx"
) as writer:
    df.to_excel(
        writer,
        sheet_name="Transactions",
        index=False
    )

    regional_summary.to_excel(
        writer,
        sheet_name="Regional Summary",
        index=False
    )

    product_summary.to_excel(
        writer,
        sheet_name="Product Summary",
        index=False
    )

The context manager closes and saves the workbook automatically.

df.to_json(
    "sales-report.json",
    orient="records",
    date_format="iso",
    indent=2,
    force_ascii=False
)

The result is a JSON array containing one object for each row.

df.to_json(
    "sales-report.jsonl",
    orient="records",
    lines=True,
    date_format="iso"
)

JSON Lines is useful when records are processed individually or in streaming workflows.

export_path = "sales-report.csv"

df.to_csv(
    export_path,
    index=False
)

check = pd.read_csv(
    export_path
)

assert len(check) == len(df)
assert list(check.columns) == list(df.columns)

A round-trip check can detect missing rows or unexpected columns.

export_path = "sales-report.parquet"

df.to_parquet(
    export_path,
    index=False
)

check = pd.read_parquet(
    export_path
)

assert check.shape == df.shape

print(check.dtypes)

Parquet normally preserves data types more reliably than CSV.

def calculate_margin(row):
    return (
        row["sales"]
        - row["cost"]
    )

df["margin"] = df.apply(
    calculate_margin,
    axis="columns"
)

This calls a Python function separately for every DataFrame row.

df["margin"] = (
    df["sales"]
    - df["cost"]
)

df["margin_percent"] = (
    df["margin"]
    .div(df["sales"])
    .mul(100)
    .round(2)
)

Vectorized operations process complete arrays using optimized implementations.

before = (
    df["region"]
    .memory_usage(deep=True)
)

df["region"] = (
    df["region"]
    .astype("category")
)

after = (
    df["region"]
    .memory_usage(deep=True)
)

print({
    "before_bytes": before,
    "after_bytes": after
})

Categories can reduce memory when a column contains many repeated values and relatively few unique labels.

unique_values = (
    df["region"]
    .nunique(dropna=True)
)

row_count = len(df)

unique_ratio = (
    unique_values
    / row_count
)

print({
    "rows": row_count,
    "unique_values": unique_values,
    "unique_ratio": unique_ratio
})

Categories may provide little benefit when almost every value is unique.

result = pd.DataFrame()

for file_path in csv_files:
    frame = pd.read_csv(
        file_path
    )

    result = pd.concat(
        [
            result,
            frame
        ],
        ignore_index=True
    )

Each iteration may copy an increasingly large amount of data.

frames = [
    pd.read_csv(file_path)
    for file_path in csv_files
]

result = pd.concat(
    frames,
    ignore_index=True,
    sort=False
)

A single concatenation avoids repeatedly rebuilding the result.

# Avoid this pattern
df[df["sales"] < 0]["sales"] = 0

This attempts to assign through an intermediate selected object.

negative_sales = (
    df["sales"] < 0
)

df.loc[
    negative_sales,
    "sales"
] = 0

This clearly modifies the original DataFrame using one assignment.

# Raises KeyError if the real
# column is named "Sales "
total = df["sales"].sum()

Column names are case-sensitive and may contain hidden whitespace.

df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(
        " ",
        "_",
        regex=False
    )
)

print(df.columns.tolist())

required_columns = {
    "order_id",
    "sales"
}

missing_columns = (
    required_columns
    - set(df.columns)
)

if missing_columns:
    raise KeyError(
        f"Missing columns: "
        f"{sorted(missing_columns)}"
    )

Validate required columns before starting the transformation.

# Raises:
# ValueError: The truth value of a
# Series is ambiguous
if df["sales"] > 0:
    print("Positive sales found")

The comparison returns one boolean value for every row.

positive_sales = (
    df["sales"] > 0
)

if positive_sales.any():
    print("At least one positive sale")

if positive_sales.all():
    print("Every sale is positive")

positive_rows = df.loc[
    positive_sales
]

Use any(), all(), or boolean filtering according to the required result.

# Does not update df correctly
df["sales"][
    df["sales"] < 0
] = 0

With Copy-on-Write, chained assignment cannot update the original DataFrame.

negative_sales = (
    df["sales"] < 0
)

df.loc[
    negative_sales,
    "sales"
] = 0

Selection and assignment now happen in one operation.

df["order_date"] = pd.to_datetime(
    df["order_date"],
    errors="coerce"
)

invalid_dates = df.loc[
    df["order_date"].isna()
]

df["order_year"] = (
    df["order_date"]
    .dt.year
)

Invalid date values become NaT and can be inspected separately.

df["customer_name"] = (
    df["customer_name"]
    .astype("string")
    .str.strip()
    .str.replace(
        r"\s+",
        " ",
        regex=True
    )
    .str.title()
)

Pandas string dtype preserves missing values while enabling vectorized string methods.

# Raises an error when more than
# one sales value exists for the
# same date and region
wide_sales = df.pivot(
    index="order_date",
    columns="region",
    values="sales"
)

pivot() cannot decide how duplicate values should be combined.

wide_sales = df.pivot_table(
    index="order_date",
    columns="region",
    values="sales",
    aggfunc="sum",
    fill_value=0
)

The aggregation function defines how repeated combinations are combined.