# Data
student_data <- data.frame(
  student_id = 1:50,
  name = paste0("Student", 1:50),
  midterm1 = sample(60:100, 50, replace = TRUE),
  midterm2 = sample(60:100, 50, replace = TRUE),
  final = sample(60:100, 50, replace = TRUE)
)

# Transform data to long format using gather:
student_data_long <- gather(
  student_data,
  key = "exam_type",
  value = "score",
  -student_id,
  -name,
  na.rm = FALSE,
  convert = FALSE,
  factor_key = FALSE
)

# Print first few rows:
head(student_data_long)
##   student_id     name exam_type score
## 1          1 Student1  midterm1    76
## 2          2 Student2  midterm1    95
## 3          3 Student3  midterm1    70
## 4          4 Student4  midterm1    99
## 5          5 Student5  midterm1    78
## 6          6 Student6  midterm1    84
# Data
student_data2 <- data.frame(
  student_id = 1:50,
  name_age = c("John_21", "Alice_20", "Bob_22", "Emily_23", "Michael_22"),
  exam_scores = c("midterm1_80,midterm2_85,final_75", "midterm1_75,midterm2_78,final_80", "midterm1_82,midterm2_80,final_85", "midterm1_88,midterm2_90,final_92", "midterm1_85,midterm2_86,final_88")
)

student_data_2 <- student_data2 %>%
  separate(
    name_age,
    c("name", "age"),
    sep = "[_]",
    remove = TRUE,
    convert = FALSE
  )

student_data_2 <- student_data_2 %>%
  separate(
    exam_scores,
    into = c("midterm1", "midterm2", "final" ),
    sep = ",",
    remove = TRUE,
    convert = FALSE
  )

student_data_results <- student_data_2 %>%
  separate(
    midterm1,
    into = c("midterm1", "res1"),
    sep = "_",
    remove = TRUE,
    convert = FALSE
  ) %>% 
  separate(
    midterm2,
    into = c("midterm2", "res2"),
    sep = "_",
    remove = TRUE,
    convert = FALSE
  ) %>% 
  separate(
    final,
    into = c("final", "resf"),
    sep = "_",
    remove = TRUE,
    convert = FALSE
  )


head(student_data_results)
##   student_id    name age midterm1 res1 midterm2 res2 final resf
## 1          1    John  21 midterm1   80 midterm2   85 final   75
## 2          2   Alice  20 midterm1   75 midterm2   78 final   80
## 3          3     Bob  22 midterm1   82 midterm2   80 final   85
## 4          4   Emily  23 midterm1   88 midterm2   90 final   92
## 5          5 Michael  22 midterm1   85 midterm2   86 final   88
## 6          6    John  21 midterm1   80 midterm2   85 final   75
student_data <- data.frame(
student_id = 1:50,
name = c("John", "Alice", "Bob", "Emily", "Michael"),
age = c("21", "20", "22", "23", "22"),
exams = c("midterm1_80,midterm2_85,final_75", "midterm1_75,midterm2_78,final_80", "midterm1_82,midterm2_80,final_85", "midterm1_88,midterm2_90,final_92", "midterm1_85,midterm2_86,final_88")
)

student_data <- student_data %>%
complete(student_id, nesting(name, age, exams), fill = list(score = NA))

head(student_data)
## # A tibble: 6 × 4
##   student_id name    age   exams                           
##        <int> <chr>   <chr> <chr>                           
## 1          1 Alice   20    midterm1_75,midterm2_78,final_80
## 2          1 Bob     22    midterm1_82,midterm2_80,final_85
## 3          1 Emily   23    midterm1_88,midterm2_90,final_92
## 4          1 John    21    midterm1_80,midterm2_85,final_75
## 5          1 Michael 22    midterm1_85,midterm2_86,final_88
## 6          2 Alice   20    midterm1_75,midterm2_78,final_80
student_data_wide <- spread(student_data_results, key = "name", value = c("midterm1"))
student_data_wide <- spread(student_data_results, key = "name", value = c("midterm2"))
student_data_wide <- spread(student_data_results, key = "name", value = c("final"))
head(student_data_wide)
##   student_id age midterm1 res1 midterm2 res2 resf Alice   Bob Emily  John
## 1          1  21 midterm1   80 midterm2   85   75  <NA>  <NA>  <NA> final
## 2          2  20 midterm1   75 midterm2   78   80 final  <NA>  <NA>  <NA>
## 3          3  22 midterm1   82 midterm2   80   85  <NA> final  <NA>  <NA>
## 4          4  23 midterm1   88 midterm2   90   92  <NA>  <NA> final  <NA>
## 5          5  22 midterm1   85 midterm2   86   88  <NA>  <NA>  <NA>  <NA>
## 6          6  21 midterm1   80 midterm2   85   75  <NA>  <NA>  <NA> final
##   Michael
## 1    <NA>
## 2    <NA>
## 3    <NA>
## 4    <NA>
## 5   final
## 6    <NA>
student_data_results <- student_data_results %>%
unite(name_age, name, age, sep = "_")
  

head(student_data)
## # A tibble: 6 × 4
##   student_id name    age   exams                           
##        <int> <chr>   <chr> <chr>                           
## 1          1 Alice   20    midterm1_75,midterm2_78,final_80
## 2          1 Bob     22    midterm1_82,midterm2_80,final_85
## 3          1 Emily   23    midterm1_88,midterm2_90,final_92
## 4          1 John    21    midterm1_80,midterm2_85,final_75
## 5          1 Michael 22    midterm1_85,midterm2_86,final_88
## 6          2 Alice   20    midterm1_75,midterm2_78,final_80