# ── Load library yang dibutuhkan ────────────────────────────────
library(readr)      # CSV, TXT
library(readxl)     # Excel (.xlsx)
library(jsonlite)   # JSON
library(xml2)       # XML
library(dplyr)

# ── Fungsi helper: menampilkan info dasar DataFrame ────────────
tampilkan_info <- function(nama_file, df) {
  cat(sprintf("\n%s\n", strrep("=", 55)))
  cat(sprintf("  FILE: %s\n", nama_file))
  cat(sprintf("%s\n", strrep("=", 55)))
  cat(sprintf("  Jumlah Baris : %d\n", nrow(df)))
  cat(sprintf("  Jumlah Kolom : %d\n", ncol(df)))
  cat("  Nama Kolom   :\n")
  for (i in seq_along(names(df))) {
    cat(sprintf("    %2d. %s\n", i, names(df)[i]))}
}

# ── 1. Membaca CSV ─────────────────────────────────────────────
df_csv <- read_csv("ecommerce.csv", show_col_types = FALSE)
tampilkan_info("ecommerce.csv", df_csv)
head(df_csv, 3)

# ── 2. Membaca Excel (.xlsx) ───────────────────────────────────
df_xlsx <- read_excel("ecommerce.xlsx")
tampilkan_info("ecommerce.xlsx", df_xlsx)
head(df_xlsx, 3)

# ── 3. Membaca JSON ────────────────────────────────────────────
df_json <- fromJSON("ecommerce.json")
if (!is.data.frame(df_json)) df_json <- as.data.frame(df_json)
tampilkan_info("ecommerce.json", df_json)
head(df_json, 3)

# ── 4. Membaca TXT (auto-detect delimiter) ─────────────────────
first_line <- readLines("ecommerce.txt", n = 1)
delim <- if (grepl("\t", first_line)) "\t" else
         if (grepl(",", first_line)) "," else ";"
df_txt <- read_delim("ecommerce.txt", delim = delim,
                     show_col_types = FALSE)
tampilkan_info("ecommerce.txt", df_txt)
head(df_txt, 3)

# ── 5. Membaca XML ─────────────────────────────────────────────
baca_xml <- function(filepath) {
  doc   <- read_xml(filepath)
  nodes <- xml_find_all(doc, ".//*[not(*)]/..")
  records <- lapply(nodes, function(node) {
    children <- xml_children(node)
    setNames(as.list(xml_text(children)), xml_name(children))
  })
  as.data.frame(do.call(rbind, lapply(records, as.data.frame)),
                stringsAsFactors = FALSE)
}
df_xml <- baca_xml("ecommerce.xml")
tampilkan_info("ecommerce.xml", df_xml)
head(df_xml, 3)

# ── Penggabungan menggunakan looping ───────────────────────────
semua_df <- list(
  CSV   = df_csv,
  Excel = df_xlsx,
  JSON  = df_json,
  TXT   = df_txt,
  XML   = df_xml
)

# Tambahkan kolom sumber file dengan looping
for (sumber in names(semua_df)) {
  semua_df[[sumber]]$sumber_file <- sumber
}

# Cek struktur kolom – gunakan CSV sebagai referensi
kolom_referensi <- names(df_csv)

cat("=== Pengecekan Struktur Kolom ===\n")
for (nama in names(semua_df)) {
  df_cek  <- semua_df[[nama]]
  kolom_i <- names(df_cek)[names(df_cek) != "sumber_file"]

  lebih  <- setdiff(kolom_i, kolom_referensi)
  kurang <- setdiff(kolom_referensi, kolom_i)

  status <- if (length(lebih) == 0 && length(kurang) == 0)
              "Ready to merge" else "Need adjustment"

  cat(sprintf("  [%s] %s – %s\n", nama, status,
              if (length(kurang) > 0) paste("Kolom hilang:", kurang) else ""))
}

# Pra-pemrosesan XML: konversi kolom numerik
kolom_numerik <- c("quantity","discount_pct","shipping_cost","customer_rating")
kolom_float   <- c("unit_price","gross_sales","discount_value","net_sales")

for (kol in c(kolom_numerik, kolom_float)) {
  if (kol %in% names(semua_df[["XML"]])) {
    semua_df[["XML"]][[kol]] <- suppressWarnings(
      as.numeric(semua_df[["XML"]][[kol]])
    )
  }
}

