How to Import Data, Manipulate It, and Making Automatically Split Data by Unique ID using R?

Import Data, Data Manipulation, and Save Split Data by Unique ID

Hello readers, I'm so happy to say that I can share on this blog again. Now, I'll show you about how to manipulate data which imported from computer. The main package that so powerful in data manipulation is dplyr. This package is like pandas used in Python.

In this section, we import the data first and then we manipulate the digit of id and code (kd_kab, kd_kec, and kd_desa) with same variable's name. It realized using mutate() function that available in the package. However, the data that used just dummy data I prepared in xlsx format (excel).

After changing the digits, we run the code to make a new variable named "splitbase". This variable will be the base of splitting data in unique id. It may be a rare case in our daily data preprocessing, but I certain that this case would be found in real data, especially administration data.

The final stage of this section is how to save split data automatically in csv format separated by ";". In this stage, we use for function as looping activity in saving file that are subsets. As tools  for Data Science, Data Mining, and Data Engineering, I think R could help us to face our problems in collecting until dissemination data. So, importing data, manipulating it, and saving the split data becomes an easy thing.

I think enough for the explanations, now we want to practice it using some codes in R programming as below:


# activate package dplyr for data manipulation
# activate package readxl for import the data
library(dplyr)
library(readxl)
# import data
mydata <- read_excel("dummydata.xlsx")
mydata
## # A tibble: 25 x 11
##    id    kd_kab kab     kd_kec kec      kd_desa desa    nbs     nrt no_art nama 
##    <lgl>  <dbl> <chr>    <dbl> <chr>      <dbl> <chr>   <chr> <dbl>  <dbl> <chr>
##  1 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      1      1 A    
##  2 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      1      2 B    
##  3 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      2      1 C    
##  4 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      2      2 D    
##  5 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      2      3 E    
##  6 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      3      1 F    
##  7 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      3      2 G    
##  8 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      3      3 H    
##  9 NA         1 PACITAN     10 DONOROJO       3 KALAK   009B      3      4 I    
## 10 NA         1 PACITAN     10 DONOROJO       4 SENDANG 014P      1      1 AA   
## # ... with 15 more rows
# change kd_kab in 2 digits
# change kd_kec in 3 digits
# change kd_desa in 3 digits
mydata <- mydata %>%
  mutate(kd_kab = case_when(
    kd_kab < 10 ~ paste0("0", kd_kab),
    TRUE ~ as.character(kd_kab)),
    kd_kec = case_when(
    kd_kec < 10 ~ paste0("00", kd_kec),
    kd_kec < 100 ~ paste0("0", kd_kec),
    TRUE ~ as.character(kd_kec)),
    kd_desa = case_when(
    kd_desa < 10 ~ paste0("00", kd_desa),
    kd_desa < 100 ~ paste0("0", kd_desa),
    TRUE ~ as.character(kd_desa))
  )
# show the result
mydata
## # A tibble: 25 x 11
##    id    kd_kab kab     kd_kec kec      kd_desa desa    nbs     nrt no_art nama 
##    <lgl> <chr>  <chr>   <chr>  <chr>    <chr>   <chr>   <chr> <dbl>  <dbl> <chr>
##  1 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      1      1 A    
##  2 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      1      2 B    
##  3 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      2      1 C    
##  4 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      2      2 D    
##  5 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      2      3 E    
##  6 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      3      1 F    
##  7 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      3      2 G    
##  8 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      3      3 H    
##  9 NA    01     PACITAN 010    DONOROJO 003     KALAK   009B      3      4 I    
## 10 NA    01     PACITAN 010    DONOROJO 004     SENDANG 014P      1      1 AA   
## # ... with 15 more rows
# fill id with combination kd_kab, kd_kec, kd_desa, nbs, and nrt
mydata <- mydata %>%
  mutate(id = paste(kd_kab, kd_kec, kd_desa, nbs, nrt, sep = ""))
