Calculate amount of rows satisfy condition in multiple columns-CodePudding

import pandas as pd


# initialize list of lists
data = [['tom', 'Y','Y','N'], ['nick', 'N','N','N'], ['juli', 'N','Y','N'],
       ['Luc', 'Y','Y','N'], ['Adg', 'Y','N','N'], ['Flav', 'N','Y','N'],
       ['Alf', 'Y','Y','N'], ['Jut', 'Y','N','N'], ['Uan', 'Y','Y','Y']]
 
# Create the pandas DataFrame
df = pd.DataFrame(data, columns = ['Name', 'Tipo_1', 'Tipo_2', 'Tipo_3'])
 
# print dataframe.
df



        Name    Tipo_1  Tipo_2  Tipo_3
0       tom     Y       Y       N
1       nick    N       N       N
2       juli    N       Y       N
3       Luc     Y       Y       N
4       Adg     Y       N       N
5       Flav    N       Y       N
6       Alf     Y       Y       N
7       Jut     Y       N       N
8       Uan     Y       Y       Y

Hi, folks. I would like to calculate how many rows are into each column combination instead to write each condition like that:

len(df[(df['Tipo_1'] == 'Y') & (df['Tipo_2'] == 'Y') & (df['Tipo_3'] == 'Y')])
len(df[(df['Tipo_1'] == 'N') & (df['Tipo_2'] == 'Y') & (df['Tipo_3'] == 'Y')])
len(df[(df['Tipo_1'] == 'Y') & (df['Tipo_2'] == 'N') & (df['Tipo_3'] == 'Y')])
len(df[(df['Tipo_1'] == 'Y') & (df['Tipo_2'] == 'Y') & (df['Tipo_3'] == 'N')])
...

And try to plot the result into barplot.

Thank you!

CodePudding user response：

just group by them:

df =  df.groupby(['Tipo_1', 'Tipo_2','Tipo_3']).count().reset_index()
print(df)

output:

>>>
  Tipo_1 Tipo_2 Tipo_3  Name
0      N      N      N     1
1      N      Y      N     2
2      Y      N      N     2
3      Y      Y      N     3
4      Y      Y      Y     1

CodePudding user response：

Use value_counts:

out = df.filter(like='Tipo').value_counts().rename('Count').reset_index()
print(out)

# Output
  Tipo_1 Tipo_2 Tipo_3  Count
0      Y      Y      N      3
1      N      Y      N      2
2      Y      N      N      2
3      N      N      N      1
4      Y      Y      Y      1