R convert table into matrix according to condition

Question

There is a table:

time<-c("02:20:00",
"02:35:00",
"02:40:00",
"03:35:00",
"03:40:00",
"04:50:00",
"04:55:00",
"05:00:00",
"05:00:00",
"05:05:00",
"05:10:00",
"05:10:00",
"05:15:00",
"05:20:00",
"05:25:00",
"05:15:00",
"05:20:00",
"05:15:00",
"05:20:00")
id<-c(1,2,2,3,3,4,4,4,5,5,5,6,6,6,6,7,7,8,8)
value<-rep(1,19)
df<-data.frame(id, time, value)

There are eight id's which occupy the cells from 5 to 20 minutes. One cell can be occupied at the same time only with one id. If cell 1 is occupied with id 1, the id 2 has to get another one cell. I use this code:

df2<-df %>%
  group_by(time) %>%
  mutate(new_names = paste0("id", 1:n())) %>%
  pivot_wider(names_from = new_names)

The Problem is, if the previous cell is free again, switchs the next one id in the cell 1 back.

I want that this id stays in the cell 2 for the whole time. I would like to get the following output:

Could You please help me?

Thanks in advance! Inna

what is the logic behind? What is with the times 05:15 and 05:20? Are the ids here correct? — Roman, Jun 30 '20 at 11:41
id 5 has to be in the cell "id2". The cell "id1" is at 05:00 already free. That's why id 6 can occupy this cell because id 5 stays in the cell "id2" — Inna, Jun 30 '20 at 11:51
Do you want to add columns only from `id1` to `id3` ? Should there be 8 id's columns instead? — Ronak Shah, Jun 30 '20 at 12:17
I assume that one id should be used as much as possible (in this case I need only three id's) But I assume that I don't know the number of id's — Inna, Jun 30 '20 at 12:34
is this correct? `df %>% group_by(time) %>% mutate(n=1:n()) %>% select(-value) %>% pivot_wider(names_from = n, values_from= id)` — Roman, Jul 01 '20 at 08:15

score 1 · Accepted Answer · answered Jun 30 '20 at 12:02

Maybe the following comes close enough to what you want - continuing of your question R data frame table set the values into columns

x <- split(df$id, df$time)
x <- do.call("rbind", lapply(x, "[", 1:max(lengths(x))))
for(i in 2:nrow(x)) {
  j <- which(!is.na(x[i,]) & x[i,] %in% x[i-1,])
  k <- match(x[i,j], x[i-1,])
  x[i,c(k, setdiff(1:3, k))[order(c(j, setdiff(1:3, j)))]] <- x[i,]
}
x
#         [,1] [,2] [,3]
#02:20:00    1   NA   NA
#02:35:00    2   NA   NA
#02:40:00    2   NA   NA
#03:35:00    3   NA   NA
#03:40:00    3   NA   NA
#04:50:00    4   NA   NA
#04:55:00    4   NA   NA
#05:00:00    4    5   NA
#05:05:00   NA    5   NA
#05:10:00    6    5   NA
#05:15:00    6    7    8
#05:20:00    6    7    8
#05:25:00    6   NA   NA

Thanks a lot for Your answer. It works! But I have an additional question: how can change this condition - x[i,c(k, setdiff(1:3, k))[order(c(j, setdiff(1:3, j)))]] - if I don't know the number of cells (columns)? — Inna, Jun 30 '20 at 12:31

R convert table into matrix according to condition

1 Answers1