Count instances in list-column row by row

user_5623577 0
Viewed 52

I'm working with an data frame that includes a list-column of integers. Each element in the list-column corresponds to a row in the data frame and now I'd like to count up how many links there are.

The dput includes a column link_count that indicates the correct count for this sample of data:

move link_count links
   1          1    NA
   2          0     1
   3          1    NA
   4          1     3
   5          4     4
   6          1     5
   7          0  5, 6
   8          2     5
   9          0     8
  10          0  5, 8

#dput results saved as `x`
x <- structure(list(move = 1:10, link_count = c(1, 0, 1, 1, 4, 1, 0, 2, 0, 0), links = list(NA_integer_, 1L, NA_integer_, 3L, 4L, 5L, 5:6, 5L, 8L, c(5L, 8L))), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"), .Names = c("move", "link count", "links"))

I found a solution using left_join, but I think there is probably a more elegant solution to do it row-by-row using dplyr::mutate, or purrr::map workflows. I'm hoping for something that could be done in a sequence of pipes. 

#This works, but is there a different way?
left_join(x,
      x %>% unnest(links) %>% count(links),
      by = c("move" = "links"))
purrr dplyr r
3 Answers

A base R option would be. Here, the elements that are missing are NA instead of 0s

as.numeric(table(unlist(x$links))[as.character(x$move)])
user_37322710

Another option with base R based entirely on @akrun answer:

x$n <- as.numeric(table(factor(unlist(x$links), levels = x$move)))
user_68363770

Just for a change of pace, I'll provide a dplyr answer. You can keep the pipe rolling by using a right_join:

x %>%
  unnest(links) %>%
  group_by(links) %>%
  summarise(link_count=n()) %>%
  right_join(x, by=c("links"="move"))

## A tibble: 10 x 4
#   links link_count `link count`   links.y
#   <int>      <int>        <dbl>    <list>
# 1     1          1            1 <int [1]>
# 2     2         NA            0 <int [1]>
# 3     3          1            1 <int [1]>
# 4     4          1            1 <int [1]>
# 5     5          4            4 <int [1]>
# 6     6          1            1 <int [1]>
# 7     7         NA            0 <int [2]>
# 8     8          2            2 <int [1]>
# 9     9         NA            0 <int [1]>
#10    10         NA            0 <int [2]>
user_4968030
Related