Python for Data Analysis with pandas

pandas is the most widely used Python library for working with tables of data. Think of it as a spreadsheet you control with code: every step is written down, so you can rerun the whole analysis on next month's file in seconds, share it, and handle millions of rows that would freeze Excel. You don't need to be a programmer to use it. In this lesson you'll repeat what you did in Lessons 5 to 9 (cleaning, statistics, pivots and joins) on the practice data, with code you can copy and run.

1. Load
2. Inspect
3. Clean
4. Analyze
5. Chart and export

Set up in two minutes

The easiest start is Google Colab: a free notebook that runs Python in your browser with pandas already installed. Sign in with a Google account, create a new notebook, click the folder icon on the left and upload the two CSV files. If you prefer working offline, install the free Anaconda distribution and use Jupyter Notebook, which works the same way.

A notebook is made of cells. Type code in a cell and press Shift+Enter to run it; the output appears right below. The code in this lesson was tested with pandas 3.0 and also works with pandas 2.x.

Step 1: Load and inspect

import pandas as pd

df = pd.read_csv("sales-practice-data.csv")
print(df.shape)
df.head(3)

(203, 10)

df is a DataFrame, pandas' name for a table. shape gives (rows, columns): 203 rows, 10 columns. The numbers in the first column (shown in bold in a notebook) are the index, pandas' row labels. Notice that revenue shows decimals (498.0): pandas stores a numeric column with missing values as decimals, a first hint that something is blank.

Now profile it, exactly like Lesson 5:

print(df.isna().sum()["revenue"])   # blank revenues
print(df.duplicated().sum())        # fully duplicated rows
print(df["state"].nunique())        # distinct state spellings

4
3
24

Four missing revenues, three duplicate rows and 24 state spellings for 17 real states. df.info() and df.describe() are two more inspection commands to run on every new dataset: the first lists column types and non-missing counts, the second gives summary statistics for numeric columns.

Step 2: Clean in five lines

df = df.drop_duplicates().reset_index(drop=True)
df["order_date"] = pd.to_datetime(df["order_date"], format="mixed")
state_fix = {"CA": "California", "TX": "Texas", "NY": "New York",
             "N. Carolina": "North Carolina"}
df["state"] = df["state"].str.strip().replace(state_fix).str.title()
df["revenue"] = df["revenue"].fillna(df["units"] * df["unit_price"]).astype(int)

print(df.shape)
print(df["state"].nunique())
print(df["revenue"].sum())

(200, 10)
17
89735

Line by line: drop exact duplicates and renumber the index; convert dates, allowing mixed formats (the one "10/15/2025"); trim spaces, map abbreviations and capitalize state names; fill missing revenue with units × price and store it as whole numbers. The result matches the clean answer key exactly: 200 orders, 17 states, $89,735. Next month, the same five lines clean the new file instantly. That's the real advantage over doing it by hand.

Step 3: Describe and group

print(df["revenue"].describe().round(2))

count     200.00
mean      448.68
std       794.93
min        35.00
25%        79.00
50%       138.00
75%       398.00
max      5229.00
Name: revenue, dtype: float64

One line gives the statistics from Lesson 6: mean $448.68, median $138, standard deviation $794.93 (pandas uses the sample version, like STDEV.S). Now group by region, the pandas version of a pivot table or GROUP BY:

by_region = (df.groupby("region")
               .agg(orders=("order_id", "count"),
                    revenue=("revenue", "sum"),
                    avg_order=("revenue", "mean"))
               .sort_values("revenue", ascending=False)
               .round(2))
print(by_region)

           orders  revenue  avg_order
region
West           64    36626     572.28
Northeast      45    20786     461.91
South          59    18684     316.68
Midwest        32    13639     426.22

Each item in agg reads as new_column=("source column", "function"). The same numbers you got with SUMIFS and SQL, which is exactly the point: three tools, one answer.

Step 4: Filter rows

west_wh = df[(df["region"] == "West") & (df["channel"] == "Wholesale")]
print(len(west_wh))
print(west_wh.nlargest(3, "revenue")[["order_id", "state", "product", "revenue"]])

14
     order_id       state       product  revenue
185  SO-10186     Arizona  Office Chair     5229
45   SO-10046  Washington   Smart Watch     4776
47   SO-10048  California   Smart Watch     4378

Inside the brackets goes a True/False condition for each row. Combine conditions with & (and) or | (or), and wrap each condition in parentheses. The numbers on the left are the rows' index labels, kept from the full table.

Step 5: Pivot tables and dates

pivot = pd.pivot_table(df, index="region", columns="channel",
                       values="revenue", aggfunc="sum",
                       margins=True, margins_name="Total")
print(pivot)

channel    Online  Retail  Wholesale  Total
region
Midwest      2429    1743       9467  13639
Northeast    3505    3253      14028  20786
South        4334    4497       9853  18684
West         5593    2902      28131  36626
Total       15861   12395      61479  89735

The same cross-tab you built by dragging fields in Lesson 7. Because order_date is now a real date, the .dt accessor extracts parts of it:

df["month"] = df["order_date"].dt.month
monthly = df.groupby("month")["revenue"].sum()
print(monthly.idxmax(), monthly.max())

12 18319

December (month 12) was the best month, with $18,319.

Step 6: Merge tables (the JOIN)

products = pd.read_csv("sales-practice-products.csv")
df = df.merge(products[["product", "unit_cost"]], on="product", how="left")
df["gross_profit"] = df["units"] * (df["unit_price"] - df["unit_cost"])
print(df["gross_profit"].sum())
print(round(df["gross_profit"].sum() / df["revenue"].sum() * 100, 1))

42514
47.4

merge is pandas' JOIN: on is the key and how is the join type ("inner", "left", "right" or "outer"). Gross profit is $42,514, a 47.4% margin, matching the SQL and XLOOKUP results. After a merge, always check that the row count didn't change unexpectedly: len(df) should still be 200.

Step 7: Chart and export

ax = monthly.plot(kind="line", marker="o", title="Revenue by month, 2025")
ax.set_xlabel("Month")
ax.set_ylabel("Revenue ($)")
ax.figure.savefig("monthly_revenue.png", dpi=150, bbox_inches="tight")

df.to_csv("sales-clean-from-python.csv", index=False)

pandas draws charts with matplotlib, which Colab and Anaconda include. savefig saves the chart as an image for slides; to_csv writes the cleaned, enriched table for Power BI, Looker Studio or a colleague. For a quick look at another breakdown, a one-liner is enough:

print(df.groupby("channel")["revenue"].sum().sort_values(ascending=False))

Excel, SQL and pandas side by side

Common pandas mistakes

by_cat = df.groupby("category")["revenue"].sum().sort_values(ascending=False)
share = (by_cat / by_cat.sum() * 100).round(1)
print(pd.DataFrame({"revenue": by_cat, "share_pct": share}))

                revenue  share_pct
category
Electronics       44068       49.1
Home & Kitchen    17875       19.9
Office            15507       17.3
Fitness           12285       13.7

Summary

• pandas is a code-based spreadsheet: reproducible, scalable and free in Google Colab or Jupyter.
• Inspect every dataset with shape, head, info, isna().sum() and duplicated().sum().
• Five lines clean the raw practice file into 200 orders and $89,735, matching the answer key.
• groupby/agg, pivot_table and merge are pandas' GROUP BY, pivot table and JOIN.
• Filter with &/| and parentheses; modify with .loc; assign results back.
• Chart with .plot() and export with to_csv for dashboards and reports.

Next lesson: Portfolio Project: Analyze a Business End to End. You'll put every skill together in a complete project you can show employers.