Skip to content
  • Official
  • Beginner
  • English
  • 9 steps · 2 quizzes

Pandas for Beginners: Analyze a Spreadsheet with Python

Pandas exercises for beginners: load a sales CSV as a DataFrame, filter, group, sort and chart it, side by side with Excel, with exercises you check.

  • Free
  • No account needed
  • Stop any time, pick up where you left off
Workshopy.io

WorkshopyOfficial

78 lessons · used by 2 teachers

For teachers

Teaching this? Run it live with your class.

  • Students join with a code — no accounts
  • See who’s done and who’s stuck, step by step
  • It’s your copy — change anything before class

Sign in to copy it to your workspace. It’s free.

Your path

9 steps · 2 quizzes · open any step, or read it here

  1. Start here: which region really sold the most?Start hereThis pandas for beginners lesson starts with a question every spreadsheet user meets: which region sold the most?InteractiveHands-on
    Read this step

    This pandas for beginners lesson starts with a question every spreadsheet user meets: which region sold the most? In Excel you would filter, build a pivot table and sort; in Python, pandas answers it in a few short lines that work the same on twelve rows or a hundred thousand.

    Interactive · Which region sold the most? From spreadsheet to answer — try it in the lesson

    You just watched a table of orders become an answer: a filter dimmed the small orders, each region's revenue stacked into one bar, and the bars sorted themselves. By the end of this lesson you will write the pandas code that does exactly that.

    What you'll do

    • Load a CSV of sales orders as a DataFrame and read it like a spreadsheet.
    • Filter rows, group and sum them, and sort the result.
    • Chart the answer with one line of code.
    • Practise with exercises you check as you go.

    By the end you can

    • Answer "how much, by what, and which is biggest" questions about a table with pandas.
    • Turn an Excel filter, SUMIFS or pivot table into pandas code, and read what pandas prints.

    What you need: a browser. A little Python helps (variables, lists, calling a function); nothing needs installing.

  2. Read a CSV: your spreadsheet as a DataFramepandas is a Python library for working with tables. Its table is called a DataFrame: rows and named columns, like one sheet in Excel. The whole lesson uses one small file, sales.csv: twelve orders from an imaginary shop (illustrative numbers).InteractiveHands-on
    Read this step

    pandas is a Python library for working with tables. Its table is called a DataFrame: rows and named columns, like one sheet in Excel. The whole lesson uses one small file, sales.csv: twelve orders from an imaginary shop (illustrative numbers).

    order_id,region,product,units,revenue
    1001,North,Laptop,2,2400
    1002,South,Monitor,5,1250
    1003,East,Laptop,1,1200
    1004,North,Mouse,10,250
    1005,West,Monitor,7,1750
    1006,South,Laptop,3,3600
    1007,East,Mouse,8,200
    1008,West,Laptop,4,4800
    1009,North,Monitor,9,2250
    1010,South,Mouse,6,150
    1011,East,Monitor,11,2750
    1012,West,Mouse,12,300
    

    Three lines read it and show the start:

    import pandas as pd
    
    df = pd.read_csv("sales.csv")
    df.head()
    

    pd is the short name everyone gives pandas. read_csv turns the file into a DataFrame, which we call df, and head() shows its first five rows:

       order_id region  product  units  revenue
    0      1001  North   Laptop      2     2400
    1      1002  South  Monitor      5     1250
    2      1003   East   Laptop      1     1200
    3      1004  North    Mouse     10      250
    4      1005   West  Monitor      7     1750
    

    Three differences from Excel matter from the start:

    • The file's first line became the column names. It is not a row of data.
    • Columns have names, not letters: you write df["region"], not "column B".
    • The numbers down the left are the index: labels pandas gave the rows, starting at 0.

    Try it: press the buttons, or click any cell on either side, and see where the same thing lives in the other view.

    Interactive · Excel and pandas, side by side — try it in the lesson

    Two more you will use all the time: df.shape prints (12, 5), twelve rows and five columns, and df.tail() shows the last five rows. In a notebook (Jupyter, Google Colab) the last line of a cell is shown by itself; in a .py script, wrap it in print().

    Takeaway: a DataFrame is your sheet, with column names instead of letters and an index instead of row numbers.

  3. Columns: pick the ones you need, add the ones you don't haveA sheet with fifty columns is hard to read. pandas lets you keep only the ones you need: One column: df["revenue"], single brackets. You get a Series: one column with its index. Several columns: df[["region", "revenue"]], double brackets.InteractiveHands-on
    Read this step

    A sheet with fifty columns is hard to read. pandas lets you keep only the ones you need:

    • One column: df["revenue"], single brackets. You get a Series: one column with its index.
    • Several columns: df[["region", "revenue"]], double brackets. The inner pair is a Python list of names, and you get a smaller DataFrame with the columns in the order you listed.

    In Excel, a new column is a formula in the first cell, filled down. In pandas, one line does every row at once:

    df["price"] = df["revenue"] / df["units"]
    

    pandas divides row by row: 2400 / 2 for the first order, 1250 / 5 for the second, and so on. The result prints as 1200.0 because / always gives decimals in Python.

    Try it: do the three tasks. Watch the code change with every click.

    Interactive · Pick columns and add one — try it in the lesson

    What happened when you picked two columns with single brackets? df["region", "revenue"] asks for ONE column whose name is the pair, and there is none: a KeyError.

    Takeaway: df["col"] is one column, df[["a", "b"]] is a smaller table, and df["new"] = … fills a whole column in one line.

  4. Filter rows with a conditionIn Excel you would click the filter arrow on the region column and tick South. In pandas you write the conditionInteractiveHands-on
    Read this step

    In Excel you would click the filter arrow on the region column and tick South. In pandas you write the condition:

    df[df["region"] == "South"]
    

    Read it from the inside out. df["region"] == "South" compares every row and gives one True or False per row: a mask. df[mask] keeps the rows where the mask is True.

       order_id region  product  units  revenue
    1      1002  South  Monitor      5     1250
    5      1006  South   Laptop      3     3600
    9      1010  South    Mouse      6      150
    

    The rules:

    • == compares; a single = would assign. Text goes in quotes, numbers don't.
    • !=, >, >=, < and <= mean what they mean in Excel.
    • Two conditions: & for "and", | for "or", and each condition in its own parentheses. Python's own and / or don't work on a whole column: pandas stops with "The truth value of a Series is ambiguous".
    • Several values of one column: df[df["region"].isin(["South", "West"])].
    • The rows you keep keep their index labels (1, 5, 9 above). pandas does not renumber them.

    Try it: build the three filters. Watch the mask: one True or False per row.

    Interactive · Build a filter and watch the mask — try it in the lesson

    The third filter in code:

    df[(df["product"] == "Mouse") & (df["units"] >= 10)]
    
        order_id region product  units  revenue
    3       1004  North   Mouse     10      250
    11      1012   West   Mouse     12      300
    

    Takeaway: a filter is a mask of True and False inside df[...]; join conditions with & or |, each in parentheses.

  5. Check: DataFrames, columns and filtersFour questions on the steps so far. Use sales.csv as in the lesson.Quiz · 4 questions
    Read the questions

    Four questions on the steps so far. Use sales.csv as in the lesson.

    Question 1. What does `df.shape` print for sales.csv?

    • (12, 5)
    • (5, 12)
    • (13, 5)
    • 60

    Question 2. Which line gives a DataFrame with only the region and revenue columns?

    • `df[["region", "revenue"]]`
    • `df["region", "revenue"]`
    • `df("region", "revenue")`
    • `df.columns("region", "revenue")`

    Question 3. In `df[df["revenue"] > 1000]`, what does the inner part `df["revenue"] > 1000` make?

    • One True or False for every row
    • A new DataFrame with only the big orders
    • The number of big orders
    • An error: you can't compare a whole column

    Question 4. The first order has index label 0 in pandas. Which Excel row holds it when you open sales.csv?

    • Row 2
    • Row 1
    • Row 0
    • Row 3
  6. Group and sum: a pivot table in one lineNow the question from the start: total revenue per region. In Excel you would build a PivotTable (region in Rows, Sum of revenue in Values) or write a SUMIFS for each region. In pandasInteractiveHands-on
    Read this step

    Now the question from the start: total revenue per region. In Excel you would build a PivotTable (region in Rows, Sum of revenue in Values) or write a SUMIFS for each region. In pandas:

    df.groupby("region")["revenue"].sum()
    

    Read it left to right: split the rows into groups by region, take each group's revenue, add it up.

    region
    East     4150
    North    4900
    South    5000
    West     6850
    Name: revenue, dtype: int64
    

    The answer is a Series: the group names became its index, and they come out A to Z, because groupby sorts its groups by name. To put the biggest first, chain one more move, .sort_values(ascending=False). To sum only some orders, put a filter in front. With one move per line, inside parentheses, it reads like a recipe:

    (df[df["revenue"] > 1000]
       .groupby("region")["revenue"]
       .sum()
       .sort_values(ascending=False))
    

    Try it: pick a filter and a column to group by, press Run, then switch the sort off and on. Each kept row's revenue becomes a block on its group's bar, so the bar's length is the sum.

    Interactive · Filter, group, sum and sort: the question in one chain — try it in the lesson

    Which region wins with all rows? Does the winner change when only orders over 1000 count? And which product brings in the most?

    sum() is one of several: .mean(), .count(), .max() and .min() work the same way, and .agg() gives several at once:

    df.groupby("region")["revenue"].agg(["sum", "mean", "count"])
    
             sum         mean  count
    region                          
    East    4150  1383.333333      3
    North   4900  1633.333333      3
    South   5000  1666.666667      3
    West    6850  2283.333333      3
    

    Takeaway: df.groupby("col")["value"].sum() is a pivot table in one line; filter before it, sort after it.

  7. Sort and keep the top rowsExcel's "Sort Largest to Smallest" is sortvaluesInteractiveHands-on
    Read this step

    Excel's "Sort Largest to Smallest" is sort_values:

    df.sort_values("revenue", ascending=False).head(3)
    
        order_id region  product  units  revenue
    7       1008   West   Laptop      4     4800
    5       1006  South   Laptop      3     3600
    10      1011   East  Monitor     11     2750
    
    • ascending=False puts the biggest first; leave it out for smallest first.
    • .head(3) keeps the first three rows of the result, so sort first, then cut: df.head(3).sort_values(...) would only sort the file's first three rows.
    • df.nlargest(3, "revenue") is a shortcut for the same top three.
    • Text sorts A to Z: df.sort_values("region"). Rows with the same value (three orders per region) can come out in any order between them; add kind="stable" to keep the file's order.
    • Each row keeps its index label, which is why the labels on the left jump around after a sort.

    Try it: three sorts, and watch the rows move.

    Interactive · Sort the orders and keep the top — try it in the lesson

    Takeaway: sort_values(column, ascending=False) then .head(n) gives you the top n.

  8. Chart the answerA sorted Series of totals is one line away from a chart. pandas draws it with matplotlib, the standard Python charting libraryInteractiveHands-on
    Read this step

    A sorted Series of totals is one line away from a chart. pandas draws it with matplotlib, the standard Python charting library:

    revenue = df.groupby("region")["revenue"].sum().sort_values(ascending=False)
    revenue.plot(kind="bar", title="Revenue by region")
    

    Each index label becomes a bar, each value its height. In Jupyter or Colab the chart appears under the cell; in a .py script, add import matplotlib.pyplot as plt at the top and plt.show() at the end. kind can be "bar", "barh" (horizontal), "pie" or "line" (for numbers over time).

    Try it: switch between the kinds and the two groupings, and turn the sort off. The chart below is drawn in your browser the way pandas would draw it.

    Interactive · One line from totals to a chart — try it in the lesson

    Notice what "barh" does with a list sorted biggest first: the first row goes at the bottom. For the biggest bar on top, sort with ascending=True before a horizontal chart.

    Takeaway: sort, then .plot(kind=...): a sorted chart answers "which is biggest" at a glance.

  9. The same questions in Excel and in pandasEverything so far, side by sideHands-on
    Read this step

    Everything so far, side by side:

    The question In Excel In pandas
    Open the data Open sales.csv df = pd.read_csv("sales.csv")
    How big is it? Scroll, or Ctrl+End df.shape
    Only some columns Hide the others df[["region", "revenue"]]
    A new column =E2/D2, filled down df["price"] = df["revenue"] / df["units"]
    Only some rows Filter arrow, or =FILTER(A2:E13, B2:B13="South") df[df["region"] == "South"]
    A total for one group =SUMIFS(E2:E13, B2:B13, "South") df[df["region"] == "South"]["revenue"].sum()
    Totals for every group PivotTable df.groupby("region")["revenue"].sum()
    Biggest first Sort Largest to Smallest .sort_values(ascending=False)
    The top 3 Sort, then read three rows .head(3) or df.nlargest(3, "revenue")
    A chart Insert, Chart .plot(kind="bar")

    The difference is not speed on twelve rows. It is that the pandas version is written down: run it again next month on a new file and you get the new answer, with nobody clicking through the same steps again.

    Run it yourself (optional)

    Open Google Colab, choose New notebook, paste this into a cell and press Run. The file sits inside the code, so there is nothing to upload.

    import pandas as pd
    from io import StringIO
    
    csv = """order_id,region,product,units,revenue
    1001,North,Laptop,2,2400
    1002,South,Monitor,5,1250
    1003,East,Laptop,1,1200
    1004,North,Mouse,10,250
    1005,West,Monitor,7,1750
    1006,South,Laptop,3,3600
    1007,East,Mouse,8,200
    1008,West,Laptop,4,4800
    1009,North,Monitor,9,2250
    1010,South,Mouse,6,150
    1011,East,Monitor,11,2750
    1012,West,Mouse,12,300"""
    
    df = pd.read_csv(StringIO(csv))
    print(df.groupby("region")["revenue"].sum().sort_values(ascending=False))
    

    Then add a line of your own that answers: how many mice were sold in all, across every order? (print(...) around it shows a clean number.)

    Check your work · Paste the number your line printed: the total units of all Mouse orders. — check it in the lesson

    Takeaway: every Excel move you know has a one-line pandas twin, and the pandas version can be run again on next month's file.

  10. Pandas exercises with solutionsSix exercises on sales.csv, the twelve orders from the lesson. Work each one out from the data, then check. A hint costs nothing.Practice · 6 exercises
    Read this step

    Six exercises on sales.csv, the twelve orders from the lesson. Work each one out from the data, then check. A hint costs nothing.

    How many rows does df[df["units"] >= 5] keep?

    What does df[df["region"] == "East"]["revenue"].sum() print?

    df.groupby("product")["units"].sum() prints one line per product. What number is on the Mouse line?

    What does df[df["product"] == "Laptop"]["units"].mean() print?

    Which line gives every region's total revenue, biggest first?

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

    Which line keeps the North orders with more than 3 units?

    • df[(df["region"] == "North") & (df["units"] > 3)]
    • df[df["region"] == "North" & df["units"] > 3]
    • df[(df["region"] == "North") and (df["units"] > 3)]
    • df[(df["region"] == "North") | (df["units"] > 3)]

Comments

You’ll sign in with Google before it’s posted.Comments are public — don’t name your students.
0 / 1,500

No comments yet. Be the first to say how it went.

Using it

Workshopy shares this lesson under CC BY 4.0. Anyone may use and change it, with credit to you. Anyone can learn it here, free. A copy in your workspace is yours to change and run live; your students never need an account.

Versions

  1. 1

    Version 1Current

    · First published

Copies are made from the current version. A copy you already have doesn’t change.

More from Workshopy

All 78 lessons
All 21 Data & AI lessons