Data Analysis with pandas: From Excel to Python

pandas is the go-to library for analyzing data with Python. It does what Excel does (filters, sorting, pivot tables, XLOOKUP) but with millions of rows, automatically and repeatably: write the analysis once and rerun it every month. If you've taken our spreadsheets for data analysis lesson, you'll recognize every concept; if not, that's fine, we start from scratch.

1. Load the CSV or Excel file
2. Explore with info()
3. Filter and clean
4. Summarize with groupby
5. Export and chart

If you're coming from Excel, here's the "translation guide" you'll use all lesson long:

Setup

pip install pandas openpyxl matplotlib

Install them inside your virtual environment, as you learned in lesson 10. openpyxl lets pandas read and write Excel files, and matplotlib draws charts. Google Colab already has all three.

To practice, this code creates the sample sales file: first-quarter orders for a small Portland coffee roaster that sells coffee, tea and chocolate to cafés through three reps:

import pandas as pd

data = {
    "date": ["2026-01-05", "2026-01-12", "2026-01-20", "2026-02-03", "2026-02-15",
             "2026-02-21", "2026-03-02", "2026-03-10", "2026-03-18", "2026-03-25"],
    "region": ["West", "East", "West", "Midwest", "East", "West", "Midwest", "East", "West", None],
    "rep": ["Emily", "Jake", "Emily", "Maria", "Jake", "Emily", "Maria", "Jake", "Emily", "Maria"],
    "product": ["Coffee", "Tea", "Coffee", "Coffee", "Tea", "Chocolate", "Coffee", "Tea", "Chocolate", "Coffee"],
    "units": [120, 85, 100, 60, 110, 40, 75, 90, 55, 70],
    "price": [14.0, 9.5, 14.0, 14.0, 9.5, 16.0, 14.0, 9.5, 16.0, 14.0],
}
pd.DataFrame(data).to_csv("sales.csv", index=False)

DataFrames and Series

A DataFrame is a table: named rows and columns, like a worksheet. Each column is a Series.

df = pd.read_csv("sales.csv", parse_dates=["date"])
# For Excel: pd.read_excel("sales.xlsx", sheet_name="Data")

print(df.head())        # first 5 rows
print(df.shape)         # (10, 6): rows and columns
df.info()               # columns, types and non-null values
print(df.describe())    # statistics for numeric columns

Exploring first is rule number one. Here's the key part of what info() shows:

 #   Column   Non-Null Count  Dtype
---  ------   --------------  -----
 0   date     10 non-null     datetime64[us]
 1   region   9 non-null      str
 2   rep      10 non-null     str
 3   product  10 non-null     str
 4   units    10 non-null     int64
 5   price    10 non-null     float64

The region column has 9 values for 10 rows: one is missing. We'll fix that in a moment.

Selecting and filtering

df["rep"]                              # one column
df[["rep", "units"]]                   # several columns (double brackets)

df[df["region"] == "West"]                                  # filter rows
df[(df["region"] == "West") & (df["units"] > 100)]         # AND
df[(df["product"] == "Tea") | (df["product"] == "Chocolate")] # OR
df[df["product"].isin(["Tea", "Chocolate"])]                # same thing, shorter

df.sort_values("units", ascending=False).head(3)            # top 3

Each condition goes in parentheses, and you combine them with & (and) and | (or). It's the same logic as Excel's FILTER function.

Creating columns and cleaning data

df["revenue"] = df["units"] * df["price"]           # calculated column
df["month"] = df["date"].dt.to_period("M")          # 2026-01, 2026-02...

df["region"] = df["region"].fillna("Unknown")       # fill blanks
df["rep"] = df["rep"].str.strip().str.title()       # clean text
df = df.drop_duplicates()                           # remove duplicate rows

Operations apply to the whole column at once, with no loops. That's much faster and easier to read than walking through rows with for, and it's the biggest mindset shift from lesson 5.

Summarizing: groupby and pivot tables

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

df.groupby("region").agg(
    sales=("revenue", "sum"),
    orders=("revenue", "count"),
    avg_order=("revenue", "mean"),
)

pd.pivot_table(df, values="revenue", index="product",
               columns="month", aggfunc="sum", fill_value=0)

rep
Emily    4600.0
Maria    2870.0
Jake     2707.5
Name: revenue, dtype: float64

groupby is pandas' SUMIFS; pivot_table is the PivotTable. The first result shows Emily sold $4,600, Maria $2,870 and Jake $2,707.50. Group by product instead (df.groupby("product")["revenue"].sum()) and you'll see where the $10,177.50 total comes from:

Coffee $5,950, tea $2,707.50 and chocolate $1,520: more than half the business depends on a single product, which is key information before negotiating with suppliers.

Joining tables: pandas' XLOOKUP

targets = pd.DataFrame({"rep": ["Emily", "Jake", "Maria"],
                        "target": [4000, 3000, 2500]})

summary = df.groupby("rep", as_index=False)["revenue"].sum()
summary = summary.merge(targets, on="rep", how="left")
summary["attainment"] = (summary["revenue"] / summary["target"]).round(3)
print(summary)

     rep  revenue  target  attainment
0  Emily   4600.0    4000       1.150
1   Jake   2707.5    3000       0.902
2  Maria   2870.0    2500       1.148

merge joins on a shared column. With how="left", every row of the left table is kept even if it finds no match (like an XLOOKUP that returns blank). Emily hit 115% of her quarterly target, Maria 114.8%, and Jake fell short at 90.2%.

