Trying to find count in R "pivot table"

Question

library(dplyr)
data %>% 
  select(trade, before.pay,after.pay,four.after.pay) %>%
  group_by(trade) %>%
  summarise(across(everything(), .f = list(median = median, max = max, min = min, count = n), na.rm = TRUE))

I can get this to work for mean, median, mean, max etc., but not for count. The pay fields are all decimals. How can I count the non-NA values in these fields with summarize?

I did try that too. Thanks: Error: Problem with `summarise()` input `..1`. x Can't convert an integer vector to function — Ben, Apr 20 '22 at 17:15
maybe `count = ~sum(!is.na(.))` ? your `na.rm` will just be ignored for this function. — moodymudskipper, Apr 20 '22 at 17:16

score 3 · Answer 1 · answered Apr 20 '22 at 17:25

Use lambda before n(): count = ~ n() Here is an example:

library(dplyr)
mtcars %>% 
  select(cyl, mpg, disp) %>%
  group_by(cyl) %>%
  summarise(across(everything(), .f = list(median = median, 
                                           max = max, 
                                           min = min,
                                           count = ~ n()), na.rm = TRUE))

    cyl mpg_median mpg_max mpg_min mpg_count disp_median disp_max disp_min disp_count
  <dbl>      <dbl>   <dbl>   <dbl>     <int>       <dbl>    <dbl>    <dbl>      <int>
1     4       26      33.9    21.4        11        108      147.     71.1         11
2     6       19.7    21.4    17.8         7        168.     258     145            7
3     8       15.2    19.2    10.4        14        350.     472     276.          14

score 0 · Answer 2 · answered Apr 21 '22 at 15:15

0

A coworker helped me get this. It doesn't count the NAs.

data %>%
select(trade, before.pay) %>%
group_by(trade) %>%
summarise(across(everything(),.f = list(median = median,n = ~sum(!is.na(.))), na.rm = TRUE)) %>%
ungroup()

answered Apr 21 '22 at 15:15

Ben

153
1
2
8

Trying to find count in R "pivot table"

2 Answers2