Data Cleaning Strategies for Administrative Data

A Systematic Workflow for Data Cleaning

Before diving into code, it's helpful to have a structured approach. A random approach to cleaning can create more problems than it solves. A clear, repeatable process ensures you are thorough and efficient.

To get a sense of what such a process looks like, please read the following article. It outlines a five-step workflow and provides a great overview of the kinds of problems we'll be solving today.

As the article highlights, a good workflow involves inspecting the data to understand its flaws before you begin cleaning.

Step 1: Inspect and Diagnose

Your first task with any new dataset is to conduct a thorough inspection. This diagnostic phase helps you create a concrete list of cleaning tasks. Several pandas methods are indispensable for this.

To summarize, this "forensic audit" involves:

• df.info(): To check data types (Dtype) and identify missing values at a high level. For example, a ZIP code column read as an integer (int64) instead of a string (object) can lose leading zeros.
• df.value_counts() or df.nunique(): To explore categorical columns. A high number of unique values in a column that should have few (e.g., 'State') often indicates typos, inconsistent capitalization, or extra whitespace.
• df.describe(): To get statistical summaries of numerical columns. This is where impossible values, like a negative age or an out-of-range performance score, will become apparent.
• df.duplicated().sum(): To count the number of duplicate rows. Administrative systems can sometimes create duplicate records that need to be removed.

Now that we know how to find the problems, let's learn how to fix them.

Step 2: Address Structural and Standardization Issues

Once you have your cleaning checklist, a good place to start is with structural issues that affect the entire DataFrame.

Standardizing Column Names

Inconsistent column names (e.g., Child ID, child_id, ChildId) make code hard to write and read. The best practice is to enforce a consistent style, such as snake_case (all lowercase, with underscores instead of spaces).

# Assume df is your DataFrame
# Before: ['First Name', 'last name', 'Date Of Birth']




# Clean the column names
df.columns = df.columns.str.lower().str.replace(' ', '_').str.strip()




# After: ['first_name', 'last_name', 'date_of_birth']

For more targeted renaming, the .rename() method is very useful.

Correcting Data Types

As we saw during inspection, pandas can sometimes infer the wrong data type. A common issue in policy data is with identifiers like FIPS codes or ZIP codes, which should be treated as strings but might be read as numbers. You can force a column to a specific type using .astype().

# A column of ZIP codes might be read as integers, losing leading zeros.
# For example, '07652' becomes 7652.
# Let's assume the column is named 'zip_code'




# Convert it to string
df['zip_code'] = df['zip_code'].astype(str)




# If you need to pad with leading zeros to a fixed length (e.g., 5 digits for a ZIP code)
df['zip_code'] = df['zip_code'].str.zfill(5) 

Removing Duplicate Records

Removing duplicate entries is usually straightforward with df.drop_duplicates(). This is crucial for ensuring that counts of individuals or cases are accurate.

Step 3: Clean Inconsistent Data Entry

Data entry errors are the most common and varied type of "messy" data. This can include everything from simple typos to complex formatting inconsistencies.

Standardizing Categorical Values

In administrative data, the same category can be entered in many different ways (e.g., 'Y', 'y', 'Yes', 'yes'). Your goal is to map all these variations to a single, standard format. The .map() and .replace() methods are perfect for this.

• map() is useful when you want to replace all values in a series based on a dictionary. Values not in the dictionary will become NaN (Not a Number).
• replace() is often safer, as it only replaces the values you specify and leaves others unchanged.

Watch how map() is used to convert 'Yes'/'No' strings to boolean True/False values. This is a common task when preparing data for modeling.

For more general replacements, .replace() is a great tool. This next video clip shows it in action, along with cleaning up extra characters using string methods.

Parsing and Splitting Combined Columns

Sometimes, multiple pieces of information are packed into a single column, like county_name, state_abbr. To analyze these separately, you need to split them. The .str.split() method is the tool for this job.

This technique is invaluable in your work. For example, AFCARS data often includes a FIPS code that combines state and county information, which you'll need to split for county-level analysis.

A Quick Word on the SettingWithCopyWarning

When you start modifying DataFrames, you will almost certainly encounter the SettingWithCopyWarning. This warning from pandas is trying to tell you that you might be modifying a temporary copy of the data, not the original DataFrame, so your changes might not stick.

This typically happens when you chain indexing operations, like df[df['column'] > 5]['other_column'] = 'new_value'.

The correct, unambiguous way to assign values is to use the .loc indexer.

df.loc[df['column'] > 5, 'other_column'] = 'new_value'

Conclusion

You've just worked through some of the most common and critical data cleaning tasks. This is where raw data begins its transformation into a reliable source for analysis.

Here are the key takeaways from this lesson:

• Adopt a systematic workflow: Always start by inspecting your data (.info(), .describe(), .value_counts()) to diagnose problems before you start cleaning.
• Standardize your DataFrame's structure: Ensure column names are consistent and data types are correct (.astype()).
• Tackle inconsistent data entry: Use string methods (.str.lower(), .str.strip(), .str.replace()) and mapping (.map(), .replace()) to standardize categorical data.
• Deconstruct compound columns: Use .str.split(expand=True) to separate information packed into a single column.
• Assign values safely: Always use .loc for assignments to avoid the SettingWithCopyWarning and ensure your changes are applied correctly.

In our next lesson, we will put this clean data to use. We will focus on how to merge child welfare data with demographic indicators from different geographic levels (e.g., tract, county). The clean, standardized identifiers you created in this lesson will be essential for making those merges successful.