Exporting and charting

summary.to_excel("sales_summary.xlsx", index=False)    # to Excel
summary.to_csv("sales_summary.csv", index=False)       # to CSV

import matplotlib.pyplot as plt

by_month = df.groupby("month")["revenue"].sum()
by_month.plot(kind="bar", title="Sales by month")
plt.ylabel("Revenue ($)")
plt.tight_layout()
plt.savefig("sales_by_month.png")    # or plt.show() to see it on screen

You'll go much deeper into charts in lesson 14. For now, notice how little code it takes to go from raw CSV to an Excel file and an image you can drop into an email.

Real case: a messy e-commerce export

An online candle shop in Asheville, North Carolina, exports its March orders from its store platform. The amounts come as text with dollar signs and commas ("$1,250.50"), the dates are in U.S. month/day/year format, and one channel was typed two ways ("Instagram" and "instagram "). It's the kind of file you'll get from Shopify, Square or Etsy every week, and pandas handles it with the right steps. To keep the example self-contained, the CSV lives inside the code via io.StringIO; with a real file you'd pass its name instead.

import io
import pandas as pd

# March orders from an online candle shop in Asheville, NC, exported from its store platform
csv_export = """order,date,channel,amount
1001,03/03/2026,Instagram,"$1,250.50"
1002,03/03/2026,Website,$389.90
1003,03/04/2026,Etsy,$720.00
1004,03/05/2026,Instagram,"$2,100.00"
1005,03/05/2026,Website,"$1,045.30"
1006,03/06/2026,instagram ,$560.00
"""
df = pd.read_csv(io.StringIO(csv_export))
df["amount"] = df["amount"].str.replace("[$,]", "", regex=True).astype(float)
df["date"] = pd.to_datetime(df["date"], format="%m/%d/%Y")
df["channel"] = df["channel"].str.strip().str.lower()

summary = df.groupby("channel")["amount"].agg(["count", "sum", "mean"]).round(2)
summary = summary.sort_values("sum", ascending=False)
print(summary)
print(f"Total: ${df['amount'].sum():,.2f}")
print(f"Instagram: {summary.loc['instagram', 'sum'] / df['amount'].sum():.1%} of sales")

Output:

           count     sum    mean
channel
instagram      3  3910.5  1303.5
website        2  1435.2   717.6
etsy           1   720.0   720.0
Total: $6,065.70
Instagram: 64.5% of sales

• .str.replace("[$,]", "", regex=True) removes every dollar sign and comma in one pass (the brackets mean "either of these characters"), and .astype(float) turns "1250.50" into a number. Without it, astype(float) fails with ValueError: could not convert string to float: '$1,250.50'.
• format="%m/%d/%Y" makes the month/day order explicit, so "03/04/2026" is always March 4, never April 3.
• Clean before grouping: without .str.strip().str.lower(), "Instagram" and "instagram " would be two channels and the report would be split.

The business takeaway: Instagram brings in 64.5% of sales and the highest average order ($1,303.50), so it deserves the most attention.

Common pandas mistakes

pandas errors are long because they include the library's internal code. If you ask for a column that doesn't exist, you'll see dozens of lines and, halfway through, The above exception was the direct cause of the following exception. The useful information is in two places:

Traceback (most recent call last):
  File "report.py", line 3, in <module>
    print(df["Region"])
          ~~^^^^^^^^^^
  ...
KeyError: 'Region'

The first line that mentions your file (report.py, line 3) tells you where the problem is; the last line tells you what happened: there's no 'Region' column (it's region, lowercase). Skip the lines in between that point into pandas/core/….

Checklist before sending a pandas report

• I didn't modify the original file: the script reads it and writes results to new files.
• I checked df.info(): no numeric column shows up as text, and I know how many blanks there are.
• I normalized the text in every column I group by (spaces, capitalization).
• The report's total matches the sum of the original column (df["revenue"].sum()).
• Next month I can rerun the script on the new file and get the report without touching anything by hand.

import pandas as pd

df = pd.read_csv("sales.csv", parse_dates=["date"])
df["region"] = df["region"].fillna("Unknown")
df["revenue"] = df["units"] * df["price"]
df["month"] = df["date"].dt.to_period("M")

by_region = df.groupby("region")["revenue"].sum().sort_values(ascending=False)
by_product = df.groupby("product")["revenue"].sum().sort_values(ascending=False)
best_month = df.groupby("month")["revenue"].sum().idxmax()
best_avg = df.groupby("rep")["revenue"].mean().idxmax()

print(f"Best month: {best_month} · Highest average order: {best_avg}")

with pd.ExcelWriter("report.xlsx") as writer:
    by_region.to_excel(writer, sheet_name="Region")
    by_product.to_excel(writer, sheet_name="Product")

ax = by_region.plot(kind="barh", title="Sales by region")
ax.figure.tight_layout()
ax.figure.savefig("sales_by_region.png")

Summary

• pd.read_csv and pd.read_excel load data; head, info and describe explore it.
• Filter with conditions in parentheses joined by & and |; create columns by operating on whole columns.
• groupby and pivot_table summarize; merge joins tables like XLOOKUP.
• to_excel, to_csv and plot share the results.
• Clean real exports before analyzing: strip symbols, set date formats and normalize text.

Next lesson: Python APIs and Automation: requests, Files and Reports. You'll connect your programs to the outside world with web APIs and JSON, and automate everyday tasks.