Skip to main content

Python Pandas: How to Fix "ValueError: cannot reindex on an axis with duplicate labels"

The pandas.errors.InvalidIndexError: cannot reindex on an axis with duplicate labels is a critical error in Pandas that arises when you attempt an operation requiring unique labels for alignment—such as reindex(), join(), certain types of concat(), or even column assignment involving index alignment—but the DataFrame's relevant axis (either the row index or column names) contains duplicate values. Pandas enforces unique labels in these contexts to prevent ambiguity and ensure data integrity.

This guide will thoroughly explain why duplicate labels on an axis lead to this InvalidIndexError, demonstrate common scenarios that trigger it (including duplicate row indices and duplicate column names), and provide robust solutions such as resetting the index, removing duplicates, or ensuring unique column names before performing the reindexing or alignment operation.

Understanding the Error: The Need for Unique Labels in Reindexing/Alignment​

Many Pandas operations that involve aligning, joining, or reshaping data rely on the labels of an axis (either the row index df.index or the column names df.columns) to correctly map and combine data. These operations include:

  • DataFrame.reindex(): Explicitly conforms a DataFrame to a new set of labels.
  • DataFrame.join() / pd.merge(): Combines DataFrames based on index or column values.
  • pd.concat(): When aligning on an axis (e.g., axis=1 aligns on row index).
  • Column assignment: df['new_col'] = some_series (aligns some_series's index with df's index).

If the axis labels used for such an operation are not unique, Pandas cannot perform an unambiguous mapping. For example, if df.reindex([label_A, label_B]) is called and df's current index has label_A appearing twice, Pandas doesn't know which of the two rows corresponding to label_A should be used for the reindexed output. This ambiguity triggers the InvalidIndexError.

Scenario 1: Duplicate Values in DataFrame's Row Index​

This is a common cause when directly using reindex() or other alignment-sensitive methods.

Reproducing the Error with DataFrame.reindex()​

import pandas as pd

data = {
    'item_code': ['X100', 'Y200', 'X100', 'Z300'], # Note: data length is 4
    'quantity': [10, 15, 5, 20],
    'price': [5.0, 3.5, 5.2, 8.0]
}
# Create DataFrame with a non-unique index: 'idx1' appears twice
df_duplicate_index = pd.DataFrame(data, index=['idx0', 'idx1', 'idx1', 'idx2'])

print("DataFrame with duplicate row index 'idx1':")
print(df_duplicate_index)
print()

try:
    # ⛔️ Attempting to reindex. The original df_duplicate_index has duplicate 'idx1'.
    new_index_labels = ['idx0', 'idx1', 'idx2', 'idx3'] # New index to conform to
    df_reindexed_error = df_duplicate_index.reindex(new_index_labels)
    print(df_reindexed_error)
except Exception as e:
    print(f"Error Type: {type(e)}")
    print(f"Error: {e}")

Output:

DataFrame with duplicate row index 'idx1':
     item_code  quantity  price
idx0      X100        10    5.0
idx1      Y200        15    3.5
idx1      X100         5    5.2
idx2      Z300        20    8.0

Error Type: <class 'ValueError'>
Error: cannot reindex on an axis with duplicate labels

Identifying Duplicate Index Values​

You can check for and display duplicate index values:

import pandas as pd

# df_duplicate_index defined as before
data = {
    'item_code': ['X100', 'Y200', 'X100', 'Z300'],
    'quantity': [10, 15, 5, 20],
    'price': [5.0, 3.5, 5.2, 8.0]
}
df_duplicate_index = pd.DataFrame(data, index=['idx0', 'idx1', 'idx1', 'idx2'])

print(f"Is the index unique? {df_duplicate_index.index.is_unique}")

# Show rows that are duplicates (keeps the first occurrence as non-duplicate by default)
print("Rows corresponding to duplicated index entries (after the first):")
print(df_duplicate_index[df_duplicate_index.index.duplicated(keep='first')])

Output:

Is the index unique? False
Rows corresponding to duplicated index entries (after the first):
     item_code  quantity  price
idx1      X100         5    5.2

Solution: Remove Rows with Duplicate Index Values​