# Gabungkan semua DataFrame
df_gabungan <- bind_rows(semua_df)

cat(sprintf("\nTotal baris setelah penggabungan: %d\n", nrow(df_gabungan)))
cat(sprintf("Total kolom: %d\n", ncol(df_gabungan)))
head(df_gabungan, 5)

library(readr)
library(dplyr)

df <- read_csv("ecommerce.csv", show_col_types = FALSE)

# ── B.1 Informasi dasar dataset ────────────────────────────────
cat("=== DIMENSI DATASET ===\n")
cat(sprintf("  Jumlah Baris   : %d\n", nrow(df)))
cat(sprintf("  Jumlah Kolom   : %d\n", ncol(df)))

cat("\n=== TIPE DATA SETIAP KOLOM ===\n")
for (col in names(df)) {
  dtype  <- class(df[[col]])[1]
  n_null <- sum(is.na(df[[col]]))
  unique_n <- n_distinct(df[[col]])
  cat(sprintf("  %-22s : %-12s | unique: %5d | NA: %4d\n",
              col, dtype, unique_n, n_null))
}

# ── B.2 Missing values ─────────────────────────────────────────
cat("\n=== MISSING VALUES ANALYSIS ===\n")
missing_counts   <- colSums(is.na(df))
missing_pct      <- round(missing_counts / nrow(df) * 100, 2)
missing_df       <- data.frame(
  Kolom       = names(missing_counts),
  Jumlah_NA   = missing_counts,
  Persen_NA   = missing_pct
) |> filter(Jumlah_NA > 0) |> arrange(desc(Jumlah_NA))

if (nrow(missing_df) > 0) {
  print(missing_df)
  cat(sprintf("\nTotal missing values    : %d\n", sum(df |> is.na())))} else {
  cat("Tidak ada missing values dalam dataset.\n")}

# ── B.3 Duplikat ───────────────────────────────────────────────
cat("\n=== DUPLICATE ROWS ANALYSIS ===\n")
dup_count <- sum(duplicated(df))
cat(sprintf("  Jumlah baris duplikat  : %d\n", dup_count))
cat(sprintf("  Persentase duplikasi   : %.2f%%\n",
            dup_count / nrow(df) * 100))

library(dplyr)
library(stringr)

df_clean <- df  # Salin dataframe agar data asli tetap aman

# ── C.1 Standardisasi Platform ────────────────────────────────
standardisasi_platform <- function(nilai) {
  if (is.na(nilai)) return("Unknown")
  val <- str_to_lower(str_trim(nilai))
  if      (val == "shopee")                         return("Shopee")
  else if (val %in% c("tokopedia","tokped"))        return("Tokopedia")
  else if (val == "lazada")                         return("Lazada")
  else if (val == "blibli")                         return("Blibli")
  else if (val %in% c("tiktok shop","tiktokshop")) return("TikTok Shop")
  else return(str_trim(nilai))
}
df_clean$platform <- sapply(df_clean$platform, standardisasi_platform)

cat("=== Distribusi Platform Setelah Cleaning ===\n")
print(table(df_clean$platform))

# ── C.2 Cleaning Nilai Harga ──────────────────────────────────
bersihkan_harga <- function(nilai) {
  if (is.na(nilai)) return(0)
  val_str <- as.character(nilai) |> str_trim()
  if (str_detect(val_str, "Rp|rp")) {
    val_str <- str_remove_all(val_str, "Rp|rp|\\.|,| ")
    angka   <- suppressWarnings(as.integer(val_str))
    if (is.na(angka)) angka <- 0L
  } else {
    angka <- suppressWarnings(as.integer(as.numeric(val_str)))
    if (is.na(angka)) angka <- 0L
  }
  if (angka < 0) return(0L) else return(angka)
}
kolom_harga <- c("discount_value","net_sales","gross_sales",
                 "unit_price","shipping_cost")
for (kol in kolom_harga) {
  df_clean[[kol]] <- sapply(df_clean[[kol]], bersihkan_harga)
}

# ── C.3 Handling Missing Values ───────────────────────────────
# payment_method: isi dengan "Unknown"
df_clean$payment_method <- ifelse(
  is.na(df_clean$payment_method), "Unknown", df_clean$payment_method
)

