TDM 10100: Project 5 — Fall 2026

Introduction to Data Cleaning and Data Wrangling in Python and R

Motivation: We learn about indexing, missing data, and how to access data stored in rows and columns, in both Python and R

Context: Both languages provide several types of reading in data and storing data in data frames. Missing data is a common feature in real-world data.

Scope: data frames

Learning Objectives:

Understand how to work with data in data frames

Please use the template found here, and the important information about project submissions here, when you are ready to submit your work.

Dataset(s)

This project will use the following datasets:

/anvil/projects/tdm/data/craigslist/vehicles.csv
/anvil/projects/tdm/data/zillow/cities_crosswalk.csv
/anvil/projects/tdm/data/zillow/State_time_series.csv
/anvil/projects/tdm/data/taxi/green/green_tripdata_2019-06.csv
/anvil/projects/tdm/data/fars/2021/accident.csv

In this project, please only use 2 cores in your Jupyter Lab session:

notebook.anvilcloud.rcac.purdue.edu/

(do not use 4 cores or 16 cores for this project)

Questions

Question 1 (2 pts)

Please use the seminar-r kernel for questions 1, 2, 3, and then switch to the seminar kernel for questions 4 and 5. Work in R in questions 1, 2, 3, and then work in Python for questions 4 and 5.

In R, we can check whether elements of a column are in a given set of values. For instance, in these examples, we check whether the state of some breweries are in a list of states:

the-examples-book.com/projects/fall2026/10100/project5examples1

After you finish considering those examples, read the data from the Craigslist vehicles data into an R data frame, using fread:

/anvil/projects/tdm/data/craigslist/vehicles.csv

Verify that there are 426880 rows and 26 columns in the Craigslist vehicles data set.

Check that tolower(state.abb) gives the lowercase version of the 50 states.

Now check that there are only 2970 rows for which state is not one of the 50 states.

Demonstrate that for all 2970 of these remaining rows, the state is equal to dc.

Deliverables

Verify that there are 426880 rows and 26 columns in the Craigslist vehicles data set.
Now check that there are only 2970 rows for which state is not one of the 50 states.
Demonstrate that for all 2970 of these remaining rows, the state is equal to dc.
As always, be sure to document your work from Question 1 (and from all of the questions!), using some comments and insights about your work. We will stop adding this note to document your work, but please remember, we always assume that you will document every single question with your comments and your insights.

Question 2 (2 pts)

Verify that there are 25341 rows and 4 columns in the the Zillow cross walk data:

/anvil/projects/tdm/data/zillow/cities_crosswalk.csv

Now find the only row for which the State column is not given in state.abb. (There is only 1 such row!)

Deliverables

Verify that there are 25341 rows and 4 columns in the the Zillow cross walk data.
Find the only row for which the State column is not given in state.abb.

Question 3 (2 pts)

This time, consider the Zillow State time series data:

/anvil/projects/tdm/data/zillow/State_time_series.csv

Instead of state abbreviations, the state names are given in full. Notice that R provides a list of state abbreviations, given as state.name.

First, observe that there are 13212 rows and 82 columns in the Zillow State time series data altogether.

Then observe that there are 2835 rows for which the RegionName is not given in state.name.

If you check the head of those rows, you will see that the names of the RegionName values do not have spaces. To remove the spaces from state.name we can use gsub(" ", "", state.name).

Now observe that there are only 328 rows for which the RegionName is not given in gsub(" ", "", state.name), namely, 233 values equal to DistrictofColumbia and 95 values equal to UnitedStates. Hint: You can achieve this last part by either writing

table(myDF[!(myDF$RegionName %in% gsub(" ", "", state.name)), ]$RegionName)

or by writing:

table(subset(myDF, !(RegionName %in% gsub(" ", "", state.name)))$RegionName)

Deliverables

First, observe that there are 13212 rows and 82 columns in the Zillow State time series data altogether.
Then observe that there are 2835 rows for which the RegionName is not given in state.name.
Now observe that there are only 328 rows for which the RegionName is not given in gsub(" ", "", state.name), namely, 233 values equal to DistrictofColumbia and 95 values equal to UnitedStates.

Question 4 (2 pts)

