Data Wrangling report
R Markdown
This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.
When you click the Knit button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:
# 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)
# Print first few rows:
head(student_data_long)## student_id name exam_type score
## 1 1 Student1 midterm1 70
## 2 2 Student2 midterm1 62
## 3 3 Student3 midterm1 66
## 4 4 Student4 midterm1 89
## 5 5 Student5 midterm1 97
## 6 6 Student6 midterm1 61# 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")
)
# Split variables name_age to 2 separate columns: name and age
student_data2 <- separate(student_data2, name_age, into = c("name", "age"), sep = "_")
# Split variables exam_scores to separate columns for each exam type
student_data2 <- separate(student_data2, exam_scores, into = c("type1", "midterm1", "type2", "midterm2", "type3", "final"), sep = "(_|,)", convert = TRUE)
# Transform student_data2 to long format
student_data2_long <- pivot_longer(student_data2, cols = c(midterm1, midterm2, final),
names_to = "exam", values_to = "score")
student_data2_long <- student_data2_long[, -c(4, 5, 6)]
student_data2_long## # A tibble: 150 × 5
## student_id name age exam score
## <int> <chr> <chr> <chr> <int>
## 1 1 John 21 midterm1 80
## 2 1 John 21 midterm2 85
## 3 1 John 21 final 75
## 4 2 Alice 20 midterm1 75
## 5 2 Alice 20 midterm2 78
## 6 2 Alice 20 final 80
## 7 3 Bob 22 midterm1 82
## 8 3 Bob 22 midterm2 80
## 9 3 Bob 22 final 85
## 10 4 Emily 23 midterm1 88
## # ℹ 140 more rows# Data
student_data <- 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")
)
# Complete missing combinations for students
combinations <- complete(student_data, student_id, name_age, exam_scores)
combinations## # A tibble: 1,250 × 3
## student_id name_age exam_scores
## <int> <chr> <chr>
## 1 1 Alice_20 midterm1_75,midterm2_78,final_80
## 2 1 Alice_20 midterm1_80,midterm2_85,final_75
## 3 1 Alice_20 midterm1_82,midterm2_80,final_85
## 4 1 Alice_20 midterm1_85,midterm2_86,final_88
## 5 1 Alice_20 midterm1_88,midterm2_90,final_92
## 6 1 Bob_22 midterm1_75,midterm2_78,final_80
## 7 1 Bob_22 midterm1_80,midterm2_85,final_75
## 8 1 Bob_22 midterm1_82,midterm2_80,final_85
## 9 1 Bob_22 midterm1_85,midterm2_86,final_88
## 10 1 Bob_22 midterm1_88,midterm2_90,final_92
## # ℹ 1,240 more rows# Data
student_data <- 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")
)
# Transform data to wide format
student_data <- student_data %>%
separate_rows(exam_scores, sep = ",") %>%
separate(exam_scores, into = c("exam", "score"), sep = "_") %>%
pivot_wider(names_from = exam, values_from = score)
student_data## # A tibble: 50 × 5
## student_id name_age midterm1 midterm2 final
## <int> <chr> <chr> <chr> <chr>
## 1 1 John_21 80 85 75
## 2 2 Alice_20 75 78 80
## 3 3 Bob_22 82 80 85
## 4 4 Emily_23 88 90 92
## 5 5 Michael_22 85 86 88
## 6 6 John_21 80 85 75
## 7 7 Alice_20 75 78 80
## 8 8 Bob_22 82 80 85
## 9 9 Emily_23 88 90 92
## 10 10 Michael_22 85 86 88
## # ℹ 40 more rows# Data
student_data <- 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")
)
# Merge variables name and age to one column called name_age
#Data is already merged
student_data## student_id name_age exam_scores
## 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
## 7 7 Alice_20 midterm1_75,midterm2_78,final_80
## 8 8 Bob_22 midterm1_82,midterm2_80,final_85
## 9 9 Emily_23 midterm1_88,midterm2_90,final_92
## 10 10 Michael_22 midterm1_85,midterm2_86,final_88
## 11 11 John_21 midterm1_80,midterm2_85,final_75
## 12 12 Alice_20 midterm1_75,midterm2_78,final_80
## 13 13 Bob_22 midterm1_82,midterm2_80,final_85
## 14 14 Emily_23 midterm1_88,midterm2_90,final_92
## 15 15 Michael_22 midterm1_85,midterm2_86,final_88
## 16 16 John_21 midterm1_80,midterm2_85,final_75
## 17 17 Alice_20 midterm1_75,midterm2_78,final_80
## 18 18 Bob_22 midterm1_82,midterm2_80,final_85
## 19 19 Emily_23 midterm1_88,midterm2_90,final_92
## 20 20 Michael_22 midterm1_85,midterm2_86,final_88
## 21 21 John_21 midterm1_80,midterm2_85,final_75
## 22 22 Alice_20 midterm1_75,midterm2_78,final_80
## 23 23 Bob_22 midterm1_82,midterm2_80,final_85
## 24 24 Emily_23 midterm1_88,midterm2_90,final_92
## 25 25 Michael_22 midterm1_85,midterm2_86,final_88
## 26 26 John_21 midterm1_80,midterm2_85,final_75
## 27 27 Alice_20 midterm1_75,midterm2_78,final_80
## 28 28 Bob_22 midterm1_82,midterm2_80,final_85
## 29 29 Emily_23 midterm1_88,midterm2_90,final_92
## 30 30 Michael_22 midterm1_85,midterm2_86,final_88
## 31 31 John_21 midterm1_80,midterm2_85,final_75
## 32 32 Alice_20 midterm1_75,midterm2_78,final_80
## 33 33 Bob_22 midterm1_82,midterm2_80,final_85
## 34 34 Emily_23 midterm1_88,midterm2_90,final_92
## 35 35 Michael_22 midterm1_85,midterm2_86,final_88
## 36 36 John_21 midterm1_80,midterm2_85,final_75
## 37 37 Alice_20 midterm1_75,midterm2_78,final_80
## 38 38 Bob_22 midterm1_82,midterm2_80,final_85
## 39 39 Emily_23 midterm1_88,midterm2_90,final_92
## 40 40 Michael_22 midterm1_85,midterm2_86,final_88
## 41 41 John_21 midterm1_80,midterm2_85,final_75
## 42 42 Alice_20 midterm1_75,midterm2_78,final_80
## 43 43 Bob_22 midterm1_82,midterm2_80,final_85
## 44 44 Emily_23 midterm1_88,midterm2_90,final_92
## 45 45 Michael_22 midterm1_85,midterm2_86,final_88
## 46 46 John_21 midterm1_80,midterm2_85,final_75
## 47 47 Alice_20 midterm1_75,midterm2_78,final_80
## 48 48 Bob_22 midterm1_82,midterm2_80,final_85
## 49 49 Emily_23 midterm1_88,midterm2_90,final_92
## 50 50 Michael_22 midterm1_85,midterm2_86,final_88