πWhy pandas Is the Analyst's Most Useful Tool
Excel is great until the file has 500,000 rows, comes from five sources, or needs the same cleaning every week. That is where Python and pandas take over. pandas lets you load, clean, combine and summarise data in a few lines of code, and the same script runs again next month in seconds.
This tutorial is hands-on. You will work through a realistic sales dataset, from loading the file to answering business questions and exporting a report. It assumes you know basic Python. If not, start with the Python roadmap.
πSetup
Create a virtual environment and install the libraries:
python -m venv .venvpip install pandas matplotlib openpyxl jupyterJupyter notebooks are ideal for analysis because you see each result immediately. Start one with the jupyter notebook command.
πStep 1: Load the Data
Assume a file called sales.csv with these columns: order_id, order_date, region, product, category, quantity, unit_price, customer_id.
import pandas as pddf = pd.read_csv("sales.csv")pandas can also read Excel with pd.read_excel, JSON with pd.read_json and SQL query results with pd.read_sql.
πStep 2: Inspect Before You Touch Anything
df.head() # first 5 rowsdf.shape # (rows, columns)df.info() # column types and missing countsdf.describe() # summary statistics for numbersdf["region"].value_counts()Always answer three questions first: how many rows, what type is each column, and where is data missing.
πStep 3: Clean the Data
Fix column names
df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")Handle missing values
df.isna().sum() # missing per columndf["region"] = df["region"].fillna("Unknown") # fill text gapsdf = df.dropna(subset=["quantity", "unit_price"]) # drop rows missing key numbersDecide per column: fill with a sensible default, fill with a median, or drop the row. Write down why, because an interviewer will ask.
Fix data types
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")df["category"] = df["category"].astype("category")errors="coerce" turns bad values into missing values instead of crashing, so you can inspect them.
Remove duplicates and clean text
df = df.drop_duplicates(subset="order_id")df["product"] = df["product"].str.strip().str.title()Add calculated columns
df["revenue"] = df["quantity"] * df["unit_price"]df["month"] = df["order_date"].dt.to_period("M")πStep 4: Filter and Sort
south = df[df["region"] == "South"]big_orders = df[(df["revenue"] > 10000) & (df["category"] == "Electronics")]recent = df[df["order_date"] >= "2026-01-01"]top = df.sort_values("revenue", ascending=False).head(10)Use & for and, | for or, and wrap each condition in brackets. The query method is a readable alternative: df.query("revenue > 10000 and region == 'South'").
πStep 5: Group and Summarise
groupby is the heart of analysis. It is the pandas version of a pivot table or SQL GROUP BY.
by_region = df.groupby("region")["revenue"].sum().sort_values(ascending=False)summary = df.groupby("category").agg( orders=("order_id", "count"), revenue=("revenue", "sum"), avg_order=("revenue", "mean"),)πStep 6: Merge Data From Several Sources
Real analysis rarely uses one file. Suppose customers.csv has customer_id, city and segment.
customers = pd.read_csv("customers.csv")full = df.merge(customers, on="customer_id", how="left")how="left" keeps every order even if the customer is missing, which is usually what you want. After merging, check the row count did not change unexpectedly; duplicate keys in the second table are a classic bug.
πStep 7: Pivot Tables
pivot = pd.pivot_table( full, values="revenue", index="month", columns="region", aggfunc="sum", fill_value=0,)This gives one row per month and one column per region, exactly like an Excel pivot table, but repeatable.
πStep 8: Work With Dates
monthly = df.set_index("order_date").resample("ME")["revenue"].sum()df["weekday"] = df["order_date"].dt.day_name()growth = monthly.pct_change() * 100resample groups by time period, and pct_change gives month on month growth. Older pandas versions use "M" instead of "ME" for month end.
πStep 9: Visualise
import matplotlib.pyplot as pltmonthly.plot(kind="line", title="Monthly revenue")plt.ylabel("Revenue (βΉ)")plt.tight_layout()plt.show()by_region.plot(kind="bar", title="Revenue by region")plt.show()Keep charts simple: one message per chart, a clear title and labelled axes.
πStep 10: Export the Results
with pd.ExcelWriter("sales_report.xlsx") as writer: summary.to_excel(writer, sheet_name="By category") pivot.to_excel(writer, sheet_name="Monthly by region")by_region.to_csv("revenue_by_region.csv")Now the whole report rebuilds with one command whenever new data arrives.
πMini Case Study: Why Did Revenue Drop in March?
A manager asks why March revenue fell. Here is how you answer with pandas.
That is the real job of a data analyst: not just code, but a clear answer with evidence. Practise telling the story in three or four sentences.
πHandy pandas Techniques Worth Knowing
Apply a function to a column
For logic that has no built-in method, use map or apply, but prefer vectorised operations when possible.def size_band(revenue): if revenue >= 50000: return "Large" if revenue >= 10000: return "Medium" return "Small"df["order_size"] = df["revenue"].apply(size_band)Bucket numbers with cut
pd.cut groups numbers into bands without writing a function.df["price_band"] = pd.cut(df["unit_price"], bins=[0, 500, 2000, 10000, float("inf")], labels=["Budget", "Mid", "Premium", "Luxury"])Rank and find top items per group
A common interview task is "top 3 products in each region".top3 = (df.groupby(["region", "product"])["revenue"].sum() .reset_index() .sort_values(["region", "revenue"], ascending=[True, False]) .groupby("region") .head(3))Reshape between wide and long
melt turns wide tables into long ones, which is the format most charting tools and BI tools prefer.long = pivot.reset_index().melt(id_vars="month", var_name="region", value_name="revenue")Read large files in chunks
For files too big for memory, read and aggregate piece by piece.totals = []for chunk in pd.read_csv("big_sales.csv", chunksize=100_000): totals.append(chunk.groupby("region")["quantity"].sum())result = pd.concat(totals).groupby(level=0).sum()πpandas and SQL Side by Side
Most analyst interviews test both, and the concepts map closely:
If you already know SQL, learning pandas is mostly learning new names for familiar ideas.
πCommon pandas Mistakes
πWhere to Go Next
πFAQ
Is pandas better than Excel for data analysis?
For large, messy or repeating tasks, yes. Excel is still great for quick checks and sharing. Most analysts use both, and Python can write formatted Excel reports.How long does it take to learn pandas?
With basic Python, you can be productive in a few weeks of regular practice on real datasets. Depth comes from solving real business questions.Do data analyst jobs in India require Python?
Many do, especially at product companies and GCCs. SQL and Excel remain essential, with Python and pandas a strong differentiator.Should I use Jupyter notebooks or Python scripts?
Use notebooks for exploring data and presenting an analysis, because you see results and charts step by step. Once a cleaning or reporting process is settled and needs to run every week, move it into a Python script that can be scheduled. Most working analysts use both, and interviewers like to see that you know when to switch.What should I learn after pandas?
SQL, a BI tool such as Power BI or Tableau, statistics basics, and then machine learning if you want to move towards data science.πLearn It Live
Prakalpana's Data Analytics course teaches Python, pandas, SQL, Excel and Power BI live online on real business datasets, with an optional 1-on-1 track. New to Python? Start with the Python course or explore the Data Analytics track. WhatsApp or call +91 9180377502 for a free demo.