R: condense row's category and split the count

In r, I have a data frame

data.frame(value1=c("apple", "orange","banana","apple,orange"), count=c(2,4,6,2))

I want the date frame to become

data.frame(value1=c("apple", "orange","banana"), count=c(3,5,6))

by eliminating the row "apple, orange", and add counts to "apple" and "orange"

I've tried to use

df$value1 <- unlist(strsplit(as.character(df$value1), ","))

, but I think this is the wrong approach...

Thank you!

Similar idea as akrun's but using slightly different functions:

df %>%
   mutate(count = count / (1+str_count(value1, ',')))%>%
   separate_rows(value1) %>%
   count(value1, wt = count)

# A tibble: 3 × 2
  value1     n
  <chr>  <dbl>
1 apple      3
2 banana     6
3 orange     5

In base R:

 a <- strsplit(df$value1, ",")
 b <- df$count/(nchar(gsub("[^,]", "", df$value1)) + 1)
 stack(tapply(rep(b, lengths(a)), unlist(a), sum))
  values    ind
1      3  apple
2      6 banana
3      5 orange

We could recalibrate the count values by dividing with the count of words i.e. assuming each entity is separated by comma, count the number of comma and add 1, only for those having comma character and then separate the value1 column, do a group by sum (reframe)

library(dplyr) # version >= 1.1.0
library(tidyr)
library(stringr)
 df1 %>% 
   mutate(count = case_when(str_detect(value1, ",") ~
      count/(str_count(value1, ",") + 1), TRUE ~ count)) %>% 
   separate_longer_delim(value1, delim = regex(",\\s*")) %>% 
   reframe(count = sum(count), .by = value1)

-output

  value1 count
1  apple     3
2 orange     5
3 banana     6

I actually figured out a dumb way...

#reframing the problem
df0 <-data.frame(value1=c("apple", "orange","banana","apple,orange", "orange,banana"), count=c(2,4,6,2,4))

#reframing the ideal solution
df0 <-data.frame(value1=c("apple", "orange","banana"), count=c(2,4,6))

### my solution: ###


df1 <-
#select the rows to be mutated
  df0[str_detect(df0$value1,","),]%>%
#separate the values into two columns
  separate(col = value1, into = paste0("fruit", 1:2), sep = ",")%>%
#divide the count by two
  mutate(count = count/2)%>%
#turn columns into rows
  pivot_longer(fruit1:fruit2, names_to = "longername", values_to = "value1") %>%
#remove the unneeded column
  select(-"longername") %>%
#add back the og column that has single value
  rbind(df0[!str_detect(df0$value1,","),])%>%
#recalculate the count
  group_by(value1)%>%
  summarise(newcount = sum(count))