If keeping only one instance of rows with duplicate indices is acceptable, you can filter them out. This alters your data.

import pandas as pd

# df_duplicate_index defined as before
data = {
    'item_code': ['X100', 'Y200', 'X100', 'Z300'],
    'quantity': [10, 15, 5, 20],
    'price': [5.0, 3.5, 5.2, 8.0]
}
df_duplicate_index = pd.DataFrame(data, index=['idx0', 'idx1', 'idx1', 'idx2'])

# ✅ Keep only the first occurrence for each index label
df_unique_idx_rows = df_duplicate_index[~df_duplicate_index.index.duplicated(keep='first')]
# The ~ (tilde) negates the boolean Series, selecting non-duplicate rows.

print("DataFrame after removing duplicate index rows (keeping first):")
print(df_unique_idx_rows)
print()

# Now reindex will work on df_unique_idx_rows
new_index_labels = ['idx0', 'idx1', 'idx2', 'idx3']
df_reindexed_fixed = df_unique_idx_rows.reindex(new_index_labels)
print("Reindexed DataFrame after fixing duplicates:")
print(df_reindexed_fixed)

Output:

DataFrame after removing duplicate index rows (keeping first):
     item_code  quantity  price
idx0      X100        10    5.0
idx1      Y200        15    3.5
idx2      Z300        20    8.0

Reindexed DataFrame after fixing duplicates:
     item_code  quantity  price
idx0      X100      10.0    5.0
idx1      Y200      15.0    3.5
idx2      Z300      20.0    8.0
idx3       NaN       NaN    NaN
note

The keep parameter in duplicated() can be 'first', 'last', or False (marks all occurrences of duplicates as True).

Solution: Reset the Index to a Default Unique Index​

If the current index labels are not critical for the reindexing logic itself (i.e., you just need a unique index for the operation to proceed, or the reindexing target is purely positional), resetting to a default RangeIndex is often the simplest fix.

import pandas as pd

# df_duplicate_index defined as above
data = {
    'item_code': ['X100', 'Y200', 'X100', 'Z300'],
    'quantity': [10, 15, 5, 20],
    'price': [5.0, 3.5, 5.2, 8.0]
}
df_duplicate_index = pd.DataFrame(data, index=['idx0', 'idx1', 'idx1', 'idx2'])

# ✅ Reset the index to a default unique integer index
df_reset = df_duplicate_index.reset_index(drop=True) # drop=True discards the old index
# If drop=False, the old index becomes a column named 'index'

print("DataFrame after reset_index(drop=True):")
print(df_reset)
print()

# Now reindex (if based on position or a new set of simple labels) can work.
# Note: reindexing with the original problematic labels on df_reset wouldn't make sense here
# as the information tying data to 'idx0', 'idx1' is lost if drop=True.
# This solution is more about getting a unique index to proceed with other operations.
# If you need to reindex based on NEW labels and don't care about old ones:
df_reindexed_after_reset = df_reset.reindex([0, 1, 2, 3, 4]) # Reindexing to a new length
print("Reindexed after reset (example):")
print(df_reindexed_after_reset)

Output:

DataFrame after reset_index(drop=True):
  item_code  quantity  price
0      X100        10    5.0
1      Y200        15    3.5
2      X100         5    5.2
3      Z300        20    8.0

Reindexed after reset (example):
  item_code  quantity  price
0      X100      10.0    5.0
1      Y200      15.0    3.5
2      X100       5.0    5.2
3      Z300      20.0    8.0
4       NaN       NaN    NaN

Scenario 2: Duplicate Index Values During DataFrame Concatenation​

When concatenating DataFrames with pd.concat(), especially along axis=0 (stacking rows), if the original DataFrames have indices that, when combined, result in duplicates in the final DataFrame's index, subsequent reindexing operations on this concatenated DataFrame can fail.

How pd.concat() Can Lead to Duplicate Indices​

import pandas as pd

df_part1 = pd.DataFrame({'A': [1,2]}, index=['row1', 'row2'])
df_part2 = pd.DataFrame({'A': [3,4]}, index=['row2', 'row3']) # 'row2' will be duplicated

