Missing data — finding the gaps, then deciding what they mean

The problem we are solving

The shop's orders for the first week of March were typed in by hand at three branches and exported as orders_raw.csv. The owner wants two numbers by the evening: how many units were sold, and how much money came in.

You read the file and, out of habit from the earlier chapters, add up the price column first, just to see the scale:

import pandas as pd

orders = pd.read_csv("orders_raw.csv")

print(orders["price"].sum())

15.060.0850.08.0-15.0120.0

That is not a number. It is every price glued together as text — and pandas raised no error to tell you so. Now the quantity column:

print(orders["quantity"].sum())
print(orders["quantity"].mean())

76.0
10.857142857142858

This one looks fine, and that is worse. There are eight orders in the file, yet 76 / 8 is 9.5, not 10.86. One quantity is blank, and pandas quietly left it out of both the total and the average. Nobody told you that either.

So the file has gaps, and the gaps come in two kinds. Some are visible: an empty cell, which pandas turns into a special "missing" marker and then silently skips in arithmetic. Others are hidden: someone typed - instead of leaving the cell empty, pandas took it at face value, and the whole price column became text.

Neither kind raises an error. Both change the answer. This chapter is about finding every gap, understanding what pandas does with it, and then making a deliberate decision — drop it, fill it, or keep it — instead of letting the default make that decision for you.

By the end of this chapter you can

• Explain what NaN is, why NaN == NaN is False, and why a whole-number column turns into decimals when one cell is empty
• Count missing values per column with isna().sum() and pull out the incomplete rows
• Turn hidden markers like - or ? into real missing values, with na_values= or pd.to_numeric(errors="coerce")
• Predict how sum, mean, count and column arithmetic treat missing values
• Remove missing values with dropna(), using subset= and how= to remove only what you mean to
• Replace missing values with fillna() — one value, a different value per column, or a statistic
• Decide, column by column, whether to drop, fill or keep — and say why
• Avoid the inplace=True trap that does nothing under pandas 3

Prerequisites: Filtering rows.

Make the file first

In your project folder, create orders_raw.csv with exactly these lines. The gaps are deliberate — look carefully at the third, fifth and seventh lines:

order_id,date,branch,product,category,quantity,price
1001,2024-03-01,north,pen,stationery,12,15.0
1002,2024-03-01,south,notebook,stationery,,60.0
1003,2024-03-02,north,bag,accessories,2,850.0
1004,2024-03-02,,bottle,accessories,7,N/A
1005,2024-03-03,north,eraser,stationery,30,8.0
1006,2024-03-03,south,bag,accessories,1,-
1007,2024-03-04,east,pen,stationery,20,15.0
1008,2024-03-04,north,bottle,accessories,4,120.0

It is the same eight orders as orders.csv from the earlier chapters, with four things broken: order 1002 has no quantity, order 1004 has no branch and its price says N/A, and order 1006 has - as its price.

Before you write the code

Missing data is the one topic where writing code first is actively dangerous: dropna() and fillna() both run without complaint and both change your answer. So the work starts with looking and deciding.

1. Say the question in one sentence. "What was the total quantity sold, and the total revenue (quantity × price), for the week — and how many orders could not be counted?" The last part matters. A total that silently leaves out two orders is not the same answer as a total that says "6 of 8 orders counted".

2. Look at what arrived, before anything else. shape and info() together tell you about every column at once:

print(orders.shape)
orders.info()

(8, 7)
<class 'pandas.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 7 columns):
 #   Column    Non-Null Count  Dtype
---  ------    --------------  -----
 0   order_id  8 non-null      int64
 1   date      8 non-null      str
 2   branch    7 non-null      str
 3   product   8 non-null      str
 4   category  8 non-null      str
 5   quantity  7 non-null      float64
 6   price     7 non-null      str
dtypes: float64(1), int64(1), str(5)
memory usage: 580.0 bytes

Read it against what you expected, column by column:

