Data analysis with pandas — Python

In this lesson you will learn

Find missing values and deal with them using a suitable strategy (dropping or filling)
Build summary tables with groupby, named aggregation, transform and pivot_table
Join tables correctly with merge and predict the number of rows in the result
Work with dates and compute a monthly report and growth rates

Real data is never as tidy as in a textbook: some cells are empty, the information is spread across several tables, and dates are stored as text. An analyst's work has four steps: clean → combine → group → answer. In this lesson you will learn the pandas tools for each step and, at the end, analyse a quarter of sales of a café chain with two branches.

Missing values

pandas shows a missing value as NaN (Not a Number). isna().sum() counts the gaps in each column. count() counts only the filled values, while size counts all rows. Functions such as mean, sum and std skip NaN by default. Then we choose: dropna() removes the rows with gaps, and fillna() fills them — with a constant, the median or the mean.

Python· Edit the code and run it again

import numpy as np
import pandas as pd

df = pd.DataFrame({
    'student': ['Aysel', 'Murad', 'Leyla', 'Elvin', 'Nigar'],
    'group': ['A', 'B', 'A', 'B', 'A'],
    'math': [91, np.nan, 85, 64, np.nan],
    'physics': [88, 72, np.nan, 70, 81],
})
print(df.isna().sum())
print(df['math'].mean(), df['math'].count())
print(df.dropna().shape)
filled = df.fillna({'math': df['math'].median(), 'physics': df['physics'].mean()})
print(filled)
print(np.nan == np.nan)

▸ Expected output

student    0
group      0
math       2
physics    1
dtype: int64
80.0 3
(2, 4)
  student group  math  physics
0   Aysel     A  91.0    88.00
1   Murad     B  85.0    72.00
2   Leyla     A  85.0    77.75
3   Elvin     B  64.0    70.00
4   Nigar     A  85.0    81.00
False

The maths mean is 80 because only the 3 filled values are used. dropna() kept only 2 of the 5 rows — most of the information was lost.

Grouping: split, apply, combine

Definition

Split–apply–combine

groupby splits a table into groups by the values of a key column, applies a function (sum, mean, count) to each group and combines the results into a new table. It is the same idea as GROUP BY in SQL.

With agg we compute several statistics at once; named aggregation name=('column', 'function') gives the result columns clear names. Grouping by two keys creates a multi-level index (MultiIndex). transform returns the result to every row of the original table — for example, to find each sale's share of its branch's revenue.

Python· Edit the code and run it again

import pandas as pd

df = pd.DataFrame({
    'store': ['Baku', 'Baku', 'Ganja', 'Baku', 'Ganja', 'Ganja', 'Baku'],
    'category': ['Food', 'Drinks', 'Food', 'Food', 'Drinks', 'Food', 'Drinks'],
    'amount': [120.0, 45.5, 80.0, 99.5, 30.0, 64.0, 52.0],
})
print(df.groupby('store')['amount'].sum())
summary = df.groupby('store').agg(
    total=('amount', 'sum'),
    average=('amount', 'mean'),
    orders=('amount', 'size'),
)
print(summary.round(2))
print(df.groupby(['store', 'category'])['amount'].sum())
df['share'] = (df['amount'] / df.groupby('store')['amount'].transform('sum')).round(3)
print(df.head(3))

▸ Expected output

store
Baku     317.0
Ganja    174.0
Name: amount, dtype: float64
       total  average  orders
store                        
Baku   317.0    79.25       4
Ganja  174.0    58.00       3
store  category
Baku   Drinks       97.5
       Food        219.5
Ganja  Drinks       30.0
       Food        144.0
Name: amount, dtype: float64
   store category  amount  share
0   Baku     Food   120.0  0.379
1   Baku   Drinks    45.5  0.144
2  Ganja     Food    80.0  0.460

x̄_w = ∑ wᵢ · xᵢ / ∑ wᵢx̄_w = ∑ wᵢ · xᵢ / ∑ wᵢ

where:

xᵢthe mean of group i (for example, the average receipt)
wᵢthe weight of the group — usually the number of observations

