Handling duplicate column names in Data Analysis Python - Mini Project: Build & Apply

Handling duplicate column names

📖 Scenario: You have a table of sales data where some columns have the same name by mistake. This can cause confusion when analyzing the data.Imagine you work in a store and the sales report has two columns both named 'Price'. You want to fix this so each column has a unique name.

🎯 Goal: You will create a data table with duplicate column names, then write code to rename the duplicate columns to unique names by adding numbers at the end.

📋 What You'll Learn

Create a pandas DataFrame with duplicate column names

Create a helper variable to track column name counts

Write code to rename duplicate columns by adding suffixes

Print the final DataFrame columns to show unique names

💡 Why This Matters

🌍 Real World

In real data, duplicate column names can cause errors or confusion. Fixing them helps clean data for analysis or reporting.

💼 Career

Data analysts and scientists often clean messy data. Handling duplicate columns is a common task to prepare data for accurate analysis.

1

Create a DataFrame with duplicate column names

Create a pandas DataFrame called df with these columns: 'Product', 'Price', 'Price', and 'Quantity'. Use these rows: ['Apple', 1.2, 1.3, 10] and ['Banana', 0.5, 0.6, 20].

Data Analysis Python

# Create the DataFrame df with duplicate column names
# Your code here

Hint

Use pd.DataFrame with data and columns parameters.

2

Create a dictionary to count column name occurrences

Create an empty dictionary called col_count to keep track of how many times each column name appears.

Data Analysis Python

import pandas as pd

df = pd.DataFrame(
    data=[['Apple', 1.2, 1.3, 10], ['Banana', 0.5, 0.6, 20]],
    columns=['Product', 'Price', 'Price', 'Quantity']
)

# Create an empty dictionary col_count
# Your code here

Hint

Use {} to create an empty dictionary.

3

Rename duplicate columns to unique names

Create a new list called new_columns. Use a for loop with variable col to go through df.columns. For each col, update col_count[col] by 1 if it exists or set to 1 if not. If col_count[col] is 1, add col to new_columns. Otherwise, add col plus underscore and the count (like Price_2) to new_columns. Finally, set df.columns = new_columns.

Data Analysis Python

import pandas as pd

df = pd.DataFrame(
    data=[['Apple', 1.2, 1.3, 10], ['Banana', 0.5, 0.6, 20]],
    columns=['Product', 'Price', 'Price', 'Quantity']
)

col_count = {}

# Create new_columns list and rename duplicates
# Your code here

Hint

Use a dictionary to count and a list to store new names. Use f"{col}_{count}" to add suffix.

4

Print the DataFrame columns to show unique names

Write print(df.columns.tolist()) to display the list of column names after renaming duplicates.

Data Analysis Python

import pandas as pd

df = pd.DataFrame(
    data=[['Apple', 1.2, 1.3, 10], ['Banana', 0.5, 0.6, 20]],
    columns=['Product', 'Price', 'Price', 'Quantity']
)

col_count = {}
new_columns = []
for col in df.columns:
    if col in col_count:
        col_count[col] += 1
        new_columns.append(f"{col}_{col_count[col]}")
    else:
        col_count[col] = 1
        new_columns.append(col)

df.columns = new_columns

# Print the list of column names
# Your code here

Hint

Use print(df.columns.tolist()) to see the column names as a list.

Practice

1. What happens if a pandas DataFrame has duplicate column names and you try to select one column by name using df['column_name']?

easy

A. It merges the columns with the same name into one.

B. It returns all columns with that name as a DataFrame.

C. It returns only the first column with that name.

D. It raises a KeyError.

Solution

1. Step 1: Understand pandas behavior with duplicate columns

   When selecting a column by name, pandas returns all columns with that name as a DataFrame if duplicates exist.

2. Step 2: Confirm selection result type

   Instead of a Series, pandas returns a DataFrame containing all matching columns.

3. Final Answer:

   It returns all columns with that name as a DataFrame. -> Option B

4. Quick Check:

   Duplicate columns selection = DataFrame [OK]

Hint: Selecting duplicate columns returns all as DataFrame, not error [OK]

Common Mistakes:

• Assuming it raises an error
• Thinking it returns only the first column
• Believing columns merge automatically

2. Which of the following is the correct way to rename duplicate columns in a pandas DataFrame df to make them unique?

easy

A. df.columns = pd.io.parsers.ParserBase({'names':df.columns})._maybe_dedup_names(df.columns)

B. df.columns = df.columns + '_1'

