Filter multiple columns based on row values in pandas dataframe

Question

i have a pandas dataframe structured as follow:

In[1]:  df = pd.DataFrame({"A":[10, 15, 13, 18, 0.6],
                           "B":[20, 12, 16, 24, 0.5],
                           "C":[23, 22, 26, 24, 0.4],
                           "D":[9, 12, 17, 24, 0.8 ]})

Out[1]: df 
        A       B       C       D
    0   10.0    20.0    23.0    9.0
    1   15.0    12.0    22.0    12.0
    2   13.0    16.0    26.0    17.0
    3   18.0    24.0    24.0    24.0
    4   0.6     0.5     0.4     0.8

From here my goal is to filter multiple columns based on the last row (index 4) values. More in detail i need to keep those columns that has a value < 0.06 in the last row. The output should be a df structured as follow:

     B       C       
0   20.0    23.0
1   12.0    22.0    
2   16.0    26.0    
3   24.0    24.0    
4   0.5     0.4

I'm trying this:

In[2]: df[(df[["A", "B", "C", "D"]] < 0.6)]

but i get the as follow:

Out[2]:    
       A   B    C   D
   0  NaN  NaN  NaN NaN
   1  NaN  NaN  NaN NaN
   2  NaN  NaN  NaN NaN
   3  NaN  NaN  NaN NaN
   4  NaN   0.5 0.4 NaN

I even try:

df[(df[["A", "B", "C", "D"]] < 0.6).all(axis=0)]

but It gives me error, It doesn't work.

Is there anybody whom can help me?

jezrael · Accepted Answer · 2019-03-15 11:15:55Z

3

Use DataFrame.loc with : for return all rows by condition - compare last row by DataFrame.iloc:

df1 = df.loc[:, df.iloc[-1] < 0.6]
print (df1)
      B     C
0  20.0  23.0
1  12.0  22.0
2  16.0  26.0
3  24.0  24.0
4   0.5   0.4

edited Mar 15, 2019 at 11:15

answered Mar 15, 2019 at 11:14

jezrael

868k102 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter multiple columns based on row values in pandas dataframe

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related