Transpose dataframe without timevar and with multiple id variables

Question

I have a question similar to a prior post.

Transpose / reshape dataframe without "timevar" from long to wide format

I am trying to transpose a dataset fromn long to wide format. I am encountering two problems. First, I don't have a timevar. Second, I have multiple id variables.

Here is what my dataset might look like

ID Location Time Y
1001 A      T1   1
1001 A      T1   2
1001 B      T1   7
1001 C      T1   3
1001 C      T1   3
1001 C      T1   3
1001 D      T1   1
1001 D      T1   4
1001 A      T2   6
1001 B      T2   3
1001 B      T2   3
1001 B      T2   3
1001 C      T2   4
1001 C      T2   4
1001 C      T2   4
1001 C      T2   4
1001 D      T2   5
1001 D      T2   5
1001 D      T2   5
1001 D      T2   5
1001 D      T2   5

Data in reproducable format

ID <- c(1001, 1001, 1001, 1001, 1001, 
        1001, 1001, 1001, 1001, 1001, 
        1001, 1001, 1001, 1001, 1001, 
        1001, 1001, 1001, 1001, 1001)
Location <- c ("A", "A", "B", "C", "C", 
               "C", "D", "D", "A", "B", 
               "B", "B", "C", "C", "C", 
               "C", "D", "D", "D", "D")
Time <- c ("T1", "T1", "T1", "T1", "T1",
           "T1", "T1", "T1", "T2", "T2", 
           "T2", "T2", "T2", "T2", "T2", 
           "T2", "T2", "T2", "T2", "T2" )
Y <- c (1, 2, 7, 3, 3, 
        3, 1, 4, 6, 3, 
        3, 3, 4, 4, 4, 
        4, 5, 5, 5, 5)
data <- data.frame (ID, Location, Time, Y)

I am hoping to get something like this.

ID Location Time Y1 Y2 Y3 Y4
1001 A      T1   1  2  NA NA
1001 B      T1   7  NA NA NA
1001 C      T1   3  3  3  NA
1001 D      T1   1  4  NA NA
1001 A      T2   6  NA NA NA
1001 B      T2   3  3  3  NA
1001 C      T2   4  4  4  4 
1001 D      T2   5  5  5  5

I would like to use reshape and generate a new timevar but I am having some issues because of the multiple time variables. Here is the current code I have.

data$uniqid <- with(data, ave(as.character(c ("ID", "Location", "Time")), c ("ID", "Location", "Time"), FUN = seq_along))
reshape(data, idvar = c ("ID", "Location", "Time"), timevar = "uniqid", direction = "wide")

score 1 · Answer 1 · answered Dec 06 '22 at 14:45

Assuming the 10001 should be 1001 in your sample data, this dplyr pipe gives you your output:

library(dplyr)
library(tidyr)
data %>%
  group_by(ID, Location, Time) %>%
  mutate(rn = paste0("Y", row_number())) %>%
  ungroup() %>%
  pivot_wider(c(ID, Location, Time), names_from = "rn", values_from = "Y")
# # A tibble: 8 x 7
#      ID Location Time     Y1    Y2    Y3    Y4
#   <dbl> <chr>    <chr> <dbl> <dbl> <dbl> <dbl>
# 1  1001 A        T1        1     2    NA    NA
# 2  1001 B        T1        7    NA    NA    NA
# 3  1001 C        T1        3     3     3    NA
# 4  1001 D        T1        1     4    NA    NA
# 5  1001 A        T2        6    NA    NA    NA
# 6  1001 B        T2        3     3     3    NA
# 7  1001 C        T2        4     4     4     4
# 8  1001 D        T2        5     5     5     5

Data

data <- structure(list(ID = c(1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001, 1001), Location = c("A", "A", "B", "C", "C", "C", "D", "D", "A", "B", "B", "B", "C", "C", "C", "C", "D", "D", "D", "D"), Time = c("T1", "T1", "T1", "T1", "T1", "T1", "T1", "T1", "T2", "T2", "T2", "T2", "T2", "T2", "T2", "T2", "T2", "T2", "T2", "T2"), Y = c(1, 2, 7, 3, 3, 3, 1, 4, 6, 3, 3, 3, 4, 4, 4, 4, 5, 5, 5, 5)), class = "data.frame", row.names = c(NA, -20L))

Transpose dataframe without timevar and with multiple id variables

1 Answers1