Counting and grouping data that have the same value in several columns-CodePudding

I have a hard time doing this particular task: from the data below I want to count the elements that are equal to 0 in all Degrees and group them. In other words, how many X1 have all of the three degrees equal to 0?

Element | Degree A | Degree B | Degree C |
.............................................
 X1 |         0          0          0 
 X1 |         1          1          0
 X1 |         0          0          0 
 X2 |         1          0          1
 X2 |         0          0          0
 X2 |         0          0          0
 X3 |         0          0          0  
 X3 |         1          1          0
 X3 |         0          1          0

This is the desired output:

    Element     All=0 counts
................................
    X1           2
    X2           2
    X3           1

This is what I have tried:

d1 = df.groupby(["Element"])["Degree A"].apply(lambda x: (x==0).sum())

I tried to add the other columns, but it doesn't work

d2 = df.groupby(["Element"])["Degree A"]&["Degree B"]&["Degree C"].apply(lambda x: (x==0).sum())

CodePudding user response：

One reasonable way to do this is to make another column before the groupby:

df['check'] = df['Degree A']   df["Degree B"]   df['Degree C'] == 0

df.groupby(['Element'])['check'].sum()

which gives the desired output.

CodePudding user response：

You can sum the 'Degree' like columns and check whether their result equals to 0 using filter sum(axis=1) eq(0). This results in a boolean series which can be passed into loc which will return dataframe of only the rows that summed to 0.

On this result you can use groupby.size() which will count the occurrence of each element:

df.loc[df.filter(like='Degree').sum(1).eq(0)]\
    .groupby('Element', as_index=False).size()

  Element  size
0      X1     2
1      X2     2
2      X3     1

Data sample:

df = pd.DataFrame(
    {'Element': {0: 'X1',
  1: 'X1',
  2: 'X1',
  3: 'X2',
  4: 'X2',
  5: 'X2',
  6: 'X3',
  7: 'X3',
  8: 'X3'},
 'Degree A': {0: 0, 1: 1, 2: 0, 3: 1, 4: 0, 5: 0, 6: 0, 7: 1, 8: 0},
 'Degree B': {0: 0, 1: 1, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0, 7: 1, 8: 1},
 'Degree C': {0: 0, 1: 0, 2: 0, 3: 1, 4: 0, 5: 0, 6: 0, 7: 0, 8: 0}}
    )