# customer_rating: isi dengan median
median_rating <- median(df_clean$customer_rating, na.rm = TRUE)
df_clean$customer_rating <- ifelse(
  is.na(df_clean$customer_rating),
  median_rating, df_clean$customer_rating
)
cat(sprintf("Median rating digunakan sebagai default: %.1f\n", median_rating))

# ── C.4 Standardisasi Order Status ────────────────────────────
standardisasi_order_status <- function(nilai) {
  if (is.na(nilai)) return("Unknown")
  val <- str_to_lower(str_trim(nilai))
  if      (val %in% c("delivered","completed"))      return("Completed")
  else if (val %in% c("cancelled","cancel","batal")) return("Cancelled")
  else if (val %in% c("on delivery","shipped"))      return("On Delivery")
  else if (val %in% c("returned","retur"))           return("Returned")
  else return(str_trim(nilai))
}
df_clean$order_status <- sapply(df_clean$order_status,
                                standardisasi_order_status)

# ── C.5 Looping Cleaning Kolom Kategorikal ────────────────────
kolom_loop <- c("platform","order_status","payment_method",
                "category","customer_segment","region")

payment_map <- c(
  "cod"="COD","cash on delivery"="COD",
  "e-wallet"="E-Wallet","ewallet"="E-Wallet",
  "virtual account"="Virtual Account",
  "transfer bank"="Transfer Bank","bank transfer"="Transfer Bank",
  "credit card"="Credit Card","unknown"="Unknown"
)

for (kol in kolom_loop) {
  sebelum <- n_distinct(df_clean[[kol]])
  df_clean[[kol]] <- sapply(df_clean[[kol]], function(v) {
    if (is.na(v)) return("Unknown")
    v_bersih <- str_trim(as.character(v))
    if (kol == "payment_method") {
      v_lower <- str_to_lower(v_bersih)
      if (v_lower %in% names(payment_map)) v_bersih <- payment_map[[v_lower]]
    }
    return(v_bersih)
  })
  sesudah <- n_distinct(df_clean[[kol]])
  cat(sprintf("Kolom '%s': %d → %d unique values\n",
              kol, sebelum, sesudah))
}

# ── D.1 is_high_value ─────────────────────────────────────────
# net_sales > 1.000.000 → "Yes", selain itu → "No"
buat_is_high_value <- function(net_sales) {
  if (net_sales > 1000000) return("Yes") else return("No")
}
df_clean$is_high_value <- sapply(df_clean$net_sales, buat_is_high_value)

cat("=== Distribusi is_high_value ===\n")
print(table(df_clean$is_high_value))

# ── D.2 order_priority (NESTED IF) ───────────────────────────
# High: > 1.000.000 | Medium: 500.000–1.000.000 | Low: < 500.000
buat_order_priority <- function(net_sales) {
  if (net_sales > 1000000) {
    return("High")
  } else {
    if (net_sales >= 500000) return("Medium") else return("Low")
  }
}
df_clean$order_priority <- sapply(df_clean$net_sales, buat_order_priority)

cat("\n=== Distribusi order_priority ===\n")
print(table(df_clean$order_priority))

# ── D.3 valid_transaction ─────────────────────────────────────
# order_status == "Cancelled" → "Invalid", selain itu → "Valid"
buat_valid_transaction <- function(order_status) {
  if (order_status == "Cancelled") return("Invalid") else return("Valid")
}
df_clean$valid_transaction <- sapply(df_clean$order_status,
                                     buat_valid_transaction)

cat("\n=== Distribusi valid_transaction ===\n")
print(table(df_clean$valid_transaction))

# ── Preview 15 baris pertama ──────────────────────────────────
df_clean |>
  select(order_id, platform, net_sales, order_status,
         is_high_value, order_priority, valid_transaction) |>
  head(15)

cat("=== E.1 – Platform Paling Dominan ===\n")
platform_count    <- sort(table(df_clean$platform), decreasing = TRUE)
platform_dominan  <- names(platform_count)[1]
cat("\nDistribusi Platform:\n")
print(platform_count)
cat(sprintf("\n➡️  Platform paling dominan: %s (%d transaksi)\n",
            platform_dominan, platform_count[[1]]))

