r - 如何转换 table() 命令以从第三列接收频率？

Question

有一个像这样的数据框:

df1 <- data.frame(stock = c("Google, Yahoo", "Google", "Yahoo, Google", "Amazon, Google", "Google, Amazon"), investor = c("Nathalie","George","Nathalie, George", "Melanie, George","Melanie"))

可以使用每个股票频率的频率

table(sapply(strsplit(as.character(df1$stock), ", "), function(x) toString(sort(x))))

如何添加过滤器以获取每只股票的频率，但基于显示投资者选择偏好的第三列。
这是预期输出的示例:

data.frame(investor = c("Nathalie", "George", "George", "George", "Melanie", "Malanie"), stock = c("Google, Yahoo", "Google", "Google, Yahoo", "Amazon, Google", "Amazon, Google", "Amazon"), frq = c(2,1,1,1,1,1))

 investor          stock frq
1 Nathalie  Google, Yahoo   2
2   George         Google   1
3   George  Google, Yahoo   1
4   George Amazon, Google   1
5  Melanie Amazon, Google   1
6  Malanie         Amazon   1

再添加一列:

df1 <- data.frame(stock = c("Google, Yahoo", "Google", "Yahoo, Google", "Amazon, Google", "Google, Amazon"), investor = c("Nathalie","George","Nathalie, George", "Melanie, George","Melanie"), year = c("2017", "2018", "2017", "2018", "2017"))

Answer 1

在我们按照上一步对值进行排序之后，而不是执行 table直接，通过赋值更新列，然后我们可以使用tidyverse拆分“投资者”行并使用 add_count 创建计数列的方法

library(tidyverse)
df1$stock <- sapply(strsplit(as.character(df1$stock), ", "),
                function(x) toString(sort(x)))
df1 %>% 
  mutate_if(is.factor, as.character) %>% 
  separate_rows(investor) %>% 
  add_count(stock, investor, year)