Geek Answers Handbook

Add a row of 0 for each identifier in a large database

I have a database where the first row of each of the unique identifiers is missing. Basically, I need to add a new line consisting of 0 for each unique identifier.

My database looks like this (I have over a million rows, so loops are basically impossible).

dt = as.data.frame( rbind(c('A1', '15', '1'), 
  c('A1', '17', '2'), 
  c('A1', '12', '3'), 
  c('B1', '3', '1'), 
  c('B1', '4', '2'), 
  c('B1', '15', '3')))

colnames(dt) = c('id', 'activity', 'time')

For each id, I need to add line 0 at time 0.

The following line of codes works, but it takes too much time for my database.

IdUnique = length(unique(dt$id))
VeK = vector('list',  IdUnique)
for(i in 1:IdUnique){  
  row0 = matrix(0, nrow = 1, ncol = ncol(dt), dimnames = list(unique(dt$id)[i], colnames(dt)))
  VeK[[i]] = rbind(row0, subset(dt, id == unique(dt$id)[i]) )
  VeK[[i]][,'id'] <- unique(dt$id)[i]
}

dt2 <- do.call("rbind", VeK)

I was wondering if there was a more economical solution, for example, merging strings and by id. But I can’t figure out how to do this.

mat = matrix(0, nrow = length(unique(dt$id)), ncol = ncol (dt) ) 
colnames(mat) <- colnames(dt)

mat[, 'id'] <- as.character(unique(dt$id))
mat <- as.data.frame(mat)

merge(mat, dt, by = 'id' )

Any solutions for row combining and id management?

+4
source share
3 answers

Try:

library(dplyr)
dt %>% 
  group_by(id) %>% 
  summarise(activity = 0, time = 0) %>% 
  merge(., dt, all = T) %>%
  arrange(id, time)

:

dt %>% 
  group_by(id) %>% 
  summarise_each(funs(as.character(0))) %>% 
  full_join(., dt) %>%
  arrange(id, time)

:

#  id activity time
#1 A1        0    0
#2 A1       15    1
#3 A1       17    2
#4 A1       12    3
#5 B1        0    0
#6 B1        3    1
#7 B1        4    2
#8 B1       15    3

, activity time , :

... %>% mutate_each(funs(type.convert(as.character(.))), -id)

dt , :

dt <- data.frame(id = c(rep("A1", 3), rep("B1", 3)),
                 activity = c(15,17,12,3,4,15),
                 time = rep(1:3, 2))

library(dplyr)
dt %>% 
  group_by(id) %>% 
  summarise(activity = 0, time = 0) %>% 
  full_join(., dt) %>%
  arrange(id, time)
+3
dt = as.data.frame( rbind(c('A1', '15', '1'), 
                          c('A1', '17', '2'), 
                          c('A1', '12', '3'), 
                          c('B1', '3', '1'), 
                          c('B1', '4', '2'), 
                          c('B1', '15', '3')
                          ))

colnames(dt) = c('id', 'activity', 'time')
#Just we need to get the levels of `id` we want to bind `zeros` to
levels <- levels(dt$id)

#create a new matrix of new data we need to append to our data frame `dt`
levels_M <- cbind(id = levels , activity =  '0' , time = '0')

#then simply bind these values to the data frame
rbind(dt , levels_M)

#if you want to order the final results

dt <- dt[order(dt$id),]

data.table, , , base R ordering

+2

First, I think you need to convert dtin such a way that both activityhave and timehave a class intinstead of factor:

dt[]<-lapply(dt,function(x) type.convert(as.character(x)))

Then you can use data.table:

require(data.table)
DT<-as.data.table(dt)
DT[,lapply(.SD,function(x) c(0,x)),by=id]
+1
source

More articles:


All Articles