Merging several columns with the same headers in R

Question

I have a data frame with several Participant_ID. For each Participant_ID, I have 4 variables (Start_day,End_day,Lights_out and Got_up) and 48 days for each variables (named here:...2 - ...3 - ...) :

I'd like to have 6 columns: id- days - Start_day - End_day - Lights_out - Got_up

Basically, keep the "id" column and switch variables with days (...2, ...3,...4,...)

Does anyone know how to do that? Many thanks!

score 1 · Answer 1 · answered Dec 27 '21 at 16:28

If I understood you correctly the problem can be solved within the tidyverse using the pivot functions from the tidyr package:

library(dplyr)
library(tidyr)

# dummy data
df <- data.frame(id = c("ALM-M", "ALM-M", "ALM-M", "ALM-M"),
                 variable = c("Start day", "End day", "Lights out", "Got Up"),
                 col1 = c(44269, 44269, 4.1319, 0.3128),
                 col2 = c(44270, 44270, 0.1928, 0.4223))

# convert data (first make it longer, than wider)
tidyr::pivot_longer(df, -c(id, variable), names_to = "cols", values_to = "vals") %>%
tidyr::pivot_wider(names_from = variable, values_from = vals)


# A tibble: 2 x 6
  id    cols  `Start day` `End day` `Lights out` `Got Up`
  <chr> <chr>       <dbl>     <dbl>        <dbl>    <dbl>
1 ALM-M col1        44269     44269        4.13     0.313
2 ALM-M col2        44270     44270        0.193    0.422

here you can find some more in depth explantion about the use of the pivot functions from the tidyr package.

Perfect!! It's working perfectly! Thank you very much!! – Virginie Dec 28 '21 at 10:43 — Virginie, Dec 28 '21 at 10:43

score 1 · Answer 2 · answered Dec 27 '21 at 16:30

We can use tidyr::pivot_longer and tidyr::pivot_wider

Example data

dat<- tibble(id = 'participant_1',
             variables = c('Start_day','End_day','Lights_out', 'Got_up'),
             `...1`=c(0,1,2,3),
             `...2`=c(5,6,7,8),
             `...3`=c(0,1,2,3),
             `...4`=c(0,1,2,3))

# A tibble: 4 × 6
  id            variables   ...1  ...2  ...3  ...4
  <chr>         <chr>      <dbl> <dbl> <dbl> <dbl>
1 participant_1 Start_day      0     5     0     0
2 participant_1 End_day        1     6     1     1
3 participant_1 Lights_out     2     7     2     2
4 participant_1 Got_up         3     8     3     3

answer

dat %>%
        pivot_longer(`...1`:`...4`) %>%
        pivot_wider(names_from = variables, values_from = value)

# A tibble: 4 × 6
  id            name  Start_day End_day Lights_out Got_up
  <chr>         <chr>     <dbl>   <dbl>      <dbl>  <dbl>
1 participant_1 ...1          0       1          2      3
2 participant_1 ...2          5       6          7      8
3 participant_1 ...3          0       1          2      3
4 participant_1 ...4          0       1          2      3

Merging several columns with the same headers in R

2 Answers2