data <- read.csv("dirty_data.csv")
knitr::kable(
head(data, 5),
caption = "Bảng dữ liệu 5 dòng đầu tiên"
)| order_id | customer_id | date | nearest_warehouse | shopping_cart | order_price | delivery_charges | customer_lat | customer_long | coupon_discount | order_total | season | is_expedited_delivery | distance_to_nearest_warehouse | latest_customer_review | is_happy_customer |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ORD182494 | ID6197211592 | 2019-06-22 | Thompson | [(‘Lucent 330S’, 1), (‘Thunder line’, 2), (‘iStream’, 2), (‘pearTV’, 1)] | 12200 | 79.89 | -37.81511 | 144.9328 | 10 | 11059.89 | Winter | True | 1.2800 | perfect phone and trusted seller. phone itself is amazing. i got this phone as shown here on the description. it came in with original packaging including all original accessories. yes this phone is unlocked, so no doubt about it. | True |
| ORD395518 | ID0282825849 | 2019-12-29 | Thompson | [(‘Thunder line’, 1), (‘Universe Note’, 2)] | 9080 | 62.71 | -37.80274 | 144.9511 | 0 | 9142.71 | Summer | False | 1.1621 | it keeps dropping calls the wifi don’t work this is a waste of money this phone is worthless, it keeps dropping calls the wifi don’t work this is a waste of money | False |
| ORD494479 | ID0579391891 | 2019-03-02 | Nickolson | [(‘Thunder line’, 1), (‘pearTV’, 2)] | 10670 | 65.87 | -37.82130 | 144.9576 | 10 | 9668.87 | Autumn | False | 1.0949 | five stars this is a great cheap phone. | True |
| ORD019224 | ID4544561904 | 2019-01-12 | Nickolson | [(‘Universe Note’, 1), (‘Alcon 10’, 2), (‘Olivia x460’, 1), (‘iAssist Line’, 1)] | 24800 | 57.61 | -37.81142 | 144.9731 | 15 | 21137.61 | Summer | False | 0.8571 | charger did not fit the charger didn’t fit. | False |
| ORD104032 | ID6231506320 | 2019-11-28 | Nickolson | [(‘Universe Note’, 1), (‘Olivia x460’, 1), (‘iStream’, 1), (‘Toshika 750’, 1)] | 9145 | 75.54 | 37.82386 | 144.9699 | 25 | 6934.29 | Spring | False | 0.5867 | four stars good | True |
Cấu trúc tệp tin
str(data)## 'data.frame': 500 obs. of 16 variables:
## $ order_id : chr "ORD182494" "ORD395518" "ORD494479" "ORD019224" ...
## $ customer_id : chr "ID6197211592" "ID0282825849" "ID0579391891" "ID4544561904" ...
## $ date : chr "2019-06-22" "2019-12-29" "2019-03-02" "2019-01-12" ...
## $ nearest_warehouse : chr "Thompson" "Thompson" "Nickolson" "Nickolson" ...
## $ shopping_cart : chr "[('Lucent 330S', 1), ('Thunder line', 2), ('iStream', 2), ('pearTV', 1)]" "[('Thunder line', 1), ('Universe Note', 2)]" "[('Thunder line', 1), ('pearTV', 2)]" "[('Universe Note', 1), ('Alcon 10', 2), ('Olivia x460', 1), ('iAssist Line', 1)]" ...
## $ order_price : int 12200 9080 10670 24800 9145 7810 13700 7960 25390 13320 ...
## $ delivery_charges : num 79.9 62.7 65.9 57.6 75.5 ...
## $ customer_lat : num -37.8 -37.8 -37.8 -37.8 37.8 ...
## $ customer_long : num 145 145 145 145 145 ...
## $ coupon_discount : int 10 0 10 15 25 10 5 5 10 15 ...
## $ order_total : num 11060 9143 9669 21138 6934 ...
## $ season : chr "Winter" "Summer" "Autumn" "Summer" ...
## $ is_expedited_delivery : chr "True" "False" "False" "False" ...
## $ distance_to_nearest_warehouse: num 1.28 1.162 1.095 0.857 0.587 ...
## $ latest_customer_review : chr "perfect phone and trusted seller. phone itself is amazing. i got this phone as shown here on the description. i"| __truncated__ "it keeps dropping calls the wifi don't work this is a waste of money this phone is worthless, it keeps dropping"| __truncated__ "five stars this is a great cheap phone." "charger did not fit the charger didn't fit." ...
## $ is_happy_customer : chr "True" "False" "True" "False" ...new_data <- data[, c("nearest_warehouse", "delivery_charges", "coupon_discount", "order_total", "season", "is_expedited_delivery", "distance_to_nearest_warehouse", "is_happy_customer")]
head(new_data, 10)## nearest_warehouse delivery_charges coupon_discount order_total season
## 1 Thompson 79.89 10 11059.89 Winter
## 2 Thompson 62.71 0 9142.71 Summer
## 3 Nickolson 65.87 10 9668.87 Autumn
## 4 Nickolson 57.61 15 21137.61 Summer
## 5 Nickolson 75.54 25 6934.29 Spring
## 6 Bakers 71.22 10 7100.22 Spring
## 7 Thompson 74.84 5 13089.84 Spring
## 8 Thompson 52.28 5 10789.79 Autumn
## 9 Bakers 107.58 10 22958.58 Spring
## 10 Thompson 62.26 15 11384.26 winter
## is_expedited_delivery distance_to_nearest_warehouse is_happy_customer
## 1 True 1.2800 True
## 2 False 1.1621 False
## 3 False 1.0949 True
## 4 False 0.8571 False
## 5 False 0.5867 True
## 6 False 2.0752 False
## 7 False 0.6767 True
## 8 False 1.3043 False
## 9 True 1.6595 True
## 10 True 0.6093 Falseapply(is.na(new_data), 2, which)## integer(0)unique(new_data$nearest_warehouse)## [1] "Thompson" "Nickolson" "Bakers" "nickolson" "thompson"Kết quả chọn biến
knitr::kable(
head(new_data, 10),
caption = "Bảng dữ liệu sau khi lựa chọn biến"
)| nearest_warehouse | delivery_charges | coupon_discount | order_total | season | is_expedited_delivery | distance_to_nearest_warehouse | is_happy_customer |
|---|---|---|---|---|---|---|---|
| Thompson | 79.89 | 10 | 11059.89 | Winter | True | 1.2800 | True |
| Thompson | 62.71 | 0 | 9142.71 | Summer | False | 1.1621 | False |
| Nickolson | 65.87 | 10 | 9668.87 | Autumn | False | 1.0949 | True |
| Nickolson | 57.61 | 15 | 21137.61 | Summer | False | 0.8571 | False |
| Nickolson | 75.54 | 25 | 6934.29 | Spring | False | 0.5867 | True |
| Bakers | 71.22 | 10 | 7100.22 | Spring | False | 2.0752 | False |
| Thompson | 74.84 | 5 | 13089.84 | Spring | False | 0.6767 | True |
| Thompson | 52.28 | 5 | 10789.79 | Autumn | False | 1.3043 | False |
| Bakers | 107.58 | 10 | 22958.58 | Spring | True | 1.6595 | True |
| Thompson | 62.26 | 15 | 11384.26 | winter | True | 0.6093 | False |
Kiểm tra nearest_house
new_data$nearest_warehouse[new_data$nearest_warehouse == 'thompson'] <- 'Thompson'
new_data$nearest_warehouse[new_data$nearest_warehouse == 'nickolson'] <- 'Nickolson'
unique(new_data$nearest_warehouse)## [1] "Thompson" "Nickolson" "Bakers"Kiểm tra season
unique(new_data$season)## [1] "Winter" "Summer" "Autumn" "Spring" "winter" "spring" "autumn" "summer"new_data$season[new_data$season == 'spring'] <- 'Spring'
new_data$season[new_data$season == 'summer'] <- 'Summer'
new_data$season[new_data$season == 'autumn'] <- 'Autumn'
new_data$season[new_data$season == 'winter'] <- 'Winter'
unique(new_data$season)## [1] "Winter" "Summer" "Autumn" "Spring"Kiểm tra điều kiện tổng hóa đơn
new_data$calculated_total <- data$order_price*(1-data$coupon_discount/100) + data$delivery_charges
all.equal(data$order_total, new_data$calculated_total)## [1] "Mean relative difference: 1.334735"data$order_total <- new_data$calculated_total
all.equal(data$order_total, new_data$calculated_total)## [1] TRUEclean <- new_data# Chuẩn hóa chữ hoa, chữ thường
new_data$nearest_warehouse <- tools::toTitleCase(
tolower(trimws(new_data$nearest_warehouse))
)
new_data$season <- tools::toTitleCase(
tolower(trimws(new_data$season))
)
# Chuyển tên kho hàng thành số
new_data$nearest_warehouse <- match(
new_data$nearest_warehouse,
c("Bakers", "Nickolson", "Thompson")
)
# Chuyển mùa thành số
new_data$season <- match(
new_data$season,
c("Spring", "Summer", "Autumn", "Winter")
)
# Chuyển TRUE/FALSE thành 1/0
new_data$is_expedited_delivery <- as.integer(
as.character(new_data$is_expedited_delivery) == "TRUE"
)
new_data$is_happy_customer <- as.integer(
as.character(new_data$is_happy_customer) == "TRUE"
)
str(new_data)## 'data.frame': 500 obs. of 9 variables:
## $ nearest_warehouse : int 3 3 2 2 2 1 3 3 1 3 ...
## $ delivery_charges : num 79.9 62.7 65.9 57.6 75.5 ...
## $ coupon_discount : int 10 0 10 15 25 10 5 5 10 15 ...
## $ order_total : num 11060 9143 9669 21138 6934 ...
## $ season : int 4 2 3 2 1 1 1 3 1 4 ...
## $ is_expedited_delivery : int 0 0 0 0 0 0 0 0 0 0 ...
## $ distance_to_nearest_warehouse: num 1.28 1.162 1.095 0.857 0.587 ...
## $ is_happy_customer : int 0 0 0 0 0 0 0 0 0 0 ...
## $ calculated_total : num 11060 9143 9669 21138 6934 ...# Chỉ kiểm tra các biến định lượng
numeric_cols <- names(new_data)[
vapply(new_data, is.numeric, logical(1))
]
check_outlier <- function(x) {
q1 <- quantile(x, 0.25, na.rm = TRUE)
q3 <- quantile(x, 0.75, na.rm = TRUE)
iqr_value <- IQR(x, na.rm = TRUE)
lower <- q1 - 1.5 * iqr_value
upper <- q3 + 1.5 * iqr_value
list(
lower = as.numeric(lower),
upper = as.numeric(upper),
outlier = !is.na(x) & (x < lower | x > upper)
)
}
outlier_summary <- do.call(rbind, lapply(numeric_cols, function(col) {
result <- check_outlier(new_data[[col]])
data.frame(
variable = col,
lower_bound = result$lower,
upper_bound = result$upper,
outlier_count = sum(result$outlier, na.rm = TRUE)
)
}))
knitr::kable(
outlier_summary,
caption = "Tổng hợp số lượng giá trị ngoại lai theo phương pháp IQR"
)| variable | lower_bound | upper_bound | outlier_count |
|---|---|---|---|
| nearest_warehouse | 0.500000 | 4.500000 | 0 |
| delivery_charges | 41.123750 | 107.413750 | 15 |
| coupon_discount | -10.000000 | 30.000000 | 0 |
| order_total | -11041.943750 | 35615.866250 | 18 |
| season | -2.000000 | 6.000000 | 0 |
| is_expedited_delivery | 0.000000 | 0.000000 | 0 |
| distance_to_nearest_warehouse | -0.234375 | 2.394425 | 12 |
| is_happy_customer | 0.000000 | 0.000000 | 0 |
| calculated_total | -10919.470000 | 35234.690000 | 18 |
# Tạo bảng đánh dấu ngoại lệ theo từng biến
outlier_flags <- as.data.frame(
lapply(numeric_cols, function(col) check_outlier(new_data[[col]])$outlier)
)
names(outlier_flags) <- paste0(numeric_cols, "_outlier")
new_data$has_outlier <- rowSums(outlier_flags, na.rm = TRUE) > 0
new_data <- cbind(new_data, outlier_flags)
outlier_data <- new_data[new_data$has_outlier, ]
outlier_data_print <- outlier_data[
, !(names(outlier_data) %in% c("has_outlier", names(outlier_flags)))
]
knitr::kable(
head(outlier_data_print, 10),
caption = "Bảng dữ liệu chứa giá trị ngoại lai"
)| nearest_warehouse | delivery_charges | coupon_discount | order_total | season | is_expedited_delivery | distance_to_nearest_warehouse | is_happy_customer | calculated_total | |
|---|---|---|---|---|---|---|---|---|---|
| 9 | 1 | 107.58 | 10 | 22958.58 | 1 | 0 | 1.6595 | 0 | 22958.58 |
| 13 | 3 | 94.75 | 0 | 926057.25 | 2 | 0 | 0.9509 | 0 | 19104.75 |
| 15 | 2 | 79.78 | 0 | 31979.78 | 3 | 0 | 66.6483 | 0 | 31979.78 |
| 20 | 2 | 77.11 | 5 | 6294.86 | 2 | 0 | 61.2935 | 0 | 6294.86 |
| 32 | 3 | 64.81 | 15 | 402307.25 | 3 | 0 | 1.0763 | 0 | 13511.81 |
| 33 | 1 | 70.74 | 0 | 7900.74 | 3 | 0 | 2.5592 | 0 | 7900.74 |
| 42 | 2 | 73.33 | 15 | 620502.30 | 2 | 0 | 0.9757 | 0 | 12032.83 |
| 56 | 2 | 62.93 | 5 | 41406.93 | 4 | 0 | 0.8949 | 0 | 41406.93 |
| 62 | 1 | 65.32 | 0 | 37215.32 | 4 | 0 | 1.2762 | 0 | 37215.32 |
| 68 | 3 | 79.39 | 15 | 18316.14 | 2 | 0 | 1.4520 | 0 | 52455.54 |
clean_data <- new_data[new_data$has_outlier == FALSE, ]
clean_data_print <- clean_data[
, !(names(clean_data) %in% c("has_outlier", names(outlier_flags)))
]
knitr::kable(
head(clean_data_print, 10),
caption = "Bảng dữ liệu sau khi loại bỏ giá trị ngoại lai"
)| nearest_warehouse | delivery_charges | coupon_discount | order_total | season | is_expedited_delivery | distance_to_nearest_warehouse | is_happy_customer | calculated_total | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 3 | 79.89 | 10 | 11059.89 | 4 | 0 | 1.2800 | 0 | 11059.89 |
| 2 | 3 | 62.71 | 0 | 9142.71 | 2 | 0 | 1.1621 | 0 | 9142.71 |
| 3 | 2 | 65.87 | 10 | 9668.87 | 3 | 0 | 1.0949 | 0 | 9668.87 |
| 4 | 2 | 57.61 | 15 | 21137.61 | 2 | 0 | 0.8571 | 0 | 21137.61 |
| 5 | 2 | 75.54 | 25 | 6934.29 | 1 | 0 | 0.5867 | 0 | 6934.29 |
| 6 | 1 | 71.22 | 10 | 7100.22 | 1 | 0 | 2.0752 | 0 | 7100.22 |
| 7 | 3 | 74.84 | 5 | 13089.84 | 1 | 0 | 0.6767 | 0 | 13089.84 |
| 8 | 3 | 52.28 | 5 | 10789.79 | 3 | 0 | 1.3043 | 0 | 7614.28 |
| 10 | 3 | 62.26 | 15 | 11384.26 | 4 | 0 | 0.6093 | 0 | 11384.26 |
| 11 | 1 | 78.25 | 0 | 31973.25 | 4 | 0 | 1.1919 | 0 | 31973.25 |
nrow(new_data)## [1] 500nrow(clean_data)## [1] 441sum(clean_data$has_outlier)## [1] 0if (!requireNamespace("openxlsx", quietly = TRUE)) {
install.packages("openxlsx")
}
if (!requireNamespace("zip", quietly = TRUE)) {
install.packages("zip")
}
folder <- "BTL_XSTK_"
dir.create(folder, showWarnings = FALSE)
openxlsx::write.xlsx(
clean,
file.path(folder, "1.Du_lieu_da_lam_sach.xlsx")
)
openxlsx::write.xlsx(
new_data,
file.path(folder, "02_Du_lieu_da_lam_sach_va_ep_kieu.xlsx")
)
openxlsx::write.xlsx(
outlier_data_print,
file.path(folder, "03_1_Du_lieu_ngoai_lai.xlsx")
)
openxlsx::write.xlsx(
outlier_data,
file.path(folder, "03_2_Du_lieu_ngoai_lai.xlsx")
)
openxlsx::write.xlsx(
clean_data_print,
file.path(folder, "04_Ket_qua.xlsx")
)
zip::zipr(
zipfile = "BTL_XSTK_.zip",
files = folder
)
cat("Da xuat thanh cong 5 file Excel!")## Da xuat thanh cong 5 file Excel!