Combine multiple rows in dataset

Question

I have a dataset with about 20 million rows with the following format:

Userid attributid timeid
1      -1         0
1      -2         0
1      -3         0
1      -4         0
1      -5         0
...

and another index that match the attributeid to one of the four attribute type:

attributeid attributetype
-1          A
-2          B
-3          C
-4          D
-5          B

I would like to batch import the dataset into neo4j by converting it into the following format:

UserID A     B      C     D     timeid
1      -1    -2,-5  -3    -4    0

I tried R by order the dataframe using userid and scanning through it. But it was too slow. I was wondering what is the most time efficient way to do that? Or is there any thing I can do to optimize my code? Here is my code:

names(node_attri)[1] = 'UserID'
names(node_attri)[2] = 'AttriID'
names(node_attri)[3] = 'TimeID'
names(attri_type)[1] = 'AttriID'
names(attri_type)[2] = 'AttriType'
#attri_type <- attri_type[order(attri_type),]
#node_attri <- node_attri[order(node_attri),]

N = length(unique(node_attri$TimeID))*length(unique(node_attri$UserID))
new_nodes = data.frame(UserID=rep(NA,N), employer=rep(NA,N), major=rep(NA,N), 
                  places_lived=rep(NA,N), school=rep(NA,N), TimeID=rep(NA,N))
row = 0
start = 1
end = 1
M =nrow(node_attri)  
while(start <= M) {
  row = row + 1
  em = ''
  ma = ''
  pl = ''
  sc = ''
  while(node_attri[start,1] == node_attri[end,1]) {
    if (attri_type[abs(node_attri[end,2]),2] == 'employer') 
       em = paste(em, node_attri[end,2], sep=',')
    else if (attri_type[abs(node_attri[end,2]),2] == 'major')
       ma = paste(ma, node_attri[end,2], sep=',')
    else if (attri_type[abs(node_attri[end,2]),2] == 'places_lived')
       pl = paste(pl, node_attri[end,2], sep=',')
    else if (attri_type[abs(node_attri[end,2]),2] == 'school')
      sc = paste(sc, node_attri[end,2], sep=',')
    end = end + 1
    if (end > M) break
  }
  new_nodes[row,] = list(UserID=node_attri[start,1], employer=substring(em,2), 
                       major=substring(ma,2), places_lived=substring(pl,2), 
                       school=substring(sc,2), TimeID=node_attri[start,3])
  start = end
  end = start
}
new_nodes = new_nodes[1:row,]

Answer 1

You need to merge, aggregate and then reshape. Assuming your dataframes are DFand DF2respectively:

x <- merge(DF, DF2)
y <- aggregate(attributeid~., data=x, FUN=function(x)paste(x, collapse=","))
z <- reshape(y, direction="wide", idvar=c("Userid","timeid"), timevar="attributetype")

Result:

> z
  Userid timeid attributeid.A attributeid.B attributeid.C attributeid.D
1      1      0            -1         -5,-2            -3            -4

Renaming and rearranging columns is trivial.