Cleaning the Mess

Real-world data is rarely ready for immediate analysis. Files from environmental sensors, satellites, or crowdsourced databases often contain irregular formatting, missing observations, or incorrect data types. Skipping the data cleaning stage is dangerous, as it leads to errors in calculations and misleading visualizations.

In this section, we will learn standard techniques to programmatically clean a dataset using Pandas. We will return to our kloten_summer_2022.txt dataset, applying the skipping logic learned in previous sections to load a fresh DataFrame.

Take a close look at the table above. What is wrong with it?

1. The column names are uppercase and cryptic (YEARMODA, TEMP1).

2. There is a missing value (NaN) sitting right in the middle of our MAX temperature column.

3. (Hidden from view) The YEARMODA column is currently stored as a number, not a date or a string!

Let’s clean this mess up step-by-step.

1. Renaming Columns¶

The first step in data cleaning is often standardizing the column headers. Messy, cryptic, or inconsistently capitalized names (like YEARMODA or TEMP1) make code harder to write and read. Standardizing headers to lowercase, descriptive names without spaces is best practice.

We can rename columns using the .rename() method. To tell Pandas exactly what to change, we use a Python dictionary passed to the columns parameter. A dictionary stores key-value pairs mapping the old name (the key) to the new name (the value).

Let’s define descriptive, lowercase names for our Kloten data.

Output:

Index(['date', 'temp_max', 'temp_min', 'temp_10am', 'temp_2pm'], dtype='object')

2. Data Type Conversions¶

Pandas makes an educated guess about data types when reading a file, but it doesn’t always get it right. Sometimes, numbers are imported as text, or integers are imported as floats. Incorrect data types will break mathematical operations and plotting functions.

The key method for changing data types in Pandas is .astype().

Let’s check the current types of our DataFrame using .dtypes.

Output:

date           int64
temp_max     float64
temp_min     float64
temp_10am    float64
temp_2pm     float64
dtype:        object

We see that date is currently stored as an integer (int64). Since we intend to perform string slicing on the date in the next subsection, we must first convert it to a string.

Output:

date          object
temp_max     float64
temp_min     float64
temp_10am    float64
temp_2pm     float64
dtype:        object

The Integer Truncation Trap¶

You might also want to convert floating-point numbers into integers to save memory. However, you must be careful. When converting from float to int, Pandas does not round the numbers; it simply drops everything after the decimal point (truncation).

Look at what happens to the first value of our temp_max column (originally 19.6):

Output:

Original: 19.6
astype(int): 19 <-- Truncated!
round().astype(int): 20 <-- Correctly rounded!

3. String Slicing and Cleaning¶

Spatial datasets often contain important information embedded within text strings. For example, a single “timestamp” column might contain the year, month, and day combined. We need tools to “cut” or clean these strings.

String slicing¶

The date column now has the structure yyyyMMdd as a string (e.g., "20220601"). We can use the .str.slice() method to extract specific parts of this string to create new categorical columns.

Let’s extract the month information. Remember that Python indexing starts at 0, and the stop parameter is exclusive (meaning it stops before that index).

Here is a visual map of our string:

String:   2  0  2  2  0  6  0  1
Index:    0  1  2  3  4  5  6  7

To get the month (06), we need to start at index 4 and stop before index 6.

Concept Check: Slicing the Year¶

Based on the visual map above, how would you write the code to extract just the year (the first four characters) into a new column called year?

data["year"] = data["date"].str.slice(start=0, stop=4)

Other String Cleaning Methods¶

Now we have a clean month column we can use for grouping later. The .str accessor offers many other useful cleaning methods that you will use frequently with messy text data:

• .str.upper() or .str.lower() to standardize capitalization.

• .str.strip() to remove accidental invisible white spaces from the beginning or end of words.

• .str.contains("keyword") to search for specific substrings.

4. The Infamous NaN¶

Some datasets have gaps where observations were not recorded (perhaps a sensor lost power or a file was corrupted by the course instructor). Pandas represents these missing values using the marker NaN, which stands for Not a Number.

If we look back at our Kloten DataFrame above, we can clearly see a NaN at index 2 in the temp_max column. These markers behave differently than standard numbers. Most dangerously, they are “infectious”: any mathematical operation involving a NaN usually results in a NaN (e.g., 5 + NaN = NaN).

Pandas provides handy tools to detect missing values. Every Series has a .hasnans attribute which quickly tells you if the column contains any missing data:

Output:

True

Finding the Missing Data¶

Knowing that NaNs exist is good, but usually, we want to know exactly where they are and how many there are. We can use the .isna() method to do this.

By itself, .isna() generates a True/False mask. If we chain the .sum() method to it, Pandas will count up all the True values, telling us exactly how many NaNs are in the column:

Output:

There are 10 missing values in temp_max.

Even better, we can use .isna() as a boolean filter inside our selection brackets to isolate and view only the rows that are missing data!

5. Dropping and Filling Missing Data¶

Having missing data in your dataset is common, and you must decide how to handle it. The two primary strategies are to either remove (drop) the rows containing missing values or replace (fill) them with another value.

Dropping NaNs¶

We can remove missing data using the .dropna() function.

Let’s drop rows where temp_max is missing. We had 92 rows initially.

Output:

Original rows: 92
Cleaned rows: 88

4 rows were dropped.

Filling NaNs¶

Dropping data might not be optimal, as you lose other valuable information in those rows (like the temp_min or temp_10am recordings that might still be perfectly fine). Pandas allows us to fill missing values using the .fillna() function.

