 |
| 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!