Convert "type" column into multiple individual columns of comments with their count

user_2359320 0
Viewed 27

I have a dataframe with 1 column and 1 value

df <- data.frame(type = c("recommended: 882, meh: 501, exceptional: 283, skip: 185"),
                 stringsAsFactors=FALSE)

I am trying to convert this type column into multiple individual columns of comments with their count

My desired output is

recommended meh exceptional skip
        882 501         283  185

I am not able to extract the number from the string instead doing it this way

df %>% mutate(ind = row_number()) %>%
  separate_rows(type, sep=",") %>%
  #mutate(f = ifelse(is.na(f),0, f)) %>%
  count(ind, type) %>%
  spread(type, n, fill = 0) %>%
  as.data.frame()

Can someone point me in the right direction?

dplyr tidyverse r
2 Answers

First split on comma into separate rows, then split on colon into separate columns and then get data in wide format.

library(tidyr)
df %>%
  separate_rows(type, sep = ',\\s*') %>%
  separate(type, c('type', 'value'), sep = ':\\s*', convert = TRUE) %>%
  pivot_wider(names_from = type, values_from = value)

#   recommended   meh exceptional  skip
#        <int> <int>       <int> <int>
#1         882   501         283   185
user_39629140

We can use base R

v1 <- strsplit(df$type, "[:,]\\s+")[[1]]
out <-  as.data.frame.list(setNames(v1[c(FALSE, TRUE)], v1[c(TRUE, FALSE)]))
type.convert(out, as.is = TRUE)
#   recommended meh exceptional skip
#1         882 501         283  185
user_37322710
Related