Web Analytics Made Easy - Statcounter
Author

invictus

Clean Data

Setup

Library

library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.5.1     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.1
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(janitor)

Attaching package: 'janitor'

The following objects are masked from 'package:stats':

    chisq.test, fisher.test
library(naniar)
library(scales)

Attaching package: 'scales'

The following object is masked from 'package:purrr':

    discard

The following object is masked from 'package:readr':

    col_factor
library(summarytools)
Warning: no DISPLAY variable so Tk is not available
system might not have X11 capabilities; in case of errors when using dfSummary(), set st_options(use.x11 = FALSE)

Attaching package: 'summarytools'

The following object is masked from 'package:tibble':

    view
library(DataExplorer)
library(corrr)

Options

options(scipen = 999)

Student Gen 23

df_student_gen_23 <- read_csv('student_list_generation_23.csv')
Rows: 8082 Columns: 5
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (3): Nama, Kelas Lama, Kelas Baru
dbl (2): No, NPM

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Remove Duplicates

df_student_gen_23 |> 
  get_dupes(NPM, Nama)
# A tibble: 4 × 6
       NPM Nama              dupe_count    No `Kelas Lama` `Kelas Baru`
     <dbl> <chr>                  <int> <dbl> <chr>        <chr>       
1 10123884 MUHAMMAD RAMADHAN          2  1060 1KA03        2KA20       
2 10123884 MUHAMMAD RAMADHAN          2  1061 1KA03        2KA20       
3 10223100 AHMAD FAUZAN               2   300 1EA19        2EA19       
4 10223100 AHMAD FAUZAN               2   301 1EA19        2EA19       
df_student_gen_23_unique <- df_student_gen_23 |> 
  distinct(NPM, Nama, .keep_all = TRUE)
df_student_gen_23_unique
# A tibble: 8,080 × 5
      No      NPM Nama                        `Kelas Lama` `Kelas Baru`
   <dbl>    <dbl> <chr>                       <chr>        <chr>       
 1     1 60223026 A'NAS TASYA GUSTI FIANA     1EC01        2EC01       
 2     2 10123014 A. PEBRIYAN MAEYADI PUTRA   1KA01        2KA10       
 3     3 10223027 AARON MATTHEW PUTRA IBRAHIM 1EA01        2EA04       
 4     4 10623004 ABANG MAULANA JAVID FANSURI 1SA05        2SA05       
 5     5 10223028 ABDAN RIJAL SYAKURA         1EA23        2EA28       
 6     6 10223029 ABDAN SYAKUR                1EA22        2EA22       
 7     7 10823003 ABDEL RAUF FEBROZA          1MA01        2MA04       
 8     8 50423017 ABDHAN ZAKI ALFAREZA        1IA01        2IA17       
 9     9 50423018 ABDIL NAYAKA RIZKY          1IA02        2IA01       
10    10 10523002 ABDILLAH ISMAIL ADHA        1PA24        2PA28       
# ℹ 8,070 more rows

Remove Irrelevant Columns

df_student_gen_23_relevant_columns <- df_student_gen_23_unique |> 
  select(NPM, `Kelas Baru`)
df_student_gen_23_relevant_columns
# A tibble: 8,080 × 2
        NPM `Kelas Baru`
      <dbl> <chr>       
 1 60223026 2EC01       
 2 10123014 2KA10       
 3 10223027 2EA04       
 4 10623004 2SA05       
 5 10223028 2EA28       
 6 10223029 2EA22       
 7 10823003 2MA04       
 8 50423017 2IA17       
 9 50423018 2IA01       
10 10523002 2PA28       
# ℹ 8,070 more rows

Rename Columns

df_student_gen_23_renamed <- df_student_gen_23_relevant_columns |> 
  rename(student_id = NPM, class_code = `Kelas Baru`)

Split Class Code

df_student_gen_23_split_classcode <- df_student_gen_23_renamed |> 
  separate_wider_regex(class_code,
                       patterns =  c(year_code = '\\d+', major_code = '[A-Z]+', class_number= '\\d+'),
                       too_few = 'align_start',
                       cols_remove = FALSE)

