Skip to content
Tutorial Reference

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

Learn how to resolve the Pandas Error "cannot reindex on an axis with duplicate labels' by fixing duplicate row indices or column names in DataFrames during operations like reindex(), join(), or concat().

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]
}
df_duplicate_index = pd.DataFrame(data, index=['idx0', 'idx1', 'idx1', 'idx2'])

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

try:
    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

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}")

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

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'])

df_unique_idx_rows = df_duplicate_index[~df_duplicate_index.index.duplicated(keep='first')]

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

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

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

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'])

df_reset = df_duplicate_index.reset_index(drop=True) # drop=True discards the old index

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

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 = pd.DataFrame({'A': [1,2]}, index=['row1', 'row2'])
df_part2 = pd.DataFrame({'A': [3,4]}, index=['row2', 'row3']) # 'row2' will be duplicated

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

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:
    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 = pd.DataFrame([[1, 20, 300], [4, 50, 600]], columns=['ID', 'Metric', 'Metric'])

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()

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_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:
    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 = pd.DataFrame({'A': [1, 2, 3]}, index=['x', 'y', 'z'])

source_series_for_values = pd.Series([100, 200, 300], index=['a', 'b', 'c']) # Unique index, correct length

df_target_assign_vals = df_target.copy()
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()

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

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.

Navigation