Summaries and missing values

In short: Aggregations in NumPy (sum, mean, max, argmax) make one number from many, axis says which dimension disappears, and NaN marks a missing value.

Problem

“What do we sell the most?”

The till sends a report: how many units of four products we sold each week, for the last six weeks. Each row is a week, each column is a product:

Pythondata: the shop tables

import numpy as np

names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans'])
sales = np.array([
    [42, 30, 25, 6],
    [38, 34, 28, 4],
    [45, 29, 31, 7],
    [40, 36, 22, 5],
    [51, 33, 27, 9],
    [47, 31, 26, 8],
])
print(sales.shape)
sales

import numpy as np names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans']) sales = np.array([ [42, 30, 25, 6], [38, 34, 28, 4], [45, 29, 31, 7], [40, 36, 22, 5], [51, 33, 27, 9], [47, 31, 26, 8], ]) print(sales.shape) sales

▶ RunRestore the code

The manager asks three things: how much we sold in total, which product does best, and which week was the strongest. Then they also send the takings by day, with one day missing.

Each answer is one number (or one per product), computed from many numbers. This is called an aggregation.

The idea

One number from many: sum, mean, min, max

An array has methods that summarise all its values: sum() for the total, mean() for the mean, min() and max() for the smallest and the largest. Here they are on the prices of the 25 products. to_numpy() takes the column as a NumPy array:

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
prices = products['price'].to_numpy()
print(prices.sum())
print(prices.mean())
print(prices.min(), prices.max())
print(round(prices.sum(), 2), round(prices.mean(), 2))

import pandas as pd products = pd.read_csv('products.csv') prices = products['price'].to_numpy() print(prices.sum()) print(prices.mean()) print(prices.min(), prices.max()) print(round(prices.sum(), 2), round(prices.mean(), 2))

▶ RunRestore the code

The sum of the prices comes out as 175.89999999999998, not 175.9. The computer stores decimals in binary and 0.1 cannot be written exactly, so tiny errors add up. They are not errors in the data.

For a report you round: round(x, 2) gives 2 decimal places. The mean price becomes €7.04, as in describe() from the first look.

More precisely, if you are curious

round is Python’s function and works on one number. An array has the method .round(2) (or np.round(arr, 2)), which rounds every number in it. Round at the end, for display: if you round halfway through, the rounding error goes into the next steps.

The idea

Which, not how much: argmax and argmin

max() tells you how much the most expensive product costs. But the manager asks which one it is. argmax() gives the position of the largest value, and argmin() that of the smallest. With the position you take the name from the array of names:

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
prices = products['price'].to_numpy()
names = products['name'].to_numpy()
i = prices.argmax()
print(i, names[i], prices[i])
j = prices.argmin()
print(j, names[j], prices[j])

import pandas as pd products = pd.read_csv('products.csv') prices = products['price'].to_numpy() names = products['name'].to_numpy() i = prices.argmax() print(i, names[i], prices[i]) j = prices.argmin() print(j, names[j], prices[j])

▶ RunRestore the code

The most expensive is the knife set at position 22 (€39), and the cheapest is the butter roll at position 2 (€0.80). Positions start at 0, as with lists.

1. prices.max() returns 39.0 and prices.argmax() returns 22. What is 22?

How many products are cheaper than the most expensive oneThe position of the most expensive product in the arrayThe price of the second most expensive product

Check

More precisely, if you are curious

If the largest value appears several times, argmax gives its first position. In the till report, weeks 1 and 4 have 103 units each, and argmin over the weeks gives only week 1. If ties matter, check them with a mask: week_totals == week_totals.min().

See it

Along which dimension: axis

sales.sum() adds up all 24 cells: 654 units in six weeks. For “which product” you need a total for each column, and for “which week” a total for each row. The axis parameter says which:

Pythondata: the shop tables

import numpy as np

names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans'])
sales = np.array([
    [42, 30, 25, 6],
    [38, 34, 28, 4],
    [45, 29, 31, 7],
    [40, 36, 22, 5],
    [51, 33, 27, 9],
    [47, 31, 26, 8],
])
print(sales.sum())
print(sales.sum(axis=0))
print(sales.sum(axis=1))
print(sales.shape, sales.sum(axis=0).shape, sales.sum(axis=1).shape)

