Convert to long form in R based on column names which include sequential numbers in names

zhg 0
Viewed 18

I have a data frame where each row includes multiple "observations" with data on the same set of variables, of which each set is appended with a number (e.g., age, gender, code, age.1, gender.1, code.1, age.2, gender.2, code.2). (The row with the maximum number of such observations includes 51, but each row includes somewhere between 1 and 51 observations).

Here is an example of how the data frame is structured:

df_asis <- data.frame("ID" = 101:104, 
                 "Age" = c("Child", "Adult", "Elder", "Adult"), 
                 "Gender" = c("Female","Male","Male","Female"),
                 "Code" = c("A", "B", "A", "C"),
                 "Age.1" = c("Adult", "Adult", "Child", "Adult"), 
                 "Gender.1" = c("Male","Male","Female","Female"),
                 "Code.1" = c("C", "B", "B", "B"),
                 "Age.2" = c("Elder", "Adult", "Child", "Adult"), 
                 "Gender.2" = c("Male","Male","Male","Male"),
                 "Code.2" = c("C", "C", "A", "A")
                 )

View of the data frame

I would like to convert the data to long-form, so that each row is one of the "observations," i.e., each ID provides multiple observations (in this example, each ID is providing three observations).

I am not sure how to use a loop or a select function to group columns by their observation (as indicated by the number in the column name). Note that there are no other numbers in column names, so grouping by or selecting column names by number is an option.

The data frame I would like to produce would look like this:

df_tomake <- data.frame("ID" = c(101,101,101,102,102,102,103,103,103,104,104,104), 
                 "Age" = c("Child", "Adult", "Elder",
                           "Adult", "Adult", "Adult",
                           "Elder", "Child", "Child",
                           "Adult", "Adult", "Adult"), 
                 "Gender" = c("Female","Male","Male",
                              "Male", "Male", "Male",
                              "Male", "Female", "Male",
                              "Female", "Male", "Male"),
                 "Code" = c("A", "C", "C", 
                            "B", "B", "C",
                            "A", "B", "A",
                            "C", "B", "A"
                            ))

enter image description here

gather reshape pivot-table r
1 Answers

You can use pivot_longer() from tidyr package:

library(dplyr)
library(tidyr)

df_asis %>% 
  rename_with(~c("Age.0", "Gender.0", "Code.0"), .cols = 2:4) %>% 
  pivot_longer(-ID, names_to=c(".value"), names_pattern = ("(^.*)\\..*$"))

Output:

      ID Age   Gender Code 
   <int> <chr> <chr>  <chr>
 1   101 Child Female A    
 2   101 Adult Male   C    
 3   101 Elder Male   C    
 4   102 Adult Male   B    
 5   102 Adult Male   B    
 6   102 Adult Male   C    
 7   103 Elder Male   A    
 8   103 Child Female B    
 9   103 Child Male   A    
10   104 Adult Female C    
11   104 Adult Female B    
12   104 Adult Male   A
langtang0
Related