• branch — expected 8 non-null str, got 7 non-null. One branch is missing.
• quantity — expected 8 non-null int64, got 7 non-null float64. One quantity is missing, and that is why it became float64 (explained below).
• price — expected 8 non-null float64, got 7 non-null str. One price is missing and some text got into the column.

Two things in that list are clues rather than facts. A whole-number column showing float64 almost always means a gap. A number column showing str always means text is mixed in — and text in a number column is usually a hidden "missing" marker.

3. Sketch the output you want. Something like:

Missing values per column: branch 1, quantity 1, price 2
Orders counted for revenue: 6 of 8
Total quantity (known):     76
Total revenue (counted):    ...

Notice it reports the gaps as well as the totals. Whoever reads the report should know the revenue is a lower bound.

4. Decide the tool for each gap — before writing it. For each column, ask: what does an empty cell here mean, and can the question still be answered without it?

• branch — a gap means the order happened but the branch was not recorded. Decision: fill with "Unknown", because the order is real and its money still counts.
• quantity — a gap means we do not know how many were sold. Decision: keep it missing and exclude the row from revenue, because inventing a number (0? the average?) would invent sales.
• price — a gap means we do not know what it sold for. Decision: keep it missing and exclude the row from revenue, for the same reason — and report how many rows were excluded.

5. Decide what you will check afterwards. After every step that removes or replaces values, print the missing counts and the shape again. If a dropna() takes you from 8 rows to 2, you want to see that immediately, not after the report has gone out.

The rest of the chapter gives you each tool in turn, and at the end we put this plan into one program.

What "missing" looks like: NaN

When pandas finds an empty cell, it does not store an empty string or zero. It stores a special floating-point value called NaN — "not a number". You can make one yourself by putting None into a Series:

import pandas as pd

quantity = pd.Series([12, None, 2])
print(quantity)

0    12.0
1     NaN
2     2.0
dtype: float64

Look at the dtype. You gave it two whole numbers and a gap, and got float64 back, with 12.0 and 2.0. This is the reason behind the clue in info(): NaN is a float value, and a classic integer column has no way to hold it. So the moment one cell is missing, pandas promotes the whole column to float64 to make room for NaN. That is why quantity in our file printed as 12.0, 2.0 and so on.

Text columns behave differently. A str column can hold a missing value without changing type:

branch = pd.Series(["north", None, "east"])
print(branch)

0    north
1      NaN
2     east
dtype: str

It still prints as NaN, and the dtype stays str. So in pandas 3 you will meet NaN in both number and text columns, and the same tools find it in both.

NaN is not equal to anything — not even itself

This is the single most surprising fact about missing values, and it breaks the most natural way of searching for them:

import numpy as np
import pandas as pd

orders = pd.read_csv("orders_raw.csv")

print(np.nan == np.nan)
print((orders["branch"] == np.nan).sum())

False
0

NaN means "some value we do not know". Two unknown values are not known to be equal, so the comparison says False — every time. That makes == np.nan a test that matches nothing, ever: the branch column has a gap, and the count says 0. Ask pandas directly instead.

Never search for missing values with ==. == np.nan runs without an error and always finds nothing, so a column full of gaps looks complete. Use isna().

Finding missing values: isna()

isna() asks the question that == cannot: "is this cell missing?" On a column it gives back a column of True/False — a mask, exactly like the ones from the filtering chapter:

print(orders["branch"].isna())

0    False
1    False
2    False
3     True
4    False
5    False
6    False
7    False
Name: branch, dtype: bool

On the whole DataFrame it does the same for every cell. You rarely want to read eight rows of True/False, though. You want a count, and since True counts as 1, .sum() gives it:

print(orders.isna().sum())

order_id    0
date        0
branch      1
product     0
category    0
quantity    1
price       1
dtype: int64

This is the line to run after info() on any new file. It is the same information as the Non-Null Count, turned around to count the gaps directly.