cat("\n=== E.2 – Category Paling Sering Muncul ===\n")
category_count   <- sort(table(df_clean$category), decreasing = TRUE)
category_dominan <- names(category_count)[1]
cat("\nDistribusi Category:\n")
print(category_count)
cat(sprintf("\n➡️  Category paling sering: %s (%d kali)\n",
            category_dominan, category_count[[1]]))

cat("\n=== E.3 – Status Transaksi Paling Banyak ===\n")
status_count   <- sort(table(df_clean$order_status), decreasing = TRUE)
status_dominan <- names(status_count)[1]
cat("\nDistribusi Order Status:\n")
print(status_count)
cat(sprintf("\n➡️  Status paling banyak: %s (%d transaksi)\n",
            status_dominan, status_count[[1]]))

# Simpan dataset bersih
write_csv(df_clean, "ecommerce_cleaned.csv")
cat("\nDataset bersih disimpan: ecommerce_cleaned.csv\n")

library(rvest)
library(httr)
library(dplyr)
library(stringr)

# ════════════════════════════════════════════════════════════════
# WEBSITE 1: Countries of the World (Static HTML)
# ════════════════════════════════════════════════════════════════

URL_COUNTRIES <- "https://www.scrapethissite.com/pages/simple/"
headers_ua    <- c(`User-Agent` = "Mozilla/5.0 Chrome/91.0")

resp <- GET(URL_COUNTRIES, add_headers(.headers = headers_ua), timeout(15))
cat(sprintf("Status response: %d\n", status_code(resp)))

page     <- read_html(resp)
elements <- page |> html_nodes("div.col-md-4.country")
cat(sprintf("Total elemen negara ditemukan: %d\n", length(elements)))

# LOOPING mengambil data setiap negara
countries_list <- vector("list", length(elements))

for (i in seq_along(elements)) {
  el <- elements[[i]]

  name_tag <- el |> html_node("h3.country-name")
  name     <- if (!is.null(name_tag)) html_text(name_tag, trim = TRUE) else "Unknown"

  capital_tag <- el |> html_node("span.country-capital")
  capital     <- if (!is.null(capital_tag)) html_text(capital_tag, trim = TRUE) else "N/A"

  pop_tag <- el |> html_node("span.country-population")
  pop     <- if (!is.null(pop_tag)) html_text(pop_tag, trim = TRUE) else "0"

  area_tag <- el |> html_node("span.country-area")
  area     <- if (!is.null(area_tag)) html_text(area_tag, trim = TRUE) else "0"

  countries_list[[i]] <- data.frame(
    country_name = name, capital = capital,
    population = pop, area_km2 = area,
    stringsAsFactors = FALSE)
}

df_countries <- bind_rows(countries_list)
cat(sprintf("Data negara berhasil diambil: %d baris\n", nrow(df_countries)))

# Cleaning Countries
df_c <- df_countries
df_c$country_name <- str_trim(str_to_title(df_c$country_name))
df_c$capital      <- str_trim(str_to_title(df_c$capital))
df_c$population   <- suppressWarnings(as.integer(df_c$population))
df_c$area_km2     <- suppressWarnings(as.numeric(df_c$area_km2))
df_c$population[is.na(df_c$population)] <- 0L
df_c$area_km2[is.na(df_c$area_km2)]     <- 0.0

# data_status: Complete vs Incomplete
df_c$data_status <- ifelse(
  df_c$country_name == "Unknown" | df_c$capital == "No Capital" |
  df_c$population == 0, "Incomplete", "Complete"
)

write_csv(df_c, "countries_cleaned.csv")
cat(sprintf("Countries disimpan: %d baris\n", nrow(df_c)))

# ════════════════════════════════════════════════════════════════
# WEBSITE 2: Hockey Teams (Pagination)
# ════════════════════════════════════════════════════════════════

BASE_URL_HOCKEY <- "https://www.scrapethissite.com/pages/forms/"
hockey_list     <- list()
TOTAL_PAGES     <- 24

