Importing non-rectangular data as rectangular in R

Question

I need to load social network data where each user has an unknown and potentially large number of friends, stored as a text file of the following format:

UserId: FriendId1, FriendId2, ...
1: 12, 33
2:
3: 4, 6, 10, 15, 16

into a two-column data.frame:

  UserId FriendId
1      1       12
2      1       33
3      3        4
4      3        6
5      3       10
6      3       15
7      3       16

How would you do that in R?

Reading, filling and then reshaping is inefficient as it requires to keep in memory many columns full of NA.

Related questions here, and here.

Answer 1

If you really have a colon as a delimiter, then just use read.table with header = FALSE to get your data into R, then consider using cSplit from my "splitstackshape" package.

mydf <- read.table("test.txt", sep = ":", header = FALSE)
mydf
##   V1                V2
## 1  1            12, 33
## 2  2                  
## 3  3  4, 6, 10, 15, 16

library(splitstackshape)
cSplit(mydf, "V2", ",", "long")
##    V1 V2
## 1:  1 12
## 2:  1 33
## 3:  3  4
## 4:  3  6
## 5:  3 10
## 6:  3 15
## 7:  3 16