pandas中drop_duplicates()函数失效问题求助

Hey there! Let's break down why drop_duplicates() isn't working as expected for removing those duplicate header rows—this is a super common gotcha when working with messy datasets, so I’ve got you covered.

Common Reasons & Fixes #

• You’re misunderstanding what drop_duplicates() does
  By default, this function removes rows that are identical to other data rows, not rows that match your DataFrame’s header. If your file has repeated header lines embedded in the data (like a CSV that repeats headers every 100 rows), drop_duplicates() won’t flag these unless another data row is identical to them. Instead, you need to explicitly target rows that match your header values:

  # Remove any row that exactly matches the header
  df = df[~df.eq(df.columns).all(axis=1)]
  

  This checks each row to see if every value matches the corresponding column name, then keeps only the rows that don’t meet that condition.

• The "duplicate header" rows aren’t actually identical to the header
  Tiny differences like whitespace, case sensitivity, or data types can break the match. For example, your header might be "CustomerID" but the duplicate row has "customerid" or "Customer ID" (with a space). Fix this by standardizing both the header and data rows first:

  # Standardize header (strip whitespace, lowercase)
  standardized_header = [col.strip().lower() for col in df.columns]
  
  # Standardize each row and check against the header
  df = df[~df.apply(
      lambda row: [str(val).strip().lower() for val in row] == standardized_header,
      axis=1
  )]
  

• You didn’t save the result
  Pandas methods like drop_duplicates() return a new DataFrame by default—they don’t modify the original one. If you ran df.drop_duplicates() without assigning it back or using inplace=True, your original DataFrame stayed the same. Make sure to do one of these:

  # Option 1: Assign the result to df
  df = df.drop_duplicates()
  
  # Option 2: Modify in place
  df.drop_duplicates(inplace=True)
  

• Your duplicate rows have missing values
  If the header has no missing values but the duplicate row has NaN in some columns, df.eq(df.columns).all(axis=1) will return False for that row. You can adjust the check to handle NaNs by using a fill value:

  def matches_header(row):
      # Fill NaNs with a placeholder that won't match the header
      return row.fillna("").equals(pd.Series(df.columns).fillna(""))
  
  df = df[~df.apply(matches_header, axis=1)]
  

Quick Test to Diagnose #

First, run this to see if any rows actually match your header:

matching_rows = df[df.eq(df.columns).all(axis=1)]
print(f"Found {len(matching_rows)} rows matching the header:")
print(matching_rows)

If this returns zero rows, you know the issue is with mismatched values (case, whitespace, etc.). If it returns rows, then the fix above will remove them.

内容的提问来源于stack exchange，提问作者bhaskar das