Remove pandas rows with duplicate indices

The reverse card

In scenarios where you might wish to hang onto the latecomer for each index, set the keep parameter to 'last':

# Spoiler: The hare wins the race this time, contrary to the fable!
df_unique_last = df[~df.index.duplicated(keep='last')].copy()


When wrestling with the multi-headed dragon, MultiIndex, the same methods still apply. Establishing epithets for index levels boosts your code readability and organization:

df_unique_multi = df[~df.index.duplicated(keep='first')].copy()


To measure performance, like a proper race car driver, do remember to time different methods, particularly on larger datasets. Using np.unique() with the return_index option could potentially edge out other methods:

import numpy as np

# Who's been naughty, and who's been nice?
# Santa Claus knows as do we, now...
unique_indices, unique_positions = np.unique(df.index.values, return_index=True)
df_unique_np = df.iloc[unique_positions].copy()


Test, test, and test some more, to ensure top-tier execution speed.

Array of Fortune: Alternative ways to find the one!

Various alternatives and their quaint little specific use cases await your perusal:

Pack 'em and label 'em: Grouping and choosing the group leader

For any efficient person, groupby along with an aggregation function is the way to work, especially when additional calculations are part of the process. To keep the last individual of each group:

# Are we playing favorites? Perhaps! 
# Just like your group projects, the one who turned up last did all the job
df_unique_group_last = df.groupby(level=df.index.names).last().reset_index()


Setting the priorities: Keep it loc and loaded

If you want to lay down the law on which duplicate should be kept, df.loc[] can take the wheel. For example, retaining high-value entries in a 'score' column:

# Bigger the score, higher you soar!
df_sorted = df.sort_values('score', ascending=False)
df_unique_priority = df_sorted.loc[~df_sorted.index.duplicated()].copy()


Flip the script: Reverse before removal

To keep the last instances, without the help of the keep bodyguard, simply invert the DataFrame order:

# Abracadabra, back to start
df_reversed = df[::-1]
df_unique_reversed = df_reversed.loc[~df_reversed.index.duplicated()].copy()


A sort of sorting: Post-removal sorting

Post duplicates exorcism, preserving index order might be essential. sort_index() does your bidding:

df_unique_sorted = df_unique.sort_index()


Deep insights: More than meets the eye

Building on several exceptional cases and details, let's dive deeper into navigating pandas DataFrames with duplicate indices.

Data integrity: Your data, your responsibility

While evicting duplicates, it is crucial to preserve data integrity. Vet your dataset post-removal to confirm that no essential data got chewed up in the process:

# Duplicity isn't always a movie starring Julia Roberts...
assert len(df_unique) == len(set(df.index))


Data operations with sharp tools: Say hello to iloc

For index-driven operations, iloc presents effortless data access. After identifying unique indices with np.unique(), iloc becomes your trusty companion:

# Nothing sketchy about using 'iloc'... I swear!
df_unique_iloc = df.iloc[unique_positions].copy()


Special cases and best practices: Being nifty with the iffy

While tackling duplicates, remember edge cases like duplicate indices harboring unique data points, or mammoth datasets that could eat away at your memory. np.unique() can be a silver bullet but remember, always perform small, harmless tests before going full guns blazing on full-scale execution.