Student Gen 24

df_student_gen_24 <- read_csv('student_list_generation_24.csv')
Rows: 8636 Columns: 6
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (4): No Pend., Nama, Kelas, Keterangan
dbl (2): No, NPM

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
df_student_gen_24
# A tibble: 8,636 × 6
      No `No Pend.` Nama                           NPM Kelas  Keterangan        
   <dbl> <chr>      <chr>                        <dbl> <chr>  <chr>             
 1     1 I241962    A FARHAN ASSIDQI          10824001 1MA01  S1-Ilmu Komunikas…
 2     2 I244822    A'PIFAH DZAKIAH           20624001 1SB01  S1-Pariwisata Pag…
 3     3 I243593    A'ROOF RAIHAN HAKIM       20224001 1EB19  SarMag S1 Akuntan…
 4     4 I247622    AAS TRI HAYATI            11524262 1PA06  S1-Psikologi Pagi…
 5     5 A240094    AATHIFAH ALISHA FAUZIYAH  19124005 ALH241 S1-Sistem Informa…
 6     6 T242801    ABABIL NUR AHMAD          10824002 1MA02  S1-Ilmu Komunikas…
 7     7 T242992    ABBAS ALFIANSYAH ARRASYID 50424001 1IA16  S1-Informatika Pa…
 8     8 P108703    ABBY PINANDITA AL'GHIFARI 30324001 1TC03  S1-Desain Interio…
 9     9 I242606    ABD. RAFA KHARIM          10124001 1KA24  S1-Sistem Informa…
10    10 I248539    ABDALLAH                  31424417 1ID02  S1-Teknik Industr…
# ℹ 8,626 more rows

Check Duplicates

df_student_gen_24 |> 
  get_dupes(NPM)
No duplicate combinations found of: NPM
# A tibble: 0 × 7
# ℹ 7 variables: NPM <dbl>, dupe_count <int>, No <dbl>, No Pend. <chr>,
#   Nama <chr>, Kelas <chr>, Keterangan <chr>

Remove Irrelevant Columns

df_student_gen_24_relevant_columns <- df_student_gen_24 |> 
  select(NPM, Kelas, Keterangan)

Rename Columns

df_student_gen_24_renamed <- df_student_gen_24_relevant_columns |> 
  rename(student_id = NPM, class_code = Kelas, additional_info = Keterangan)
df_student_gen_24_renamed
# A tibble: 8,636 × 3
   student_id class_code additional_info                              
        <dbl> <chr>      <chr>                                        
 1   10824001 1MA01      S1-Ilmu Komunikasi Pagi Depok                
 2   20624001 1SB01      S1-Pariwisata Pagi Depok                     
 3   20224001 1EB19      SarMag S1 Akuntansi - S2 Manajemen Sistem Inf
 4   11524262 1PA06      S1-Psikologi Pagi Depok                      
 5   19124005 ALH241     S1-Sistem Informasi Pagi Depok               
 6   10824002 1MA02      S1-Ilmu Komunikasi Pagi Depok                
 7   50424001 1IA16      S1-Informatika Pagi Kalimalang               
 8   30324001 1TC03      S1-Desain Interior Pagi Depok                
 9   10124001 1KA24      S1-Sistem Informasi Pagi Kalimalang          
10   31424417 1ID02      S1-Teknik Industri Pagi Depok                
# ℹ 8,626 more rows

Split Class Code

df_student_gen_24_split_class <-  df_student_gen_24_renamed |> 
  separate_wider_regex(class_code,
                       patterns = c(year_code = '\\d+',
                                    major_code = '[A-Z]+',
                                    class_number = '\\d+'),
                       too_few = 'align_start',
                       cols_remove = FALSE)

Check Invalid Class Code

df_student_gen_24_split_class |> 
  select(year_code, class_code) |> 
  filter(is.na(year_code))
