Tukukkk

So, I currently have two data frames. One contains information on nodes for a given network and it looks something like this:

id age

01  14

02  23

03  52

04  41

05  32

The other data frame contains the information for the connections between the nodes and looks something like this:

id1 id2

01  02

01  05

03  04

05  02

So given these 2 data frames what I want to do is combine them in such a way that the resultant data frame looks like the first one but now has columns for every connection that the node has as well as a column that contains the number of connections that node has (assuming that it never exceeds 5). So it looks something like this:

id age  friend1 friend2  Connections

01  14  02     05        2

02  23  01     05        2

03  52  04               1

04  41  03               1

05  32  01     02        2  

With dplyr and tidyr:

df1<-structure(list(id = 1:5, age = c(14L, 23L, 52L, 41L, 32L)), .Names = c("id", "age"), class = "data.frame", row.names = c(NA, -5L))

df2<-structure(list(id1 = c(1L, 1L, 3L, 5L), id2 = c(2L, 5L, 4L, 2L)), .Names = c("id1", "id2"), class = "data.frame", row.names = c(NA, -4L))



require(dplyr,tidyr)

dff<-left_join(df1,df2,by=c(id="id1")) %>% 

  left_join(df2,by=c(id="id2")) %>% 

  gather("friend","friend_id",id1,id2) %>%

  filter(!is.na(friend_id)) %>%

  group_by(id) %>%

  mutate(friend=paste0("friend",row_number()),connections=n()) %>%

  spread(friend,friend_id)



# A tibble: 5 x 5

# Groups:   id [5]

     id   age connections friend1 friend2

  <int> <int>       <int>   <int>   <int>

1     1    14           2       2       5

2     2    23           2       1       5

3     3    52           1       4      NA

4     4    41           1       3      NA

5     5    32           2       1       2

It might be better to convert the second data.frame to adjacency matrix.

# DATA

df1 = structure(list(id = c("01", "02", "03", "04", "05"), age = c(14, 

23, 52, 41, 32)), class = "data.frame", row.names = c(NA, -5L))



df2 = structure(list(id1 = c("01", "01", "03", "05"), id2 = c("02", 

"05", "04", "02")), class = "data.frame", row.names = c(NA, -4L))



library(igraph)

g = graph.data.frame(df2)

m = as.matrix(get.adjacency(g))

m

#   01 03 05 02 04

#01  0  0  1  1  0

#03  0  0  0  0  1

#05  0  0  0  1  0

#02  0  0  0  0  0

#04  0  0  0  0  0



pmax(rowSums(m), colSums(m)) #Total Connections

#01 03 05 02 04 

# 2  1  1  2  1



#Add a column for age

mydat = as.data.frame(m)

mydat$id = row.names(mydat)

merge(mydat, df1, by = "id")

#  id 01 03 05 02 04 age

#1 01  0  0  1  1  0  14

#2 02  0  0  0  0  0  23

#3 03  0  0  0  0  1  52

#4 04  0  0  0  0  0  41

#5 05  0  0  0  1  0  32