Counts of particular strings in columns

Question

I want to count the number of "Agree" and "Disagree" in each column. ALl I have done so far is some recoding of the data, but how do I get the counts for each column. I have tried summarise_all but this has not worked. I am trying to work in dplyr

new<-subjects1%>%
  filter(Qx1_mod1=='Health, Public Services and Care')%>%
  mutate_all(funs(str_replace(., "Strongly agree", "Agree")))%>%
  mutate_all(funs(str_replace(., "Strongly disagree", "Disagree")))%>%
  mutate_all(funs(str_replace(., "Neither agree nor disagree", "Neither")))

A little bit of sample data would help. Probably the easiest solution is to convert to a long format, but it's hard to know for sure, and hard to demonstrate, without an idea of what your data looks like. — Gregor Thomas, Sep 19 '19 at 14:07
I see you've asked a few R questions on here where you've been asked to share data. Please make it a habit when asking questions---it makes it much easier and faster to help. If you don't share data, we just end up asking you to share data, which wastes time for everyone. [This FAQ](https://stackoverflow.com/q/5963269/903061) is full of resources for creating a reproducible example. We just need enough data to illustrate the problem and get a sense for your data structure. `dput(subjects1[1:10, 1:5])` is a great way to share the first 10 rows of the first 5 columns, which is probably plenty. — Gregor Thomas, Sep 19 '19 at 14:11
@Gregor is right. You see that you have received a couple of very different solutions since nobody knows how your actual data looks like and everyone seems to imagine something else it seems. — JBGruber, Sep 19 '19 at 14:37

score 1 · Answer 1 · answered Sep 19 '19 at 14:22

1

Base R solution:

df <- data.frame(
  col1 = c("", "somewhat agree", "agree"),
  col2 = c("", "I agree", "agree"),
  col3 = c("agree", "agree", "agree"),
  col4 = c("disagree", "agree, Agree", "agree, agree"), stringsAsFactors = FALSE
)

pattern <- "(^a| a)gree"
res <- colSums(apply(df, 2, function(x, s = pattern) grepl(s, x, ignore.case = TRUE)))

> res
# col1 col2 col3 col4 
#   2    2    3    2

answered Sep 19 '19 at 14:22

slava-kohut

4,203
1
7
24

why use `"(^a| a)gree"` and not just "agree"? Could you explain? – JBGruber Sep 19 '19 at 14:25
1

I do not want to count `disagree` as `agree` – slava-kohut Sep 19 '19 at 14:26
Good thinking. I altered my pattern as well. But I prefer word boundaries (`\\b`). – JBGruber Sep 19 '19 at 14:29

JBGruber · Answer 2 · 2019-09-19T14:28:57.590

A simple setup would be something like this:

library(dplyr)
library(stringr)
subjects1 <- tibble(
  col1 = c("", "", "agree"),
  col2 = c("", "agree", "agree"),
  col3 = c("agree", "agree", "agree"),
  col4 = c("agree", "agree, agree", "agree, agree"),
  col5 = c("disagree", "agree, Agree", "Neither")
)

subjects1 %>% 
  mutate_all(function(x) str_count(x,  regex(pattern = "\\bagree\\b", ignore_case = TRUE))) %>%
  colSums()
#> col1 col2 col3 col4 col5 
#>    1    2    3    5    2

^{Created on 2019-09-19 by the reprex package (v0.3.0)}

score 0 · Answer 3 · answered Sep 19 '19 at 14:24

If you want to produce a simple frequency cross-table, you can always use table like this:

col_1 <- c("Agree","Agree","Disagree","Neither","Disagree", "Agree")
col_2 <- c("Neither","Neither","Disagree","Neither","Disagree", "Agree")

data.frame(col_1,col_2) %>%
  gather(columns,values) %>%
  table()

# output 
columns Agree Disagree Neither
  col_1     3        2       1
  col_2     1        2       3

Counts of particular strings in columns

3 Answers3