如何使用Pandas筛选仅含单个非零值的行？

Problem Context #

I have the following Pandas DataFrame:

I need to filter rows where only one non-zero value exists across the Value* columns. For example:

• ID=1 has two non-zeros, so it's excluded
• ID=3 and ID=4 have exactly one non-zero, so these are the rows we want

Desired Output #

Solution #

Here's a straightforward approach to achieve this with Pandas:

Step-by-Step Explanation #

1. Calculate non-zero counts per row: We first create a boolean mask where each cell is True if it's non-zero (ignoring the ID column). Then we sum these booleans across each row to get the total number of non-zero values.
2. Filter rows with exactly one non-zero: Use the count to create a filter and apply it to the original DataFrame.

Full Code #

import pandas as pd

# Create the original DataFrame
data = {
    'ID': [1, 2, 3, 4, 5],
    'Value1': [0, 0, 0, 0, 50],
    'Value2': [0, 0, 0, 100, 0],
    'Value3': [50, 0, 0, 0, 100],
    'Value4': [100, 0, 50, 0, 50],
    'Value5': [0, 0, 0, 0, 50]
}
df = pd.DataFrame(data)

# Count non-zero values in each row (exclude ID column)
non_zero_count = df.drop('ID', axis=1).ne(0).sum(axis=1)

# Keep only rows with exactly one non-zero value
filtered_df = df[non_zero_count == 1]

# Print the result
print(filtered_df)

Breakdown of Key Lines #

• df.drop('ID', axis=1): Removes the ID column so we only count non-zeros in the Value columns.
• .ne(0): Short for "not equal to 0" — returns a boolean DataFrame marking non-zero cells.
• .sum(axis=1): Sums the boolean values (True=1, False=0) horizontally across each row to get the non-zero count.
• df[non_zero_count == 1]: Filters the original DataFrame to retain only rows where the non-zero count is exactly 1.

Output #

Running this code will give you exactly the desired result:

ID  Value1  Value2  Value3  Value4  Value5
2   3       0       0       0      50       0
3   4       0     100       0       0       0

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