4

I need to group my data frame by two columns and after that count the occurrences of values from third column, which are between 1 and 20.

Data frame:

col1  col2  value
  a     b     1
  a     b     3
  a     b     22
  a     c     0
  a     c     3
  a     c     19

Result:

col1  col2  counter
 a     b      2
 a     c      2

My code:

counter = data_frame.groupby(['column1', 'column2'])[((data_frame['value'] >= 1) & (data_frame['value'] < 20))].sum()

Any ideas?

bartblons
  • 125
  • 2
  • 7

2 Answers2

4

You need filter first by boolean indexing or query and then groupby with aggregating size:

df = data_frame[(data_frame['value'] >= 1) & (data_frame['value'] < 20)]
df = df.groupby(['col1', 'col2']).size().reset_index(name='counter')
print (df)
  col1 col2  counter
0    a    b        2
1    a    c        2

Or:

df = data_frame.query('value >= 1 & value < 20')
df = df.groupby(['col1', 'col2']).size().reset_index(name='counter')
print (df)
  col1 col2  counter
0    a    b        2
1    a    c        2

What is the difference between size and count in pandas?

Graham
  • 7,431
  • 18
  • 59
  • 84
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
0

You need to filter those value first, then you can use the groupby and the count functions like this : 

df[(df.value<=20) & (df.value >= 1)].groupby(['col1','col2']).count().reset_index()

output : 

    col1    col2    value
0   a       b       2
1   a       c       2


100 loops, best of 3: 2.5 ms per loop
Tbaki
  • 1,013
  • 7
  • 12