But look at price: it says 1. We know two prices are bad — N/A and -. Keep that in mind; we come back to it in the next section.

Which rows are incomplete?

A count tells you how many; to see which, use the mask as a filter. isna().any(axis=1) asks, for each row, "is anything in this row missing?":

incomplete = orders[orders.isna().any(axis=1)]
print(incomplete)

order_id        date branch   product     category  quantity price
1      1002  2024-03-01  south  notebook   stationery       NaN  60.0
3      1004  2024-03-02    NaN    bottle  accessories       7.0   NaN

axis=1 means "look across each row" rather than down each column. And notice who is not in the list: order 1006, whose price is -. As far as pandas knows, that row is complete. Hold that thought. Looking at the actual rows is worth the extra line: it is how you notice that, say, every missing quantity comes from the same branch, which is a question for the people at that branch rather than for pandas.

As a share, not a count

With eight rows, counts are fine. With eighty thousand, "312 missing" means little until you know it is 0.4%. .mean() of a True/False column is the fraction of True:

print(orders.isna().mean())

order_id    0.000
date        0.000
branch      0.125
product     0.000
category    0.000
quantity    0.125
price       0.125
dtype: float64

One in eight cells of branch, quantity and price is missing — 12.5% each. A column that is 90% empty and a column that is 0.1% empty call for completely different decisions, and this is how you tell them apart.

Hidden missing values: when the gap is written as text

isna() counted one missing price, but two prices are unusable. The second one is the - in order 1006. To pandas, - is not missing — it is a perfectly good piece of text. And one piece of text in a column of numbers is enough to make the entire column str, which is exactly what info() showed.

Here is how you find out what is actually in a column:

print(orders["price"].unique())

<StringArray>
['15.0', '60.0', '850.0', nan, '8.0', '-', '120.0']
Length: 7, dtype: str

Every value is in quotes — they are all text, '15.0' included. And there are two kinds of gap side by side: nan, which pandas recognised, and '-', which it did not.

Why did N/A become nan but - did not? Because read_csv comes with a built-in list of strings it treats as missing: an empty cell, NA, N/A, n/a, NaN, null, None and a few others. -, ?, missing, 0 and unknown are not on that list. Whatever the person typing the data used for "don't know" decides which kind of gap you get.

There are two ways to turn the hidden ones into real NaN.

Fix 1: tell read_csv about the marker — na_values=

If you know the marker, the cleanest fix is at the moment of reading. na_values= adds your strings to the built-in list:

orders = pd.read_csv("orders_raw.csv", na_values=["-"])

print(orders["price"].dtype)
print(orders.isna().sum())

float64
order_id    0
date        0
branch      1
product     0
category    0
quantity    1
price       2
dtype: int64

Compare it with the count before. branch and quantity are unchanged — na_values= adds to the list, it does not replace it, so empty cells and N/A are still recognised. What changed is price: it is now float64, and it reports 2 missing — the N/A and the - are both real NaN now. The column is numeric again, so arithmetic on it means arithmetic.

Note that na_values=["-"] applies to every column. Here that is what we want. If a - could be a real value somewhere else — a product code, say — pass a dict instead, na_values={"price": ["-"]}, and only the price column is affected.

Fix 2: convert afterwards — pd.to_numeric(errors="coerce")

Sometimes you do not know in advance what odd strings are in a column, or the file was read by someone else's code. Then you convert the column after reading. First, see what a plain conversion does:

raw = pd.read_csv("orders_raw.csv")

price = pd.to_numeric(raw["price"])

ValueError: Unable to parse string "-" at position 5

That error is useful: it names the offending value and its position. Adding errors="coerce" says "anything you cannot turn into a number, make it NaN":

price = pd.to_numeric(raw["price"], errors="coerce")
print(price)

0     15.0
1     60.0
2    850.0
3      NaN
4      8.0
5      NaN
6     15.0
7    120.0
Name: price, dtype: float64

