在dataframe中统计每个条目的出现次数

Question

我有以下类型的dataframe (这是简化的示例)：

id = c("1", "1", "1", "2", "3", "3", "4", "4")
bank = c("a", "b", "c", "b", "b", "c", "a", "c")
df = data.frame(id, bank)
df

  id bank
1  1    a
2  1    b
3  1    c
4  2    b
5  3    b
6  3    c
7  4    a
8  4    c

在这个数据文件中，您可以看到，对于某些ids，有多个银行，即id==1、bank=c(a,b,c)。

我想从这个数据中提取的信息是不同银行内部的id与计数之间的重叠。

例如，对于银行a：a有两个人(唯一的ids)：1和4。

• 第1人:b和c银行
• 第4人:c银行

其他银行的总额: 3，其中b= 1，c= 2。

因此，我希望创建一个类似重叠表的输出，如下所示：

bank overlap amount
a    b       1
a    c       2
b    a       1
b    c       2
c    a       2
c    b       2

Answer 1

我花了一段时间才得到结果，所以我把它发了出来。不像罗纳克·沙赫那么性感，但结果是一样的。

id = c("1", "1", "1", "2", "3", "3", "4", "4")
    bank = c("a", "b", "c", "b", "b", "c", "a", "c")
    df = data.frame(id, bank)

df$bank <- as.character(df$bank)

resultlist <- list()
dflist <- split(df, df$id)

for(i in 1:length(dflist)) {
    if(nrow(dflist[[i]]) < 2) {
        resultlist[[i]] <- data.frame(matrix(nrow = 0, ncol = 2))
    } else {
        resultlist[[i]] <- as.data.frame(t(combn(dflist[[i]]$bank, 2))) 
    }
}
result <-  setNames(data.table(rbindlist(resultlist)), c("bank", "overlap"))

result %>%
    group_by(bank, overlap) %>%
    summarise(amount = n())

  bank  overlap amount
  <fct> <fct>    <int>
1 a     b            1
2 a     c            2
3 b     c            2

Answer 2

其中一个选项是full_join

library(dplyr)
full_join(df, df, by = "id") %>%     
      filter(bank.x != bank.y) %>%
      dplyr::count(bank.x, bank.y) %>%
      select(bank = bank.x, overlap = bank.y, amount = n)
# A tibble: 6 x 3
#  bank  overlap amount
#  <fct> <fct>    <int>
#1 a     b            1
#2 a     c            2
#3 b     a            1
#4 b     c            2
#5 c     a            2
#6 c     b            2

Answer 3

你需要在两个方向覆盖两家银行吗？因为在本例中，-> b与b -> a相同。我们可以使用combn并创建唯一的bank组合，每次取2，找出在组合中找到的通用id的length。

as.data.frame(t(combn(unique(df$bank), 2, function(x) 
     c(x, with(df, length(intersect(id[bank == x[1]], id[bank == x[2]])))))))

#  V1 V2 V3
#1  a  b  1
#2  a  c  2
#3  b  c  2

数据

id = c("1", "1", "1", "2", "3", "3", "4", "4")
bank = c("a", "b", "c", "b", "b", "c", "a", "c")
df = data.frame(id, bank, stringsAsFactors = FALSE)