Day 3 - Data Cleaning Topics

Welcome to the last day of the Intro to Python Bootcamp! You've learned a lot over the past couple days, starting with basic syntax and moving on to fully functional scripts that interact with external data. For this last day, we're going to take a look at some more practical topics and tasks that can be achieved with Python, including data cleaning, extraction, and visualization. Then, you're going to be working on a short project where you will implement everything you've learned.

Learning Objectives

In this notebook, you will learn to:

• Load and explore data using Pandas DataFrames and exploratory data analysis (EDA) techniques
• Identify data quality issues including missing values, incorrect data types, duplicates, and inconsistent formatting
• Handle missing data strategically by choosing appropriate methods for different columns (filling, dropping, deriving from related fields)
• Recognize and remove duplicate records using composite keys (combinations of multiple fields) to identify true duplicates
• Convert data types appropriately to preserve data integrity (e.g., keeping leading zeros in ZIP codes by storing as strings)
• Use Pandas methods for practical data manipulation (.fillna(), .astype(), .drop_duplicates(), .groupby(), boolean indexing)
• Apply domain knowledge to make informed decisions about data cleaning strategies

About This Dataset

You'll be working with a realistic dataset intentionally containing common data quality issues. This hands-on approach means you'll encounter the types of problems data professionals face daily:

• Missing values represented in multiple ways (NaN, -999 sentinel values)
• Inconsistent text formatting and typos
• Duplicate records
• Data type mismatches (numbers stored as text, text stored as numbers)

By the end of this lesson, you'll have cleaned this dataset and learned transferable skills applicable to any real-world data cleaning project.

Examining Our Dataset

The data we will be working with for this session is a fictional dataset of registration survey responses for an upcoming health education workshop. Below are the details of the dataset:

Data Dictionary: Workshop Participants Dataset

Dataset Name: workshop_participants_east_coast.csv
Description: Contains registration profiles for 500 individuals signed up for an upcoming regional health education workshop. The dataset intentionally includes common real-world data anomalies to practice programmatic data cleaning, validation, and preparing geographic strings for geocoding.

Table Schema Summary

Session Setup

Before we begin working with data, we need to set up our environment by importing the necessary Python libraries. Each library provides specific functionality that we'll use throughout this lesson:

• Pandas (pd): Our primary tool for data manipulation and cleaning. It provides DataFrames (tabular data structures) and methods for filtering, transforming, and analyzing data.
• NumPy (np): Fundamental library for numerical computing. We'll use it for mathematical operations and handling special values like NaN (Not a Number).
• Matplotlib: Library for creating visualizations and plots to help us understand data patterns.
• GeoPy: Tools for geocoding (converting addresses to geographic coordinates), which we may use later for geographic analysis.

Run the following cell to import these modules into your notebook environment.

If you are working in your own environment or encounter module import errors, make sure you have installed all required dependencies. You can install them using:

pip install -r requirements.txt

Pro-tip: You can also run bash commands directly from within your notebook by prefixing a command with !. For example:

!pip install -r requirements.txt

This is useful for installing packages on-the-fly without leaving your notebook environment.

Using Exploratory Data Analysis to Find Data Quality Issues

Leveraging Data Exploration Tools

Before diving into data cleaning, we need to understand the structure and content of our dataset. In this section, we've imported the CSV file containing workshop participant registration data into a Pandas DataFrame. This gives us a structured, tabular format that allows us to easily analyze and manipulate the data using Python.

The first thing we're going to do is generate some basic info about our DataFrame using the .info() method. If we didn't have the data dictionary provided above, we could still use this method to give us some initial facts about the number of rows, columns, and data types of those columns:

Exploring Dataset Metrics

The .info() method provides a great high-level overview, but sometimes we need to dig deeper into specific aspects of our data. Below are some methods for extracting individual pieces of information from your DataFrame that can help with analysis and troubleshooting:

• .shape - Returns the dimensions (number of rows, number of columns) as a tuple
• .columns - Shows the names of all columns
• .dtypes - Displays the data type of each column
• .isnull().sum() - Counts missing values per column
• .describe() - Generates summary statistics for numeric columns

Discussion

Take a look at the summary statistics and the datatype tables. What are some things you notice about the calculations? How might we fix them?

