Reading and selecting data

What you will be able to do

After this lesson you can

• read a table from a CSV file or text,
• find and handle missing values, and
• choose the rows and columns that you need.

Reading a table

Data usually comes in a file: a spreadsheet saved as CSV (comma-separated values), as in chapter 3 of the Going Further book. One line is one row, and the commas separate the columns. The first line holds the names.

Predict first. One value is missing, the mass of Mars. How will it be shown? Then run it.

Missing values

Which is the better choice? Do not decide without thinking. Dropping rows loses data, but it adds nothing that is not true. Filling with the mean keeps the rows, but the filled values are not measurements, and they make the data look less spread out than it is. The first question is always: why is the value missing? Look at it, and describe in your report what you did.

Choosing rows

Predict first. Which planets are farther than 1 AU?

The condition df["mass"] > limit is a Series of True and False, one for each row. Using it inside df[...] keeps the True rows.

Labels and places

The rule is: loc for labels, iloc for places.

Common mistakes

• Writing and and or between conditions. Use & and |, each condition in brackets.
• Filling or dropping missing values without looking at them.
• Believing that a column of numbers has no missing values. Check with isna().sum().
• Forgetting that a label range includes both ends.
• Changing a part of a filtered table and expecting the original to change. A filter makes a new table.

Practice

Start with two quick questions. Each New numbers gives you a fresh one, so you can practise as long as you like.

Then the labs and the problems:

1. Warm-up. Open the lab Clean the table. You fill the missing numbers and pick rows.
2. Build. Open the problem How many are missing? and the problem The heavy planets.

Remember these

• pd.read_csv reads a table. isna().sum() counts the missing values (NaN).
• dropna drops rows. fillna fills values. Think before you do either.
• df[condition] picks rows. Join conditions with & and | in brackets. isin, sort_values and query help.
• loc is by label (a range includes both ends). iloc is by place.

Check yourself

Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.

1. How do you find the number of missing values in each column?

2. How do you pick the rows in which mass is above 10 and kind is "giant"?

3. What is the difference between loc and iloc?

4. Why is filling missing values with the mean not always a good idea?