Multiple values in one cell

Question

I have data looking somewhat similar to this:

number    type    results
1         5       x, y, z
2         6       a
3         8       x
1         5       x, y

Basically, I have data in Excel that has commas in a couple of individual cells and I need to count each value that is separated by a comma, after a certain requirement is met by subsetting.

Question: How do I go about receiving the sum of 5 when subsetting the data with number == 1 and type == 5, in R?

Answer 1

If we need the total count, then another option is str_count after subsetting

library(stringr)
with(df, sum(str_count(results[number==1 & type==5], "[a-z]"), na.rm = TRUE))
#[1] 5

---

Or with gregexpr from base R

with(df, sum(lengths(gregexpr("[a-z]", results[number==1 & type==5])), na.rm = TRUE))
#[1] 5

If there are no matching pattern for an element, use

with(df, sum(unlist(lapply(gregexpr("[a-z]", 
         results[number==1 & type==5]), `>`, 0)), na.rm = TRUE))

Answer 2

Here is an option using dplyr and tidyr. filter function can filter the rows based on conditions. separate_rows can separate the comma. group_by is to group the data. tally can count the numbers.

dt2 <- dt %>%
  filter(number == 1, type == 5) %>%
  separate_rows(results) %>%
  group_by(results) %>%
  tally()
# # A tibble: 3 x 2
#   results     n
#     <chr> <int>
# 1       x     2
# 2       y     2
# 3       z     1

Or you can use count(results) only as the following code shows.

dt2 <- dt %>%
  filter(number == 1, type == 5) %>%
  separate_rows(results) %>%
  count(results)

DATA

dt <- read.table(text = "number    type    results
1         5       'x, y, z'
                 2         6       a
                 3         8       x
                 1         5       'x, y'",
                 header = TRUE, stringsAsFactors = FALSE)