The weighted mean. In pandas: np.average(x, weights=w), or simply divide the overall total by the overall count.

Example 1: the mean-of-means trap

The Baku branch has 300 receipts with an average of 8 manat; the Ganja branch has 100 receipts with an average of 12 manat. What is the average receipt for the whole chain?

Show solution

Wrong way: (8 + 12) / 2 = 10 manat — this gives both branches equal weight.
Right way: total revenue = 300 · 8 + 100 · 12 = 2400 + 1200 = 3600 manat over 400 receipts.
x̄_w = 3600 / 400 = 9 manat.
In pandas, do not take the mean of a groupby(...).mean() result — use mean() on the original rows or divide the totals.

Joining tables: merge

Orders are in one table and customers in another; we join them on a shared key column (customer_id). The how parameter decides what happens to rows whose key exists in only one table. indicator=True adds a _merge column that shows where each row came from.

how	Which keys stay in the result
`inner`	only those present in both tables (the default)
`left`	all keys of the left table; missing matches become `NaN`
`right`	all keys of the right table
`outer`	all keys of both tables

Python· Edit the code and run it again

import pandas as pd

orders = pd.DataFrame({'order_id': [1, 2, 3, 4],
                       'customer_id': [10, 11, 10, 13],
                       'amount': [25.0, 40.0, 15.5, 60.0]})
customers = pd.DataFrame({'customer_id': [10, 11, 12],
                          'name': ['Aysel', 'Murad', 'Leyla']})
inner = orders.merge(customers, on='customer_id')
print(inner)
left = orders.merge(customers, on='customer_id', how='left')
print(left[['order_id', 'name']])
outer = orders.merge(customers, on='customer_id', how='outer', indicator=True)
print(outer[['customer_id', 'order_id', 'name', '_merge']])

▸ Expected output

   order_id  customer_id  amount   name
0         1           10    25.0  Aysel
1         2           11    40.0  Murad
2         3           10    15.5  Aysel
   order_id   name
0         1  Aysel
1         2  Murad
2         3  Aysel
3         4    NaN
   customer_id  order_id   name      _merge
0           10       1.0  Aysel        both
1           10       3.0  Aysel        both
2           11       2.0  Murad        both
3           12       NaN  Leyla  right_only
4           13       4.0    NaN   left_only

Customer 13 is missing from the customers table, and Leyla (12) has no orders. In the outer result order_id became a float, because an integer column cannot hold NaN.

Example 2: how many rows will the result have?

a) For orders (4 rows) and customers (3 rows) above, predict the number of rows of the inner, left and outer joins. b) If a key appears 3 times in the left table and 2 times in the right one, how many rows does an inner join produce for that key?

Show solution

a) Key 10 → 2 orders · 1 customer = 2 rows, key 11 → 1 row: inner = 3.
left keeps all orders: 4 (the name is NaN for customer 13).
outer = 4 + Leyla (12) = 5.
b) Every left row pairs with every right row: 3 · 2 = 6 rows. This is the “many-to-many” explosion — the table grows unexpectedly and totals are counted twice.

pivot_table, map, apply and dates

pivot_table is the same as a PivotTable in Excel: index sets the rows, columns the columns, and values and aggfunc what is computed in the cells. fill_value=0 fills empty cells, and margins=True adds a total row and column called “All”.

Python· Edit the code and run it again

import pandas as pd

df = pd.DataFrame({
    'store': ['Baku', 'Baku', 'Ganja', 'Baku', 'Ganja', 'Ganja', 'Baku'],
    'category': ['Food', 'Drinks', 'Food', 'Food', 'Drinks', 'Food', 'Drinks'],
    'amount': [120.0, 45.5, 80.0, 99.5, 30.0, 64.0, 52.0],
})
pt = df.pivot_table(index='store', columns='category', values='amount',
                    aggfunc='sum', fill_value=0, margins=True)
print(pt)
print(df.pivot_table(index='store', columns='category', values='amount', aggfunc='count'))

▸ Expected output