df_concatenated_dupe_idx = pd.concat([df_part1, df_part2])
print("Concatenated DataFrame with duplicate index 'row2':")
print(df_concatenated_dupe_idx)

Output:

Concatenated DataFrame with duplicate index 'row2':
      A
row1  1
row2  2
row2  3
row3  4

Solution: Use ignore_index=True in pd.concat()​

This creates a new default RangeIndex for the concatenated DataFrame.

import pandas as pd

# df_part1, df_part2 defined as above
df_part1 = pd.DataFrame({'A': [1,2]}, index=['row1', 'row2'])
df_part2 = pd.DataFrame({'A': [3,4]}, index=['row2', 'row3']) # 'row2' will be duplicated

# ✅ Concatenate and ignore original indices, creating a new unique RangeIndex
df_concat_ignored_index = pd.concat([df_part1, df_part2], ignore_index=True)

print("Concatenated DataFrame with ignore_index=True:")
print(df_concat_ignored_index)

Output:

Concatenated DataFrame with ignore_index=True:
   A
0  1
1  2
2  3
3  4

Solution: Manually Create Unique Indices Before Concatenation​

Alternatively, ensure indices are unique before concatenation if the original index values have meaning you want to preserve in a modified form. This is more complex.

Scenario 3: Duplicate Column Names (Affecting Column Axis)​

If a DataFrame has duplicate column names, operations that try to reindex or align based on the column axis can fail.

How Duplicate Column Names Cause Reindexing Issues​

import pandas as pd

# DataFrame with duplicate column name 'Metric'
df_duplicate_cols = pd.DataFrame([[1, 20, 300], [4, 50, 600]], columns=['ID', 'Metric', 'Metric'])
print("DataFrame with duplicate column 'Metric':")
print(df_duplicate_cols)
print()

try:
    # ⛔️ Reindexing columns when duplicate column names exist
    df_reindexed_cols_error = df_duplicate_cols.reindex(columns=['ID', 'Metric', 'NewLabel'])
    print(df_reindexed_cols_error)
except Exception as e:
    print(f"Error Type: {type(e)}")
    print(f"Error with duplicate columns: {e}")

Output:

DataFrame with duplicate column 'Metric':
   ID  Metric  Metric
0   1      20     300
1   4      50     600

Error Type: <class 'ValueError'>
Error with duplicate columns: cannot reindex on an axis with duplicate labels

Solution: Remove or Rename Duplicate Columns​

Ensure column names are unique before the reindexing operation.

import pandas as pd

# df_duplicate_cols defined as above
df_duplicate_cols = pd.DataFrame([[1, 20, 300], [4, 50, 600]], columns=['ID', 'Metric', 'Metric'])

# ✅ Solution 1: Rename columns to be unique
df_renamed_cols = df_duplicate_cols.copy() # Work on a copy
df_renamed_cols.columns = ['ID', 'Metric_A', 'Metric_B'] # Assign unique names

df_reindexed_renamed = df_renamed_cols.reindex(columns=['ID', 'Metric_A', 'Metric_B', 'NewMetric'])
print("Reindexed after renaming duplicate columns:")
print(df_reindexed_renamed)
print()

# ✅ Solution 2: Remove duplicate columns (keeping first occurrence)
df_unique_cols = df_duplicate_cols.loc[:, ~df_duplicate_cols.columns.duplicated(keep='first')]
print("DataFrame after removing duplicate columns:")
print(df_unique_cols)
print()

df_reindexed_deduped = df_unique_cols.reindex(columns=['ID', 'Metric', 'NewLabel'])
print("Reindexed after removing duplicate columns:")
print(df_reindexed_deduped)

Output:

Reindexed after renaming duplicate columns:
   ID  Metric_A  Metric_B  NewMetric
0   1        20       300        NaN
1   4        50       600        NaN

DataFrame after removing duplicate columns:
   ID  Metric
0   1      20
1   4      50

Reindexed after removing duplicate columns:
   ID  Metric  NewLabel
0   1      20       NaN
1   4      50       NaN

Scenario 4: Error When Assigning a Column if Source DataFrame Has Duplicate Index**​

