Reading a Table with Python

#The Table We Will Read

A tea stall owner writes down how many cups were sold each day, the temperature at noon, and whether it rained. One day, the cups were never written down, so that cell is empty.

In one sentence: this is a table of 8 days with one fact missing, and we will ask it four questions.

#Question 1: How Many Rows, and How Many Are Empty?

Why count the empty cells? Because an average of "7 values" and an average of "8 values" are different, and a program that quietly skips the empty one can hide the problem from you.

In one sentence: count the rows, count the empty cells, and keep the difference in mind before any average.

#Question 2: What Is a Typical Value?

Take only the 7 cups values you have: 40, 44, 61, 38, 70, 66, 42.

Notice that the mean (51.6) and the median (44) are not the same. The three big days (61, 66, 70) pull the mean upward, while the median ignores how big the big values are and only cares about position. When the two differ a lot, the values are not bunched evenly, and it is worth finding out why.

In one sentence: the smallest, biggest, mean and median each describe a column in a different way, and the gap between mean and median is a clue.

#Question 3: Do Rainy Days Differ From Dry Ones?

This question splits the table into groups. Group the rows by the rain column.

Rainy days sell about 65.7 cups and dry days sell about 41.0. That is a real difference in this table, and it is the kind of link a model could use: rain is a feature that helps guess the label, cups sold. With only 8 rows, though, it is a hint and not a proof.

This is why the empty cell deserves thought. Two fills give two different answers: the overall mean (51.6) or the rainy-day mean (65.7). Neither is the true value, because the true value is unknown. Which one is more honest depends on the question you ask next, and a later lesson in this track on data quality treats this properly. For now, remember that filling in a number is a decision, not a free repair.

In one sentence: splitting rows into groups and comparing the groups is how a table answers "does this fact change that fact?"

#Do It in Python

Do: Run the cell. Then change Thursday's None to 55 and run it again.

Notice: The first run prints missing: 1, a smallest of 38 and a biggest of 70, a mean of 51.6 and a middle value of 44, then 65.7 cups for rainy days and 41.0 for dry days. After you give Thursday a value, missing becomes 0 and the mean changes.

Why: The code does exactly the hand steps above: pull a column, drop the empty cells, add, divide, and group by a fact. The data is made up and tiny, so the rain link is a hint and not a proven rule.

If you work with tables often, you will use a library called pandas, which does these steps in one line each. It is too large to run in your browser here, so the cell below is a read-only copy. Copy it into Google Colab if you want to try it. It prints the same numbers.

# Copy to Colab (needs pandas; it does not run in the browser here)
import io
import pandas as pd

csv_text = """day,cups,temp,rain
Mon,40,24,no
Tue,44,22,no
Wed,61,15,yes
Thu,,18,yes
Fri,38,26,no
Sat,70,14,yes
Sun,66,16,yes
Mon2,42,23,no
"""
# CSV stands for comma-separated values: one row per line, cells split by commas.
df = pd.read_csv(io.StringIO(csv_text))
print(df.shape)                                  # (8, 4): 8 rows, 4 columns
print(df["cups"].isna().sum())                   # 1 empty cell
print(df["cups"].describe().round(1))            # count, mean, min, median (50%), max
print(df.groupby("rain")["cups"].mean().round(1))  # no 41.0, yes 65.7

In one sentence: a few lines of Python repeat every hand step, and a library such as pandas shortens them further.

#Look Before You Fix

Real tables have more than one kind of problem. Empty cells are one. A value that cannot be right, such as an age of -5, is another. The same row written twice is a third. A date written two different ways is a fourth.

The practice table below has all four, each marked with a colour. Find them first. Only then use the buttons to fix them.

Do: Read the table and find the cells that are coloured. Match each colour to the legend. Then use the cleaning buttons one at a time and watch the score.

Notice: Each button fixes one kind of problem, and the quality score rises as problems go away. Some fixes change the rows you have, and the table you end with is not the table you started with.

Why: Spotting a problem comes before fixing it, because a fix is a decision about your data. The table is a small drawn example, not a real company's records.

In one sentence: find and name the problems in a table before you fix any of them.

#Key Takeaways

• Count the rows and the empty cells first, so you know how many values an average really uses
• The smallest, biggest, mean and median describe a column in four ways, and a gap between mean and median is a clue
• Splitting rows into groups and comparing the groups is how a table answers a question
• Filling an empty cell is a decision: the overall mean and a group mean give different answers
• A short Python cell repeats every hand step, and pandas shortens it, but you should be able to do the step by hand first

#Quick Check