Handling inconsistent values in Pandas - Mini Project: Build & Apply

Handling inconsistent values in pandas

📖 Scenario: You work in a company that collects customer feedback. The feedback data has inconsistent values for the rating column, such as different spellings and cases for the same rating.Cleaning this data will help the company analyze customer satisfaction accurately.

🎯 Goal: You will clean the rating column in a pandas DataFrame by replacing inconsistent values with consistent ones.

📋 What You'll Learn

Create a pandas DataFrame with given customer feedback data

Create a dictionary to map inconsistent ratings to consistent ratings

Use the replace() method with the mapping dictionary to clean the ratings

Print the cleaned DataFrame

💡 Why This Matters

🌍 Real World

Data collected from customers often has inconsistent text entries. Cleaning these values helps in accurate analysis and reporting.

💼 Career

Data scientists and analysts frequently clean and preprocess data to prepare it for analysis, making this skill essential in many data-related jobs.

1

Create the initial DataFrame

Create a pandas DataFrame called feedback with these exact columns and values:
customer_id: [101, 102, 103, 104, 105]
rating: ['Good', 'good', 'Excellent', 'excellent', 'Bad']

Pandas

import pandas as pd
# Create the DataFrame called feedback with the given data
# Your code here

Hint

Use pd.DataFrame() with a dictionary of lists for columns.

2

Create a mapping dictionary for ratings

Create a dictionary called rating_map that maps these inconsistent ratings to consistent ones:
'good' to 'Good',
'excellent' to 'Excellent',
and keep 'Bad' as is (no mapping needed for 'Bad').

Pandas

import pandas as pd

feedback = pd.DataFrame({
    'customer_id': [101, 102, 103, 104, 105],
    'rating': ['Good', 'good', 'Excellent', 'excellent', 'Bad']
})
# Create the rating_map dictionary to fix inconsistent ratings
# Your code here

Hint

Use a dictionary with keys as inconsistent values and values as correct ones.

3

Replace inconsistent ratings using the mapping

Use the replace() method on the rating column of feedback with the rating_map dictionary to fix inconsistent values. Assign the result back to the rating column.

Pandas

import pandas as pd

feedback = pd.DataFrame({
    'customer_id': [101, 102, 103, 104, 105],
    'rating': ['Good', 'good', 'Excellent', 'excellent', 'Bad']
})

rating_map = {
    'good': 'Good',
    'excellent': 'Excellent'
}
# Replace inconsistent ratings in feedback['rating'] using rating_map
# Your code here

Hint

Use feedback['rating'].replace(rating_map) and assign it back to feedback['rating'].

4

Print the cleaned DataFrame

Print the feedback DataFrame to see the cleaned ratings.

Pandas

import pandas as pd

feedback = pd.DataFrame({
    'customer_id': [101, 102, 103, 104, 105],
    'rating': ['Good', 'good', 'Excellent', 'excellent', 'Bad']
})

rating_map = {
    'good': 'Good',
    'excellent': 'Excellent'
}

feedback['rating'] = feedback['rating'].replace(rating_map)
# Print the feedback DataFrame
# Your code here

Hint

Use print(feedback) to display the DataFrame.

Practice

1. What is the main purpose of handling inconsistent values in a pandas DataFrame?

easy

A. To clean data so analysis results are accurate and reliable

B. To make the DataFrame larger by adding more rows

C. To convert all numbers to strings

D. To sort the DataFrame by index

Solution

1. Step 1: Understand inconsistent values

   Inconsistent values are errors or variations in data that can cause wrong analysis results.

2. Step 2: Purpose of handling them

   Cleaning these values ensures the data is uniform and trustworthy for analysis.

3. Final Answer:

   To clean data so analysis results are accurate and reliable -> Option A

4. Quick Check:

   Cleaning data = Accurate analysis [OK]

Hint: Cleaning inconsistent data ensures correct analysis results [OK]

Common Mistakes:

• Thinking inconsistent values add more data
• Confusing cleaning with sorting
• Assuming conversion to string fixes inconsistencies

2. Which pandas method is commonly used to replace inconsistent values in a DataFrame column?

easy