import numpy as np names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans']) sales = np.array([ [42, 30, 25, 6], [38, 34, 28, 4], [45, 29, 31, 7], [40, 36, 22, 5], [51, 33, 27, 9], [47, 31, 26, 8], ]) print(sales.sum()) print(sales.sum(axis=0)) print(sales.sum(axis=1)) print(sales.shape, sales.sum(axis=0).shape, sales.sum(axis=1).shape)

▶ RunRestore the code

• axis=0 is the dimension of the rows (the weeks). The weeks merge and one number per product remains: bread is 263 units, coffee 39.
• axis=1 is the dimension of the columns (the products). The products merge and one number per week remains: from 103 to 120.

Everyone gets confused here, because “axis=0” sounds like “by rows”, yet it gives a result by column. The rule that does not lie: axis is the dimension that disappears. The shape (6, 4) with axis=0 loses the 6 and becomes (4,), and with axis=1 it loses the 4 and becomes (6,).

The same goes for mean, min, max, argmax and the rest. The mean per week for each product, rounded to one decimal place:

Pythondata: the shop tables

import numpy as np

names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans'])
sales = np.array([
    [42, 30, 25, 6],
    [38, 34, 28, 4],
    [45, 29, 31, 7],
    [40, 36, 22, 5],
    [51, 33, 27, 9],
    [47, 31, 26, 8],
])
print(sales.mean(axis=0))
sales.mean(axis=0).round(1)

import numpy as np names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans']) sales = np.array([ [42, 30, 25, 6], [38, 34, 28, 4], [45, 29, 31, 7], [40, 36, 22, 5], [51, 33, 27, 9], [47, 31, 26, 8], ]) print(sales.mean(axis=0)) sales.mean(axis=0).round(1)

▶ RunRestore the code

Try it

The strongest week

Which week sold the most units in total? Put the total of each week in week_totals, and its number (1 to 6) in best_week.

Pythondata: the shop tables

import numpy as np

names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans'])
sales = np.array([
    [42, 30, 25, 6],
    [38, 34, 28, 4],
    [45, 29, 31, 7],
    [40, 36, 22, 5],
    [51, 33, 27, 9],
    [47, 31, 26, 8],
])
week_totals = ...
best_week = ...

import numpy as np names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans']) sales = np.array([ [42, 30, 25, 6], [38, 34, 28, 4], [45, 29, 31, 7], [40, 36, 22, 5], [51, 33, 27, 9], [47, 31, 26, 8], ]) week_totals = ... best_week = ...

Check▶ Run

The idea

How much so far: cumsum

Sometimes the manager asks not “how much this week” but “how much from the start up to this week”. That is the running total: cumsum() adds the numbers one by one and keeps every partial sum:

Pythondata: the shop tables

import numpy as np

names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans'])
sales = np.array([
    [42, 30, 25, 6],
    [38, 34, 28, 4],
    [45, 29, 31, 7],
    [40, 36, 22, 5],
    [51, 33, 27, 9],
    [47, 31, 26, 8],
])
week_totals = sales.sum(axis=1)
print(week_totals)
week_totals.cumsum()

import numpy as np names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans']) sales = np.array([ [42, 30, 25, 6], [38, 34, 28, 4], [45, 29, 31, 7], [40, 36, 22, 5], [51, 33, 27, 9], [47, 31, 26, 8], ]) week_totals = sales.sum(axis=1) print(week_totals) week_totals.cumsum()

▶ RunRestore the code

After the third week we have sold 319 units, and the last number, 654, is the grand total from sales.sum(). The running total is the line that grows on “sales year to date” charts.

The idea

One missing day: NaN

Now the takings by day for the last week. On Wednesday the till did not send a report, so Wednesday’s place holds np.nan: the value for “missing” you saw in the first look. Here is what happens to the mean:

Pythondata: the shop tables

import numpy as np

# Takings by day, Monday to Sunday. The Wednesday report is missing.
revenue = np.array([412.5, 388.0, np.nan, 455.2, 501.3, 620.8, 590.4])
print(revenue.mean())
print(np.isnan(revenue).sum())
print(round(np.nanmean(revenue), 2))
print(round(np.nansum(revenue), 2))

