Should I delete or flag an impossible value? How to find it in pandas

The kilos inventory said they range from -12 to 9999. The median is 53. Nobody sells -12 kilos of coffee to a cafe, and 9999 kilos is almost ten tons in a sheet where the middle sale is 53. Something happened with those two sales. The question is what to do about them.

The first impulse is to filter: keep only what's reasonable. Predict what a filter like that leaves.

Filtering what's reasonable

Example 1 of 2

example.py

import pandas as pd

kilos = pd.Series([40, -12, None, 9999, 53])
buenos = kilos[kilos.between(1, 500)]

print(len(kilos), len(buenos))

There are 5 sales and 2 impossible ones. How many are left after the filter?

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

The rule comes from whoever knows the business

"Impossible" is not your opinion about the data. It's a business rule, and like the decisions about blanks, you ask. In the story, Marisol confirms two things: a sale is between 1 and 500 kilos, and returns are entered on another sheet, so a negative number here isn't a return, it's an entry error. With that you can write the rule in code, with a name and in one place:

Python

KILOS_MIN = 1
KILOS_MAX = 500

kilos = ventas["kilos"]
ventas["revisar_kilos"] = kilos.notna() & ~kilos.between(KILOS_MIN, KILOS_MAX)

print(ventas["revisar_kilos"].sum())
print(ventas.loc[ventas["revisar_kilos"], ["folio", "kilos", "monto"]])

The rule has a name; if Marisol changes it, you change it in a single line.

In the March sheet the new column flags 2 sales: V-0023 with -12 kilos and V-0045 with 9999. Why flag instead of delete? Because a flagged sale can still be fixed. Look at each one's amount. With the amounts already converted, you can divide amount by kilos and see how much was charged per kilo:

In the normal sales, roasted coffee comes out between 265 and 320 pesos per kilo, and green coffee between 95 and 120. (There's one exception, an entry of V-0031 that comes out at almost 3,000 pesos a kilo; you resolve that one in module 3.)
V-0045 charged 12,012 pesos for 9999 kilos: 1.20 pesos a kilo. Given the amount, the most likely story is that someone typed 9999 instead of a much smaller number. Marisol can look up the note and correct it.
V-0023 charged 2,601 pesos for -12 kilos. The amount exists; the sign of the kilos doesn't.

If you had deleted them, Marisol would have two fewer sales in the report, charged and real, and nobody would know they ever existed. Flagged, they're two rows on a to-do list.

And there's a third case hiding here: sale V-0012, with no kilos. A filter with between would have deleted it along with the impossible ones. The notna() flag keeps it off the impossible list because its problem is different: a piece of data is missing, and it's already on your list of blanks.

How would you set a rule like this for the data at your job? Talk it over with your Maestro.

When the table does need to be without those sales (for example, for an average of kilos per sale), you drop them in that calculation, using the flag: ventas.loc[~ventas["revisar_kilos"], "kilos"].mean(). The clean table keeps them; the calculation decides what it uses, and the report says how many it left out and why.

Try it

In the member list, Marisol confirms that no plot in the cooperative is smaller than a quarter hectare or bigger than 20. If you don't know how to combine the two conditions, ask your Maestro for a hint.

Flag in a column revisar_ha the hectares that have data and are outside 0.25 to 20. Print the flagged members. Then compare the growers' average hectares with and without the flagged ones. You're done when you see a single flagged member and the two averages.

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

A single member, José Luis Cruz Velasco with 250 hectares, raises the growers' average from 3.06 to 12.94 hectares: more than four times. That's why an impossible value is found before calculating anything, and why it's flagged: maybe it was 2.50 hectares and Marisol can confirm it in one phone call.

Should I delete or flag an impossible value? How to find it in pandas

The rule comes from whoever knows the business

Try it

Check yourself: Sale V-0045 says 9999 kilos and 12,012 pesos. What do you do with it in your cleaning script?