JIT — Intro to Data Science · Python Lab · Problem 23 of 30

Filling the gaps in a column

One gap pandas can see and two it cannot — and the decision about what to put in their place.

Two kinds of gap, and pandas can only see one

Problem 21 found an empty cell and pandas turned it into NaN. That gap announces itself: isna() counts it, mean() skips it, every tool in pandas knows it is not a real value.

The other kind of gap is worse. Somebody typed -999 to mean "no mark recorded", and to pandas that is simply a number. It counts towards the average. It survives every check. Nothing you run will mention it, because nothing you run has been told it is special.

The file you are given marks_coded.csv has both problems: one blank cell, and two rows where somebody typed the code.
name,city,marks Anjali,Nagpur,78 Farhan,Pune,-999 Priya,Nagpur,80 Rohit,Mumbai,← blank Meera,Nagpur,54 Imran,Pune,88 Sneha,Nagpur,-999 Kabir,Mumbai,71

isna().sum() reports 1. There are three. The average of the column comes out at -232.4, which at least looks wrong — but a code of 0 instead of -999 would have given an average of 46 and looked entirely believable.

Look at the smallest value first The same habit as problem 9. A minimum of −999 in a column of marks is not a mark, and one line finds it. Do this before you calculate anything, on every column you did not create yourself.
New word — replace df["marks"].replace(-999, np.nan) turns the code into a gap pandas can see. After that line isna() reports 3, and every pandas tool starts skipping all three instead of one.
Decoding comes before deciding. You cannot choose sensibly what to do about missing values while two of them are still pretending to be numbers.
Then a decision: fill, or drop marks.fillna(marks.mean()) puts the average of the known marks into each gap. Every student stays in the table; three of them now carry a number nobody gave them.
df.dropna() throws away any row with a gap in it. The marks that remain are all real; the class is now five students instead of eight.
There is no correct answer, only a defensible one. What you must not do is neither — leave the codes in place and let them quietly do arithmetic on your behalf.
Careful — dropna takes an axis df.dropna() drops incomplete rows. df.dropna(axis=1) drops incomplete columns, which here means throwing away the marks entirely and keeping all eight students with their names and cities. It runs, and it leaves you with nothing to analyse.

What you are building

the table, then: Missing according to pandas: 1 Smallest value on file: -999.0 Missing after decoding: 3 Average after filling: 74.19999999999999 Rows kept: 5 Average of those rows: 74.2

Those last two averages agree, and they should: filling every gap with the average cannot move the average. That is worth knowing — it is the reason filling with the mean is safe for a headline figure and misleading for anything about spread.

Coming later Filling gaps with the average keeps the centre and shrinks the spread. Problem 24 measures spread properly, with quartiles and the IQR, and shows which values are genuinely unusual rather than merely coded.

Build the programme

Your plan

The steps still to place

The whole programme is laid out below. Five pieces are missing. The order they appear in is the order the job has to be done in: find out what is wrong, decode it, then decide.

Leave nothing on "choose…". A wrong pick does not always cause an error — sometimes it just prints something you did not expect, which is the harder kind of mistake to spot.

Run and read

Here is the whole programme. Two files are available, and they differ in one respect only: the coded one has two rows where somebody typed −999 instead of leaving the cell blank. Run both.

Your programme

Output

Nothing has run yet.