To actually change the table, assign the result back to the column — to_numeric returns a new Series and leaves raw alone:

raw["price"] = pd.to_numeric(raw["price"], errors="coerce")
print(raw["price"].dtype, raw["price"].isna().sum())

float64 2

Which fix to choose

errors="coerce" is powerful and blunt. It turns anything non-numeric into NaN — including a real price mistyped as 1O0 (letter O) or 1,200 with a thousands comma. Those are not missing values; they are values with a typo, and coercing them silently throws information away.

So the habit is: count before, count after, and explain the difference.

• You know the marker (-, ?, missing): use na_values= in read_csv. It is precise — only that string becomes missing.
• Unknown junk, and the column must be numeric: use pd.to_numeric(errors="coerce"). It catches everything, so check what it caught.
• Some values are typos you can repair: look at unique() first, fix them, then convert. Coercing would destroy real data.

Before coercing, raw["price"].unique() showed exactly one non-numeric string ('-') plus one nan. After coercing, there are exactly 2 NaN. The numbers agree, so nothing else was lost. That check is the whole difference between cleaning data and damaging it.

From here on we use the version read with na_values=["-"].

How calculations treat NaN

Now that the gaps are real NaN, you need to know what pandas does with them — because it does not stop and ask.

Column summaries skip NaN

orders = pd.read_csv("orders_raw.csv", na_values=["-"])
q = orders["quantity"]

print("len  :", len(q))
print("count:", q.count())
print("sum  :", q.sum())
print("mean :", q.mean())
print("check:", q.sum() / q.count())

len  : 8
count: 7
sum  : 76.0
mean : 10.857142857142858
check: 10.857142857142858

Three behaviours to remember:

• len counts rows; count() counts values. The difference between them is the number of gaps. This is the same idea as Non-Null Count in info().
• sum() skips NaN. It adds the seven known quantities. That is reasonable — but it means "76" is the total of known quantities, not of all orders.
• mean() divides by count(), not by len. The average is over the seven known values. That is usually what you want; treating the gap as 0 would drag the average down for no reason.

If you would rather be told that something is missing, skipna=False makes the result NaN as soon as any value is:

print(q.sum(skipna=False))

nan

Arithmetic between columns spreads NaN

Summaries skip gaps; arithmetic spreads them. Anything combined with NaN gives NaN, because "7 times an unknown price" is itself unknown:

revenue = orders["quantity"] * orders["price"]
print(revenue)
print("total:", revenue.sum())
print("rows counted:", revenue.count(), "of", len(revenue))

0     180.0
1       NaN
2    1700.0
3       NaN
4     240.0
5       NaN
6     300.0
7     480.0
dtype: float64
total: 2900.0
rows counted: 5 of 8

Put the two behaviours together and you see the trap from the start of the chapter. revenue.sum() printed 2900.0 with no warning — but three of the eight orders contributed nothing. Order 1002 is missing a quantity, 1004 and 1006 are missing a price. The total is honest only if you say "5 of 8 orders". That is why the plan asked for a count next to every total.

Removing missing values: dropna()

dropna() removes rows that contain NaN. With no arguments it removes every row with any gap in any column:

print(orders.shape)
print(orders.dropna().shape)

(8, 7)
(5, 7)

Three of eight rows are gone — and order 1004 went because its branch was missing, even though for a question about quantity it was a perfectly good row. That is the problem with a bare dropna(): it answers "remove anything imperfect", which is rarely the question.

subset= — only the columns the question needs

Say which columns must be present:

known_qty = orders.dropna(subset=["quantity"])
print(known_qty.shape)
print(known_qty["quantity"].sum())

(7, 7)
76.0

Only order 1002 is removed — the one row where the quantity really is unknown. For revenue, both columns are needed:

countable = orders.dropna(subset=["quantity", "price"])
print(countable[["order_id", "quantity", "price"]])

order_id  quantity  price
0      1001      12.0   15.0
2      1003       2.0  850.0
4      1005      30.0    8.0
6      1007      20.0   15.0
7      1008       4.0  120.0

