Long to wide format

[Lika]

Hello,

I have a rather simple problem, but somehow can´t find the solution. I have data in long format. See this table as an example: there are 5 participants who all have a value on mean_A, mean_B, and mean_C.

How can I create a wide-format data-frame, with one row for every participant (like in the table at the bottom)

Thanks in advance for your help,

Lika

[joels]

The data frame isn't really in long format, but it's not properly combined. If there are previous steps in the data shaping, it's possible you can avoid the problem by revising the earlier code. In any case, given the current data frame, you could do the following: In the code below, we keep only the non-missing values from each column and then bind that back to a single copy of the IDs

library(tidyverse)

# Fake data
d = tibble(id=rep(1:5, 3),
           mean_a=c(1:5,rep(NA,10)),
           mean_b=c(rep(NA,5),6:10,rep(NA,5)),
           mean_c=c(rep(NA,10),11:15))

distinct(d, id) %>% 
  bind_cols(
    d %>% 
      select(-id) %>% 
      map_df(na.omit)
  )
#> # A tibble: 5 × 4
#>      id mean_a mean_b mean_c
#>   <int>  <int>  <int>  <int>
#> 1     1      1      6     11
#> 2     2      2      7     12
#> 3     3      3      8     13
#> 4     4      4      9     14
#> 5     5      5     10     15

It might actually be safer to use a join, so that we don't have to count on the data being in the right positions to match properly. For example:

library(tidyverse)

# Fake data
d = tibble(id=rep(1:5, 3),
           mean_a=c(1:5,rep(NA,10)),
           mean_b=c(rep(NA,5),6:10,rep(NA,5)),
           mean_c=c(rep(NA,10),11:15))

paste0("mean_", letters[1:3]) %>% 
  map(
    ~d %>% 
      select(id, all_of(.x)) %>% 
      na.omit
  ) %>% 
  reduce(left_join)
#> Joining, by = "id"
#> Joining, by = "id"
#> # A tibble: 5 × 4
#>      id mean_a mean_b mean_c
#>   <int>  <int>  <int>  <int>
#> 1     1      1      6     11
#> 2     2      2      7     12
#> 3     3      3      8     13
#> 4     4      4      9     14
#> 5     5      5     10     15

[Lika]

@joels

distinct(d, id) %>% 
  bind_cols(
    d %>% 
      select(-id) %>% 
      map_df(na.omit)
  )

Dear Joels,

Thank you so much for your help. Both options work just fine with my data and I am very happy now. I wouldn't have found out by myself, so it was really a huge help.

Thanks again,

Lika