pandas: read_excel, filtering, groupby, merge

We'll use the sales data from the Foundations track (Date, Salesperson, Region, Product, Amount) saved as sales.xlsx, sheet "Data".

1. Reading

import pandas as pd

df = pd.read_excel("sales.xlsx", sheet_name="Data")
print(df.shape)       # (rows, columns)
print(df.head())      # first 5 rows
print(df.dtypes)      # type of each column

Useful options:

pd.read_excel("file.xlsx", sheet_name=None)          # ALL sheets -> dict of DataFrames
pd.read_excel("file.xlsx", skiprows=3)               # report has 3 title rows on top
pd.read_excel("file.xlsx", usecols="A:E")            # only these columns
pd.read_excel("file.xlsx", dtype={"PIN": str})       # keep leading zeros
pd.read_csv("file.csv")                              # CSV

A DataFrame is a table; a single column (df["Amount"]) is a Series.

2. Filtering — like AutoFilter

north = df[df["Region"] == "North"]
big_laptops = df[(df["Product"] == "Laptop") & (df["Amount"] > 50000)]
north_west = df[df["Region"].isin(["North", "West"])]

Use & for AND, | for OR, and put each condition in brackets.

query reads more like English:

df.query("Amount >= 30000 and Region != 'West'")

3. New columns

df["GST"] = df["Amount"] * 0.18
df["Total"] = df["Amount"] + df["GST"]
df["Month"] = df["Date"].dt.to_period("M").astype(str)     # "2026-04"
df["Name"] = df["Salesperson"].str.strip().str.title()     # TRIM + PROPER

The whole column is calculated at once — no loop, no dragging formulas.

4. groupby — like SUMIFS / pivot table

df.groupby("Salesperson")["Amount"].sum()

Several totals at once, as a clean table:

summary = (df.groupby("Region")
             .agg(Orders=("Amount", "count"), Total=("Amount", "sum"), Average=("Amount", "mean"))
             .reset_index()
             .sort_values("Total", ascending=False))

A cross-table like an Excel pivot:

df.pivot_table(index="Region", columns="Product", values="Amount", aggfunc="sum", fill_value=0)

5. merge — like XLOOKUP

products.xlsx has Product, Category, Cost. Bring Category and Cost into every sales row:

prod = pd.read_excel("products.xlsx")
merged = df.merge(prod, on="Product", how="left")
merged["Profit"] = merged["Amount"] - merged["Cost"]

If the key column has different names: left_on="Item", right_on="Product".

6. Combining many files

from pathlib import Path
files = Path("data").glob("*.xlsx")
all_data = pd.concat([pd.read_excel(f) for f in files], ignore_index=True)

concat stacks tables on top of each other (same columns).

7. Quick cleaning

df = df.drop_duplicates()
df = df.dropna(subset=["Amount"])                                    # remove rows with no amount
df["Date"] = pd.to_datetime(df["Date"], format="%d/%m/%Y", errors="coerce")   # text dates → dates
df["Amount"] = pd.to_numeric(df["Amount"], errors="coerce")          # "NA" → NaN instead of crashing

errors="coerce" turns bad values into empty (NaT/NaN) so you can find and report them.

Common mistakes

Using and/or instead of &/| in filters. Forgetting brackets around each condition. Not using format= or dayfirst=True for Indian dates. Merging on a key with extra spaces (strip first).