Python pandas Tutorial: DataFrames and GroupBy

Python with AI Tutorial · Chapter 38 of 48

Python pandas is the library for working with tables of data. Its DataFrame holds rows and columns like a spreadsheet, and with a few methods you can filter, sort, group and pivot thousands of rows in seconds. This chapter builds a small sales DataFrame and uses it to learn selection, filtering, new columns, groupby() and pivot_table().

Table of Contents

Install pandas and build a DataFrame

Install with pip and import it as pd. The quickest way to build a DataFrame by hand is a dictionary whose keys become column names. We reuse this sales table in the next three chapters.

pip install pandas

Output:
Successfully installed pandas-2.2.3 ...

import pandas as pd

sales = pd.DataFrame({
    "Region":  ["North", "South", "East", "North", "West", "South", "East", "West"],
    "Product": ["Dashboard", "Tracker", "Dashboard", "Calendar", "Tracker", "Dashboard", "Calendar", "Dashboard"],
    "Units":   [4, 10, 2, 15, 7, 3, 12, 5],
    "Revenue": [3996, 2990, 1998, 1485, 2093, 2997, 1188, 4995],
    "Date": pd.to_datetime(["2026-01-05", "2026-01-06", "2026-01-07", "2026-01-08",
                            "2026-02-02", "2026-02-03", "2026-02-04", "2026-02-05"]),
})
print(sales.head())

Output:
   Region    Product  Units  Revenue       Date
0   North  Dashboard      4     3996 2026-01-05
1   South    Tracker     10     2990 2026-01-06
2    East  Dashboard      2     1998 2026-01-07
3   North   Calendar     15     1485 2026-01-08
4    West    Tracker      7     2093 2026-02-02

head() shows the first five rows. The unlabelled column on the left is the index, which pandas numbers from 0 unless you set one.

Inspect the data

Before analysing, check the shape, the data types and a numeric summary. Wrong dtypes (numbers stored as text, dates stored as strings) are the most common cause of confusing results.

print(sales.shape)
print(sales.dtypes)
print(sales["Revenue"].describe().round(2))

Output:
(8, 5)
Region             object
Product            object
Units               int64
Revenue             int64
Date       datetime64[ns]
dtype: object
count       8.00
mean     2717.75
std      1295.05
min      1188.00
25%      1869.75
50%      2541.50
75%      3246.75
max      4995.00
Name: Revenue, dtype: float64

On pandas 3 the text columns show as str instead of object; everything else is the same. describe() uses the sample standard deviation, matching Excel’s STDEV.S.

Selecting columns and rows

Square brackets pick columns. loc selects by label and iloc by position, each taking [rows, columns].

print(sales["Revenue"].sum())
print(sales.loc[0, "Region"], sales.iloc[-1, 3])
print(sales.loc[sales["Region"] == "North", ["Product", "Revenue"]])

Output:
21742
North 4995
     Product  Revenue
0  Dashboard     3996
3   Calendar     1485

The mask sales["Region"] == "North" is a Series of True and False values, one per row. loc keeps the rows where it is True and returns only the two columns requested. The table below lists the selection tools you will use most often.

Method	Selects	Example
`df["col"]`	One column as a Series	`sales["Revenue"]`
`df[["a", "b"]]`	Several columns as a DataFrame	`sales[["Region", "Revenue"]]`
`df.loc[rows, cols]`	By label or Boolean mask	`sales.loc[sales["Units"] > 5, "Region"]`
`df.iloc[rows, cols]`	By integer position	`sales.iloc[0:3, 0:2]`
`df[mask]`	Rows where the mask is True	`sales[sales["Revenue"] > 2000]`
`df.query("expr")`	Rows matching a string expression	`sales.query("Units >= 10")`
`df.at[row, col]`	One scalar value, fast	`sales.at[0, "Revenue"]`
`df.head(n)` / `df.tail(n)`	First or last n rows	`sales.tail(2)`

Filtering with conditions

Combine conditions with & and |, wrapping each in parentheses. query() offers a readable alternative for simple filters.

big = sales[(sales["Revenue"] > 2000) & (sales["Product"] == "Dashboard")]
print(big[["Region", "Units", "Revenue"]])
print(sales.query("Units >= 10")["Region"].tolist())

Output:
   Region  Units  Revenue
0   North      4     3996
5   South      3     2997
7    West      5     4995
['South', 'North', 'East']

SettingWithCopyWarning: if you filter first (big = sales[mask]) and then assign to a column of big, pandas warns that the change may not stick. Either write big = sales[mask].copy() or assign directly with sales.loc[mask, "Flag"] = "High".

Adding columns and sorting

Assigning to a new column name creates it, and arithmetic between columns is vectorised. The .dt accessor exposes date parts for datetime columns.

sales["Price"] = sales["Revenue"] / sales["Units"]
sales["Month"] = sales["Date"].dt.strftime("%Y-%m")
top = sales.sort_values("Revenue", ascending=False).head(3)
print(top[["Region", "Product", "Price", "Revenue", "Month"]])

