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()
