Counting the number of instances in a row

#1

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"))
#2

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)

#4

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

#5

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.

#3

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