What does an empty cell mean? Zero, "unknown" or "not applicable"

The inventory turned up 44 blanks in descuento, one in kilos and one in monto. In the cooperative's member list there are 5 blanks in hectareas and 6 in telefono. They all look the same: an empty cell, NaN in pandas, NULL in SQL. But they don't all mean the same thing, and what you do with them changes the numbers you'll report.

First, look at what happens to an average. These are the hectares of five members; two are empty.

The average with blanks

Example 1 of 2

example.py

import pandas as pd

hectareas = pd.Series([2.0, None, 4.0, None, 3.0])

print(hectareas.mean())

What average does 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

Three meanings, three treatments

A blank means one of three things, and each is handled differently:

What it means	Example in the cooperative	What you do
Zero	empty `descuento`: that sale had no discount	You fill it with 0 and note it in the log
Unknown	`hectareas` of a grower who never gave it; the `monto` of sale V-0036	You leave it empty and say so in the report: "we don't have the data for 2 growers"
Not applicable	`hectareas` of someone who works in the warehouse (role `acopio`): no plot	You leave it empty and set it apart: the plot average is for growers only

The table doesn't tell you which is which. The three cells look identical. What tells a "zero" from an "unknown" is how the sheet was filled in, and the person who knows that is whoever enters the data or uses it every day.

There's a fourth case that isn't a meaning but a disguise: the cell that says "N/A", "-" or just a space. If pandas counted it as a blank (it does for "N/A"), it falls into this table. If it left it as text (the dash, the space), you find it with value_counts, as on the previous page, and then you decide just as you would for any blank: zero, unknown or not applicable?

An empty cell
NaN in pandas, NULL in SQL
What does it mean?
ask whoever enters the data
- Path 1 of 3: zero
  1. Fill it with 0
    and note it in the log
  Ends here.
- Path 2 of 3: unknown
  1. Leave it empty
    and say how many are missing in the report
  Ends here.
- Path 3 of 3: not applicable
  1. Set it apart from the calculation
    for example, filter by role
  Ends here.

The person who knows the data confirms the decision; you write it in code and in the log.

Ask, and ask everything at once

The person who knows the data is almost never the one cleaning it. In the cooperative that's Marisol. A good message saves time for both of you: it says what you found, how many cases there are, what you think it means and what you'll do while she answers. Something like this:

mensaje-a-marisol.txt

Hi Marisol, I'm cleaning the March sales sheet and I have three questions before I put the report together:

1. In 44 of 60 sales the discount is empty. Does that mean there was no discount (0)?
2. Sale V-0036 has no amount. Was it charged? Do you know how much it was for?
3. Sale V-0012 is missing its kilos. Do you have them somewhere else?

Until I hear back, I'll leave those blanks as they are and flag them in the report.

A message with numbers and a proposal gets answered in a minute.

In the course's story, Marisol answers: the empty discount is 0 (nobody writes "0" when there's no discount); sale V-0036 is waiting on its invoice and has no amount yet; nobody has the kilos for V-0012. So the discount is filled with 0, and the other two blanks stay empty and get reported.

Do you have a blank in your data and don't know who to ask? Tell your Maestro where the table comes from.

Tip: When nobody can answer you

Don't guess. Leave the blank and say how many there are: "average of 25 growers; no data for 2" holds up in front of anyone; an invented zero doesn't.

Try it

Now with the complete member list, 30 rows. Three members work in the warehouse (role acopio) and have no plot. Count how many hectare blanks there are in each role and calculate the average hectares of growers only, without filling any blank. If you don't know how to split the blanks by role, ask your Maestro for a hint.

Print how many hectareas blanks there are per rol, and then the growers' average hectares, without filling any blank. You're done when you see 3 blanks in acopio (not applicable), 2 in productor (unknown) and a growers' average. Does it seem high? Hold on to the number: in module 2 you'll see where it comes from.

Your code (Python)

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: socios-cooperativa, 30 rows

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

The exercise's data, "socios-cooperativa". 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 2

Run it yourself

The average you got leaves out the warehouse staff (not applicable) and skips the 2 growers with no data (unknown). It's the right average for this question, though it still hides another problem that you'll find on the impossible-values page.

Check yourself: Lucía works in the warehouse (role `acopio`) and her `hectareas` cell is empty. You're about to calculate the cooperative's average hectares. What do you do with her blank?

pandas: Series.mean (and its skipna)