A note about DataTypes in Pandas

In Pandas, data types (or "dtypes") determine how data is stored in memory and what operations can be performed on it. The most common types include:

• int64: Integer numbers (whole numbers with no decimal point)
• float64: Floating-point numbers (numbers with decimals)
• object: Typically used for text strings, but can hold any Python object; this is the default for mixed data types
• bool: Boolean values (True or False)
• datetime64: Date and time values

Choosing the correct data type is important because it affects:

1. Memory usage: Integer types use less memory than float types, which use less than object types
2. Operations available: Some operations only work on specific types (e.g., you can't average text strings)
3. Data integrity: Storing phone numbers or IDs as integers can lose leading zeros (e.g., "04184" becomes 4184)

In our dataset, we notice that Participant_ID and Zip_Code are stored as numeric types, but they should be strings to preserve leading zeros and prevent unwanted mathematical operations.

Converting Data Types

Now that we've identified the data quality issues, let's start cleaning. One of the first steps is to fix the data types for columns where the current type doesn't match the data's purpose:

• Participant_ID: Should be a string (not a number) because IDs are identifiers, not values to calculate with
• Zip_Code: Should be a string to preserve leading zeros (e.g., "04184" should not become "4184")
• Age: Should be an integer (whole number) rather than float, since ages are whole numbers and the decimal values are artifacts of our missing data handling

We'll also need to handle the sentinel value -999 that represents missing age data. The .fillna() method allows us to replace NaN (missing) values, and .astype() converts between data types.

Now we can see age is the only numeric column being calculated in the summary stats. The string conversion on Zip_Code still preserved the .0 decimal place at the end, but we'll take care of that in the "String Functions" section.

Data Issue #1: Missing Values

Understanding Missing Data

Missing data is one of the most common data quality issues in real-world datasets. It can occur due to various reasons: incomplete form submissions, system errors, non-applicable responses, or intentional redactions. Before we can proceed with analysis, we need to decide how to handle these missing values.

There are several strategies for dealing with missing data, each with different trade-offs:

The best approach depends on:

1. How much data is missing (5% vs 50%?)
2. Why it's missing (random or systematic pattern?)
3. What column it is (critical identifier vs optional text?)
4. Your analysis goals (descriptive statistics vs machine learning?)

In this dataset, we'll use a mixed approach based on the column's importance:

Data Issue #2: Duplicate Values

Identifying Duplicate Registrations

Duplicate entries are another common data quality issue. They can occur when:

• The same person registers multiple times (accidental double-registration)
• Data is merged from multiple sources without deduplication
• Test records are left in production datasets

In our dataset, the high frequency of certain names may indicate duplicate registrations. While common names, cities, and states are expected in any dataset, and even duplicate addresses can occur (e.g., apartment buildings), repeatedly seeing the exact same name suggests potential duplicates.

However, simply filtering by name is insufficient. After all, there can legitimately be multiple people with the same name. We need to combine multiple columns to identify likely duplicates:

Our approach:

1. Look at name frequencies to spot potential duplicates
2. Combine Name with other fields (Age, Phone) to create a "composite key"
3. Find rows where this composite key is identical
4. Examine the full records to decide if they're true duplicates
5. Remove duplicates while keeping the first occurrence

Let's investigate by filtering for rows where the Name equals "Evelyn Davis" to see if the other information matches:

When you write survey_data["Name"] == "Evelyn Davis":

• survey_data["Name"] selects the Name column from the DataFrame.
• That selection is a pandas Series — a one-dimensional labeled array with the same row index as the DataFrame.
• The == "Evelyn Davis" comparison is applied elementwise to the Series.
• The result is another Series of boolean values (True or False), one for each row.
• Each True means that row has Name equal to "Evelyn Davis"; each False means it does not.

So the code produces a boolean mask Series, not a filtered DataFrame. If you want the matching rows, you would use that mask to index survey_data, for example:

Analysis: Are These Duplicates?

Looking at the results above, we can see that all the "Evelyn Davis" records have different values in almost every other column:

• Different ages (27, 61, 23, 52, 46)
• Different phone numbers
• Different addresses and cities
• Different workshop goals

This suggests these are actually different people who happen to share the same name—not duplicates. So simply filtering by name is not a reliable deduplication strategy.

Finding a Unique Identifier

To reliably identify true duplicates, we need to find a combination of fields that would uniquely identify a person. Some possible strategies:

For this dataset, we'll use Name + Age + Phone as our composite key. While there's a small chance this could miss some duplicates (e.g., if someone registered with and without a phone number), it's a good balance between specificity and practicality. The likelihood of the same person registering twice with identical name, age, AND phone number in a sample of 500 is relatively low.

Grouping by Multiple Columns

Now that we've established that Name alone is insufficient for identifying duplicates, let's combine Name with Age and check how many times each combination appears. The .groupby() method groups rows by one or more columns and allows us to count occurrences within each group.

Looking at the output:

• Evelyn Rodriguez, age 49 appears 4 times (most frequent Name+Age combo)
• Several other combinations appear 2 times
• Most combinations appear only once

Let's examine the top duplicate candidate (Evelyn Rodriguez, 49) more closely to see if these are true duplicates or just coincidences:

Even though we have 4 people with the same name and age, we have two sets of different phone numbers and addresses. From this data, do you still think it's the same Evelyn Rodriguez?

• the combination of name, age, and city/state could suggest that it is still the same person
• different phone number and address could signal a recent move since the time they first registered for the workshop (phone number is likely for a home phone)
• think of the data source: online survey or registration tool could have taken their location and coded 2 different addresses
• Likely the same person, but we still have duplicate entries based on the two addresses
• We are forced to make an assumption in this case (name + age + phone gives you the unique person), but it will result in Evelyn still showing up on the list twice. This is where some manual work during or after the workshop will have to be done (There is still the possibility of these actually being two separate people!)

Let's look at the duplicated rows based on this criteria

Removing Duplicate Records

Now that we've identified our deduplication strategy—treating Name, Age, and Phone as a composite unique identifier—we can use the .drop_duplicates() method to remove duplicate records.

The .drop_duplicates() method has two important parameters:

• subset: Which columns to check for duplicates (we specify Name, Age, Phone)
• keep: Which copy to keep when duplicates are found:
  • 'first' - keeps the first occurrence (most common)
  • 'last' - keeps the last occurrence
  • False - removes all occurrences of duplicates

After deduplication, we'll verify our results by grouping by the same columns to ensure there are no more duplicates (each count should be 1).

Data Issue #3: Text Formatting and Invalid Values

After handling missing values and removing duplicates, we still have text-level quality issues that can break analysis and reporting.

Common issues remaining in this dataset include:

• Multiple representations of missing/invalid values (None, "None", -999, "0")
• Inconsistent formatting in text fields (capitalization, punctuation, abbreviations)
• Numeric-looking strings stored with artifacts (e.g., ZIP codes like 4184.0)
• Phone numbers in mixed formats (XXXXXXXXXX, XXX-XXX-XXXX, (XXX) XXX-XXXX)

Why this matters

Even when values look similar to humans, computers treat differently formatted strings as different categories.
For example:

• "(716) 800-7877" and "7168007877" are not equal as text
• "Philly" and "Philadelphia" split counts across categories
• ZIP codes without leading zeros can map to the wrong location

Cleaning goals for this section

We will standardize key text fields so they are analysis-ready:

1. ZIP codes: remove formatting artifacts and enforce 5-character strings with leading zeros.
2. Names: split full names into First_Name and Last_Name for easier filtering and matching.
3. Phone numbers: strip non-digits, handle invalid placeholders, and apply one consistent display format.
4. Workshop goals: normalize placeholder values before summarizing response categories.

This step improves consistency, reduces false mismatches, and prepares the dataset for downstream joins, geocoding, and visualization.

Splitting the Name Columns into First and Last

Now that the ZIP code column has been standardized, the next step is to break the full Name field into separate parts.

Why do this?

• It makes filtering and sorting easier
• It helps with matching records across datasets
• It gives us more flexible columns for analysis and reporting

We can use Pandas string methods to split the full name into two columns:

• First_Name
• Last_Name

The .str.split(expand=True) method separates the text on spaces and returns the result as a DataFrame, which we can rename and assign back to our dataset.

How we'll do it today:

Another way to do it is by joining the expanded columns back to the original dataframe:

Cleaning Phone Numbers

Phone numbers should be stored in a consistent format so they can be searched, compared, and validated correctly.

Our cleaning process will:

1. Remove non-numeric characters

   • Strip out parentheses, spaces, and hyphens
   • This leaves only the digits in each phone number

2. Handle invalid or missing values

   • Replace placeholders like -999 with a standard missing value such as None
   • Keep missing phone numbers as blank/placeholder values instead of forcing them into an invalid format

3. Reformat valid numbers

   • If a phone number has exactly 10 digits, convert it into the standard U.S. format: "(XXX) XXX-XXXX"
   • This makes the data easier to read and consistent across the dataset

This approach ensures phone numbers are stored in a clean, uniform format while preserving missing values appropriately.

Cleaning Workshop Goals Responses

Integrating Additional Datasets and File Types

Pandas has the capability to read in multiple different file types, including Excel workbooks. In the following example, we have an additional list of workshop participants that we received from a different source, and our task is to integrate it with our existing list. There is a possibility for some overlap between the two lists, so we're going to need to account for any duplicates.

Extracting Data from PDF Files with pdfplumber

Extracting data from PDF files can be tricky depending on their quality and source of the information. While some are completely digitally generated, which makes the text fully capable of being scanned and copied, others can be a converted image or scan of a page that makes any type of copying of the text impossible. In the case of the second situation, different technologies like OCR (Optical Character Recognition) may be deployed to extract certain visual elements from files.

In this next section, we will look at the PDFPlumber package in order to extract data from a PDF file. On your computer or in your browser, open the data/pdf/afr_contact_list.pdf practice file. We're going to create our dataset from the first 3 columns of this table. You'll notice that the values in the second column contain multiple individual's contact information, and we're only going to take the first one in each row and designate it as the primary coordinator.

Using a context manager to connect to the pdf, we can get a list of all of the pages contained in the file.

Each item in this list is a special object created by pdfplumber that represents that particular page of the document. This object comes with several methods that can extract the content from the page in several forms according to your needs:

• page.extract_text() - Extracts all text, preserving line breaks, spacing, and other formatting
• page.extract_words() - Extracts the exact locations of every character, word, and shape
• page.extract_table()/page.extract_tables() - Extracts tables as a nested list of rows that can easily be converted into a pandas dataframe.
• page.images - Returns a list of the images found on the page.

We're going to use the .extract_table() method because there is only one table on each page. Here is the core workflow:

Once we have our list of rows, we can easily pass it to a pd.DataFrame() object to create a dataframe. Note that we don't have any named columns brought over yet from the original source, so the columns get a numbered value instead.

You'll notice that our second column contains the list of multiple coordinators, which is represented as a string formatted with line breaks. We can target this column and use a string method like .split() to separate each piece into its own column

Because we're only taking the first person from each list, we will only select the first two columns, which will later be designated as primary_coordinator and coordinator_phone. We will also manually fix the blip in the second row that resulted from the extra formatting breaking our structure.

We will then construct a new dataframe made up of two columns from our original afr_contact_list dataframe and the two new columns we created from splitting up the coordinator column

Here's a more efficient version of the entire workflow coded using functions:

Now let's say that we have a folder full of these types of files, and we wanted to write a script to automate the processing of each file and output the master list as a CSV file. We would simply use these same functions we created to loop through each file:

Exercise 1: PDF Scraping Practice

In the data/pdf/ folder, you'll find a collection of PDF files from various sources. Select 1-2 files and practice scraping tables and portions of text from them.

As an added challenge, you can find a specially formatted piece of text, like a table of contents, and write a function that will convert it to tabular data.

Exercise 2: Combining Health Workshop Participants list

For the upcoming health education workshop, you've been given 3 lists of registered participants from different sources. Your task:

• Combine these lists into a single participant table
• Perform the necessary data cleaning steps, such as deduplication, handling missing values, and clearing out invalid values.
• Create a PDF file of the final consolidated list for staff to print and sign people in on the day of the workshop
• Add a column for workshop staff to check off with a pen when they record the attendance of a participant

Bonus Challenge

Write a script that will process an entire folder of participant lists stored in various file formats and combine them into a single Excel file.