Clean Data from e-Stat

library(dplyr)
## 
## 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
library(ggplot2)
## Warning: package 'ggplot2' was built under R version 4.5.2
# 1. 讀取資料
# e-Stat 的檔案有時會有 BOM 編碼問題，而且前 8 行是說明，我們跳過它們 (skip = 8)
df_raw <- read.csv("C:/Users/user/Downloads/FEI_PREF_260806025740.csv", skip = 8, fileEncoding = "UTF-8-BOM", stringsAsFactors = FALSE)

# 2. 資料清理
# 透過欄位索引提取我們需要的資料：第 2 欄是年份，第 6、8、10 欄對應幼年、成年、老年人口
df_clean <- df_raw[, c(2, 6, 8, 10)]
colnames(df_clean) <- c("Year", "Child", "Adult", "Elderly")

# 移除年份中的「年度」文字並轉為數字
df_clean$Year <- as.numeric(gsub("年度", "", df_clean$Year))

# 移除數字中的千分位逗號 (,) 並轉為數值
df_clean$Child <- as.numeric(gsub(",", "", df_clean$Child))
df_clean$Adult <- as.numeric(gsub(",", "", df_clean$Adult))
df_clean$Elderly <- as.numeric(gsub(",", "", df_clean$Elderly))

# 只篩選出你們小組報告需要的近十年資料 (2014 ~ 2024)
df_clean <- df_clean[df_clean$Year >= 2014 & df_clean$Year <= 2024, ]

# 3. 計算人口比例 (%)
df_clean$Total <- df_clean$Child + df_clean$Adult + df_clean$Elderly
df_clean$Child_Pct <- (df_clean$Child / df_clean$Total) * 100
df_clean$Adult_Pct <- (df_clean$Adult / df_clean$Total) * 100
df_clean$Elderly_Pct <- (df_clean$Elderly / df_clean$Total) * 100

# 看看處理好的漂漂亮亮的數據！
print(df_clean)
##    Year  Child   Adult Elderly   Total Child_Pct Adult_Pct Elderly_Pct
## 1  2024 266000 1502000  752000 2520000  10.55556  59.60317    29.84127
## 2  2023 275000 1507000  753000 2535000  10.84813  59.44773    29.70414
## 3  2022 282000 1512000  755000 2549000  11.06316  59.31738    29.61946
## 4  2021 289000 1515000  758000 2562000  11.28025  59.13349    29.58626
## 5  2020 293465 1467216  734493 2495174  11.76130  58.80215    29.43654
## 6  2019 299000 1531000  753000 2583000  11.57569  59.27216    29.15215
## 7  2018 304000 1539000  749000 2592000  11.72840  59.37500    28.89660
## 8  2017 308000 1548000  743000 2599000  11.85071  59.56137    28.58792
## 9  2016 312000 1560000  733000 2605000  11.97697  59.88484    28.13820
## 10 2015 313866 1539540  703419 2556825  12.27562  60.21296    27.51143
## 11 2014 322000 1586000  701000 2609000  12.34189  60.78957    26.86853

Plots for before & after COVID

##   Year  Child   Adult Elderly   Total Child_Pct Adult_Pct Elderly_Pct
## 1 2024 266000 1502000  752000 2520000  10.55556  59.60317    29.84127
## 2 2019 299000 1531000  753000 2583000  11.57569  59.27216    29.15215

#next work: ## 1.combine Heo san data for 52 週定點報告數據 ## 2.比例推算年齡分層(waiting for 高森san) vs.real data (?)