如何删除DataFrame中多列值相同的行？筛选与ww12有差异的行

Hey there! Let's tackle this problem—since your column names change over time (like ww16, ww17, etc.), we need a flexible solution that doesn't depend on hardcoding specific column names. Below are two practical approaches based on common tools:

Using Excel #

This method uses an auxiliary column to dynamically check all "ww" columns (except ww12) for mismatches:

1. Add an auxiliary column: Insert a new column (e.g., named Check Mismatch) at the end of your table.
2. Enter the dynamic check formula: In the first data row of this new column, paste this formula (adjust the range $A$1:$Z$1 to cover all your header columns):

   =SUMPRODUCT(--(ISNUMBER(SEARCH("ww",$A$1:$Z$1))*($A$1:$Z$1<>"ww12")*($A2:$Z2<>$ww12$2)))>0
   

   What this does:
   • SEARCH("ww",$A$1:$Z$1) finds all columns starting with "ww"
   • $A$1:$Z$1<>"ww12" excludes the ww12 column itself
   • $A2:$Z2<>$ww12$2 checks if each "ww" column's value differs from ww12 in the same row
   • SUMPRODUCT counts the number of mismatches; if it's greater than 0, the formula returns TRUE
3. Filter and keep relevant rows: Filter the auxiliary column to show only TRUE values, then copy these rows to a new sheet or delete the rows marked FALSE.

Using Python Pandas #

If you prefer automated, code-based handling (great for recurring tasks with changing columns):

1. Load your data:

   import pandas as pd
   # Replace with your file path (Excel, CSV, etc.)
   df = pd.read_excel("your_table.xlsx")
   

2. Identify dynamic "ww" columns:

   # Get all columns starting with "ww" except "ww12"
   ww_columns = [col for col in df.columns if col.startswith("ww") and col != "ww12"]
   

3. Create a filter mask:

   # Check if any "ww" column in the row doesn't match ww12
   mismatch_mask = df[ww_columns].ne(df["ww12"], axis=0).any(axis=1)
   

4. Filter your dataframe:

   # Keep only rows where at least one "ww" column differs from ww12
   filtered_df = df[mismatch_mask]
   

5. Save the result:

   filtered_df.to_excel("filtered_table.xlsx", index=False)
   

Both methods work regardless of how many new "ww" columns are added over time—no need to update formulas or code every time column names change!

内容的提问来源于stack exchange，提问作者Minh Tran