Hi everyone,
I have tried to collapse multiple rows per id in terms of the maximum values. I can do it using dplyr command:
data%
group_by(id) %>%
summarise_each(funs(max(., na.rm = TRUE)))
However, for a large data, it is little too slow. I tried the following using data.table but it produces wrong result:
data
You may try tapply, the id should be numbers or factors
tapply(data[,2],data[,'id'],max,na.rm=T)
you can write a for loop to run this function to each column, then will get the results you want
There is a smarter solution:
sort the data.frame by ID and value (decreasing), then take the subset of rows for which the ID is not duplicated:
data = data[ order(data$ID, data$value, decreasing=TRUE), ]
data = data[!duplicated(data$ID), ]
--
If there are many columns with values and for each of these colums the respective maximum per ID should be retained:
data = data[ order(data$ID), ]
idx = !duplicated(data$ID)
out = data[idx, ]
for (j in startCol:endCol) out[,j] = sort(data[,j])[idx]
--
In fact tapply is a slightly bit faster when the number of columns is not too large. Thus, changing the last line as follows could improve the performance a bit:
for (j in startCol:endCol) out[,j] = tapply(data[,j], data$ID, max, na.rm=TRUE)
However, larger number of columns make ovverride this advantage.
It could also be rewarding to convert the matrix of values into a very long vector, sort this vector by an ID made from the combination of the row-ID and a column-ID. But this takes some more thinking how to correctly re-create a proper resulting data.frame containing the max values in the correct cells... This may be advanatgous when the number of columns is huge compared to the number of rows.
- Badges
- Science topic
- Similar topics
- Mathematics
- Statistics
- Statistical Software
- R