Notice the index: 0, 2, 4, 6, 7. dropna() keeps the original labels, just like filtering does, so you can always trace a row back to the file. If you need 0, 1, 2… again, add .reset_index(drop=True).

how="all" — only rows that are completely empty

Exported spreadsheets often end with a few rows that are blank in every column. how="all" removes just those and leaves any row with even one value:

print(orders.dropna(how="all").shape)

(8, 7)

Nothing removed here — none of our rows is entirely empty — which is exactly what you want to confirm.

dropna() returns a new table

Like most pandas methods, dropna() does not change orders; it hands you a new DataFrame. If you write orders.dropna() on a line by itself, nothing happens to orders. Assign the result — to a new name if you will still need the full table, which in a report like ours you will (to say how many were dropped).

Replacing missing values: fillna()

Sometimes a gap has a sensible replacement. fillna() puts a value where NaN was.

One value for one column

The missing branch is the clearest case. The order happened and its money is real; we just do not know which branch took it. Filling it with a label keeps the row and makes the gap visible in any later count by branch:

orders["branch"] = orders["branch"].fillna("Unknown")
print(orders["branch"].tolist())

['north', 'south', 'north', 'Unknown', 'north', 'south', 'east', 'north']

The shape of that line is important: take the column, fillna, and assign it back to the same column. fillna returns a new Series; without the orders["branch"] = in front, nothing is stored.

Different values for different columns: a dict

Pass a dict of {column: value} to fill several columns at once, each with its own value. Columns not named are left untouched:

orders = pd.read_csv("orders_raw.csv", na_values=["-"])

filled = orders.fillna({"branch": "Unknown", "quantity": 0})
print(filled.isna().sum())

order_id    0
date        0
branch      0
product     0
category    0
quantity    0
price       2
dtype: int64

price still has its two gaps, because we did not mention it. That is the dict form's advantage over orders.fillna(0), which would fill every column, prices included, with 0 — and a price of 0 is a free bag.

Filling changes the answer

Filling quantity with 0 looked harmless. Compare the averages:

print("skip the gap :", orders["quantity"].mean())
print("fill with 0  :", orders["quantity"].fillna(0).mean())
print("fill with median:", orders["quantity"].fillna(orders["quantity"].median()).mean())

skip the gap : 10.857142857142858
fill with 0  : 9.5
fill with median: 10.375

Three different averages from one column, and the only difference is what you decided an unknown quantity means:

• 0 says "this order sold nothing". For order 1002, a notebook order that certainly sold something, that is false, and it drags the average down.
• The median (7 here) says "assume a typical order". It keeps the average roughly in place, but the total now includes 7 notebooks nobody counted.
• Skipping says "we do not know, so leave it out". The average is over the seven orders we do know.

None of these is "correct" in general. Each answers a slightly different question. That is why the decision belongs in your plan, written down, rather than in whatever default you reached for.

A rule of thumb that works most of the time:

• A label (branch, category, name): fill with "Unknown" or similar. The row is kept, and the gap stays visible as its own group.
• A count where empty really means none (returns, complaints): fill with 0 — but only when the source really means zero by a blank.
• A measurement or money (quantity sold, price, a mark): keep NaN, exclude it from the calculation, and report how many were excluded. Invented numbers become invented totals.
• A column the question needs, mostly present: dropna(subset=[...]) for that question only. A small loss, and an honest answer.

Getting whole numbers back

After filling, quantity has no gaps, but it is still float64 — pandas does not change it back by itself. Once there is no NaN left, astype(int) works:

filled["quantity"] = filled["quantity"].astype(int)
print(filled["quantity"].tolist())
print(filled["quantity"].dtype)

[12, 0, 2, 7, 30, 1, 20, 4]
int64

Try it before filling and it fails, because there is no integer that means "unknown" — see "When it breaks" below.

A complete example

