Counting filtered rows inline


#1

Hi - does anyone know if there’s a way to apply a count function to a filter function on the fly?

For example - instead of this:

mtcars %>% 
  filter(cyl==6) %>% 
  nrow()

is something like this possible:

mtcars %>% 
  nrow(filter(cyl==6))

The reason I’m asking is because I want to use this in mutate to add a column that returns a count of the number of filtered rows i.e. let’s say add a column called “num_six_cyl” where the count of six cylinder cars repeats for each row.

If anyone knows DAX, this would be the equivalent of a CALCULATE function where you could put ‘cyl = 6’ in the filters section of the formula.


#2

Hey there – I’m not totally sure what your expected output is, but will this achieve what you want?

library(dplyr)
mtcars %>%
  mutate(num_six_cyl = length(cyl[cyl == 6]))
#>     mpg cyl  disp  hp drat    wt  qsec vs am gear carb num_six_cyl
#> 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4           7
#> 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4           7
#> 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1           7
#> 4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1           7
#> 5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2           7
#> 6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1           7
#> 7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4           7
#> 8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2           7
#> 9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2           7
#> 10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4           7
#> 11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4           7
#> 12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3           7
#> 13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3           7
#> 14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3           7
#> 15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4           7
#> 16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4           7
#> 17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4           7
#> 18 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1           7
#> 19 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2           7
#> 20 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1           7
#> 21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1           7
#> 22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2           7
#> 23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2           7
#> 24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4           7
#> 25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2           7
#> 26 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1           7
#> 27 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2           7
#> 28 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2           7
#> 29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4           7
#> 30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6           7
#> 31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8           7
#> 32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2           7

Instead of filter(), which expects a data frame as input, you can use the base subset operator, [], on the cyl column for the conditions you want within the mutate() call, and then the length of that vector will be the number of rows that meet the given condition (cyl == 6).


#3

It is a variant of @mfherman answer. in R, logical values are converted to 0 and 1 if needed. You can then sum a logical vector to count the number of TRUE values.

library(dplyr, warn.conflicts = F)

mtcars %>%
  mutate(num_six_cyl = sum(cyl == 6))
#>     mpg cyl  disp  hp drat    wt  qsec vs am gear carb num_six_cyl
#> 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4           7
#> 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4           7
#> 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1           7
#> 4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1           7
#> 5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2           7
#> 6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1           7
#> 7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4           7
#> 8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2           7
#> 9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2           7
#> 10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4           7
#> 11 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4           7
#> 12 16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3           7
#> 13 17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3           7
#> 14 15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3           7
#> 15 10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4           7
#> 16 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4           7
#> 17 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4           7
#> 18 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1           7
#> 19 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2           7
#> 20 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1           7
#> 21 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1           7
#> 22 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2           7
#> 23 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2           7
#> 24 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4           7
#> 25 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2           7
#> 26 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1           7
#> 27 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2           7
#> 28 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2           7
#> 29 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4           7
#> 30 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6           7
#> 31 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8           7
#> 32 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2           7

Created on 2018-01-01 by the reprex package (v0.1.1.9000).


#4

Thank you both so much for your prompt replies, this is exactly what I was looking to do.