Output:
   Region    Product  Price  Revenue    Month
7    West  Dashboard  999.0     4995  2026-02
0   North  Dashboard  999.0     3996  2026-01
5   South  Dashboard  999.0     2997  2026-02

Sorting keeps the original index labels, which is why the rows are numbered 7, 0 and 5. Add .reset_index(drop=True) if you want them renumbered.

Try it with AI

Paste df.head() and describe the report you need; the assistant writes the pandas chain.

Here is my DataFrame's df.head() output:

   Region    Product  Units  Revenue       Date
0   North  Dashboard      4     3996 2026-01-05
1   South    Tracker     10     2990 2026-01-06
2    East  Dashboard      2     1998 2026-01-07

Date is already datetime64. Write pandas code that adds a Month column (YYYY-MM), returns monthly revenue by region as a pivot table with regions as rows, months as columns and zeros instead of NaN, and adds a Total column and a Total row. Explain each step in one line.

GroupBy: split, apply, combine

groupby() splits the rows by one or more keys, applies an aggregation to each group and combines the results. Named aggregation lets you compute several statistics at once with tidy column names.

print(sales.groupby("Region")["Revenue"].sum())

summary = sales.groupby("Product").agg(
    Units=("Units", "sum"),
    Revenue=("Revenue", "sum"),
    Orders=("Revenue", "count"),
)
print(summary)

Output:
Region
East     3186
North    5481
South    5987
West     7088
Name: Revenue, dtype: int64
           Units  Revenue  Orders
Product                          
Calendar      27     2673       2
Dashboard     14    13986       4
Tracker       17     5083       2

The group keys become the index of the result. Call .reset_index() to turn them back into an ordinary column, which is usually what you want before exporting to Excel.

Pivot tables

pivot_table() is the two-dimensional version of groupby, and it works exactly like a spreadsheet pivot: pick the rows, the columns, the value and the aggregation.

pivot = sales.pivot_table(index="Region", columns="Month", values="Revenue",
                          aggfunc="sum", fill_value=0)
print(pivot)

Output:
Month   2026-01  2026-02
Region                  
East       1998     1188
North      5481        0
South      2990     2997
West          0     7088

Without fill_value=0 the empty cells would show NaN, pandas’ marker for missing data. Add margins=True to append row and column totals. Because the result is an ordinary DataFrame, you can round it, sort it or send it straight to Excel with to_excel(), which the next chapter covers.

Try it with AI

Ask the assistant to explain a pandas result you did not expect.

I ran sales.groupby("Region")["Revenue"].mean() in pandas and got a Series indexed by Region, but when I then tried result["Revenue"] I got a KeyError. Explain the difference between a Series and a DataFrame in this situation, show two ways to get a DataFrame with Region and Revenue as ordinary columns (reset_index and as_index=False), and show how to round the mean to 2 decimals.

Common mistakes

Using and / or between two conditions; pandas needs & / | with parentheses.
Forgetting that sort_values(), dropna() and most methods return a new DataFrame. Assign the result or pass inplace=True.
Leaving dates as strings. Convert with pd.to_datetime() so .dt, sorting and resampling work.
Chained indexing such as sales[mask]["Flag"] = 1, which triggers SettingWithCopyWarning and may not modify anything.
Confusing loc (labels, inclusive end) with iloc (positions, exclusive end). sales.loc[0:2] returns three rows; sales.iloc[0:2] returns two.

Exercise

Using the sales DataFrame with the Price and Month columns added, print the average price per product and the total units sold per month.

Show answer

print(sales.groupby("Product")["Price"].mean())
print(sales.groupby("Month")["Units"].sum())

Output:
Product
Calendar      99.0
Dashboard    999.0
Tracker      299.0
Name: Price, dtype: float64
Month
2026-01    31
2026-02    27
Name: Units, dtype: int64

Related chapters

Python NumPy – the arrays underneath every pandas column.
Python pandas with Excel and CSV – load real files into a DataFrame and save results back.
Python Matplotlib – chart the groupby and pivot results.
Python with AI course hub – all 48 chapters in order.

FAQ

What is pandas used for in Python?

pandas is used to load, clean, transform and analyse tabular data. Its DataFrame handles filtering, sorting, grouping, pivoting, joining and time series, and it reads and writes CSV, Excel, JSON and SQL directly.

What is the difference between loc and iloc?

loc selects rows and columns by their labels and accepts Boolean masks, with an inclusive end in slices. iloc selects by integer position like a Python list, with an exclusive end.

How is groupby different from pivot_table?

groupby aggregates by one or more keys and returns the keys as the index in a long format. pivot_table spreads a second key across the columns to produce a wide, spreadsheet-style table and can fill missing cells and add totals.

Working with spreadsheets too? Ready-made Excel, Google Sheets and Power BI templates are at NextGenTemplates.com.

Chapter 38 of 48 · Python with AI: all 48 chapters