Pandas If function or groupby

Question

New to Pandas. I had this code originally written to read .csv and now I am writing to read .xlsx files. Anyway, I used if function before to read if Valid Part == 'YES' then ..... follow rest of code.

Now that I am using Pandas I have been testing groupby to achieve my count but haven't quite figured it out.

I am looking in this example if Valid Part == 'Yes' and Appl Req == 'Yes' give me the count.

Any advise is greatly appreciated.

import pandas as pd

df = pd.read_excel('IMPORT.xlsx')

app_req = df.groupby(['Valid Part', 'Appl Req']).count()

print(app_req)

Sample of Data

Answer 1

I think you need filter by boolean indexing or query first and then aggregate by size:

df = df[(df['Valid Part'] == 'Yes') & (df['Appl Req'] == 'Yes')]
app_req = df.groupby(['Valid Part', 'Appl Req']).size()

What is the difference between size and count in pandas?

EDIT:

Sample:

np.random.seed(100)
N = 10
df = pd.DataFrame(np.random.choice(['Yes','No'], size=(N,3)), 
                  columns=['Valid Part', 'Appl Req', 'A'])
print (df)
   Valid Part Appl Req    A
0         Yes      Yes   No
1          No       No   No
2         Yes      Yes  Yes
3         Yes      Yes   No
4         Yes      Yes  Yes
5         Yes       No  Yes
6         Yes       No  Yes
7          No      Yes  Yes
8         Yes       No   No
9          No      Yes  Yes

It seems you need only sum of True values:

print ((df['Valid Part'] == 'Yes') & (df['Appl Req'] == 'Yes'))
0     True
1    False
2     True
3     True
4     True
5    False
6    False
7    False
8    False
9    False
dtype: bool

app_req = ((df['Valid Part'] == 'Yes') & (df['Appl Req'] == 'Yes')).sum()
print (app_req)
4

---

df = df[(df['Valid Part'] == 'Yes') & (df['Appl Req'] == 'Yes')]
app_req = df.groupby(['Valid Part', 'Appl Req']).size().reset_index(name='COUNT')
print (app_req)
  Valid Part Appl Req  COUNT
0        Yes      Yes      4