import numpy as np # Takings by day, Monday to Sunday. The Wednesday report is missing. revenue = np.array([412.5, 388.0, np.nan, 455.2, 501.3, 620.8, 590.4]) print(revenue.mean()) print(np.isnan(revenue).sum()) print(round(np.nanmean(revenue), 2)) print(round(np.nansum(revenue), 2))

▶ RunRestore the code

The ordinary mean is nan. Any arithmetic with NaN gives NaN: what is 412.5 plus “I don’t know”? I don’t know. NumPy raises no error; it quietly returns nan, and in a long report it goes unnoticed.

np.isnan(revenue) is a mask that says where values are missing, and .sum() on it counts them: 1 day. np.nanmean and np.nansum skip NaN: the mean takings of the six known days are €494.70, and their sum is €2,968.20. That sum is “what we know so far”, not the takings for the whole week: Wednesday is not in it.

See it

Why not 0

The temptation is to replace the missing value with 0 and move on. np.nan_to_num does exactly that:

Pythondata: the shop tables

import numpy as np

revenue = np.array([412.5, 388.0, np.nan, 455.2, 501.3, 620.8, 590.4])
filled = np.nan_to_num(revenue)
print(filled)
print(round(filled.mean(), 2))
print(round(np.nanmean(revenue), 2))

import numpy as np revenue = np.array([412.5, 388.0, np.nan, 455.2, 501.3, 620.8, 590.4]) filled = np.nan_to_num(revenue) print(filled) print(round(filled.mean(), 2)) print(round(np.nanmean(revenue), 2))

▶ RunRestore the code

The mean drops from €494.70 to €424.03. The zero says “we sold nothing on Wednesday”, and that is not true: the shop was open, only the report is missing. 0 is not “missing”. Zero is a value, and NaN is the absence of a value.

0 is the right replacement only when it really means zero. If the till sends no line for a product nobody bought, then the gap is zero sales. The meaning of the data decides this, not the code.

Try it

A week without a count

The till did not send how many packs of coffee we sold in the third week. Put in avg_coffee how many packs we sell per week on average.

Pythondata: the shop tables

import numpy as np

# Packs of coffee by week. The till did not send the count for week 3.
coffee = np.array([6, 4, np.nan, 5, 9, 8])
avg_coffee = ...
print(avg_coffee)

import numpy as np # Packs of coffee by week. The till did not send the count for week 3. coffee = np.array([6, 4, np.nan, 5, 9, 8]) avg_coffee = ... print(avg_coffee)

Check▶ Run

Exercise

The report for the manager

Answer the manager from the till report. Put in product_totals the units of each product over the six weeks, in best_product the name of the best-selling product, and in avg_per_week how many units each product sells per week on average, rounded to one decimal place.

Pythondata: the shop tables

import numpy as np

names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans'])
sales = np.array([
    [42, 30, 25, 6],
    [38, 34, 28, 4],
    [45, 29, 31, 7],
    [40, 36, 22, 5],
    [51, 33, 27, 9],
    [47, 31, 26, 8],
])
product_totals = ...
best_product = ...
avg_per_week = ...

import numpy as np names = np.array(['White bread', 'Fresh milk', 'Apples', 'Coffee beans']) sales = np.array([ [42, 30, 25, 6], [38, 34, 28, 4], [45, 29, 31, 7], [40, 36, 22, 5], [51, 33, 27, 9], [47, 31, 26, 8], ]) product_totals = ... best_product = ... avg_per_week = ...

Check▶ Run

Test

Check yourself

1. sales has shape (6, 4): 6 weeks and 4 products. What is the shape of sales.mean(axis=0)?

(6, 4): the same as sales(6,): one number for each week(4,): one number for each product

2. In an array of takings by day, one value is np.nan. What does revenue.sum() give?

An errornanThe sum of the other days

3. Why is it wrong to replace the missing takings with 0 and then take the mean?

0 is a real value (“we sold nothing”) and pulls the mean downIt is not wrong: the mean comes out the sameBecause NumPy does not accept 0 in an array of decimals

4. The units by week are [103, 104, 112]. What does .cumsum() give?

319[103, 1, 8][103, 207, 319]

Check

Start the lesson over

Related lessons

• Builds on: The first table · The NumPy array · Vectorization
• Continues in: Broadcasting: the number, the row and the column · Series and DataFrame · Filters and sorting · New columns
• Tasks on this topic: NumPy