Standardizing column names in Pandas - Mini Project: Build & Apply

Standardizing column names

📖 Scenario: You have a dataset with messy column names from different sources. Some columns have spaces, uppercase letters, or special characters. You want to clean these column names so they are all lowercase and use underscores instead of spaces. This makes it easier to work with the data later.

🎯 Goal: Clean and standardize the column names of a pandas DataFrame by making all letters lowercase and replacing spaces with underscores.

📋 What You'll Learn

Create a pandas DataFrame with specific columns

Create a variable to hold the cleaned column names

Use a list comprehension to clean each column name

Assign the cleaned column names back to the DataFrame

Print the updated DataFrame columns

💡 Why This Matters

🌍 Real World

Data scientists often get data from many sources with inconsistent column names. Cleaning column names helps avoid errors and makes data easier to analyze.

💼 Career

Standardizing column names is a common task in data cleaning, which is a key skill for data analysts and data scientists.

1

Create the initial DataFrame

Create a pandas DataFrame called df with columns exactly named 'First Name', 'Last Name', and 'Age'. The data can be any three rows you like.

Pandas

import pandas as pd
# Create the DataFrame df with columns 'First Name', 'Last Name', and 'Age'
# Your code here

Hint

Use pd.DataFrame with a dictionary where keys are column names and values are lists of data.

2

Create a variable for cleaned column names

Create a variable called clean_columns and set it to an empty list. This will hold the cleaned column names.

Pandas

import pandas as pd

df = pd.DataFrame({
    'First Name': ['Alice', 'Bob', 'Charlie'],
    'Last Name': ['Smith', 'Jones', 'Brown'],
    'Age': [25, 30, 35]
})

# Create an empty list called clean_columns
# Your code here

Hint

Just create an empty list named clean_columns.

3

Clean the column names using list comprehension

Use a list comprehension to fill clean_columns with the column names from df.columns, but make each name lowercase and replace spaces with underscores. Use clean_columns = [col.lower().replace(' ', '_') for col in df.columns].

Pandas

import pandas as pd

df = pd.DataFrame({
    'First Name': ['Alice', 'Bob', 'Charlie'],
    'Last Name': ['Smith', 'Jones', 'Brown'],
    'Age': [25, 30, 35]
})

clean_columns = []

# Use list comprehension to fill clean_columns with lowercase column names with underscores instead of spaces
# Your code here

Hint

Use a list comprehension with col.lower().replace(' ', '_') for each col in df.columns.

4

Assign cleaned names and print the result

Assign clean_columns back to df.columns. Then print df.columns to show the updated column names.

Pandas

import pandas as pd

df = pd.DataFrame({
    'First Name': ['Alice', 'Bob', 'Charlie'],
    'Last Name': ['Smith', 'Jones', 'Brown'],
    'Age': [25, 30, 35]
})

clean_columns = [col.lower().replace(' ', '_') for col in df.columns]

# Assign clean_columns to df.columns
# Print df.columns
# Your code here

Hint

Set df.columns = clean_columns and then print df.columns.

Practice

1. Why is it important to standardize column names in a pandas DataFrame?

easy

A. To increase the size of the DataFrame

B. To change the data types of columns automatically

C. To make data easier to work with and reduce errors in code

D. To delete duplicate rows in the DataFrame

Solution

1. Step 1: Understand the purpose of standardizing column names

   Standardizing column names helps avoid mistakes caused by inconsistent naming like spaces, uppercase letters, or special characters.

2. Step 2: Recognize the benefits in coding

   Consistent column names make it easier to write and read code, reducing bugs and confusion.

3. Final Answer:

   To make data easier to work with and reduce errors in code -> Option C

4. Quick Check:

   Standardizing column names = easier coding [OK]

Hint: Standardize names to avoid typos and errors [OK]

Common Mistakes:

• Thinking it changes data values
• Believing it affects DataFrame size
• Confusing with data type conversion

2. Which of the following is the correct way to convert all column names in a DataFrame df to lowercase?

easy

A. df.columns = df.columns.str.lower()

B. df.columns = lower(df.columns)

C. df.columns = df.columns.lower()

