1. Inspeksi & Pencarian Data China di Seluruh File Excel

file_path <- "C:/Users/HP/Downloads/data asia pasifik.revalina.xlsx"
sheets <- excel_sheets(file_path)
cat("Daftar Sheet:", paste(sheets, collapse = ", "), "\n\n")
## Daftar Sheet: Dataset Panel, Kandidat 18x26, Missing Data, Statistik Deskriptif, Catatan
# Fungsi pembersih angka
bersihkan_angka <- function(x) {
  x_clean <- gsub("[^0-9.-]", "", gsub(",", ".", as.character(x)))
  as.numeric(x_clean)
}

data_china <- numeric(0)
df_preview <- NULL

for (s in sheets) {
  # Baca tanpa header agar semua baris (termasuk judul/header di tengah) terbaca sebagai sel
  raw_df <- read_excel(file_path, sheet = s, col_names = FALSE)
  if (is.null(df_preview)) df_preview <- raw_df
  
  # Cari koordinat (baris, kolom) yang mengandung kata "China" atau "Tiongkok"
  posisi <- which(apply(raw_df, 2, function(col) grepl("china|tiongkok", as.character(col), ignore.case = TRUE)), arr.ind = TRUE)
  
  if (nrow(posisi) > 0) {
    df_preview <- raw_df
    cat("Ditemukan 'China' pada Sheet:", s, "- Jumlah kecocokan:", nrow(posisi), "\n")
    
    if (nrow(posisi) == 1) {
      # KASUS 1: 'China' hanya muncul 1 kali (bisa sebagai Header Kolom atau Nama Baris di tabel horizontal)
      r <- posisi[1, 1]
      c <- posisi[1, 2]
      
      # Cek angka ke arah BAWAH (China adalah judul kolom, data ke bawah)
      ke_bawah <- na.omit(bersihkan_angka(raw_df[(r + 1):nrow(raw_df), c]))
      # Cek angka ke arah KANAN (China adalah label baris, tahun/data menyamping ke kanan)
      ke_kanan <- na.omit(bersihkan_angka(as.character(raw_df[r, (c + 1):ncol(raw_df)])))
      
      if (length(ke_bawah) >= length(ke_kanan) && length(ke_bawah) > 2) {
        data_china <- ke_bawah
      } else if (length(ke_kanan) > 2) {
        data_china <- ke_kanan
      }
      
    } else {
      # KASUS 2: 'China' muncul berkali-kali di satu kolom (Format Panel / Long)
      kol_negara <- posisi[1, 2]
      baris_china <- posisi[, 1]
      sub_df <- raw_df[baris_china, , drop = FALSE]
      
      # Ambil kolom numerik terakhir (biasanya nilai indikator, bukan kolom tahun)
      kandidat <- sapply(seq_along(sub_df), function(j) {
        if (j == kol_negara) return(FALSE)
        length(na.omit(bersihkan_angka(sub_df[[j]]))) >= 3
      })
      
      if (any(kandidat)) {
        kol_nilai <- tail(which(kandidat), 1)
        data_china <- na.omit(bersihkan_angka(sub_df[[kol_nilai]]))
      }
    }
    
    if (length(data_china) > 0) break
  }
}
## Ditemukan 'China' pada Sheet: Dataset Panel - Jumlah kecocokan: 25
# Tampilkan 10 baris pertama mentah untuk melihat struktur tabel Anda
print("Pratinjau 10 baris pertama file Excel:")
## [1] "Pratinjau 10 baris pertama file Excel:"
print(head(df_preview, 10))
## # A tibble: 10 × 8
##    ...1      ...2         ...3  ...4              ...5         ...6  ...7  ...8 
##    <chr>     <chr>        <chr> <chr>             <chr>        <chr> <chr> <chr>
##  1 Country   Country Code Year  Y_FDI             X1_GDP_Grow… X2_I… X3_T… X4_E…
##  2 Australia AUS          2000  3.57229745403377  3.916057488… 4.45… 40.8… 1.72…
##  3 Australia AUS          2001  2.81762721403267  2.031458802… 4.40… 44.1… 1.93…
##  4 Australia AUS          2002  3.69701188608667  3.949805357… 2.98… 41.3… 1.84…
##  5 Australia AUS          2003  1.9178050221833   3.091280099… 2.73… 40.1… 1.54…
##  6 Australia AUS          2004  6.96956991636737  4.218941475… 2.34… 36.9… 1.35…
##  7 Australia AUS          2005  -3.60113772652235 3.155481213… 2.69… 39.0… 1.30…
##  8 Australia AUS          2006  4.0750646338694   2.770926173… 3.55… 41.4… 1.32…
##  9 Australia AUS          2007  5.18803286061474  3.789471198… 2.32… 41.9… 1.19…
## 10 Australia AUS          2008  4.26757732505596  3.623985035… 4.35… 42.7… 1.19…
if (length(data_china) == 0) {
  stop("Data numerik China masih 0. Salin tabel 'Pratinjau 10 baris pertama file Excel' di atas agar struktur selnya bisa disesuaikan.")
}

cat("\nBerhasil mengekstrak", length(data_china), "observasi untuk China:\n")
## 
## Berhasil mengekstrak 25 observasi untuk China:
print(data_china)
##  [1] 8.278504 8.277068 8.276957 8.277037 8.276801 8.194317 7.973438 7.607532
##  [9] 6.948655 6.831416 6.770269 6.461461 6.312333 6.195758 6.143434 6.227489
## [17] 6.644478 6.758755 6.615957 6.908385 6.900767 6.448975 6.737158 7.083998
## [25] 7.197491

2. Visualisasi Deret Waktu (Time Series)

ts_china <- ts(data_china, frequency = 1)

