Repeated measures in messy format, need help to tidy

user_10377705 0
Viewed 71

I have a very large data set containing weekly weights that have been coded with week of study and the weight at that visit. There are some missing visits and the data is not currently aligned.

df <- data.frame(ID=1:3, Week_A=c(6,6,7), Weight_A=c(23,24,23), Week_B=c(7,7,8), 
                 Weight_B=c(25,26,27), Week_C=c(8,9,9), Weight_C=c(27,26,28)) 

df
  ID Week_A Weight_A Week_B Weight_B Week_C Weight_C
1  1      6       23      7       25      8       27
2  2      6       24      7       26      9       26
3  3      7       23      8       27      9       28

I would like to align the data by week number (ideal output below). 

df_ideal <- data.frame (ID=1:3, Week_6=c(23,24,NA), Week_7=c(25,26,23), 
                        Week_8=c(27,NA,27), Week_9=c(NA,26,28))

df_ideal
  ID Week_6 Week_7 Week_8 Week_9
1  1     23     25     27     NA
2  2     24     26     NA     26
3  3     NA     23     27     28

I would appreciate some help with this, even to find a starting point to manipulate this data to an easier to manage format.

r
4 Answers

A tidyverse solution:

df <- data.frame(ID=1:3, 
                 Week_A=c(6,6,7), 
                 Weight_A=c(23,24,23), 
                 Week_B=c(7,7,8), 
                 Weight_B=c(25,26,27),
                 Week_C=c(8,9,9),
                 Weight_C=c(27,26,28))

library(tidyverse)
df_long <- df %>% gather(key="v", value="value", -ID) %>% 
  separate(v, into=c("v1", "v2")) %>% 
  spread(v1, value) %>% 
  complete(ID, Week) %>% 
  arrange(Week, ID)

df_long
# A tibble: 12 x 4
#      ID  Week v2    Weight
#   <int> <dbl> <chr>  <dbl>
# 1     1     6 A         23
# 2     2     6 A         24
# 3     3     6 <NA>      NA
# 4     1     7 B         25
# 5     2     7 B         26
# 6     3     7 A         23
# 7     1     8 C         27
# 8     2     8 <NA>      NA
# 9     3     8 B         27
#10     1     9 <NA>      NA
#11     2     9 C         26
#12     3     9 C         28

df_wide <- df_long %>% select(-v2) %>% 
  spread(Week, Weight, sep="_")
df_wide
# A tibble: 3 x 5
#     ID Week_6 Week_7 Week_8 Week_9
#  <int>  <dbl>  <dbl>  <dbl>  <dbl>
#1     1     23     25     27     NA
#2     2     24     26     NA     26
#3     3     NA     23     27     28

Personally, I'd keep using df_long instead of df_wide, as it is a tidy data frame, while df_wide is not.

user_6886930

Here is a possible approach using the data.table package

library(data.table)
#convert into a data.table
setDT(df)

#convert into a long format
mdat <- melt(df, id.vars="ID", measure.vars=patterns("^Week", "^Weight", cols=names(df)))

#pivot into desired output
ans <- dcast(mdat, ID ~ value1, value.var="value2")

ans output:

   ID  6  7  8  9
1:  1 23 25 27 NA
2:  2 24 26 NA 26
3:  3 NA 23 27 28

And if you really need the "Week_" in your column names, you can use

setnames(ans, names(ans)[-1L], paste("Week_", names(ans)[-1L]))
user_19894800

Another tidyverse solution using a double-gather with a final spread

df %>%
    gather(k, v, -ID, -starts_with("Weight")) %>%
    separate(k, into = c("k1", "k2")) %>%
    unite(k1, k1, v) %>%
    gather(k, v, starts_with("Weight")) %>%
    separate(k, into = c("k3", "k4")) %>%
    filter(k2 == k4) %>%
    select(-k2, -k3, -k4) %>%
    spread(k1, v)
#  ID Week_6 Week_7 Week_8 Week_9
#1  1     23     25     27     NA
#2  2     24     26     NA     26
#3  3     NA     23     27     28
user_65309700

In base R, it's a double reshape, firstly to long and then back to wide on a different variable:

tmp <- reshape(df, idvar="ID", varying=lapply(c("Week_","Weight_"), grep, names(df)),
               v.names=c("time","Week"), direction="long")
reshape(tmp, idvar="ID", direction="wide", sep="_")

#    ID Week_6 Week_7 Week_8 Week_9
#1.1  1     23     25     27     NA
#2.1  2     24     26     NA     26
#3.1  3     NA     23     27     28
user_4968030
Related