# A tibble: 19 × 2
   year_code class_code
   <chr>     <chr>     
 1 <NA>      ALH241    
 2 <NA>      ALH241    
 3 <NA>      ALH241    
 4 <NA>      ALH241    
 5 <NA>      ALH241    
 6 <NA>      ALH241    
 7 <NA>      ALH241    
 8 <NA>      ALH241    
 9 <NA>      ALH241    
10 <NA>      ALH241    
11 <NA>      ALH241    
12 <NA>      ALH241    
13 <NA>      ALH241    
14 <NA>      ALH241    
15 <NA>      ALH241    
16 <NA>      ALH241    
17 <NA>      ALH241    
18 <NA>      ALH241    
19 <NA>      ALH241

Split Additional Info

df_student_gen_24_split_class |> 
  select(additional_info) |> 
  write_csv('additional_info.csv')
df_student_gen_24_split_info <-  df_student_gen_24_split_class |> 
  separate_wider_regex(additional_info,
      patterns = c(
          degree = "(?:S1|D3|SarMag S1)",
          "(?:\\s?-?\\s?)",
          major = "(?:.+?)(?=\\s+(?:Pagi|Sore|Malam))",
          "(?:\\s+)",
          shift = "(?:Pagi|Sore|Malam)",
          "(?:\\s+)",
          branch = "(?:.+)"
      ),
      too_few = 'align_start',
      cols_remove = FALSE
  )

Investigate SarMag

df_student_gen_24_split_info
# A tibble: 8,636 × 10
   student_id year_code major_code class_number class_code degree    major shift
        <dbl> <chr>     <chr>      <chr>        <chr>      <chr>     <chr> <chr>
 1   10824001 1         MA         01           1MA01      S1        Ilmu… Pagi 
 2   20624001 1         SB         01           1SB01      S1        Pari… Pagi 
 3   20224001 1         EB         19           1EB19      SarMag S1 <NA>  <NA> 
 4   11524262 1         PA         06           1PA06      S1        Psik… Pagi 
 5   19124005 <NA>      <NA>       <NA>         ALH241     S1        Sist… Pagi 
 6   10824002 1         MA         02           1MA02      S1        Ilmu… Pagi 
 7   50424001 1         IA         16           1IA16      S1        Info… Pagi 
 8   30324001 1         TC         03           1TC03      S1        Desa… Pagi 
 9   10124001 1         KA         24           1KA24      S1        Sist… Pagi 
10   31424417 1         ID         02           1ID02      S1        Tekn… Pagi 
# ℹ 8,626 more rows
# ℹ 2 more variables: branch <chr>, additional_info <chr>
df_student_gen_24_sarmag <- df_student_gen_24_split_info |> 
  filter(degree == 'SarMag S1')
df_student_gen_24_sarmag |> 
  select(degree:additional_info) |> 
  write_csv('sarmag.csv')

Parse SarMag Special Info

df_student_gen_24_parse_sarmag <- df_student_gen_24_split_info |> separate_wider_regex(
    additional_info,
    patterns = c(
      "SarMag S1 ",
      major_s1 = "[^-]+",
      " - S2 ",
      major_s2 = ".+"
    ),
    too_few = 'align_start',
    cols_remove = FALSE
  )

Merge major into major_s1

df_student_gen_24_parse_sarmag <- df_student_gen_24_parse_sarmag |> 
   mutate(major = if_else(is.na(major), major_s1, major))

Fix Typo

df_student_gen_24_parse_sarmag_fixed_typo <- df_student_gen_24_parse_sarmag |> 
  mutate(major = if_else(major == 'SistemInformasi', 'Sistem Informasi', major)) |> 
  mutate(major = if_else(major == "Arsitektur", "Teknik Arsitektur", major))

add SarMag Branch

df_student_gen_24_parse_sarmag_add_branch <- df_student_gen_24_parse_sarmag_fixed_typo |> 
  mutate(branch = if_else(str_detect(degree, 'SarMag'), 'Jakarta', branch))

Combine Dataset

Review Gen 23 Dataset