D. df.columns = df.columns.toLowerCase()

Solution

1. Step 1: Recall pandas string methods for columns

   Column names are an Index object; to apply string methods, use .str accessor.

2. Step 2: Identify correct syntax for lowercase conversion

   The correct method is df.columns.str.lower(), which converts all column names to lowercase.

3. Final Answer:

   df.columns = df.columns.str.lower() -> Option A

4. Quick Check:

   Use .str.lower() on df.columns [OK]

Hint: Use .str accessor for column name string methods [OK]

Common Mistakes:

• Calling .lower() directly on df.columns (Index object)
• Using Python's lower() function instead of pandas method
• Using JavaScript style toLowerCase()

3. Given the DataFrame df with columns [' Name ', 'AGE', 'eMail'], what will be the result of running:

df.columns = df.columns.str.strip().str.lower()

medium

A. [' name', 'AGE', 'eMail']

B. ['Name', 'AGE', 'eMail']

C. [' name ', 'age', 'email']

D. ['name', 'age', 'email']

Solution

1. Step 1: Apply .str.strip() to remove spaces

   Leading and trailing spaces in ' Name ' are removed, becoming 'Name'.

2. Step 2: Apply .str.lower() to convert all to lowercase

   All column names become lowercase: 'Name' -> 'name', 'AGE' -> 'age', 'eMail' -> 'email'.

3. Final Answer:

   ['name', 'age', 'email'] -> Option D

4. Quick Check:

   Strip spaces + lowercase = ['name', 'age', 'email'] [OK]

Hint: Chain .str.strip() then .str.lower() for clean names [OK]

Common Mistakes:

• Not removing spaces before lowering case
• Only stripping spaces but not lowering case
• Assuming original names remain unchanged

4. You tried to standardize column names using:

df.columns = df.columns.lower()

but got an error. What is the problem?

medium

A. You need to import a special library for .lower()

B. df.columns is an Index object and needs .str accessor for string methods

C. The DataFrame must be converted to a list first

D. You must use uppercase letters instead

Solution

1. Step 1: Understand the type of df.columns

   df.columns is a pandas Index, not a plain list or string, so string methods need .str accessor.

2. Step 2: Identify correct usage for string methods on columns

   Use df.columns.str.lower() to apply lowercase conversion to each column name.

3. Final Answer:

   df.columns is an Index object and needs .str accessor for string methods -> Option B

4. Quick Check:

   Use .str.lower() on df.columns [OK]

Hint: Use .str before string methods on df.columns [OK]

Common Mistakes:

• Calling .lower() directly on df.columns
• Trying to convert df.columns to list unnecessarily
• Thinking uppercase is required

5. You have a DataFrame df with columns: [' First Name ', 'Last-Name', 'E-mail Address']. You want to standardize these names by:
1. Removing spaces at start and end
2. Replacing dashes and spaces with underscores
3. Converting all letters to lowercase

Which code correctly does this?

hard

A. df.columns = df.columns.str.strip().str.replace('[- ]', '_', regex=True).str.lower()

B. df.columns = df.columns.str.lower().str.strip().str.replace(' ', '_')

C. df.columns = df.columns.replace(' ', '_').lower().strip()

D. df.columns = df.columns.str.strip().str.replace('-', '_').str.lower()

Solution

1. Step 1: Remove spaces at start and end with .str.strip()

   This cleans ' First Name ' to 'First Name'.

2. Step 2: Replace dashes and spaces with underscores using regex

   Use .str.replace('[- ]', '_', regex=True) to replace both '-' and ' ' with '_'.

3. Step 3: Convert all names to lowercase with .str.lower()

   Finally, convert all letters to lowercase for consistency.

4. Final Answer:

   df.columns = df.columns.str.strip().str.replace('[- ]', '_', regex=True).str.lower() -> Option A

5. Quick Check:

   Strip, replace with regex, then lower [OK]

Hint: Chain strip, regex replace, then lower for full cleanup [OK]

Common Mistakes:

• Not using regex to replace both dash and space
• Wrong order of operations causing partial cleanup
• Using .replace without .str accessor