pandas · Chapter 8 of 22
Missing values
By the end of this chapter you can find the blanks in any table with isna and notna, count them and their share, decide what a blank means in each column, and then fill it (fillna, ffill, bfill), drop its row (dropna) or leave it, knowing what sums and means do with the blanks you keep. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Selecting and cleaning
- 4 tasks
- pandas
- In the all labs plan
The first 2 chapters of pandas are free. This one comes with the all labs plan.
Why you need this
Every file Kargha gets has blanks, and they mean different things. On the website's lines, a blank discount_rs means there was no coupon, so 0. A blank rating means the customer never rated: not 0 stars, just unknown. A blank payment means the gateway never reported it. A blank delivered_on means the order has not been delivered. A blank qty means the line is broken and nobody knows how many pieces were sold.
Shabnam wants each design's average rating, and an average with the blanks taken as 0 is badly wrong. Here is the whole file's average both ways:
lines = pd.read_csv("/workspace/data/orders_site.csv")
print(lines["rating"].mean())
print(lines["rating"].fillna(0).mean())
4.293724966622163
1.3657984144960362
Customers who rated gave 4.3 stars on average; counting every silent customer as a 0 says 1.4, and a weaver whose design is loved would be told it is not. Gopal tried the other way out, throwing away every line with a blank in it before making the courier's list, and kept 304 of 14,128 lines. Harpreet needs lines she can add up, with the coupons as 0 and the broken lines out.
What you know so far cannot even find a blank: you will see below that == np.nan matches nothing. This chapter is about finding them first, then deciding, column by column.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
blank_report.py: the blanks in every column
Write blank_report(table) in blank_report.py. table is any DataFrame, such as the website's lines as read_csv reads orders_site.csv.
Return a DataFrame with one row for each column of table, labelled by the column's name, in the table's column order (every column has a row, even one with no blanks), and two columns: blanks, how many values of that column are missing, as whole numbers; and share, the blanks as a share of the rows, a decimal from 0 to 1. For a table of 4 rows whose note column has 3 blanks, the row labelled note has blanks 3 and share 0.75.
The Check tries the website's lines, the catalogue (no blanks: every row has 0 and 0.0) and a small table of 4 lines with blanks in text and in numbers.
- Task 2
clean_lines.py: the website's lines by Harpreet's rules
Write clean_lines(lines) in clean_lines.py. lines is the website's lines, as read_csv reads orders_site.csv. Harpreet's rules: a line with a blank qty is broken, so drop it; a blank discount_rs means no coupon, so fill it with 0; a blank payment was not reported, so fill it with the text "unknown"; every other blank stays as it is (rating, size, pincode and delivered_on keep theirs).
Return a new DataFrame with every column of lines, in the same order, with only the lines that have a qty, in the table's order and with their own labels from lines (not 0, 1, 2).
The Check tries the website's lines and a small table of 4 lines labelled 20 to 23 with every kind of blank.
- Task 3
ratings.py: an honest average rating
Write two functions in ratings.py. lines is the website's lines, as read_csv reads orders_site.csv; a blank rating means the customer did not rate, and must not count as 0.
average_rating(lines, sku) returns the average rating of the lines whose sku is sku (written exactly so), counting only the lines that were rated, as a decimal number. If none of those lines was rated, it returns a missing value (NaN).
rated_share(lines) returns the share of all the lines that have a rating, a decimal from 0 to 1.
The Check tries the website's lines with "KG-DUP-001", "KG-SAR-019" and "KG-KUR-014" (2 lines, neither rated: NaN), and a small table of 4 lines.
- Task 4 · interview question
Interview: shippable.py, the list that kept almost nothing
The courier needs a pincode for every parcel, and must know the payment, because a COD parcel collects cash. Gopal's ship_list(lines) returns lines.dropna(), and out of the website's 14,128 lines it keeps 304. Fix it so it returns a DataFrame of the lines whose pincode and payment are both there (not blank), with every column, every other blank kept as it was, in the table's order and with their own labels. lines is the website's lines, as read_csv reads orders_site.csv.
The Check tries the website's lines and a small table of 4 lines labelled 20 to 23.
Devika will ask: why did dropna() on the whole website file keep almost nothing, and how do you see that before you drop?
What you will learn
- NaN, None and pd.NA: how pandas shows a missing value in each dtype.
- isna and notna: a mask of the blanks, and of the values that are there; summed, the count; averaged, the share.
- What a blank means: a decision for each column, not one rule for the table.
- dropna: drop the rows with blanks, with how=, subset= and thresh=.
- fillna, ffill and bfill: put a value in, one for each column with a dict, or the one before or after.
- Sums and means skip blanks: count against size, and skipna=.
More in Selecting and cleaning
Build "Missing values" yourself
Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of pandas are free. This one comes with the all labs plan.