category  Drinks   Food    All
store                         
Baku        97.5  219.5  317.0
Ganja       30.0  144.0  174.0
All        127.5  363.5  491.0
category  Drinks  Food
store                 
Baku           2     2
Ganja          1     2

There are three ways to transform a column: map replaces values using a dictionary, apply calls any function on each element, and pd.cut puts numbers into intervals (bins).

Python· Edit the code and run it again

import numpy as np
import pandas as pd

df = pd.DataFrame({'code': ['BAK', 'GNJ', 'SMQ', 'BAK'], 'amount': [120.0, 45.5, 80.0, 30.0]})
df['city'] = df['code'].map({'BAK': 'Baku', 'GNJ': 'Ganja', 'SMQ': 'Sumgait'})
df['size_apply'] = df['amount'].apply(lambda x: 'large' if x >= 60 else 'small')
df['size_where'] = np.where(df['amount'] >= 60, 'large', 'small')
df['band'] = pd.cut(df['amount'], bins=[0, 50, 100, 200], labels=['low', 'mid', 'high'])
print(df)
print((df['size_apply'] == df['size_where']).all())

▸ Expected output

  code  amount     city size_apply size_where  band
0  BAK   120.0     Baku      large      large  high
1  GNJ    45.5    Ganja      small      small   low
2  SMQ    80.0  Sumgait      large      large   mid
3  BAK    30.0     Baku      small      small   low
True

We convert dates stored as text into real dates with pd.to_datetime (or with read_csv(..., parse_dates=['date'])). Then .dt gives the year, month and weekday, and to_period('M') “rounds” a date to its month. The difference between two dates is a Timedelta.

Python· Edit the code and run it again

import pandas as pd

sales = pd.DataFrame({
    'date': ['2026-01-15', '2026-01-28', '2026-02-03', '2026-02-20', '2026-03-05', '2026-03-18'],
    'amount': [310.0, 275.5, 402.0, 350.0, 390.0, 441.5],
})
sales['date'] = pd.to_datetime(sales['date'])
sales['month'] = sales['date'].dt.to_period('M')
sales['weekday'] = sales['date'].dt.day_name()
print(sales)
print(sales.groupby('month')['amount'].sum())
print((sales['date'].max() - sales['date'].min()).days, 'days')

▸ Expected output

        date  amount    month    weekday
0 2026-01-15   310.0  2026-01   Thursday
1 2026-01-28   275.5  2026-01  Wednesday
2 2026-02-03   402.0  2026-02    Tuesday
3 2026-02-20   350.0  2026-02     Friday
4 2026-03-05   390.0  2026-03   Thursday
5 2026-03-18   441.5  2026-03  Wednesday
month
2026-01    585.5
2026-02    752.0
2026-03    831.5
Freq: M, Name: amount, dtype: float64
62 days

Mini analysis: a café chain's sales

A café chain has branches in Baku and Ganja. The sales log stores the date, branch, product code and quantity, and a separate table stores the product names and prices in manat (the data is invented). Management asks: how much revenue did each branch bring each month, how did the total change, and which product leads?

1
Read
Read the log with read_csv and convert the dates at once with parse_dates.
2
Join
Attach the products table with how='left' and validate='many_to_one'.
3
Compute
Revenue = quantity · price; extract the month from the date.
4
Summarise
Build a month × branch table with pivot_table, add the total and the growth rate, and rank the products by revenue.

Δ% = (xₜ − xₜ₋₁) / xₜ₋₁ · 100%Δ% = (xₜ − xₜ₋₁) / xₜ₋₁ · 100%

where:

xₜthe value in the current period
xₜ₋₁the value in the previous period

The growth rate; in pandas pct_change() computes it (without multiplying by 100). The first period has no previous value, so it gets NaN.

Python· Edit the code and run it again

import io
import pandas as pd

