Data manipulation using pandas dataframe

Question

I have MxN numpy array data. they are in csv file and I am reading using pandas module.

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.10    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      1.23    3.21

... ... ... ... ... .. .. ... ....

For my calculation purpose I need two variable. In each row if status is A then corresponding satellite would not considered. So total SV in first row is 3. Similarly 2,1,0,1 for other rows. Another variable is number of count. If any row does not have single B ,then that row would not be counted. last row would be considered as 2 since two B are present.

 #So my expected output is 
 #Total count is 4
 #and number of satellites used is 3,2,1,0,1 in each row or iteration

How can I iterate my data.

Thanks

Answer 1

First filter and compare all data with A, sum Trues and last subtract B by rsub:

a = df.filter(like='state').eq('A').sum(axis=1).rsub(3)
#same as
#a = 3 - (df.filter(like='state') == 'A').sum(axis=1)
print (a)
0    3
1    2
2    1
3    0
4    2
dtype: int64

Detail:

print (df.filter(like='state'))
  state-01 state-02 state-03
0        B        B        B
1        B        B        A
2        B        A        A
3        A        A        A
4        A        B        B

print (df.filter(like='state').eq('A'))
   state-01  state-02  state-03
0     False     False     False
1     False     False      True
2     False      True      True
3      True      True      True
4      True     False     False

For count all non A rows use any for check at least one True and sum:

b = df.filter(like='state').eq('A').any(1).sum()
print (b)
4

print (df.filter(like='state').eq('A').any(1))
0    False
1     True
2     True
3     True
4     True
dtype: bool

---

All together:

mask = df.filter(like='state').eq('A')
a = mask.sum(axis=1).rsub(3)
print (a)
0    3
1    2
2    1
3    0
4    2
dtype: int64

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