Python pandas for Data Analysis: Load, Explore, Group and Merge

If Excel is a calculator and SQL is a question you ask a database, pandas is a full workshop. It loads data from CSV, Excel, databases and APIs, cleans and reshapes it, and every step is saved as code you can rerun next month in seconds. That repeatability is why analysts move to Python.

Load and look

import pandas as pd

orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
print(orders.shape)          # (rows, columns)
print(orders.head())         # first five rows
orders.info()                # column types and missing values
print(orders.describe())     # count, mean, min, max, quartiles for numbers

Always start with these four. They answer "what does one row mean, how big is it, and what looks wrong?" before you calculate anything.

Select and filter

orders["city"]                                   # one column (a Series)
orders[["order_id", "city", "amount"]]          # several columns (a DataFrame)

pune_big = orders[(orders["city"] == "Pune") & (orders["amount"] > 20000)]
south    = orders[orders["city"].isin(["Chennai", "Bengaluru", "Hyderabad", "Kochi"])]
october  = orders[orders["order_date"].dt.month == 10]

Use & and | (not and / or) and put each condition in brackets — the most common beginner error in pandas.

New columns

orders["month"]      = orders["order_date"].dt.to_period("M")
orders["high_value"] = orders["amount"] >= 20000
orders["band"]       = pd.cut(orders["amount"], bins=[0, 5000, 20000, float("inf")],
                              labels=["Low", "Medium", "High"])

Group and summarise (pandas' GROUP BY)

summary = (orders.groupby("city")
                 .agg(orders=("order_id", "nunique"),
                      revenue=("amount", "sum"),
                      avg_order=("amount", "mean"))
                 .sort_values("revenue", ascending=False))
print(summary.head(10))

# Share of revenue by category
share = orders.groupby("category")["amount"].sum() / orders["amount"].sum() * 100
print(share.round(1))

Pivot tables in code

pivot = orders.pivot_table(index="city", columns="category", values="amount",
                           aggfunc="sum", fill_value=0, margins=True)

That is the Excel pivot from earlier — except it reruns on next month's file with no clicking.

Merge tables (pandas' JOIN)

customers = pd.read_csv("customers.csv")
full = orders.merge(customers, on="customer_id", how="left", validate="many_to_one")
print(len(orders), len(full))     # should be equal for a many-to-one left merge

Time series in one line

monthly = orders.set_index("order_date").resample("MS")["amount"].sum()
growth  = monthly.pct_change() * 100          # month-over-month growth %

Quick charts

import matplotlib.pyplot as plt

monthly.plot(kind="line", title="Monthly revenue (₹)")
plt.tight_layout(); plt.savefig("monthly_revenue.png")

summary["revenue"].head(8).plot(kind="barh", title="Top cities by revenue")
plt.gca().invert_yaxis(); plt.tight_layout(); plt.savefig("top_cities.png")

Save your result

summary.to_csv("city_summary.csv")
pivot.to_excel("city_category_pivot.xlsx")     # needs the openpyxl package

pandas, SQL and Excel — the same ideas

Learn the idea once, and you can use it in all three tools.