autoplot(ts_china) +
  ggtitle("Data Deret Waktu (Time Series) - China") +
  xlab("Periode") +
  ylab("Nilai") +
  theme_minimal()

3. Uji Stasioneritas (Augmented Dickey-Fuller Test)

adf_asli <- adf.test(ts_china)
print(adf_asli)
## 
##  Augmented Dickey-Fuller Test
## 
## data:  ts_china
## Dickey-Fuller = -0.53529, Lag order = 2, p-value = 0.9721
## alternative hypothesis: stationary
ts_diff1 <- diff(ts_china, differences = 1)
adf_diff1 <- adf.test(ts_diff1)
print(adf_diff1)
## 
##  Augmented Dickey-Fuller Test
## 
## data:  ts_diff1
## Dickey-Fuller = -1.9305, Lag order = 2, p-value = 0.5988
## alternative hypothesis: stationary

4. Plot ACF & PACF

data_plot <- if (adf_asli$p.value <= 0.05) ts_china else ts_diff1

par(mfrow = c(1, 2))
acf(data_plot, main = "Plot ACF (Orde MA / q)")
pacf(data_plot, main = "Plot PACF (Orde AR / p)")

par(mfrow = c(1, 1))

5. Estimasi & Perbandingan Model (AR, MA, ARMA, ARIMA, ETS)

n <- length(ts_china)
train_size <- floor(0.8 * n)
train <- subset(ts_china, end = train_size)
test  <- subset(ts_china, start = train_size + 1)

model_ar1    <- Arima(train, order = c(1, 0, 0))
model_ar2    <- Arima(train, order = c(2, 0, 0))
model_ma1    <- Arima(train, order = c(0, 0, 1))
model_ma2    <- Arima(train, order = c(0, 0, 2))
model_arma   <- Arima(train, order = c(1, 0, 1))
model_arima  <- Arima(train, order = c(1, 1, 1))
model_auto   <- auto.arima(train, stepwise = FALSE, approximation = FALSE)
model_ets    <- ets(train)

perbandingan <- data.frame(
  Model = c("AR(1)", "AR(2)", "MA(1)", "MA(2)", "ARMA(1,1)", "ARIMA(1,1,1)", "Auto-ARIMA", "ETS"),
  AIC   = c(AIC(model_ar1), AIC(model_ar2), AIC(model_ma1), AIC(model_ma2),
            AIC(model_arma), AIC(model_arima), AIC(model_auto), AIC(model_ets)),
  BIC   = c(BIC(model_ar1), BIC(model_ar2), BIC(model_ma1), BIC(model_ma2),
            BIC(model_arma), BIC(model_arima), BIC(model_auto), BIC(model_ets))
)

perbandingan <- perbandingan[order(perbandingan$AIC), ]
print(perbandingan, row.names = FALSE)
##         Model       AIC        BIC
##    Auto-ARIMA -3.741433 -1.8525546
##  ARIMA(1,1,1) -2.011919  0.8213979
##     ARMA(1,1)  1.371648  5.3545772
##         AR(2)  2.395264  6.3781928
##         AR(1)  7.143649 10.1308462
##           ETS  7.446951 10.4341477
##         MA(2) 19.446693 23.4296221
##         MA(1) 31.471132 34.4583288

6. Evaluasi Akurasi Prediksi & Diagnostik Model Terbaik

summary(model_auto)
## Series: train 
## ARIMA(0,1,1) 
## 
## Coefficients:
##          ma1
##       0.8392
## s.e.  0.2722
## 
## sigma^2 = 0.03857:  log likelihood = 3.87
## AIC=-3.74   AICc=-2.99   BIC=-1.85
## 
## Training set error measures:
##                       ME      RMSE       MAE       MPE     MAPE      MASE
## Training set -0.03005954 0.1863206 0.1297936 -0.396622 1.876998 0.7740905
##                     ACF1
## Training set -0.08503237
checkresiduals(model_auto)

## 
##  Ljung-Box test
## 
## data:  Residuals from ARIMA(0,1,1)
## Q* = 1.5642, df = 3, p-value = 0.6675
## 
## Model df: 1.   Total lags used: 4
pred_test <- forecast(model_auto, h = length(test))
print(accuracy(pred_test, test))
##                       ME      RMSE       MAE       MPE     MAPE      MASE
## Training set -0.03005954 0.1863206 0.1297936 -0.396622 1.876998 0.7740905
## Test set     -0.28155249 0.3860551 0.2984567 -4.252549 4.487412 1.7799994
##                     ACF1 Theil's U
## Training set -0.08503237        NA
## Test set      0.24615486  1.270063
pred_future <- forecast(model_auto, h = length(test) + 5)
print(pred_future)
##    Point Forecast    Lo 80    Hi 80    Lo 95    Hi 95
## 21       7.155231 6.903501 7.406960 6.770244 7.540217
## 22       7.155231 6.628306 7.682155 6.349369 7.961092
## 23       7.155231 6.453852 7.856609 6.082565 8.227896
## 24       7.155231 6.314865 7.995596 5.870002 8.440459
## 25       7.155231 6.195806 8.114655 5.687917 8.622544
## 26       7.155231 6.089971 8.220490 5.526056 8.784405
## 27       7.155231 5.993740 8.316721 5.378884 8.931577
## 28       7.155231 5.904893 8.405568 5.243005 9.067456
## 29       7.155231 5.821954 8.488507 5.116161 9.194300
## 30       7.155231 5.743881 8.566580 4.996758 9.313703
autoplot(pred_future) +
  autolayer(test, series = "Data Aktual (Test)", color = "darkgreen") +
  ggtitle("Hasil Peramalan Time Series China") +
  xlab("Periode") +
  ylab("Nilai") +
  theme_minimal()