How to filter data from csv file based on certain conditions using pandas dataframe

Question

I have below data. These are satellite number ,its state and value. Here Value of status 'B' is changing either 0 or some value. Value of status 'A' is always 0. Sat with status A and Val=0 for corresponding status 'B'is not acceptable and not counted.

My aim is to iterate through each row and find correct Satellite present at each row. If any row is all A or B=0 that row is not counted.

So my desire output is : a=3,1,1,1 Count=4 ,Row 4 is not counted

sv-01   sv-02  SV-03  state-01 state-02 state-03    val-01   val-02  val-03
 7        12     8         B          B         B     .23     0.34    1.03
 7        12     8         B          B         A     .35     0       0
 7        12     8         B          A         A     1.45    0       0
 7        12     8         A          A         A      0      0       0
 7        12     8         A          B         B      0      0     3.21

# My python implementation is

  mask = read_data.filter(like='state').eq('A')
  result_count_Nj1 = mask.sum(axis=1).rsub(3)

# I have tried

mask = read_data.filter(like='state').eq('A')  and read_data.filter(like='state').eq('B'!=0)

# But it shows error. Please suggest where I am making mistake

Thanks

I'm not sure I understand what you mean by Value, State, and Status; that said, would this framework be helpful? df2 = df.drop(df[(df['state-01'] == 'A') & (df['val-02'] == 0)].index) From: stackoverflow.com/questions/13851535/… — Evan
– Evan, Commented Dec 4, 2017 at 5:20
@ Evan.Yeah it would be state. Value is nothing but val-01,val-02,val-03. Then how would I count number of satellite at each row and number of count — Poka
– Poka, Commented Dec 4, 2017 at 5:23

jezrael · Accepted Answer · 2017-12-04 06:47:04Z

1

I believe you need:

#check B values
maskB = read_data.filter(like='state') == 'B'
print (maskB)
   state-01  state-02  state-03
0      True      True      True
1      True      True     False
2      True     False     False
3     False     False     False
4     False      True      True

#check not 0 values for B values only
mask0 = read_data.filter(like='val').where(maskB.values, 0) != 0
print (mask0)
   val-01  val-02  val-03
0    True    True    True
1    True   False   False
2    True   False   False
3   False   False   False
4   False   False    True

a = mask0.sum(1)
print (a)
0    3
1    1
2    1
3    0
4    1
dtype: int64

b = mask0.any(1).sum()
print (b)
4

answered Dec 4, 2017 at 6:47

jezrael

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

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

4 Comments

Poka Over a year ago

@ Jezrael . You are genius man !! Have tried many conditions for hours but not useful

jezrael Over a year ago

Glad can help! Nice day!

Poka Over a year ago

@ Jezrael. I was close to maskB but did not apply mask.values :)

jezrael Over a year ago

Yes, there is problem need same columns names in both masks or simplier use values for numpy array ;)

Collectives™ on Stack Overflow

How to filter data from csv file based on certain conditions using pandas dataframe

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related