Counting the number of instances in a row

I have a dataset with 99 rows with 43 columns. I want to summarise() the number of times a specific value occurred in each row. I've tried the rowSums() but keep getting an error message.

head(townRatings)
 Municipality   2002  2003  2004  2005  2006  2007  2008 
1        Acton  Aaa   Aaa   Aa1   Aa3   Aaa   Aa2   Aa1
2    Arlington  Baa   Aa1   Aa3   Aaa   Aa2   Aaa   Baa  
3      Ashland  A1    Aa3   Aa3   Aaa   Aa2   Aa1   Aaa

What I want is:

Municipality  Aaa   Aa1  Aa2  Aa3 A1 Baa
Acton         3     2    1    1   0  0
Arlington     2     1    1    1   0  2
Ashland       2     1    1    2   1  0     

I tried to implement the following code:

townRatingsCounts <- townRatings %>% group_by(Municipality) %>% 
summarise(Aaa == rowSums("Aaa"), Aa1 == rowSums("Aa1"), Aa2 == rowSums("Aa2"), 
Aa3 ==rowSums("Aa3"), A1 == rowSums("A1"), Baa == rowSums("Baa"))

This kind of thing is done using pivot_longer and pivot_wider.

library(tidyverse)

townRatingsCounts <- tibble::tribble(
  ~Municipality, ~`2002`, ~`2003`, ~`2004`, ~`2005`, ~`2006`, ~`2007`, ~`2008`,
  "Acton", "Aaa", "Aaa", "Aa1", "Aa3", "Aaa", "Aa2", "Aa1",
  "Arlington", "Baa", "Aa1", "Aa3", "Aaa", "Aa2", "Aaa", "Baa",
  "Ashland", "A1", "Aa3", "Aa3", "Aaa", "Aa2", "Aa1", "Aaa"
)

townRatingsCounts %>%
  pivot_longer(cols = starts_with("200")) %>% 
  group_by(Municipality, value) %>%
  summarise(count = n()) %>%
  ungroup() %>%
  pivot_wider(names_from = "value", values_from = "count", values_fill = 0)
#> `summarise()` regrouping output by 'Municipality' (override with `.groups` argument)
#> # A tibble: 3 x 7
#>   Municipality   Aa1   Aa2   Aa3   Aaa   Baa    A1
#>   <chr>        <int> <int> <int> <int> <int> <int>
#> 1 Acton            2     1     1     3     0     0
#> 2 Arlington        1     1     1     2     2     0
#> 3 Ashland          1     1     2     2     0     1

Created on 2020-11-13 by the reprex package (v0.3.0)

Please provide some of your data using dput(head(Type3Moody ))

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

I have tried this code and continue to get an error message.


> Type3MoodyCount1 <- Type3Moody %>% pivot_longer(cols = starts_with("X")) %>% group_by(Municipality, value) %>% summarise(count = n()) %>% ungroup() %>% pivot_wider(names_from = "value", values_from = "count", values_fill = 0)
`summarise()` regrouping output by 'Municipality' (override with `.groups` argument)
Error in values_fill[[value]] : subscript out of bounds

The year columns start with an X