# Muhammad Dzaki Naufal
# 5003241147
# Data Mining W2 Latihan Soal
##### Read Data #####
setwd("C:/Users/Dzaki Naufal/Downloads")
# Load package
library(readxl)
## Warning: package 'readxl' was built under R version 4.4.3
library(ggplot2)
## Warning: package 'ggplot2' was built under R version 4.4.3
library(scales)
## Warning: package 'scales' was built under R version 4.4.3
library(dplyr)
## Warning: package 'dplyr' was built under R version 4.4.3
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
# Nama/path file
data_file <- "data_kopi.xlsx"
# Cek nama sheet
excel_sheets(data_file)
## [1] "produk" "transaksi"
# Membaca masing-masing sheet
Produk <- read_excel(data_file, sheet = "produk")
transaksi <- read_excel(data_file, sheet = "transaksi")
#cek masing2 sheet
head(Produk)
## # A tibble: 6 × 4
## id_produk nama_produk harga diskon
## <chr> <chr> <chr> <chr>
## 1 P01 Kopi Susu Rp15.000 10%
## 2 P02 Americano Rp12.000 0%
## 3 P03 Matcha Latte Rp18.000 5%
## 4 P04 Cappuccino Rp17.000 15%
## 5 P05 Es Kopi Gula Aren Rp16.000 20%
## 6 P06 Chocolate Latte Rp19.000 0%
head(transaksi)
## # A tibble: 6 × 4
## id_trx tgl_trx id_produk qty
## <dbl> <chr> <chr> <dbl>
## 1 1 01/09/2026 P02 1
## 2 2 01/09/2026 P05 2
## 3 3 01/09/2026 P04 2
## 4 4 01/09/2026 P02 5
## 5 5 02/09/2026 P07 1
## 6 6 02/09/2026 P01 1
str(Produk)
## tibble [8 × 4] (S3: tbl_df/tbl/data.frame)
## $ id_produk : chr [1:8] "P01" "P02" "P03" "P04" ...
## $ nama_produk: chr [1:8] "Kopi Susu" "Americano" "Matcha Latte" "Cappuccino" ...
## $ harga : chr [1:8] "Rp15.000" "Rp12.000" "Rp18.000" "Rp17.000" ...
## $ diskon : chr [1:8] "10%" "0%" "5%" "15%" ...
str(transaksi)
## tibble [345 × 4] (S3: tbl_df/tbl/data.frame)
## $ id_trx : num [1:345] 1 2 3 4 5 6 7 8 9 10 ...
## $ tgl_trx : chr [1:345] "01/09/2026" "01/09/2026" "01/09/2026" "01/09/2026" ...
## $ id_produk: chr [1:345] "P02" "P05" "P04" "P02" ...
## $ qty : num [1:345] 1 2 2 5 1 1 5 5 2 5 ...
##### Soal 1 Gabungkan sheet jadi data terpadu ######
# Gabungkan data berdasarkan id_produk
df_gabungan_mentah <- transaksi %>%
left_join(Produk,by = "id_produk") %>%
select(
id_trx,
tgl_trx,
nama_produk,
harga,
diskon,
qty
)
head(df_gabungan_mentah)
## # A tibble: 6 × 6
## id_trx tgl_trx nama_produk harga diskon qty
## <dbl> <chr> <chr> <chr> <chr> <dbl>
## 1 1 01/09/2026 Americano Rp12.000 0% 1
## 2 2 01/09/2026 Es Kopi Gula Aren Rp16.000 20% 2
## 3 3 01/09/2026 Cappuccino Rp17.000 15% 2
## 4 4 01/09/2026 Americano Rp12.000 0% 5
## 5 5 02/09/2026 Vietnam Drip Rp14.000 10% 1
## 6 6 02/09/2026 Kopi Susu Rp15.000 10% 1
##### Soal 2 Beberapa kolom pada df_produk dan df_transaksi tersimpan sebagai teks
#salin data
Produk_clean <- Produk
transaksi_clean <- transaksi
# 1. Konversi harga dari teks ke angka murni
Produk_clean$harga_num <- as.numeric(
gsub("[^0-9]", "", Produk_clean$harga)
)
# Verifikasi hasil konversi harga
head(Produk_clean[, c("harga", "harga_num")], 10)
## # A tibble: 8 × 2
## harga harga_num
## <chr> <dbl>
## 1 Rp15.000 15000
## 2 Rp12.000 12000
## 3 Rp18.000 18000
## 4 Rp17.000 17000
## 5 Rp16.000 16000
## 6 Rp19.000 19000
## 7 Rp14.000 14000
## 8 Rp20.000 20000
class(Produk_clean$harga_num)
## [1] "numeric"
sum(is.na(Produk_clean$harga_num))
## [1] 0
# 2. Konversi diskon ke pecahan desimal (0 - 1)
Produk_clean$diskon_num <- as.numeric(
gsub("%", "", Produk_clean$diskon)
) / 100
# Verifikasi hasil konversi diskon
head(Produk_clean[, c("diskon", "diskon_num")], 10)
## # A tibble: 8 × 2
## diskon diskon_num
## <chr> <dbl>
## 1 10% 0.1
## 2 0% 0
## 3 5% 0.05
## 4 15% 0.15
## 5 20% 0.2
## 6 0% 0
## 7 10% 0.1
## 8 25% 0.25
range(Produk_clean$diskon_num, na.rm = TRUE)
## [1] 0.00 0.25
sum(is.na(Produk_clean$diskon_num))
## [1] 0
# 3. Konversi qty menjadi numerik
transaksi_clean$qty_num <- as.numeric(
transaksi_clean$qty
)
# Verifikasi hasil konversi qty
head(transaksi_clean[, c("qty", "qty_num")], 10)
## # A tibble: 10 × 2
## qty qty_num
## <dbl> <dbl>
## 1 1 1
## 2 2 2
## 3 2 2
## 4 5 5
## 5 1 1
## 6 1 1
## 7 5 5
## 8 5 5
## 9 2 2
## 10 5 5
class(transaksi_clean$qty_num)
## [1] "numeric"
sum(is.na(transaksi_clean$qty_num))
## [1] 0
# 4. Konversi tanggal transaksi menjadi Date valid
transaksi_clean$tgl_trx_valid <- as.Date(
transaksi_clean$tgl_trx,
format = "%d/%m/%Y"
)
# Verifikasi hasil konversi tanggal
head(
transaksi_clean[, c("tgl_trx", "tgl_trx_valid")],
10
)
## # A tibble: 10 × 2
## tgl_trx tgl_trx_valid
## <chr> <date>
## 1 01/09/2026 2026-09-01
## 2 01/09/2026 2026-09-01
## 3 01/09/2026 2026-09-01
## 4 01/09/2026 2026-09-01
## 5 02/09/2026 2026-09-02
## 6 02/09/2026 2026-09-02
## 7 03/09/2026 2026-09-03
## 8 03/09/2026 2026-09-03
## 9 04/09/2026 2026-09-04
## 10 04/09/2026 2026-09-04
# 5. Gabungkan data hasil cleaning
df_gabungan_clean <- transaksi_clean %>%
left_join(Produk_clean, by = "id_produk") %>%
select(
id_trx,
tgl_trx_valid,
nama_produk,
harga_num,
diskon_num,
qty_num
)
# Verifikasi akhir
head(df_gabungan_clean)
## # A tibble: 6 × 6
## id_trx tgl_trx_valid nama_produk harga_num diskon_num qty_num
## <dbl> <date> <chr> <dbl> <dbl> <dbl>
## 1 1 2026-09-01 Americano 12000 0 1
## 2 2 2026-09-01 Es Kopi Gula Aren 16000 0.2 2
## 3 3 2026-09-01 Cappuccino 17000 0.15 2
## 4 4 2026-09-01 Americano 12000 0 5
## 5 5 2026-09-02 Vietnam Drip 14000 0.1 1
## 6 6 2026-09-02 Kopi Susu 15000 0.1 1
str(df_gabungan_clean)
## tibble [345 × 6] (S3: tbl_df/tbl/data.frame)
## $ id_trx : num [1:345] 1 2 3 4 5 6 7 8 9 10 ...
## $ tgl_trx_valid: Date[1:345], format: "2026-09-01" "2026-09-01" ...
## $ nama_produk : chr [1:345] "Americano" "Es Kopi Gula Aren" "Cappuccino" "Americano" ...
## $ harga_num : num [1:345] 12000 16000 17000 12000 14000 15000 17000 15000 14000 20000 ...
## $ diskon_num : num [1:345] 0 0.2 0.15 0 0.1 0.1 0.15 0.1 0.1 0.25 ...
## $ qty_num : num [1:345] 1 2 2 5 1 1 5 5 2 5 ...
##### Soal 3 Buat kolom-kolom turunan untuk tabel integrasi #####
# 1. Hitung total nilai transaksi per baris
df_gabungan_clean$total_bayar <-
df_gabungan_clean$qty_num *
df_gabungan_clean$harga_num *
(1 - df_gabungan_clean$diskon_num)
# 2. Ekstraksi nama hari transaksi
df_gabungan_clean$hari_transaksi <- format(
df_gabungan_clean$tgl_trx_valid,
"%A"
)
# 3. Kategori Diskon menggunakan nested ifelse
df_gabungan_clean$kategori_diskon <- ifelse(
df_gabungan_clean$diskon_num < 0.30,
"Rendah",
ifelse(
df_gabungan_clean$diskon_num <= 0.60,
"Sedang",
"Tinggi"
)
)
# Cek hasil kolom turunan
head(
df_gabungan_clean[
,
c(
"id_trx",
"total_bayar",
"hari_transaksi",
"kategori_diskon"
)
],
10
)
## # A tibble: 10 × 4
## id_trx total_bayar hari_transaksi kategori_diskon
## <dbl> <dbl> <chr> <chr>
## 1 1 12000 Tuesday Rendah
## 2 2 25600 Tuesday Rendah
## 3 3 28900 Tuesday Rendah
## 4 4 60000 Tuesday Rendah
## 5 5 12600 Wednesday Rendah
## 6 6 13500 Wednesday Rendah
## 7 7 72250 Thursday Rendah
## 8 8 67500 Thursday Rendah
## 9 9 25200 Friday Rendah
## 10 10 75000 Friday Rendah
##### SOAL 4 VISUALISASI #####
# 1. Distribusi frekuensi kategori diskon
table(df_gabungan_clean$kategori_diskon)
##
## Rendah
## 345
# 2. Membuat periode transaksi per bulan
library(lubridate)
## Warning: package 'lubridate' was built under R version 4.4.3
##
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, union
df_gabungan_clean$periode_transaksi <- floor_date(
df_gabungan_clean$tgl_trx_valid,
unit = "month"
)
tren_bulanan <- aggregate(
total_bayar ~ periode_transaksi,
data = df_gabungan_clean,
FUN = sum,
na.rm = TRUE
)
tren_bulanan <- tren_bulanan[
order(tren_bulanan$periode_transaksi),
]
# Line Chart
ggplot(
tren_bulanan,
aes(x = periode_transaksi, y = total_bayar)
) +
geom_line(color = "steelblue", linewidth = 1) +
geom_point(color = "steelblue", size = 2) +
labs(
title = "Tren Total Bayar per Bulan",
x = "Bulan Transaksi",
y = "Total Bayar"
) +
theme_minimal()
# 3. Agregasi Total Bayar per Produk
transaksi_produk <- aggregate(
total_bayar ~ nama_produk,
data = df_gabungan_clean,
FUN = sum,
na.rm = TRUE
)
transaksi_produk <- transaksi_produk[
order(transaksi_produk$total_bayar, decreasing = TRUE),
]
top_10_produk <- head(transaksi_produk, 10)
ggplot(
top_10_produk,
aes(x = reorder(nama_produk, total_bayar), y = total_bayar)
) +
geom_col(fill = "#3498DB") +
coord_flip() +
labs(
title = "Top 10 Produk Berdasarkan Total Bayar",
x = "Nama Produk",
y = "Total Bayar"
) +
theme_minimal()
# 4. Visualisasi Total Bayar Berdasarkan Kategori Diskon
transaksi_diskon <- aggregate(
total_bayar ~ kategori_diskon,
data = df_gabungan_clean,
FUN = sum,
na.rm = TRUE
)
ggplot(
transaksi_diskon,
aes(
x = kategori_diskon,
y = total_bayar,
fill = kategori_diskon
)
) +
geom_col() +
labs(
title = "Total Bayar Berdasarkan Kategori Diskon",
x = "Kategori Diskon",
y = "Total Bayar"
) +
theme_minimal() +
theme(legend.position = "none")
# 5. Boxplot Total Bayar Berdasarkan Kategori Diskon
ggplot(
df_gabungan_clean,
aes(
x = kategori_diskon,
y = total_bayar
)
) +
geom_boxplot() +
labs(
title = "Distribusi Total Bayar Berdasarkan Kategori Diskon",
x = "Kategori Diskon",
y = "Total Bayar"
) +
theme_minimal()