sales_csv = '''date,store,product_id,qty
2026-01-05,Baku,1,30
2026-01-12,Ganja,2,25
2026-01-20,Baku,3,12
2026-02-02,Baku,1,34
2026-02-14,Ganja,3,18
2026-02-25,Ganja,1,20
2026-03-03,Baku,2,41
2026-03-15,Ganja,2,28
2026-03-28,Baku,3,15'''
products = pd.DataFrame({'product_id': [1, 2, 3], 'product': ['Tea', 'Coffee', 'Cake'],
                         'price': [4.5, 6.0, 12.0]})
sales = pd.read_csv(io.StringIO(sales_csv), parse_dates=['date'])
df = sales.merge(products, on='product_id', how='left', validate='many_to_one')
df['revenue'] = df['qty'] * df['price']
df['month'] = df['date'].dt.month
report = df.pivot_table(index='month', columns='store', values='revenue',
                        aggfunc='sum', fill_value=0)
report['total'] = report.sum(axis=1)
report['growth_%'] = report['total'].pct_change().mul(100).round(1)
print(report)
print(df.groupby('product')['revenue'].sum().sort_values(ascending=False))

▸ Expected output

store   Baku  Ganja  total  growth_%
month                               
1      279.0  150.0  429.0       NaN
2      153.0  306.0  459.0       7.0
3      426.0  168.0  594.0      29.4
product
Coffee    564.0
Cake      540.0
Tea       378.0
Name: revenue, dtype: float64

Findings: over the quarter the total revenue grew from 429 to 594 manat, with 29.4% growth in March — mainly thanks to coffee and cake sales at the Baku branch. In February Ganja overtook Baku because cakes sold well there. The leading product of the quarter is coffee (564 manat). Tea is last in revenue (378 manat): 84 cups were sold, but at a low price, whereas just 45 cakes brought in 540 manat. One table and a few lines of code gave management a basis for concrete decisions.

Exercise

Using the expenses table, print on separate lines: 1) each person's total spending as a dictionary (to_dict()); 2) the category with the largest total; 3) the number of expenses per person as a dictionary.

Exercise · Python· Edit the code and run it again

import pandas as pd

expenses = pd.DataFrame({
    'person': ['Aysel', 'Murad', 'Aysel', 'Leyla', 'Murad', 'Aysel', 'Leyla'],
    'category': ['food', 'transport', 'books', 'food', 'food', 'food', 'books'],
    'amount': [12.5, 3.0, 25.0, 18.0, 9.5, 7.0, 14.0],
})
# 1) total per person (dict)
# 2) category with the largest total
# 3) number of expenses per person (dict)

▸ Expected output

{'Aysel': 44.5, 'Leyla': 32.0, 'Murad': 12.5}
food
{'Aysel': 3, 'Leyla': 2, 'Murad': 2}

Exercise

Join the orders with the customers using how='left' and fill the missing cities with 'Unknown'. Then print: 1) the total order amount per city as a dictionary; 2) Baku's share of the total amount in percent, rounded to 1 decimal place.

Exercise · Python· Edit the code and run it again

import pandas as pd

orders = pd.DataFrame({'order_id': [1, 2, 3, 4, 5], 'customer_id': [1, 2, 1, 3, 4],
                       'amount': [30.0, 45.0, 20.0, 15.0, 50.0]})
customers = pd.DataFrame({'customer_id': [1, 2, 3], 'city': ['Baku', 'Ganja', 'Baku']})
# merge, fill missing cities, then print the two results

▸ Expected output

{'Baku': 65.0, 'Ganja': 45.0, 'Unknown': 50.0}
40.6

Key points

isna().sum() counts gaps; dropna removes and fillna fills them; NaN == NaN is False.
groupby + agg implements split–apply–combine; transform returns the result to every row.
When combining group means, use the weighted mean: ∑ wᵢxᵢ / ∑ wᵢ.
For merge, how (inner, left, right, outer) shapes the result; duplicated keys multiply rows — check with validate.
pivot_table is Excel's PivotTable; prefer vectorized operations to apply whenever possible.
Convert dates with to_datetime or parse_dates, then analyse periods with .dt and pct_change.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

For s = pd.Series([4, np.nan, 8]), what are s.mean() and s.count()?