clean_orders.py puts the plan from "Before you write the code" into one program: read with the known marker, report every gap, fill the branch, exclude rows that cannot be counted, and report the totals together with how many orders they cover.

import pandas as pd

orders = pd.read_csv("orders_raw.csv", na_values=["-"])

# 1. Look first
print("Rows, columns:", orders.shape)
missing = orders.isna().sum()
print("Missing per column:")
print(missing[missing > 0])
print()

# 2. Decide per column: branch -> label; quantity and price -> keep, exclude
orders["branch"] = orders["branch"].fillna("Unknown")

# 3. Only rows with both numbers can be counted for revenue
countable = orders.dropna(subset=["quantity", "price"])
skipped = orders[orders[["quantity", "price"]].isna().any(axis=1)]

revenue = countable["quantity"] * countable["price"]

print("Orders counted for revenue:", len(countable), "of", len(orders))
print("Total quantity (known):    ", int(orders["quantity"].sum()))
print("Total revenue (counted):   ", revenue.sum())
print()
print("Not counted - check these with the branch:")
print(skipped[["order_id", "branch", "quantity", "price"]])

Rows, columns: (8, 7)
Missing per column:
branch      1
quantity    1
price       2
dtype: int64

Orders counted for revenue: 5 of 8
Total quantity (known):     76
Total revenue (counted):    2900.0

Not counted - check these with the branch:
   order_id   branch  quantity  price
1      1002    south       NaN   60.0
3      1004  Unknown       7.0    NaN
5      1006    south       1.0    NaN

Why it is written this way:

• na_values=["-"] is in the very first line. The marker was found once, by looking at unique(). Putting it in read_csv means the column is numeric from the start, and anyone reading the program sees which marker the file uses.
• missing[missing > 0] prints only the columns that have gaps. On a file with forty columns, that is the difference between a readable report and a wall of zeros.
• The branch is filled, the numbers are not. That is the decision list from the plan, written as code. A reader can disagree with it, but they can see it.
• dropna(subset=...) names the two columns revenue needs, so order 1004 is excluded for its missing price, not for its missing branch.
• The skipped rows are printed, not just counted. "3 orders not counted" invites the question "which ones?" — answer it in the report. Those three orders are a list someone can actually go and fix.
• "Total quantity (known)" and "(counted)" say what the numbers are. Both are lower bounds; the labels stop anyone treating them as complete.

When it breaks

TypeError: Cannot perform reduction 'mean' with string dtype You asked for the mean of a column that pandas read as text — usually because one cell holds a marker like -, ? or missing. Run df["col"].unique() to find the culprit, then either re-read with na_values=["-"] or convert with pd.to_numeric(df["col"], errors="coerce").

raw = pd.read_csv("orders_raw.csv")
print(raw["price"].mean())

TypeError: Cannot perform reduction 'mean' with string dtype

sum() returned a long string of digits, like 15.060.0850.0... Same cause, worse symptom: on a text column sum() joins the strings instead of adding numbers, and raises nothing. Any total that looks like that came from a str column. Check df.dtypes before adding anything up.

ValueError: Unable to parse string "-" at position 5 pd.to_numeric met text it could not convert, and told you exactly what and where. Either add errors="coerce" (after checking with unique() that the odd values really are "unknown" markers and not typos), or fix the typos first.

(df["col"] == np.nan).sum() says 0, but there are gaps NaN is never equal to anything, itself included, so == np.nan matches no cell. Use df["col"].isna().

IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer You called astype(int) on a column that still has NaN. There is no integer meaning "unknown". Fill or drop the gaps first, then convert:

orders = pd.read_csv("orders_raw.csv", na_values=["-"])
orders["quantity"] = orders["quantity"].astype(int)

pandas.errors.IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer.Replace or remove non-finite values or cast to an integer typethat supports these values (e.g. 'Int64')

