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
# Chuẩn hóa chữ hoa, chữ thường và khoảng trắng để tránh lỗi nhận diện
new_data$is_expedited_delivery <- as.integer(
tolower(trimws(as.character(new_data$is_expedited_delivery))) == "true"
)
new_data$is_happy_customer <- as.integer(
tolower(trimws(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 1 0 0 0 0 0 0 0 1 1 ...
## $ distance_to_nearest_warehouse: num 1.28 1.162 1.095 0.857 0.587 ...
## $ is_happy_customer : int 1 0 1 0 1 0 1 0 1 0 ...
## $ calculated_total : num 11060 9143 9669 21138 6934 ...numeric_data <- new_data# Chỉ kiểm tra biến order_total
numeric_cols <- "order_total"
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)
)
}
# Tổng hợp kết quả outlier
outlier_summary <- data.frame(
variable = "order_total",
lower_bound = check_outlier(new_data$order_total)$lower,
upper_bound = check_outlier(new_data$order_total)$upper,
outlier_count = sum(
check_outlier(new_data$order_total)$outlier,
na.rm = TRUE
)
)
knitr::kable(
outlier_summary,
caption = "Tổng hợp giá trị ngoại lai của order_total theo phương pháp IQR"
)| variable | lower_bound | upper_bound | outlier_count |
|---|---|---|---|
| order_total | -11041.94 | 35615.87 | 18 |
# Đánh dấu ngoại lai chỉ dựa trên order_total
outlier_flags <- data.frame(
order_total_outlier =
check_outlier(new_data$order_total)$outlier
)
new_data$has_outlier <- outlier_flags$order_total_outlier
new_data <- cbind(new_data, outlier_flags)
# Lọc dữ liệu có ngoại lai
outlier_data <- new_data[
new_data$has_outlier == TRUE,
]
# Bảng dữ liệu thuần, bỏ cột đánh dấu
outlier_data_print <- outlier_data[
,
!(names(outlier_data) %in%
c("has_outlier", "order_total_outlier"))
]
knitr::kable(
head(outlier_data_print, 10),
caption = "Bảng dữ liệu có order_total ngoại lai"
)| nearest_warehouse | delivery_charges | coupon_discount | order_total | season | is_expedited_delivery | distance_to_nearest_warehouse | is_happy_customer | calculated_total | |
|---|---|---|---|---|---|---|---|---|---|
| 13 | 3 | 94.75 | 0 | 926057.25 | 2 | 1 | 0.9509 | 1 | 19104.75 |
| 32 | 3 | 64.81 | 15 | 402307.25 | 3 | 0 | 1.0763 | 1 | 13511.81 |
| 42 | 2 | 73.33 | 15 | 620502.30 | 2 | 0 | 0.9757 | 1 | 12032.83 |
| 56 | 2 | 62.93 | 5 | 41406.93 | 4 | 1 | 0.8949 | 0 | 41406.93 |
| 62 | 1 | 65.32 | 0 | 37215.32 | 4 | 1 | 1.2762 | 0 | 37215.32 |
| 82 | 3 | 71.34 | 25 | 393761.23 | 1 | 0 | 0.2703 | 1 | 14445.09 |
| 205 | 3 | 85.53 | 5 | 967408.88 | 1 | 0 | 1.8957 | 1 | 8436.03 |
| 239 | 1 | 79.05 | 0 | 648477.60 | 1 | 0 | 1.0833 | 1 | 17609.05 |
| 262 | 1 | 108.92 | 25 | 649193.76 | 1 | 1 | 1.9565 | 1 | 16706.42 |
| 268 | 1 | 62.70 | 5 | 969651.79 | 2 | 0 | 1.3244 | 0 | 15405.20 |
# Chỉ loại các dòng có order_total ngoại lai
clean_data <- new_data[
new_data$has_outlier == FALSE,
]
# Bỏ các cột đánh dấu
clean_data_print <- clean_data[
,
!(names(clean_data) %in%
c("has_outlier", "order_total_outlier"))
]
knitr::kable(
head(clean_data_print, 10),
caption = "Bảng dữ liệu sau khi loại bỏ ngoại lai theo order_total"
)| nearest_warehouse | delivery_charges | coupon_discount | order_total | season | is_expedited_delivery | distance_to_nearest_warehouse | is_happy_customer | calculated_total |
|---|---|---|---|---|---|---|---|---|
| 3 | 79.89 | 10 | 11059.89 | 4 | 1 | 1.2800 | 1 | 11059.89 |
| 3 | 62.71 | 0 | 9142.71 | 2 | 0 | 1.1621 | 0 | 9142.71 |
| 2 | 65.87 | 10 | 9668.87 | 3 | 0 | 1.0949 | 1 | 9668.87 |
| 2 | 57.61 | 15 | 21137.61 | 2 | 0 | 0.8571 | 0 | 21137.61 |
| 2 | 75.54 | 25 | 6934.29 | 1 | 0 | 0.5867 | 1 | 6934.29 |
| 1 | 71.22 | 10 | 7100.22 | 1 | 0 | 2.0752 | 0 | 7100.22 |
| 3 | 74.84 | 5 | 13089.84 | 1 | 0 | 0.6767 | 1 | 13089.84 |
| 3 | 52.28 | 5 | 10789.79 | 3 | 0 | 1.3043 | 0 | 7614.28 |
| 1 | 107.58 | 10 | 22958.58 | 1 | 1 | 1.6595 | 1 | 22958.58 |
| 3 | 62.26 | 15 | 11384.26 | 4 | 1 | 0.6093 | 0 | 11384.26 |
# Kiểm tra số lượng dòng
nrow(new_data)## [1] 500nrow(outlier_data)## [1] 18nrow(clean_data)## [1] 482str(clean_data_print)## 'data.frame': 482 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 1 0 0 0 0 0 0 0 1 1 ...
## $ distance_to_nearest_warehouse: num 1.28 1.162 1.095 0.857 0.587 ...
## $ is_happy_customer : int 1 0 1 0 1 0 1 0 1 0 ...
## $ calculated_total : num 11060 9143 9669 21138 6934 ...sum(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(
numeric_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!