Does an empty cell count as zero in an average?

The third question is what did it leave out? What wasn't counted never shows up in the answer's sentence, and it changes the number as much as what was. In this module you will see three ways something gets left out or slips in extra: empty cells, rows that don't make it to the final table, and repeated rows that get in twice.

Start with empty cells. Imagine you ask your assistant "what was the average rating in the March survey?" and it answers: "The average rating is 4.4 out of 5. Customers are very satisfied." Your coworker in customer service did the same calculation in Excel and got 1.77. Nobody used a wrong formula. The difference is in 67 empty cells: the customers who received the survey and didn't answer.

Before looking at the file, a warm-up with five ratings, two of them empty. In Python, an empty cell is written None when you create the table, and pandas shows it as NaN (for not a number). The survey's rating column is called calificacion.

Five surveys: three with a rating and two empty. Write what you think each program prints, and why, before you run it.

Counting and averaging with empty cells

Example 1 of 2

example.py

import pandas as pd

calificacion = pd.Series([5, 4, None, None, 3])

print(len(calificacion))
print(calificacion.count())
print(calificacion.mean())

What will it print?

Why?

A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.

Example 2 of 2, opens once you explain this one

Make your prediction

An empty cell is not a zero

The warm-up shows the whole rule. When pandas averages a column, it skips the empty cells: the 4.0 is the average of the three who answered. If someone turns the empty cells into zeros, the sum is divided by everyone and the average drops to 2.4.

Excel does the same with its AVERAGE function: it skips empty cells, but it does count cells that hold a zero. So in this survey, the gap between 4.4 and 1.77 comes from one of two things: someone typed 0 into the unanswered surveys, or someone (a person or an AI) filled the empty cells with zeros before averaging.

Which one is right? It depends on what the empty cell means. In this survey, empty means "didn't answer," not "had a terrible time." Counting it as zero invents 67 ratings of zero that nobody gave. The 4.4 is the honest number, as long as you state its base: it is the average of the 45 who answered. It is the same question as in module 3, now with an average instead of a percentage. Does the survey's 4.4 represent all customers?

There are cases where empty really does mean zero. In a discounts column, an empty cell can mean "no discount," that is, 0 pesos. There, filling with zero is correct. Nobody can decide it for you by looking at the file alone: the person who enters the data knows. Give me another example of a column where empty does mean zero.

Python

len(data)                                # all the rows, empty ones included
data["calificacion"].count()             # only the cells that are not empty
data["calificacion"].isna().sum()        # how many cells are empty
data["calificacion"].mean()              # average of the ones that have something
data["calificacion"].fillna(0).mean()    # average if the empty ones were zeros

The first three lines are your quick check: if count() is much smaller than len, the column has gaps.

AVERAGE function (Microsoft, in Spanish): which cells it skips and which it counts

pandas.Series.mean (pandas documentation): the skipna parameter

Try it

The survey file (synthetic data, CC0) has 112 rows, one per ticket that was sent the survey. The code calculates the three figures of the quick check and the average. Run it with Run (or Ctrl + Enter, ⌘ + Enter on a Mac) and compare with the AI's 4.4. Then add the average with the empty cells as zero, and a table by branch with how many surveys were sent and how many have a rating.

Run the code and compare with the AI's 4.4. Then add two things: the average with the empty cells as zero (the Excel 1.77) and, for each branch, how many surveys were sent (size) and how many have a rating (count). You are done when you see 4.4 and 1.77, and a table where Centro shows 36 and 14.

Your code (Python)

# The March survey is already loaded into the table data.

# A row is a ticket that was sent the survey.

filas = len(data) # all the rows

con_calificacion = data["calificacion"].count() # only the ones that are not empty

vacias = data["calificacion"].isna().sum() # the empty ones

promedio = data["calificacion"].mean() # pandas skips the empty ones

print("Rows:", filas)

print("With a rating:", con_calificacion)

print("Empty:", vacias)

print("Average:", round(promedio, 2))

# Your turn 1: the average if the empty cells counted as zero.

# como_cero = data["calificacion"].fillna(0).mean()

# Your turn 2: by branch, surveys sent and surveys with a rating.

# data.groupby("sucursal")["calificacion"].agg(["size", "count"])

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

Data: encuesta-marzo, 112 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "encuesta-marzo". Your edits run with your code.
Row						Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 6

Run it yourself

If you add up the size column of your table by branch, you get 111, not 112. One survey doesn't appear under any branch. It's not an error in your code: it is the second way something gets left out, and you see it on the next page.

When an AI gives you an average, ask it how many empty cells there were and what it did with them. An answer like "I averaged the 45 ratings; 67 surveys have no answer and I didn't count them" can be checked with the three lines above. An answer that just says "4.4" can't. How do I ask the AI what it did with the empty cells?

Check yourself: The AI tells you: "The average rating for March is 4.4." The survey was sent to 112 customers and 45 answered. Which sentence best describes that number?