A. df.sort_values()

B. df.dropna()

C. df.replace()

D. df.groupby()

Solution

1. Step 1: Identify method for replacing values

   df.replace() is designed to substitute specified values with new ones.

2. Step 2: Confirm other methods' purposes

   df.dropna() removes missing data, df.sort_values() sorts data, df.groupby() groups data; none replace values.

3. Final Answer:

   df.replace() -> Option C

4. Quick Check:

   Replace values = df.replace() [OK]

Hint: Use df.replace() to swap inconsistent values [OK]

Common Mistakes:

• Using dropna() to replace values
• Confusing sorting with replacing
• Trying to group data to fix values

3. Given the DataFrame:

import pandas as pd
df = pd.DataFrame({'color': ['red', 'Red', 'blue', 'Blue', 'RED']})
df['color'] = df['color'].str.lower()

What will be the unique values in df['color'] after this code runs?

medium

A. ['red', 'blue']

B. ['Red', 'Blue', 'RED']

C. ['red', 'Red', 'blue', 'Blue', 'RED']

D. ['RED', 'BLUE']

Solution

1. Step 1: Understand str.lower() effect

   str.lower() converts all strings in the column to lowercase.

2. Step 2: Apply to values and find unique

   All 'red', 'Red', 'RED' become 'red'; 'blue', 'Blue' become 'blue'. Unique values are ['red', 'blue'].

3. Final Answer:

   ['red', 'blue'] -> Option A

4. Quick Check:

   Lowercase unique values = ['red', 'blue'] [OK]

Hint: str.lower() unifies case to find unique values [OK]

Common Mistakes:

• Thinking original case stays
• Assuming unique keeps all original variants
• Confusing str.lower() with str.upper()

4. What is wrong with this code snippet for fixing inconsistent 'gender' values?

df['gender'].replace({'M': 'Male', 'F': 'Female'})

Why might it not update the DataFrame as expected?

medium

A. Because replace() cannot handle dictionaries

B. Because replace() needs inplace=true or assignment to update df

C. Because 'M' and 'F' are not valid keys

D. Because replace() only works on numeric columns

Solution

1. Step 1: Check replace() behavior

   By default, replace() returns a new Series and does not change df unless inplace=true or assigned.

2. Step 2: Identify missing update step

   The code does not assign back or use inplace=true, so df['gender'] remains unchanged.

3. Final Answer:

   Because replace() needs inplace=true or assignment to update df -> Option B

4. Quick Check:

   replace() without assignment = no change [OK]

Hint: Always assign or use inplace=true with replace() [OK]

Common Mistakes:

• Assuming replace() changes df without assignment
• Thinking replace() can't use dict
• Believing replace() only works on numbers

5. You have a DataFrame with a 'status' column containing inconsistent values like 'Active', 'active', 'ACTIVE', and some misspelled as 'Actve'. Which sequence of pandas operations best cleans this column to have only 'Active' or 'Inactive' values?

hard

A. df['status'] = df['status'].str.strip().replace({'Actve': 'Inactive'})"

B. df['status'] = df['status'].replace({'Active': 'active', 'ACTIVE': 'active'}).str.upper()"

C. df['status'] = df['status'].fillna('Inactive').replace({'active': 'Active'})"

D. df['status'] = df['status'].str.lower().replace({'actve': 'active'}).str.capitalize()"

Solution

1. Step 1: Normalize case to lowercase

   Using str.lower() converts all variations like 'Active', 'ACTIVE' to 'active'.

2. Step 2: Fix misspelled value

   replace({'actve': 'active'}) corrects the misspelled 'actve' to 'active'.

3. Step 3: Capitalize for consistent formatting

   str.capitalize() changes 'active' to 'Active' for uniform appearance.

4. Final Answer:

   df['status'] = df['status'].str.lower().replace({'actve': 'active'}).str.capitalize() -> Option D

5. Quick Check:

   Lowercase + replace typo + capitalize = clean status [OK]

Hint: Lowercase, fix typos, then capitalize for consistent values [OK]

Common Mistakes:

• Not fixing misspelled values
• Skipping case normalization
• Replacing with wrong values