Now switch from the seminar-r kernel to the seminar kernel, and work in Python, for questions 4 and 5.

In Python, we can read in data in csv (comma-separated-values) format, as seen in earlier projects, or we can even read in data in Parquet format (a compressed format). For instance, consider some examples in which we learn a little about some Yellow Taxi Cab data from September 2025:

the-examples-book.com/projects/fall2026/10100/project5examples4

After you finish considering those examples about Yellow Taxi Cab data, we can now turn our attention to some Green Taxi Cab data, stored in csv format, from June 2019:

/anvil/projects/tdm/data/taxi/green/green_tripdata_2019-06.csv

Find the 7 values of DOLocationID that each occur 10000 or more times in the Green Taxi Cab data.

How many Green Taxi Cab rides had only 1 passenger? How many Green Taxi Cab rides had 2 passengers?

How many Green Taxi Cab rides had a tip that was 21 dollars or greater?

Deliverables

Find the 7 values of DOLocationID that each occur 10000 or more times in the Green Taxi Cab data.
How many Green Taxi Cab rides had only 1 passenger?
How many Green Taxi Cab rides had 2 passengers?
How many Green Taxi Cab rides had a tip that was 21 dollars or greater?

Question 5 (2 pts)

Now, read the examples about the CMS Diabetes Prescriber data:

the-examples-book.com/projects/fall2026/10100/project5examples5

Afterwards, consider the FARS 2021 accident data:

import pandas as pd
myDF = pd.read_csv("/anvil/projects/tdm/data/fars/2021/accident.csv", encoding="latin1")

(The latin1 is used because some of the data in this data set has a slightly different encoding than usual.)

The state names are not available by default in Python, but we can make a list of them:

states = [
    "Alabama", "Alaska", "Arizona", "Arkansas", "California",
    "Colorado", "Connecticut", "Delaware", "Florida", "Georgia",
    "Hawaii", "Idaho", "Illinois", "Indiana", "Iowa",
    "Kansas", "Kentucky", "Louisiana", "Maine", "Maryland",
    "Massachusetts", "Michigan", "Minnesota", "Mississippi", "Missouri",
    "Montana", "Nebraska", "Nevada", "New Hampshire", "New Jersey",
    "New Mexico", "New York", "North Carolina", "North Dakota", "Ohio",
    "Oklahoma", "Oregon", "Pennsylvania", "Rhode Island", "South Carolina",
    "South Dakota", "Tennessee", "Texas", "Utah", "Vermont",
    "Virginia", "Washington", "West Virginia", "Wisconsin", "Wyoming"
]

By studying the example we provide, find out how many rows of the FARS 2021 accident data are not from the 50 states. What location are those (other) rows of the FARS 2021 from?

Deliverables

Find out how many rows of the FARS 2021 accident data are not from the 50 states. What location are those (other) rows of the FARS 2021 from?

Submitting your Work

Please make sure that you added comments for each question, which explain your thinking about your method of solving each question. Please also make sure that your work is your own work, and that any outside sources (people, internet pages, generative AI, etc.) are cited properly in the project template.

Prior to submitting your work, you need to put your work into the project template, and re-run all of the code in Jupyter Lab and make sure that the results of running that code is visible in your template. Please check the detailed instructions on how to ensure that your submission is formatted correctly. To download your completed project, you can right-click on the file in the file explorer and click 'download'.

Once you upload your submission to Gradescope, make sure that everything appears as you would expect to ensure that you don’t lose any points. We hope your first project with us went well, and we look forward to continuing to learn with you on future projects!!

Items to submit

firstname_lastname_project5.ipynb

It is necessary to document your work, with comments about each solution. All of your work needs to be your own work, with citations to any source that you used. Please make sure that your work is your own work, and that any outside sources (people, internet pages, generative AI, etc.) are cited properly in the project template.

You must double check your .ipynb after submitting it in gradescope. A very common mistake is to assume that your .ipynb file has been rendered properly and contains your code, markdown, and code output even though it may not.

Please take the time to double check your work. See here for instructions on how to double check this.

You will not receive full credit if your .ipynb file does not contain all of the information you expect it to, or if it does not render properly in Gradescope. Please ask a TA if you need help with this.