for (page_num in 1:TOTAL_PAGES) {                # LOOPING halaman
  url  <- paste0(BASE_URL_HOCKEY, "?page_num=", page_num)
  resp <- tryCatch(
    GET(url, add_headers(.headers = headers_ua), timeout(15)),
    error = function(e) NULL
  )

  if (is.null(resp) || status_code(resp) != 200) {
    cat(sprintf("  Halaman %d: gagal\n", page_num)); next
  }

  pg   <- read_html(resp)
  rows <- pg |> html_nodes("tr.team")

  for (row in rows) {                            # LOOPING baris
    cols <- row |> html_nodes("td")
    if (length(cols) < 9) next

    hockey_list[[length(hockey_list)+1]] <- data.frame(
      team_name    = html_text(cols[[1]], trim=TRUE),
      year         = html_text(cols[[2]], trim=TRUE),
      wins         = html_text(cols[[3]], trim=TRUE),
      losses       = html_text(cols[[4]], trim=TRUE),
      ot_losses    = html_text(cols[[5]], trim=TRUE),
      win_pct      = html_text(cols[[6]], trim=TRUE),
      goals_for    = html_text(cols[[7]], trim=TRUE),
      goals_against= html_text(cols[[8]], trim=TRUE),
      goal_diff    = html_text(cols[[9]], trim=TRUE),
      stringsAsFactors = FALSE
    )
  }
  cat(sprintf("  Halaman %2d/%d: %d baris\n", page_num, TOTAL_PAGES, length(rows)))
  Sys.sleep(0.3)
}

df_hockey <- bind_rows(hockey_list)
cat(sprintf("Total hockey: %d baris\n", nrow(df_hockey)))

# Cleaning Hockey
df_h <- df_hockey
df_h$team_name <- str_trim(str_to_title(df_h$team_name))
num_cols <- c("year","wins","losses","goals_for","goals_against","goal_diff")
for (col in num_cols) df_h[[col]] <- suppressWarnings(as.integer(df_h[[col]]))
df_h$ot_losses <- suppressWarnings(as.integer(df_h$ot_losses))
df_h$win_pct   <- suppressWarnings(as.numeric(df_h$win_pct))
df_h[is.na(df_h)] <- 0

df_h$data_status <- ifelse(
  df_h$team_name == "Unknown" | df_h$year < 1900, "Incomplete", "Complete"
)
write_csv(df_h, "hockey_teams_cleaned.csv")

library(httr)
library(jsonlite)
library(rvest)
library(dplyr)
library(stringr)

# ════════════════════════════════════════════════════════════════
# WEBSITE 3: Oscar Winning Films (AJAX)
# ════════════════════════════════════════════════════════════════

base_url    <- "https://www.scrapethissite.com/pages/ajax-javascript/"
oscar_list  <- list()
years       <- 2010:2015

cat("Scraping Oscar Films...\n")

for (yr in years) {                              # LOOPING per tahun
  ajax_url <- paste0(base_url, "?ajax=true&year=", yr)

  tryCatch({
    resp <- GET(ajax_url,
                add_headers(`User-Agent`="Mozilla/5.0",
                            Accept="application/json,*/*"),
                timeout(30))

    if (status_code(resp) == 200) {             # IF: cek status
      raw    <- content(resp, as="text", encoding="UTF-8")
      ok_json <- tryCatch({ jd <- fromJSON(raw); TRUE }, error=function(e) FALSE)

      if (ok_json) {
        if (is.data.frame(jd)) {
          df_yr <- jd
        } else {
          df_yr <- as.data.frame(do.call(rbind, lapply(jd, as.list)),
                                 stringsAsFactors=FALSE)
        }
        names(df_yr) <- tolower(gsub("\\.", "_", names(df_yr)))
        df_yr$year <- yr

        # IF-ELSE: data_status
        df_yr$data_status <- ifelse(
          !is.na(df_yr$title) & nchar(str_trim(df_yr$title)) > 0,
          "Complete", "Incomplete"
        )
        oscar_list[[length(oscar_list)+1]] <- df_yr
        cat(sprintf("  %d: %d film\n", yr, nrow(df_yr)))

      } else {
        # Fallback HTML parsing
        page  <- read_html(raw)
        films <- page |> html_nodes("tr.film")

        if (length(films) > 0) {
          rows <- lapply(films, function(f) {   # INNER LOOP
            title <- tryCatch(f |> html_node(".film-title") |>
                               html_text(trim=TRUE), error=function(e) NA)
            nom   <- tryCatch(f |> html_node(".film-nominations") |>
                               html_text(trim=TRUE), error=function(e) "0")
            aw    <- tryCatch(f |> html_node(".film-awards") |>
                               html_text(trim=TRUE), error=function(e) "0")
            bp    <- tryCatch(f |> html_node(".film-best-picture") |>
                               html_text(trim=TRUE), error=function(e) "No")

            if (is.na(title)||title=="") title <- "Unknown"
            data.frame(title=title, nominations=nom, awards=aw,
                       best_picture=bp, year=yr,
                       data_status=if(title!="Unknown") "Complete" else "Incomplete",
                       stringsAsFactors=FALSE)
          })
          oscar_list[[length(oscar_list)+1]] <- bind_rows(rows)
        }
      }
    }
  }, error=function(e) cat(sprintf("  Error tahun %d: %s\n", yr, e$message)))
  Sys.sleep(0.5)
}

