CODEWITHSUNDEEP
pythonpandasrowfiltering
Carol M
Carol M

Reputation: 131

Filter by row sum and value

I'm trying to filter rows in my DataFrame. I have to filter out all rows that sum to 0 and also all rows that have 5% or more of its values equal to 0.

The DataFrame is 50000 rows x 120 columns. I managed to filter out all rows that sum to 0, but not the ones that have 5% or more of its values equal to 0.

import pandas as pd

df = pd.read_csv("file.
a = df[df.sum(axis=1) > 0] 

gene1   0.000000     0.000000    4108.683105      41.675945        0.000000
gene2   2650.009521  3437.226807  20.767439         0.000000      902.217712

Upvotes: 2

Views: 1632

Answers (1)

Stefan
Stefan

Reputation: 42905

You can filter out non-zero values using .mask():

masked = df.mask(df!=0)

If you then .count(axis=1), you get the count of non-zero values per row, and can obtain a boolean index from these by comparing the result to the column count.

Using the following sample data:

df = pd.DataFrame(np.random.randint(low=0, high=10, size=(100, 50)))
df_colcount = float(len(df.columns))
df['zero_count'] = df.mask(df!=0).count(axis=1)
df['zero_share'] = df.mask(df!=0).count(axis=1).div(df_colcount)

From here you can filter the rows you need:

df[df.zero_share < 0.05]

    0  1  2  3  4  5  6  7  8  9     ...      42  43  44  45  46  47  48  49  \
0   4  0  3  1  6  4  5  8  8  9     ...       4   7   9   4   5   9   4   5   
8   7  1  2  1  5  2  4  4  5  7     ...       5   6   3   3   3   4   9   4   
19  6  6  2  9  2  4  9  8  6  1     ...       2   6   5   9   4   9   7   5   
23  7  8  4  1  4  5  6  5  5  5     ...       3   8   9   8   5   5   5   3   
53  3  7  9  5  0  2  3  3  3  1     ...       5   4   7   1   2   7   7   1   
70  7  9  6  4  4  8  6  3  1  3     ...       1   1   1   9   1   3   1   5   
77  4  4  2  4  2  9  8  2  6  8     ...       8   8   7   8   2   3   5   9   
85  5  7  0  4  6  2  6  5  7  8     ...       9   8   6   6   2   4   5   5   
98  9  9  6  6  4  7  9  1  6  4     ...       4   6   1   2   4   1   8   1   

    zero_count  zero_share  
0            2        0.04  
8            1        0.02  
19           2        0.04  
23           2        0.04  
53           2        0.04  
70           1        0.02  
77           2        0.04  
85           2        0.04  
98           1        0.02

You can of course do all this in one step:

df[df.mask(df!=0).count(axis=1).div(float(len(df.columns))) < 0.05]

Alternatively, you could indeed apply a mask to identify the rows that have non-zero values with .mask(df==0) and then keep only those with over 95% of such values. These are equivalent ways of getting to the same result.

Upvotes: 1

Related Questions