chunjin chunjin - 3 months ago 11
R Question

Transform ids -> items to {pairs of ids} -> items

I have a data.frame like this:



x1 <- data.frame(id=1:3,item=c("A","B","A","B","C","D"))
x1[order(x1$item),]
id item
1 1 A
3 3 A
2 2 B
4 1 B
5 2 C
6 3 D


I want to get :



id1=c(1,2,1,3,2,3)
id2 = c(2,1,3,1,3,2)
A=c(0,0,1,1,0,0)
B=c(1,1,0,0,0,0)
C = 0
D=0
datawanted <- data.frame(id1,id2,A,B,C,D)
id1 id2 A B C D
1 1 2 0 1 0 0
2 2 1 0 1 0 0
3 1 3 1 0 0 0
4 3 1 1 0 0 0
5 2 3 0 0 0 0
6 3 2 0 0 0 0


if person1 and person2 both have B,then in the datawanted dataframe,column A ,got 1,else get 0.

Can someone give me some suggestions or functions in R,to deal with this problem?

Answer

Cool question. You have a bipartite graph, so following Gabor's tutorial...

library(igraph)
g = graph_from_edgelist(as.matrix(x1))
V(g)$type = grepl("[A-Z]", V(g)$name)

For OP's desired output, first we can extract the incidence matrix:

gi = get.incidence(g)
#   A B C D
# 1 1 1 0 0
# 2 0 1 1 0
# 3 1 0 0 1

Note (thanks @thelatemail), that if you don't want to use igraph, you can get to gi as table(x1).

Then, we look at the combinations of ids:

res = t(combn(nrow(gi), 2, function(x) c(
    as.integer(rownames(gi)[x]), 
    pmin( gi[x[1], ], gi[x[2], ] ) 
)))

dimnames(res) <- list( NULL, c("id1", "id2", colnames(gi)))
#      id1 id2 A B C D
# [1,]   1   2 0 1 0 0
# [2,]   1   3 1 0 0 0
# [3,]   2   3 0 0 0 0

This essentially is the OP's desired output. They had included redundant rows (e.g., 1,2 and 2,1).


Fun reason to use a graph (ht Chris):

V(g)$color <- ifelse(V(g)$type, "red", "light blue")
V(g)$x     <- (1:2)[ V(g)$type + 1 ]
V(g)$y     <- ave(seq_along(V(g)), V(g)$type, FUN = seq_along)
plot(g)

enter image description here

Or, apparently this can be done more or less like

plot(g, layout = layout.bipartite(g)[,2:1])
Comments