我搜索了这个问题的答案并发现了类似的答案（计算每组内的行数 https://stackoverflow.com/questions/9809166/count-number-of-rows-within-each-group, 计算 R 数据帧列中变量值的唯一组合 https://stackoverflow.com/questions/63062416/count-unique-combinations-of-variable-values-in-an-r-dataframe-column?noredirect, R 按组计数元素的出现次数 https://stackoverflow.com/questions/32586674/r-count-occurrences-of-an-element-by-groups）但他们都没有解决我的特定问题。
我有一个带有变量的数据框year, ID, and code。每个人都有一个ID并且可以有多个code（可能）多个过程中的值years.
df = data.frame(ID = c(1,1,1,1, 2,2,2, 3, 4,4,4,4,4,4,4,4, 5,5,5),
year = c(2018, 2018, 2020, 2020,
2020, 2020, 2020,
2011,
2019, 2019, 2019, 2019, 2020, 2020, 2020, 2020,
2018, 2019, 2020),
code = c("A", "B", "C", "D",
"A", "B", "Q",
"G",
"A", "B", "Q", "G", "C", "D", "T", "S",
"S", "Z", "F")
)
df
ID year code
1 1 2018 A
2 1 2018 B
3 1 2020 C
4 1 2020 D
5 2 2020 A
6 2 2020 B
7 2 2020 Q
8 3 2011 G
9 4 2019 A
10 4 2019 B
11 4 2019 Q
12 4 2019 G
13 4 2020 C
14 4 2020 D
15 4 2020 T
16 4 2020 S
17 5 2018 S
18 5 2019 Z
19 5 2020 F
我想要的是另一个数据框，给出两个不同值的次数code在以下组内同时发生ID and year（在这个例子中，A和B共出现3次，A和C共出现0次），然后我将用它来进行网络分析。
到目前为止我有这样的语法：
1：制作宽版数据
library(tidyverse)
wide = df %>%
group_by(year, ID) %>%
mutate(row = row_number()) %>%
ungroup() %>%
pivot_wider(
id_cols = c(ID, year),
names_from = row,
names_prefix = "code_",
values_from = code
)
2：制作节点列表
nodes = distinct(df, code) %>% rowid_to_column("id")
3：制作边缘列表
#edge list needs to be three vars: source, dest, and weight
# source and dest are simply code names that (potentially) co-occur in the same year for an ID
# weight is the number of times the codes co-occurred in the same year for each ID.
#all combinations of two codes
edges = combn(x = nodes$code, m = 2 ) %>%
t() %>%
as.data.frame()
colnames(edges) = c("source", "dest")
edges$weight = NA_integer_
#oh, no! a for() loop! a coder's last ditch effort to make something work
for(i in 1:nrow(edges)){
source = edges$source[i]
dest = edges$dest[i]
#get the cases with the first code of interest
temp = df %>%
filter( code == source ) %>%
select(ID, year)
#get the other codes that occurred for that ID in that year
temp = left_join(temp,
wide,
by = c("ID", "year"))
#convert to a logical showing if the other codes are the one I want
temp = temp %>% mutate_at(vars(starts_with("code_")),
function(x){ x == dest }
)
#sum the number of times `source` and `dest` co-occurred
temp$dest = temp %>% select(starts_with("code_")) %>% rowSums(., na.rm=TRUE)
edges$weight[i] = sum(temp$dest, na.rm = TRUE)
}
编辑以添加结果：
Result:
edges
source dest weight
1 A B 3
2 A C 0
3 A D 0
4 A Q 2
5 A G 1
6 A T 0
7 A S 0
8 A Z 0
9 A F 0
10 B C 0
11 B D 0
12 B Q 2
13 B G 1
14 B T 0
15 B S 0
16 B Z 0
17 B F 0
18 C D 2
19 C Q 0
20 C G 0
21 C T 1
22 C S 1
23 C Z 0
24 C F 0
25 D Q 0
26 D G 0
27 D T 1
28 D S 1
29 D Z 0
30 D F 0
31 Q G 1
32 Q T 0
33 Q S 0
34 Q Z 0
35 Q F 0
36 G T 0
37 G S 0
38 G Z 0
39 G F 0
40 T S 1
41 T Z 0
42 T F 0
43 S Z 0
44 S F 0
45 Z F 0
这给了我我想要的（一个数据框显示A和B共出现3次，A和C共出现0次，A和D共出现0次，A和G共出现1次，A和Q共出现1次） -发生2次，等等...）。所以这可行，但即使对于这个小例子也需要一两秒钟。我的真实数据集约为 3,000,000 个观察值。我让它运行了一段时间，但停下来却发现它已经完成了约 1%。
有更好/更快的方法来做到这一点吗？