For mathematical data like temperatures, you must be extremely cautious about what you fill. While filling with a constant like 0 or -9999 might save the file without errors, it will dramatically alter your statistical analyses (e.g., making your average summer temperature artificially freezing!).

A better approach is often to fill NaNs with a representative statistic, like the average (mean) value of the rest of the column.

Notice index 2 is now successfully filled with the calculated average (25.845).

Interpolation: A Better Way for Time-Series¶

While filling with the overall average is okay for some datasets, it is actually quite dangerous for weather data.

Think about it: if a sensor breaks during a massive 35°C heatwave, filling that missing day with the overall summer average of 25°C will completely distort your heatwave analysis!

For time-series data (where the order of the rows matters), the best approach is to guess the missing value based on the day immediately before and the day immediately after. In Pandas, this is called linear interpolation, and we use the .interpolate() method.

Let’s look at the original data around our missing value at Index 2:

• Index 1 (June 2): 21.8

• Index 2 (June 3): NaN

• Index 3 (June 4): 24.3

Notice how clever Pandas is! It looked at the day before (21.8) and the day after (24.3), drew a straight line between them, and calculated the exact midpoint: (21.8 + 24.3) / 2 = 23.05. This is a much safer and more accurate way to clean environmental data than using the overall summer average.

Concept Check: To Drop or To Fill?¶

Imagine you are analyzing a dataset of 1,000 houses to see how the number of bedrooms affects the house price.

• Scenario A: 5 houses are missing their “Price” data.

• Scenario B: 200 houses are missing their “Year Built” data, but you really want to include age in your analysis.

Which strategy makes the most sense for each scenario?

Saving the Cleaned Data¶

Now that we have successfully sanitized our dataset by standardizing headers, fixing data types, and safely interpolating missing values, we need to save our hard work! This allows us to load the pristine data in the next lesson without having to rewrite all our cleaning code.

As you learned in the Getting Data In and Out section, we use the .to_csv() method for this.

6. Exercise: Sanitize the City Data¶

Real-world spatial and demographic data is rarely perfect. Let’s apply our cleaning skills to the global worldcities.csv dataset you downloaded in the previous section.

Because this specific CSV happens to be quite clean already, we are going to intentionally simulate a “messy” state in Step 3 so you can practice fixing it.

Tasks:

1. Load worldcities.csv into a DataFrame.

2. Select only these columns: city, country, population. (Crucial: Append .copy() to this selection so you don’t trigger a SettingWithCopyWarning later!)

3. Check the datatypes. Notice population is a float. Convert population to a string (astype(str)) to simulate a messy real-world file where numbers are accidentally stored as text.

4. Use .str.upper() to standardize all the city names to uppercase.

5. Use .str.strip() to remove any accidental leading or trailing white spaces from the city names.

6. Convert the population column (currently a simulated string) back into a float so we can do math on it later.

7. Crucial Check: Use .dropna() (using the subset parameter) to remove any cities where the population is missing (NaN).

8. Display the first 5 rows of the cleaned DataFrame, and print the total number of rows left to verify your drop worked.

import pandas as pd

# 1. Load the data
cities_df = pd.read_csv("worldcities.csv")

# 2. Select columns AND create a safe independent copy
cities_subset = cities_df[["city", "country", "population"]].copy()

# 3. Check types and simulate messy object state for population
cities_subset["population"] = cities_subset["population"].astype(str)

# 4. Standardize text (uppercase)
cities_subset["city"] = cities_subset["city"].str.upper()

# 5. Clean whitespace (strip)
cities_subset["city"] = cities_subset["city"].str.strip()

# 6. Convert messy object back to float for math
cities_subset["population"] = cities_subset["population"].astype(float)

# 7. Crucial check: Drop rows where critical population data is missing
cols_needed = ["population"]
cities_final = cities_subset.dropna(subset=cols_needed)

# 8. Display result
display(cities_final.head())
print(f"Remaining rows after cleaning: {len(cities_final)}")

7. Summary: Data Cleaning Practices¶

Sanitizing your dataset is a critical best practice before any analysis. Real environmental and spatial data is almost always heterogeneous and messy. If you skip this step, your future calculations will fail!

Key takeaways¶

• Columns: Renaming columns using dictionaries standardizes cryptic sensor headers into descriptive lowercase names.

• Types: Always verify .dtypes. Use .astype() to correct numbers incorrectly stored as text, but beware of integer truncation!

• Strings: The .str accessor allows you to programmatically slice strings (like extracting a month from a timestamp) or clean text by changing case and stripping whitespace.

• Missing Data (NaNs): Gaps are common and infectious.

  • Detect: Find them using .hasnans or .isna().sum().

  • Drop: Use .dropna(subset=[...]) to safely remove rows missing crucial target data.

  • Fill: Use .fillna() cautiously. For time-series data (like daily temperatures), use .interpolate() to draw a line between the day before and the day after rather than filling with a static average.

What comes next?¶

Now that your dataset is clean, properly formatted, and free of missing values, you are finally ready to do some actual data analysis!

But wait, if you want to divide a column of populations by a column of areas to find the density for 48,000 cities, how do you do it? In pure Python, you would have to write a massive, slow for loop to calculate each row one by one.

In Pandas, we have a superpower called Vectorization. In the next section, Math Without Loops, we will learn how to apply instant arithmetic across entire datasets and create brand new columns of data in a single line of code.