This error can occur during column assignment if the Series/DataFrame being assigned (source_series) has a non-unique index, and Pandas tries to align it with the target DataFrame's index.

Reproducing the Error​

import pandas as pd

df_target = pd.DataFrame({'A': [1, 2, 3]}, index=['x', 'y', 'z'])

# Source Series with duplicate index 'b'
source_series_dupe_idx = pd.Series([100, 200, 300, 400], index=['a', 'b', 'b', 'c'])

print("Target DataFrame:")
print(df_target)
print("Source Series with duplicate index:")
print(source_series_dupe_idx)
print()

try:
    # ⛔️ Assigning a Series with duplicate index. Pandas tries to align, encounters duplicates.
    df_target['NewCol'] = source_series_dupe_idx
    print(df_target)
except Exception as e:
    print(f"Error Type: {type(e)}")
    print(f"Error during column assignment: {e}")

Output:

Target DataFrame:
   A
x  1
y  2
z  3
Source Series with duplicate index:
a    100
b    200
b    300
c    400
dtype: int64

Error Type: <class 'ValueError'>
Error during column assignment: cannot reindex on an axis with duplicate labels

Solution: Use .values or .to_numpy() from the Source Series​

If you want to assign the values from source_series_dupe_idx directly without index alignment (assuming source_series_dupe_idx has the same length as df_target's number of rows), use its underlying NumPy array. This bypasses index alignment.

import pandas as pd

# df_target defined as above
df_target = pd.DataFrame({'A': [1, 2, 3]}, index=['x', 'y', 'z'])

# Let's make source_series_dupe_idx suitable for this solution by matching length
source_series_for_values = pd.Series([100, 200, 300], index=['a', 'b', 'c']) # Unique index, correct length

df_target_assign_vals = df_target.copy()
# ✅ Assign values directly, ignoring source_series_for_values's index
df_target_assign_vals['NewCol_vals'] = source_series_for_values.values

print("DataFrame after assigning .values (ignores source index):")
print(df_target_assign_vals)
print()

# If source_series_dupe_idx had duplicate index BUT appropriate length for .values:
source_series_len3_dupeidx = pd.Series([10,20,30], index=['q','q','r'])
if len(df_target) == len(source_series_len3_dupeidx):
    df_target['NewCol_dupe_source'] = source_series_len3_dupeidx.values
    print("DF after assigning values from Series with dupe index but matching length:")
    print(df_target)

Output:

DataFrame after assigning .values (ignores source index):
   A  NewCol_vals
x  1          100
y  2          200
z  3          300

DF after assigning values from Series with dupe index but matching length:
   A  NewCol_dupe_source
x  1                  10
y  2                  20
z  3                  30
warning

Caution: Using .values or .to_numpy() breaks any index alignment. The assignment becomes purely positional (first value to first row, etc.). Ensure the lengths match.

Solution: Ensure Unique Index on Source Before Assignment (if alignment is desired)​

If you do want index alignment during assignment, the source Series/DataFrame must first have its duplicate index labels resolved (e.g., by methods in Solution 2.3 or 2.4).

Conclusion​

The ValueError: cannot reindex on an axis with duplicate labels is a critical safeguard in Pandas against ambiguous data operations. It underscores the importance of maintaining unique labels on the axis (row index or column names) that Pandas uses for alignment or reindexing. The primary solutions involve:

  1. For Duplicate Row Indices:
    • Remove duplicate rows: df[~df.index.duplicated(keep='first')].
    • Reset the index: df.reset_index(drop=True).
    • When concatenating, use ignore_index=True in pd.concat().
  2. For Duplicate Column Names:
    • Rename columns to be unique: df.columns = ['unique_name1', ...].
    • Remove duplicate columns: df.loc[:, ~df.columns.duplicated(keep='first')].
  3. For Column Assignment from a Source with Duplicate Index:
    • If alignment is not needed and lengths match, assign .values or .to_numpy().
    • If alignment is needed, first ensure the source Series/DataFrame has a unique index.

By proactively checking for and resolving duplicate labels using methods like index.is_unique, index.duplicated(), and columns.duplicated(), you can prevent this error and ensure the integrity of your data operations.

Table of Contents