# show the result
mydata
## # A tibble: 25 x 11
##    id            kd_kab kab     kd_kec kec      kd_desa desa    nbs     nrt no_art nama 
##    <chr>         <chr>  <chr>   <chr>  <chr>    <chr>   <chr>   <chr> <dbl>  <dbl> <chr>
##  1 01010003009B1 01     PACITAN 010    DONOROJO 003     KALAK   009B      1      1 A    
##  2 01010003009B1 01     PACITAN 010    DONOROJO 003     KALAK   009B      1      2 B    
##  3 01010003009B2 01     PACITAN 010    DONOROJO 003     KALAK   009B      2      1 C    
##  4 01010003009B2 01     PACITAN 010    DONOROJO 003     KALAK   009B      2      2 D    
##  5 01010003009B2 01     PACITAN 010    DONOROJO 003     KALAK   009B      2      3 E    
##  6 01010003009B3 01     PACITAN 010    DONOROJO 003     KALAK   009B      3      1 F    
##  7 01010003009B3 01     PACITAN 010    DONOROJO 003     KALAK   009B      3      2 G    
##  8 01010003009B3 01     PACITAN 010    DONOROJO 003     KALAK   009B      3      3 H    
##  9 01010003009B3 01     PACITAN 010    DONOROJO 003     KALAK   009B      3      4 I    
## 10 01010004014P1 01     PACITAN 010    DONOROJO 004     SENDANG 014P      1      1 AA   
## # ... with 15 more rows
# make a new variable for split base then make it unique
mydata <- mydata %>%
  mutate(splitbase = paste(kd_kab, kd_kec, kd_desa, nbs, sep = ""))

# unique nbs
myunique <- unique(mydata$splitbase)

# show the result
mydata
## # A tibble: 25 x 12
##    id            kd_kab kab     kd_kec kec      kd_desa desa    nbs     nrt no_art nama  splitbase   
##    <chr>         <chr>  <chr>   <chr>  <chr>    <chr>   <chr>   <chr> <dbl>  <dbl> <chr> <chr>       
##  1 01010003009B1 01     PACITAN 010    DONOROJO 003     KALAK   009B      1      1 A     01010003009B
##  2 01010003009B1 01     PACITAN 010    DONOROJO 003     KALAK   009B      1      2 B     01010003009B
##  3 01010003009B2 01     PACITAN 010    DONOROJO 003     KALAK   009B      2      1 C     01010003009B
##  4 01010003009B2 01     PACITAN 010    DONOROJO 003     KALAK   009B      2      2 D     01010003009B
##  5 01010003009B2 01     PACITAN 010    DONOROJO 003     KALAK   009B      2      3 E     01010003009B
##  6 01010003009B3 01     PACITAN 010    DONOROJO 003     KALAK   009B      3      1 F     01010003009B
##  7 01010003009B3 01     PACITAN 010    DONOROJO 003     KALAK   009B      3      2 G     01010003009B
##  8 01010003009B3 01     PACITAN 010    DONOROJO 003     KALAK   009B      3      3 H     01010003009B
##  9 01010003009B3 01     PACITAN 010    DONOROJO 003     KALAK   009B      3      4 I     01010003009B
## 10 01010004014P1 01     PACITAN 010    DONOROJO 004     SENDANG 014P      1      1 AA    01010004014P
## # ... with 15 more rows
# export splited data in csv format and named it using splitbase
# as default, separation of the element of data with comma "," but in this case we use ";"
# the result can be found in Documents
for (i in myunique) {
  mysubset <- subset(mydata, splitbase == i)
  assign(paste0("data_list", i), mysubset)

  # Mengekspor subset data frame menjadi file CSV
  write.csv(mysubset[-12], file = paste0("data_list", i, ".csv"),
            row.names = FALSE, fileEncoding = "UTF-8", sep = ";")
}
## Warning in write.csv(mysubset[-12], file = paste0("data_list", i, ".csv"), : attempt to set 'sep' ignored

## Warning in write.csv(mysubset[-12], file = paste0("data_list", i, ".csv"), : attempt to set 'sep' ignored

## Warning in write.csv(mysubset[-12], file = paste0("data_list", i, ".csv"), : attempt to set 'sep' ignored

The results seem below:

Data list by id

This is the end of our sharing, thank you for your attention. Feel free to like, share, and leave a command as your support to this blog. Happy learning!

Add Comments


EmoticonEmoticon