df_student_gen_23_split_classcode
# A tibble: 8,080 × 5
   student_id year_code major_code class_number class_code
        <dbl> <chr>     <chr>      <chr>        <chr>     
 1   60223026 2         EC         01           2EC01     
 2   10123014 2         KA         10           2KA10     
 3   10223027 2         EA         04           2EA04     
 4   10623004 2         SA         05           2SA05     
 5   10223028 2         EA         28           2EA28     
 6   10223029 2         EA         22           2EA22     
 7   10823003 2         MA         04           2MA04     
 8   50423017 2         IA         17           2IA17     
 9   50423018 2         IA         01           2IA01     
10   10523002 2         PA         28           2PA28     
# ℹ 8,070 more rows

Extract Major Codes from Gen 24 Dataset

df_major_code <- df_student_gen_24_parse_sarmag_add_branch |> 
  distinct(major_code, major) |>
  drop_na()
df_major_code
# A tibble: 34 × 2
   major_code major           
   <chr>      <chr>           
 1 MA         Ilmu Komunikasi 
 2 SB         Pariwisata      
 3 EB         Akuntansi       
 4 PA         Psikologi       
 5 IA         Informatika     
 6 TC         Desain Interior 
 7 KA         Sistem Informasi
 8 ID         Teknik Industri 
 9 EA         Manajemen       
10 IC         Teknik Mesin    
# ℹ 24 more rows

Check Duplicates

df_major_code |> 
  get_dupes(major_code)
No duplicate combinations found of: major_code
# A tibble: 0 × 3
# ℹ 3 variables: major_code <chr>, dupe_count <int>, major <chr>

Integrate Major Code Dataset to Gen 24 Dataset

df_student_gen_23_add_major <- df_student_gen_23_split_classcode |> 
  left_join(df_major_code, by = 'major_code')

Add generation_year column to both dataset

df_student_gen_23_add_gen_year <- df_student_gen_23_add_major |> 
  mutate(generation_year = 2023)
df_student_gen_24_parse_sarmag_add_gen_year <-  df_student_gen_24_parse_sarmag_add_branch |> 
  mutate(generation_year = 2024)

Bind Rows

df_combined_dataset <- df_student_gen_23_add_gen_year |> 
  bind_rows(df_student_gen_24_parse_sarmag_add_gen_year)
df_combined_dataset |> 
  glimpse()
Rows: 16,716
Columns: 13
$ student_id      <dbl> 60223026, 10123014, 10223027, 10623004, 10223028, 1022…
$ year_code       <chr> "2", "2", "2", "2", "2", "2", "2", "2", "2", "2", "2",…
$ major_code      <chr> "EC", "KA", "EA", "SA", "EA", "EA", "MA", "IA", "IA", …
$ class_number    <chr> "01", "10", "04", "05", "28", "22", "04", "17", "01", …
$ class_code      <chr> "2EC01", "2KA10", "2EA04", "2SA05", "2EA28", "2EA22", …
$ major           <chr> "Ekonomi Syariah", "Sistem Informasi", "Manajemen", "S…
$ generation_year <dbl> 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, …
$ degree          <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ shift           <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ branch          <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ major_s1        <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ major_s2        <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ additional_info <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…

Final Cleanup

Remove unnecessary columns

df_combined_dataset_removed_columns <- df_combined_dataset |> 
  select(-(major_s1:additional_info))

Slight Degree Value Change

df_combined_dataset_change_degree <- df_combined_dataset_removed_columns |> 
  mutate(degree = if_else(degree == "SarMag S1", "SarMag", degree))
df_combined_dataset_fix_unknown_major <- df_combined_dataset_change_degree |>
  mutate(major = case_when(
    major_code == 'HD' ~ 'Profesi Kedokteran',
    major_code == 'HE' ~ 'Profesi Bidan',
    TRUE ~ major  # This will keep the original value for all other cases, including NA
  ))

Export Cleaned Data

df_combined_dataset_fix_unknown_major |> 
  write_csv('combined_cleaned_dataset.csv')
Back to top