(The missing spaces in "integer.Replace" and "typethat" are in pandas' own message.) The message also mentions 'Int64' with a capital I: a separate pandas integer type that can hold missing values. It is useful, but filling or dropping first is the simpler fix and the one to reach for.

ChainedAssignmentError — and the gap is still there You wrote the inplace=True form you will find in many older tutorials:

orders = pd.read_csv("orders_raw.csv", na_values=["-"])
orders["quantity"].fillna(0, inplace=True)
print(orders["quantity"].isna().sum())

1
ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series through chained assignment using an inplace method.
Such inplace method never works to update the original DataFrame or Series, because the intermediate object on which we are setting values always behaves as a copy (due to Copy-on-Write).

For example, when doing 'df[col].method(value, inplace=True)', try using 'df.method({col: value}, inplace=True)' instead, to perform the operation inplace on the original object, or try to avoid an inplace operation using 'df[col] = df[col].method(value)'.

See the documentation for a more detailed explanation: https://pandas.pydata.org/pandas-docs/stable/user_guide/copy_on_write.html

The count is still 1: nothing was filled. In pandas 3, orders["quantity"] always behaves as its own copy (Copy-on-Write), so filling it "in place" fills that copy, which is then thrown away. Older pandas sometimes let this work, which is why the pattern is everywhere online. Write the assignment out instead — it works in every version and says plainly what changes:

orders["quantity"] = orders["quantity"].fillna(0)
print(orders["quantity"].isna().sum())

0

dropna() removed far more rows than you expected A bare dropna() removes a row for a gap in any column — including a mostly-empty notes or discount column you were not even using. Print df.isna().sum() to find the column with many gaps, then use dropna(subset=[...]) with only the columns your question needs.

TypeError: can't multiply sequence by non-int of type 'float' You multiplied a number column by a text column (quantity * price while price is str). The fix is the same as the first error: make price numeric first.

Your turn

The shop has started home delivery, and the riders' log for the first week arrives as deliveries.csv. Create it exactly like this:

order_id,branch,distance_km,fee,rider
2001,north,3.5,60,R1
2002,north,,60,R2
2003,south,8.0,?,R1
2004,east,12.0,120,
2005,north,2.0,40,R2
2006,south,6.5,90,?
2007,east,,110,R3
2008,north,4.0,60,R3

The owner asks: how much was collected in delivery fees, how many deliveries are missing a fee, and what is the average fee per kilometre?

Before writing any code, look at the file with unique() and decide what the hidden marker is. Then write deliveries_report.py that:

1. Reads the file so that the hidden marker becomes a real missing value in every column where it appears
2. Prints the number of missing values per column, and the incomplete rows
3. Fills the missing rider with "unassigned"
4. Prints the known fee total, the number of deliveries with no fee, and the fee per kilometre — total fee divided by total distance — computed only from deliveries where both the fee and the distance are known

The last three lines your program prints should be exactly:

Fees collected (known): 540.0
Deliveries with no fee: 1
Fee per km: 13.21 from 5 of 8 deliveries

Also write down, before the code, a decision list like the one in "Before you write the code": for each of distance_km, fee and rider, what does a gap mean, and will you fill, drop or keep?

Then try one experiment: compute the fee per kilometre with a bare dropna() instead of dropna(subset=...). Does the number change? How many rows did it use, and why?

Solution

The plan first. The question has three parts: a total, a count of gaps, and a ratio. Reading info() would show fee as str — a fee column should be numbers, so text is hiding in it. Decisions per column:

• rider — the delivery happened, the rider was not logged. Fill with "unassigned".
• fee — we do not know what was charged. Keep NaN; sum the known fees and count the unknown ones.
• distance_km — we do not know how far. Keep NaN; exclude those rows from the per-km ratio.

For the ratio, both numbers must be known for the same delivery — otherwise you would divide the fees of seven deliveries by the distances of six.

Step 1 — read plainly and look, to find the marker:

import pandas as pd

raw = pd.read_csv("deliveries.csv")
print(raw.shape)
print(raw.dtypes)
print(raw["fee"].unique())
print(raw["rider"].unique())

(8, 5)
order_id         int64
branch             str
distance_km    float64
fee                str
rider              str
dtype: object
<StringArray>
['60', '?', '120', '40', '90', '110']
Length: 6, dtype: str
<StringArray>
['R1', 'R2', nan, '?', 'R3']
Length: 5, dtype: str

fee is str because of the ?. And ? also appears as a rider — the same marker, used in two columns. distance_km is already float64: its gaps are empty cells, which pandas recognised by itself. Since the marker is the same everywhere, na_values=["?"] at read time fixes both columns at once.

The full program, deliveries_report.py:

import pandas as pd

deliveries = pd.read_csv("deliveries.csv", na_values=["?"])

print("Types after reading:")
print(deliveries.dtypes)
print()

print("Missing per column:")
print(deliveries.isna().sum())
print()
print("Incomplete rows:")
print(deliveries[deliveries.isna().any(axis=1)])
print()

deliveries["rider"] = deliveries["rider"].fillna("unassigned")

fee_total = deliveries["fee"].sum()
fee_missing = deliveries["fee"].isna().sum()
print("Fees collected (known):", fee_total)
print("Deliveries with no fee:", fee_missing)

both = deliveries.dropna(subset=["fee", "distance_km"])
per_km = both["fee"].sum() / both["distance_km"].sum()
print("Fee per km:", round(per_km, 2), "from", len(both), "of", len(deliveries), "deliveries")

Types after reading:
order_id         int64
branch             str
distance_km    float64
fee            float64
rider              str
dtype: object

Missing per column:
order_id       0
branch         0
distance_km    2
fee            1
rider          2
dtype: int64

Incomplete rows:
   order_id branch  distance_km    fee rider
1      2002  north          NaN   60.0    R2
2      2003  south          8.0    NaN    R1
3      2004   east         12.0  120.0   NaN
5      2006  south          6.5   90.0   NaN
6      2007   east          NaN  110.0    R3

Fees collected (known): 540.0
Deliveries with no fee: 1
Fee per km: 13.21 from 5 of 8 deliveries

Check the numbers by hand once, because that is how you learn to trust a cleaned total. Known fees: 60 + 60 + 120 + 40 + 90 + 110 + 60 = 540, from seven deliveries — 2003 has none. For the ratio, dropna(subset=["fee", "distance_km"]) drops 2003 (no fee) and 2002 and 2007 (no distance). Ask pandas which rows were left and what they add up to, rather than trusting mental arithmetic:

print(both["order_id"].tolist())
print(both["fee"].sum(), both["distance_km"].sum())

[2001, 2004, 2005, 2006, 2008]
370.0 28.0

370 / 28.0 = 13.21, which is the number the program printed. Notice what the ratio did not do: it did not divide the 540 from seven fees by the distances of six deliveries. Both numbers come from the same five rows, which is the only way a "per kilometre" figure means anything.

Two more decisions worth noticing. rider was filled before the calculation, but it never affected it — subset= only looked at fee and distance_km. And the fee total is labelled "(known)", with the count of missing fees printed right under it, so nobody mistakes 540 for everything that was collected.

The experiment. A bare dropna() also removes 2004 and 2006 — whose fee and distance were both fine — because their rider was missing. If you run it on the table before filling rider:

deliveries = pd.read_csv("deliveries.csv", na_values=["?"])
strict = deliveries.dropna()
print(strict["order_id"].tolist())
print(round(strict["fee"].sum() / strict["distance_km"].sum(), 2))

[2001, 2005, 2008]
16.84

Only three deliveries survive, and the ratio jumps from 13.21 to 16.84 — not because any fee changed, but because the two longest trips (12 and 6.5 km), which happen to be cheaper per kilometre, were thrown out over a missing name. The rider column has nothing to do with the question, so it should not decide which rows count. That is what subset= is for.