C. df.rename_duplicates()

D. df.columns = list(set(df.columns))

Solution

1. Step 1: Identify pandas method for deduplication

   pandas internal method _maybe_dedup_names can rename duplicate columns to unique names.

2. Step 2: Check each option

   A uses pandas internal deduplication correctly; B adds suffix to all columns, not just duplicates; C is invalid method; D removes duplicates losing data.

3. Final Answer:

   df.columns = pd.io.parsers.ParserBase({'names':df.columns})._maybe_dedup_names(df.columns) -> Option A

4. Quick Check:

   Use pandas internal deduplication = df.columns = pd.io.parsers.ParserBase({'names':df.columns})._maybe_dedup_names(df.columns) [OK]

Hint: Use pandas internal _maybe_dedup_names to rename duplicates [OK]

Common Mistakes:

• Trying to rename with set() which loses order and data
• Using non-existent method rename_duplicates()
• Adding suffix to all columns blindly

3. Given the DataFrame df with columns ['A', 'B', 'A'] and data:

   A  B  A
0  1  2  3
1  4  5  6

What is the output of df['A']?

medium

A. A Series with values [1, 4]

B. A DataFrame with columns 'A' and 'B'

C. A DataFrame with two columns both named 'A' containing values [[1, 3], [4, 6]]

D. Raises a KeyError

Solution

1. Step 1: Check columns and duplicates

   DataFrame has duplicate columns named 'A' at positions 0 and 2.

2. Step 2: Selecting df['A'] returns all columns named 'A'

   pandas returns a DataFrame with both 'A' columns, values [[1, 3], [4, 6]].

3. Final Answer:

   A DataFrame with two columns both named 'A' containing values [[1, 3], [4, 6]] -> Option C

4. Quick Check:

   Duplicate column selection = DataFrame with both columns [OK]

Hint: Selecting duplicate columns returns DataFrame with all matching columns [OK]

Common Mistakes:

• Expecting a single Series output
• Thinking it raises an error
• Confusing columns returned with all DataFrame columns

4. You have a DataFrame df with duplicate columns. You try to rename duplicates by running:

df.columns = df.columns + '_dup'

but it renames all columns, not just duplicates. How to fix this?

medium

A. Use df.columns = pd.io.parsers.ParserBase({'names':df.columns})._maybe_dedup_names(df.columns).

B. Drop duplicate columns before renaming.

C. Use df.rename(columns=lambda x: x+'_dup').

D. Use a loop to add suffix only to duplicate columns.

Solution

1. Step 1: Understand why all columns get renamed

   Adding '_dup' suffix to all columns renames every column, not just duplicates.

2. Step 2: Fix by renaming only duplicates

   Use a loop or comprehension to detect duplicates and rename only those columns.

3. Final Answer:

   Use a loop to add suffix only to duplicate columns. -> Option D

4. Quick Check:

   Rename duplicates selectively = loop method [OK]

Hint: Rename only duplicates by looping and checking counts [OK]

Common Mistakes:

• Renaming all columns blindly
• Using rename() without condition
• Dropping duplicates losing data

5. You have a DataFrame df with columns ['X', 'Y', 'X', 'Z', 'Y']. You want to rename duplicates by adding suffixes _1, _2, ... to make all column names unique. Which code correctly achieves this?

hard

A. df.columns = pd.Series(df.columns).groupby(df.columns).cumcount().add(1).astype(str).radd(df.columns + '_')

B. df.columns = pd.Series(df.columns).groupby(df.columns).cumcount().astype(str) + df.columns

C. df.columns = [f'{col}_{i}' for i, col in enumerate(df.columns)]

D. df.columns = list(set(df.columns))

Solution

1. Step 1: Understand the goal

   We want to add suffixes _1, _2, ... to duplicate columns to make names unique.

2. Step 2: Analyze each option

   A correctly groups by column names and adds suffixes starting at 1; B adds suffix before column name incorrectly; C adds suffixes by position, not duplicates; D removes duplicates losing data.

3. Final Answer:

   df.columns = pd.Series(df.columns).groupby(df.columns).cumcount().add(1).astype(str).radd(df.columns + '_') -> Option A

4. Quick Check:

   Groupby cumcount + suffix = unique names [OK]

Hint: Use groupby cumcount to add numeric suffixes to duplicates [OK]

Common Mistakes:

• Using set() which loses order and data
• Adding suffixes by position, not duplicates
• Ignoring duplicates and renaming all columns