What does a row in a table represent, and how do I check?

Before you read anything else, answer these three questions. They don't count for anything: they're there so that at the end of the course you can compare what you think today with what you think then. Pick the first thing you would do at work; your Maestro tells you why in a couple of lines, and the course comes back to each one later.

Check yourself: In the trip table, 73 rows have no birth year. What do you do first?

Check yourself: The average trip duration is 34 minutes. What does that tell you about a typical trip?

Check yourself: One trip lasts 1,452 minutes: more than 24 hours. What do you do?

The first question: what does a row represent?

It seems obvious and almost never is. A row can be a person, a purchase, a product inside a purchase or one day at one branch. Everything else depends on that answer: if you count rows, you count that; if you add up a column, you add up that.

Throughout the course you work with the trip table from Rueda, a bike-share system. Rueda and its data are made up (synthetic data, CC0), but the table has the same problems as the ones at your job. Imagine your boss just sent it to you with a message: "Take a look and tell me what you see before we build the monthly report."

Start by looking at real rows, not summaries. In the exercise, data is already the Rueda table, loaded with .

Run the cell and read the ten rows as if each one were something that happened on the street. Then change head(10) to sample(8, random_state=3) to see rows from the middle and the end, not just the first ones. You're done when you can say in your own words what happened in any one row.

Your code (Python)

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

Data: viajes-rueda, 311 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "viajes-rueda". Your edits run with your code.
Row										Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 16

Run it yourself

Read a row out loud. The first one says: "On March 2 at 8:11 a.m., someone with a membership took bike B-0717 from Terminal de Autobuses and left it at Parque Alameda 24.7 minutes later." That's a trip. What you just did with sample has a name: a . The first rows of a table tend to be the cleanest, so it pays to look at the middle and the end too.

If a row is a trip, two things should hold:

Each viaje_id appears exactly once. It's the trip's identifier, and each trip happens once.
bici_id can repeat. A bike makes many trips in two weeks.

Before checking it on the Rueda table, practice on one with five rows. Write down what you think each line prints and why; then it runs and you compare.

A five-row table with a hidden problem.

Rows, trips and bikes

Example 1 of 2

example.py

import pandas as pd

viajes = pd.DataFrame({
    "viaje_id": [501, 502, 502, 503, 504],
    "bici_id": ["B-01", "B-07", "B-07", "B-01", "B-03"],
    "duracion_min": [12.5, 20.1, 20.1, 8.0, 31.4],
})

print(len(viajes))
print(viajes["viaje_id"].nunique())
print(viajes["bici_id"].nunique())

What three numbers does it print?

Why?

A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.

Example 2 of 2, opens once you explain this one

Make your prediction

Now on the full table. The code counts the rows and the distinct values of viaje_id. If a row is a trip, the two numbers should be equal.

Run the cell and compare the two numbers. If they don't match, add a line at the end that shows the repeated rows: data[data.duplicated(keep=False)]. You're done when you can see the repeated rows and know how many distinct trips there are.

Your code (Python)

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

Data: viajes-rueda, 311 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "viajes-rueda". Your edits run with your code.
Row										Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 16

Run it yourself

The table has 311 rows but 308 distinct trips. Three trips (40327, 40456 and 40465) appear twice, identical in every column. If someone counts rows to report "trips in the period," they report 311 when it's 308. Here the difference is small; in a table with thousands of copies it wouldn't be, and nobody would notice by looking at the total.

Notice what you did not do: you didn't delete anything. You don't know yet why they're repeated. It could be an export error, or the system could be logging a restarted trip twice. Whoever knows the system knows; you don't yet. For now, your job is to write it down.

Start your log

An exploratory analysis leaves two things behind: what you now know about the table and what you still have to ask. Both get lost if you don't write them down. Throughout the course you'll keep a log: a short list of observations, each with its evidence and the question it leaves you with. It can be a text file, a notes cell in your notebook or a sheet of paper; in the last module you write it in a project file.

Your first entry could look like this:

Observation: there are 3 repeated rows, identical in every column (trips 40327, 40456 and 40465). Evidence: 311 rows and 308 distinct values of viaje_id. Question: does the system export some trips twice? For the report, do we count 308?

The question matters as much as the observation. A log with only observations is an inventory; with questions, it's what you bring to your boss so the two of you can decide what to do.

If you have a table from your job in mind, tell your Maestro what columns it has and ask what a row would represent.

Try it on another table

The same question works for any table. Try it on a sales table.

Check yourself: A sales table has the columns ticket_id, producto and cantidad, and the same ticket_id appears on several rows. What does a row represent?

pandas: DataFrame.duplicated, in the official documentation