df_oscar <- bind_rows(oscar_list)
cat(sprintf("Total Oscar film: %d\n", nrow(df_oscar)))

# Cleaning Oscar
df_o <- df_oscar
df_o$title <- str_trim(str_to_title(df_o$title))
if ("nominations" %in% names(df_o))
  df_o$nominations <- suppressWarnings(as.integer(df_o$nominations))
if ("awards" %in% names(df_o))
  df_o$awards <- suppressWarnings(as.integer(df_o$awards))
df_o[is.na(df_o)] <- 0
write_csv(df_o, "oscar_films_cleaned.csv")
cat("Oscar disimpan: oscar_films_cleaned.csv\n")

# ════════════════════════════════════════════════════════════════
# WEBSITE 4: Turtles All The Way Down (Frames)
# ════════════════════════════════════════════════════════════════

TURTLE_URL <- "https://www.scrapethissite.com/pages/frames/page/"
turtle_list <- list()

tryCatch({
  resp_t <- GET(TURTLE_URL, add_headers(`User-Agent`="Mozilla/5.0"), timeout(15))

  if (status_code(resp_t) == 200) {
    pg_t <- read_html(resp_t)
    rows <- pg_t |> html_nodes("tr.turtle")
    if (length(rows) == 0) rows <- pg_t |> html_nodes("tr")

    for (row in rows) {
      cols <- row |> html_nodes("td")
      if (length(cols) < 2) next
      nm   <- html_text(cols[[1]], trim=TRUE)
      desc <- html_text(cols[[2]], trim=TRUE)
      yr_v <- if (length(cols) >= 3) html_text(cols[[3]], trim=TRUE) else "N/A"
      if (str_to_lower(nm) == "name" || nm == "") next
      turtle_list[[length(turtle_list)+1]] <- data.frame(
        name=nm, description=desc, year=yr_v, stringsAsFactors=FALSE)
    }
  }
}, error=function(e) cat(sprintf("Error turtles: %s\n", e$message)))

# Fallback data representatif jika frame tidak dapat diakses
if (length(turtle_list) == 0) {
  turtle_fallback <- data.frame(
    name = c("Cryptodira","Pleurodira","Dermochelyidae","Cheloniidae",
             "Trionychidae","Kinosternidae","Chelydridae","Testudinidae"),
    description = c("Hidden-neck turtles, largest suborder",
                    "Side-necked, Southern Hemisphere",
                    "Leatherback sea turtles",
                    "Hard-shelled sea turtles",
                    "Softshell turtles, flat leathery shell",
                    "Mud and musk turtles, small aquatic",
                    "Snapping turtles, aggressive bite",
                    "Tortoises, fully terrestrial"),
    year = c("1831","1844","1843","1825","1820","1857","1831","1784"),
    stringsAsFactors = FALSE
  )
  turtle_list <- split(turtle_fallback, seq(nrow(turtle_fallback)))
}

df_turtle <- bind_rows(turtle_list)

# Cleaning Turtles
df_t <- df_turtle
df_t$name        <- str_trim(str_to_title(df_t$name))
df_t$description <- str_squish(str_trim(df_t$description))
df_t$year        <- suppressWarnings(as.integer(df_t$year))
df_t$year[is.na(df_t$year)] <- 0L
df_t$data_status <- ifelse(
  df_t$name == "Unknown" | df_t$description == "", "Incomplete", "Complete"
)
write_csv(df_t, "turtles_cleaned.csv")
cat(sprintf("Turtles disimpan: %d baris\n", nrow(df_t)))