Lesson · 18 min

Clean messy data

Real files have mistakes

The files of the first lessons were clean. Real files are not clean. People type the data, programs export it in different ways, and machines fail. Select the tab raw_trips.csv and look for these problems:

A value has spaces around it: harbor gate.
The same value has different capital letters: member, Member, MEMBER.
A number is missing, or the value is not a number: an empty value, and n/a.
A number cannot be true: a trip of 0 minutes, and a trip of -4 minutes.
One row is in the file two times.

If you do not fix these problems, your answers are wrong. A count by rider gives member and Member as two rider types. int("n/a") stops your program.

Two rules

Do not change the source file. Write code that makes clean rows from the raw rows. You can run the code again when a new file arrives, and other people can read what you did.
Count the rows that you remove. A person who reads your result must know how many rows you left out. "Kept 9 of 14 rows" is a part of the answer.

Clean text

You know these tools from the Python course.

Code	Result
`" Old Mill ".strip()`	`"Old Mill"`
`"MEMBER".lower()`	`"member"`
`"old mill".title()`	`"Old Mill"`
`" old mill ".split()`	`["old", "mill"]`
`" ".join(["old", "mill"])`	`"old mill"`

split() with nothing in the parentheses cuts at each group of spaces, and it ignores the spaces at both ends. So " ".join(text.split()) removes every extra space in one step.

Numbers that can fail

int("n/a") raises a ValueError, and the program stops. With try and except, your code handles the error and continues.

def to_number(text):
    try:
        return int(text)
    except ValueError:
        return None

Python runs the block under try. If a ValueError occurs there, Python runs the block under except. If no error occurs, Python skips the except block.

None is the value that means "no value". Compare with is and is not.

number = to_number("n/a")
if number is None:
    print("This is not a number.")

int accepts spaces around the digits, so int(" 19 ") is 19. It does not accept "19 min", "3.5", or "".

Rows that repeat

Keep a set of the ids that are already in your result. A set tells you quickly whether a value is in it.

seen = set()
unique = []
for row in rows:
    if row["trip_id"] not in seen:
        seen.add(row["trip_id"])
        unique.append(row)

Your turn

main.py reads raw_trips.csv into the list raw. Every value in raw is text. Write three functions, and then use them. Do the tasks in order.

Tasks

5 to do

Task 1, not checked yet. Write a function clean_name(text). It removes the spaces at both ends, it leaves one space between two words, and it writes each word with a capital letter first. clean_name(" harbor gate") returns "Harbor Gate".
Task 2, not checked yet. Write a function to_minutes(text). It returns the whole number in the text. It returns None when the text is not a whole number, or when the number is less than 1.
Task 3, not checked yet. Write a function clean_rows(rows). It returns a new list of rows. In each row, start and end go through clean_name, rider has no spaces around it and only lower-case letters, and minutes is the result of to_minutes. It leaves out a row when to_minutes gives None. It leaves out a row when its trip_id is already in the result.
Task 4, not checked yet. Make a list clean with the clean rows of raw. Use clean_rows.
Task 5, not checked yet. Print the line Kept 9 of 14 rows. Take the two numbers from the lengths of clean and raw.

Task 1 of 5

Write a function clean_name(text). It removes the spaces at both ends, it leaves one space between two words, and it writes each word with a capital letter first. clean_name(" harbor gate") returns "Harbor Gate".

All tasks