CODEWITHSUNDEEP
rdata.table
Jenny0322
Jenny0322

Reputation: 63

How to take first 5 rows by a group without replacement in another variable value

I am trying to figure out how I can take only first 5 rows by a group without replacement in another variable value. For example, if the existing data table (or frame) looks like this: 

id V1
1 101
1 102
1 103
1 104
1 105
1 106
1 107
1 108
1 109
1 110
2 101
2 103
2 105
2 107
2 108
2 109
2 110
2 111
2 112
2 101
3 104
3 105
3 107
3 108
3 109
3 110
3 101
3 102
3 103
3 104

But I just want to get first 5 rows for each group but without replacement in V1 values across the groups. So the result table I want is...: 

id V1
1 101
1 102
1 103
1 104
1 105
2 107
2 108
2 109
2 110
2 111
3 NA

I have been trying to do this using for loop by going through each id one at a time ....taking first 5 rows for each id and excluding the following rows with V1 values in the previous ids. But as my data is really big (the number of ids is over a million), it takes forever for the for loop to go through all the ids.

Is there anyone smarter than me to help me to find a better, more efficient and clever way to deal with this problem? Thanks much!

Upvotes: 4

Views: 147

Answers (2)

Umberto
Umberto

Reputation: 1421

Still working on it. This is what I came up with (notice that since id = 3 has only duplicate values it will not be shown at the end). One can Change that. I am not sure about the Performance. Will see if I can come up with something smarter...

df = data.frame (id = c (1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,
      2,2,2,2,2,3,3,3,3,3,3,3,3,3,3),
V1 = c(101,102, 103,104,105,106,107,108,109,110,101,
103,105,107,108,109,110,111,112,101,104,
105,107,108,109,110,101,102,103,104))


df2 <- df
for (i in unique(df$id)) {
   dfsel <- data.frame(df2 %>% group_by(id) %>% filter(row_number() <= 5 & id == i))
   df3 <- df2[!(df2$V1 %in% dfsel$V1) & df2$id != i,]
   df2 <- rbind(dfsel,df3)
}
df2[with (df2, order(id)),]

result is

id  V1
1 101
1 102
1 103
1 104
1 105
2 107
2 108
2 109
2 110
2 111

EDITED: found another way. Probably not really smarter but I had fun :) One should check Performance, did not have time to think about it properly.

Here is the code

dd <- split(df$V1, df$id)
maxdf <- data.frame(mx = rep(0,length(dd)))

maxdf[1,1] <- dd[[1]][5]
dd[[1]][dd[[1]] > maxdf[1,1]] <- NA

n <- unique(df$id)[2:length(unique(df$id))]
for (i in n) {
  dd[[i]][dd[[i]] <= maxdf[i-1,1]] <- NA
  maxdf[i,1] <- dd[[i]][!is.na(dd[[i]])][5]
  dd[[i]][dd[[i]] > maxdf[i,1]] <- NA
}

df <- stack(dd)
names(df) <- c("V1","id")
df <- df[!is.na(df$V1),]

PS: Solution below is still much more elegant :)

Upvotes: 1

talat
talat

Reputation: 70266

Here's an option in three steps: 

# create a vector to store set values
x <- numeric()
# compute the values by id and update x in the process
res <- lapply(split(df$V1, df$id), function(y) {
     y <- head(setdiff(y, x), 5)
     x <<- union(x, y)
     if(!length(y)) NA else y
})
# combine the result to data.frame
stack(res)
#   values ind
#1     101   1
#2     102   1
#3     103   1
#4     104   1
#5     105   1
#6     107   2
#7     108   2
#8     109   2
#9     110   2
#10    111   2
#11     NA   3

Upvotes: 4

Related Questions