mapping over a character string and then pivot_wider the results

Question

I have some data that I am trying to count and pivot_wider. The data looks like:

1   second
2   c("fourth", "fourth", "fourth", "fourth")
3   c("second", "second")
4   c("second", "second")
5   second
6   c("second", "third", "second")
7   fourth
8   fourth
9   c("third", "fourth")
10  c("fourth", "fourth", "fourth", "fourth", "fourth", "fourth")

I am trying to get the data to look like:

    first second third fourth
1            1
2                         4
3            2
4            2
5            1
6            2     1
7                         1
8                         1
9                  1      1
10                        6

Where the numbers are just counts of the number of times the word appears in the column

Data

d1 <- structure(list(quarterMentioned = list("second", c("fourth", 
"fourth", "fourth", "fourth"), c("second", "second"), c("second", 
"second"), "second", c("second", "third", "second"), "fourth", 
    "fourth", c("third", "fourth"), c("fourth", "fourth", "fourth", 
    "fourth", "fourth", "fourth"))), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))

score 2 · Accepted Answer · answered Aug 04 '20 at 13:59

One tidyr solution could be:

d1 %>%
 mutate(quarterMentioned = lapply(quarterMentioned, table)) %>%
 unnest_wider(quarterMentioned)

  second fourth third
    <int>  <int> <int>
 1      1     NA    NA
 2     NA      4    NA
 3      2     NA    NA
 4      2     NA    NA
 5      1     NA    NA
 6      2     NA     1
 7     NA      1    NA
 8     NA      1    NA
 9     NA      1     1
10     NA      6    NA

score 1 · Answer 2 · answered Aug 04 '20 at 13:55

You can create a row number column, unnest the data in long format and get it in wide format with counts using pivot_wider.

library(dplyr)
library(tidyr)

d1 %>%
  mutate(row = row_number()) %>%
  unnest(quarterMentioned) %>%
  pivot_wider(names_from = quarterMentioned, values_from = quarterMentioned, 
              values_fill = 0, values_fn = length)

# A tibble: 10 x 4
#     row second fourth third
#   <int>  <int>  <int> <int>
# 1     1      1      0     0
# 2     2      0      4     0
# 3     3      2      0     0
# 4     4      2      0     0
# 5     5      1      0     0
# 6     6      2      0     1
# 7     7      0      1     0
# 8     8      0      1     0
# 9     9      0      1     1
#10    10      0      6     0

score 1 · Answer 3 · answered Aug 04 '20 at 14:14

Here is a base R option

v <- c("first","second","third","fourth")
as.data.frame(do.call(rbind,Map(function(x) table(factor(x,levels = v)),d1$quarterMentioned)))

such that

   first second third fourth
1      0      1     0      0
2      0      0     0      4
3      0      2     0      0
4      0      2     0      0
5      0      1     0      0
6      0      2     1      0
7      0      0     0      1
8      0      0     0      1
9      0      0     1      1
10     0      0     0      6

mapping over a character string and then pivot_wider the results

3 Answers3