111年 大宗物資製圖
CTMoney
Library
library(readr)
library(dplyr)## Warning: package 'dplyr' was built under R version 4.0.5##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(zoo)##
## Attaching package: 'zoo'## The following objects are masked from 'package:base':
##
## as.Date, as.Date.numericlibrary(ggplot2)
library(stringr)
library(scales)##
## Attaching package: 'scales'## The following object is masked from 'package:readr':
##
## col_factorlibrary(lubridate)##
## Attaching package: 'lubridate'## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, unionlibrary(reshape2)
library(writexl)
library(stringr)
library(readxl)
library(car)## Loading required package: carData##
## Attaching package: 'car'## The following object is masked from 'package:dplyr':
##
## recodelibrary(tidyr)## Warning: package 'tidyr' was built under R version 4.0.5##
## Attaching package: 'tidyr'## The following object is masked from 'package:reshape2':
##
## smithsImport Data
setwd("~/Desktop/R/CIER/CTMoney")
df <- read_csv("111大宗物資製圖.csv")## Warning: Missing column names filled in: 'X1' [1], 'X2' [2]##
## ── Column specification ────────────────────────────────────────────────────────
## cols(
## .default = col_double(),
## X1 = col_character(),
## X2 = col_date(format = "")
## )
## ℹ Use `spec()` for the full column specifications.head(df)## # A tibble: 6 × 72
## X1 X2 `原油\r\n西德州\r\n庫欣\…` `原油\r\n布蘭…` `原油\r\n杜拜\r…`
## <chr> <date> <dbl> <dbl> <dbl>
## 1 上限價 2021-01-01 NA NA NA
## 2 上限價 2021-01-02 NA NA NA
## 3 上限價 2021-01-03 NA NA NA
## 4 上限價 2021-01-04 47.5 50.3 52.4
## 5 上限價 2021-01-05 49.8 52.8 50.4
## 6 上限價 2021-01-06 50.4 54.5 53.4
## # … with 67 more variables:
## # `煤\r\n澳洲Newcastle\r\n週\r\n指數價格\r\n(美元/公噸)` <dbl>,
## # `煤\r\n倫敦ICE\r\n鹿特丹近月期貨\r\n收盤價\r\n(美元/公噸)` <dbl>,
## # `TRC-CRB\r\n全球商品指數\r\n收盤指數\r\n(其他)` <dbl>,
## # `航運指數\r\n波羅的海乾散裝\r\n綜合指數\r\nBDI\r\n(其他)` <dbl>,
## # `黃豆\r\n芝加哥CBOT\r\n近月期貨\r\n收盤價\r\n(美分/英斗)` <dbl>,
## # `黃豆\r\n總豆\r\n自提\r\n中盤價\r\n(元/公斤)` <dbl>, …Data transformation
df <- select(df, -1)
colnames(df) <- c("Date", str_replace_all(colnames(df)[-1], "\r\n", " "))
df <- mutate(df, Date = as.Date(Date))
df <- filter(df, Date >= as.Date("2021-01-01"))
df <- df %>%
mutate(year = format(Date, "%Y")) %>%
mutate(across(.col = where(is.numeric),
.fn = function(x) zoo(x) %>% na.locf(na.rm = FALSE) %>% as.numeric,
.names = "{.col}"))Line Plot in 2021 and 2022
df <- mutate(df, Date.r = as.Date(ifelse(year == "2021", Date, Date-365)))
# ggplot(df, aes(x = Date.r)) +
# geom_smooth(aes(y = 苯.台灣.CFR..美元.公噸. ,
# color = year))Unit Transformation
unit <- str_extract(colnames(df), "\\((?!(進口原料|國際咖啡組織|下午|HOMO|票期價|其他))(.+)\\)")
unit[is.na(unit)] <- ""
# df <- mutate(df, across(contains("元/公斤"), ~.x*1000/30, .names = "{.col}a"))
df <- mutate(df,
across(.col = contains("元/公斤"), ~.x * 1000 / 30),
across(.col = contains("美分/英斗") & !(contains("玉米")), ~.x * 36.7437 / 100),
across(.col = contains("美分/英斗") & contains("玉米"), ~.x * 39.3683 / 100),
across(.col = contains("元/袋") & contains("水泥"), ~.x * 20),
across(.col = contains("(元/公噸") & !(contains("水泥")), ~.x / 30),
across(.col = contains("美分/磅"), ~.x * 2204.62/100))
# str(df)
colnames(df) <- str_replace(colnames(df), str_c("\\(", unit, "\\)"), "")
unit[str_detect(unit, "元/公斤")] <- "(美元/公噸)"
unit[str_detect(unit, "美分/英斗")] <- "(美元/公噸)"
unit[str_detect(unit, "元/袋") & str_detect(unit, "水泥")] <- "(元/公噸)"
unit[str_detect(unit, "元/公噸") & !(str_detect(unit, "水泥"))] <- "(美元/公噸)"
unit[str_detect(unit, "美分/磅")] <- "(美元/公噸)"
# col_unit <- data.frame(product = colnames(df), unit = unit)Growth Rate in 2022
df_d <- filter(df, (Date<as.Date("2021-11-30") & year == 2021) | year == 2022)
stat <- df_d %>%
group_by(year) %>%
summarise_all(mean, na.rm = TRUE) %>%
melt() %>%
dcast(variable ~ year)## Using year as id variables## Warning: attributes are not identical across measure variables; they will be
## droppedcolnames(stat) <- c("Product", "year2021", "year2022")
stat <- mutate(stat,
gr2022 = round((year2022 - year2021) / year2021, 4) * 100)
stat <- mutate(stat, Product = str_c(Product, unit[-length(unit)]))
stat <- filter(stat, !(Product %in% c("Date", "year", "Date.r")))
head(stat)## Product year2021 year2022 gr2022
## 1 原油 西德州 庫欣 現貨價 (美元/桶) 67.86806 96.76042 42.57
## 2 原油 布蘭特 北海 現貨價 (美元/桶) 70.94312 105.80551 49.14
## 3 原油 杜拜 現貨價 (美元/桶) 68.99297 98.21663 42.36
## 4 煤 澳洲Newcastle 週 指數價格 (美元/公噸) 136.84835 337.21687 146.42
## 5 煤 倫敦ICE 鹿特丹近月期貨 收盤價 (美元/公噸) 117.92545 292.59955 148.12
## 6 TRC-CRB 全球商品指數 收盤指數 (其他) 207.43912 285.82766 37.79yearly plot each product
There are 69 products.
month <- c("一月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月","十一月", "十二月")
for(i in 2:70){
ggplot(df, aes(x = Date.r)) +
geom_line(mapping = aes(y = df[[i]] , color = year),
size = 1) +
labs(x = "月份", y = str_c("價格 ", unit[i]), color = "年份",
title = paste0(colnames(df)[i], "價格走勢圖"), caption = "資料來源：情報贏家，2022年11月") +
theme_linedraw() +
theme(legend.position = "bottom",
legend.background = element_rect(size = 0.25, linetype = "solid",
colour = "black"),
text = element_text(face = "bold"),
plot.title = element_text(hjust = 0.5),
plot.caption = element_text(hjust = 0, face = "plain")) +
scale_x_date(breaks = "1 month", labels = date_format("%B"),
limits = as.Date(c("2021-01-01", "2021-12-31"))) +
coord_cartesian(xlim = as.Date(c("2021-01-15", "2021-12-15")))
ggsave(paste0("Plots/year/", str_replace_all(colnames(df)[[i]], "/|%|>", " "), ".png"),
width = 8, height = 4, dpi = 600)
}## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 7 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 7 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 7 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 3 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 7 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.## Warning: Removed 7 row(s) containing missing values (geom_path).## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
## Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.Example
i <- 5
ggplot(df, aes(x = Date.r)) +
geom_line(mapping = aes(y = df[[i]] , color = year),
size = 1) +
labs(x = "月份", y = str_c("價格 ", unit[i]), color = "年份",
title = paste0(colnames(df)[i], "價格走勢圖"), caption = "資料來源：情報贏家，2022年11月") +
theme_linedraw() +
theme(legend.position = "bottom",
legend.background = element_rect(size = 0.25, linetype = "solid",
colour = "black"),
text = element_text(face = "bold",
family = "PingFangTC-Regular"),
plot.title = element_text(hjust = 0.5),
plot.caption = element_text(hjust = 0, face = "plain")) +
scale_x_date(breaks = "1 month", labels = date_format("%b"),
limits = as.Date(c("2021-01-01", "2021-12-31"))) +
coord_cartesian(xlim = as.Date(c("2021-01-15", "2021-12-15")))## Warning: Use of `df[[i]]` is discouraged. Use `.data[[i]]` instead.
quarter plot each group
colnames(df) <- str_c(colnames(df), unit)
g <- c("原油", "煤", "商品指數", "航運指數", "黃豆", "小麥", "麵粉", "玉米", "咖啡豆",
"銅 ", "紅銅片", "鋁板片", "鋁 ", "鎳", "鋅 ", "鉛", "錫", "鎂",
"鋼鐵價格", "鐵礦石", "廢鋼", "小鋼胚", "軋鋼捲", "板捲", "熱浸鍍鋅鋼捲", "鋼筋", "H型鋼",
"乙烯 ", "丙烯 ", "苯 ", "聚氯乙烯", "低密度聚乙烯", "高密度聚乙烯", "聚丙烯", "苯乙烯",
"紙漿", "廢紙", "水泥", "河礫", "混凝土", "奶粉")
df_g <- melt(df, id = "Date") %>%
filter(Date >= as.Date("2022-10-01"),
!(variable %in% c("year", "Date.r")))## Warning: attributes are not identical across measure variables; they will be
## dropped#head(df_g, 20)
#tail(df_g)
df_g <- mutate(df_g, group = "NA", value = as.numeric(value))for (i in g){
df_g <- mutate(df_g, group = ifelse(str_detect(variable, i), i, group))
ggplot(df_g[df_g$group == i,],
aes(x = Date, y = value, color = variable)) +
geom_line(size = 1.5) +
labs(x = "月份", y = str_c("價格 "), col = NULL,
title = paste0(i, " 近一季價格走勢"), caption = "資料來源：情報贏家，2022年11月") +
theme_linedraw() +
theme(legend.position = "bottom",
legend.background = element_rect(size = 0.25, linetype = "solid",
colour = "black"),
text = element_text(face = "bold"),
plot.title = element_text(hjust = 0.5),
plot.caption = element_text(hjust = 0, face = "plain")) +
scale_x_date(limits = as.Date(c("2022-10-01", "2022-11-20")), labels = date_format("%m/%d"))
ggsave(paste0("Plots/quarter/", i, " 近一季價格.png"),
width = 8, height = 4, dpi = 600)
}## Warning: Removed 24 row(s) containing missing values (geom_path).## Warning: Removed 16 row(s) containing missing values (geom_path).## Warning: Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).## Warning: Removed 16 row(s) containing missing values (geom_path).## Warning: Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).## Warning: Removed 16 row(s) containing missing values (geom_path).## Warning: Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).## Warning: Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).## Warning: Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).## Warning: Removed 16 row(s) containing missing values (geom_path).## Warning: Removed 8 row(s) containing missing values (geom_path).## Warning: Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).## Warning: Removed 8 row(s) containing missing values (geom_path).## Warning: Removed 24 row(s) containing missing values (geom_path).
## Removed 24 row(s) containing missing values (geom_path).## Warning: Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).## Warning: Removed 8 row(s) containing missing values (geom_path).## Warning: Removed 16 row(s) containing missing values (geom_path).
## Removed 16 row(s) containing missing values (geom_path).## Warning: Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).
## Removed 8 row(s) containing missing values (geom_path).Example
i <- g[1]
ggplot(df_g[df_g$group == i,],
aes(x = Date, y = value, color = variable)) +
geom_line(size = 1.5) +
labs(x = "月份", y = str_c("價格 "), col = NULL,
title = paste0(i, " 近一季價格走勢"), caption = "資料來源：情報贏家，2022年11月") +
theme_linedraw() +
theme(legend.position = "bottom",
legend.background = element_rect(size = 0.25, linetype = "solid",
colour = "black"),
text = element_text(face = "bold", family = "PingFangTC-Regular"),
plot.title = element_text(hjust = 0.5),
plot.caption = element_text(hjust = 0, face = "plain")) +
scale_x_date(limits = as.Date(c("2022-10-01", "2022-11-20")), labels = date_format("%m/%d"))## Warning: Removed 24 row(s) containing missing values (geom_path).
## Other Products out of CTMoney
hhng <- read_xls("RNGWHHDd.xls", sheet = "Data 1", skip = 2)
#jlng <- read_xlsx("Japan Liquefied Natural Gas Import Price.xlsx") %>%
# mutate(Date = as.character(as.Date(Date))) %>%
# mutate(Date = str_replace(Date, "\\d\\d$", "01"))
jlng <- read_xlsx("Japan Liquefied Natural Gas Import Price.xlsx") %>%
mutate(Date = as.Date(floor_date(Date, "month")))
milk <- read_xlsx("Whole Milk Powder Average Prices.xlsx")
fao <- read_xls("Food_price_indices_data_nov491.xls",
sheet = "Indices_Monthly", skip = 2)## New names:
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...15`
## • `` -> `...16`
## • `` -> `...17`
## • `` -> `...18`
## • `` -> `...19`
## • `` -> `...20`
## • `` -> `...21`
## • `` -> `...22`
## • `` -> `...23`
## • `` -> `...24`
## • `` -> `...25`
## • `` -> `...26`
## • `` -> `...27`
## • `` -> `...28`
## • `` -> `...29`
## • `` -> `...30`
## • `` -> `...31`
## • `` -> `...32`
## • `` -> `...33`
## • `` -> `...34`
## • `` -> `...35`
## • `` -> `...36`
## • `` -> `...37`
## • `` -> `...38`
## • `` -> `...39`
## • `` -> `...40`
## • `` -> `...41`
## • `` -> `...42`
## • `` -> `...43`
## • `` -> `...44`
## • `` -> `...45`
## • `` -> `...46`
## • `` -> `...47`
## • `` -> `...48`
## • `` -> `...49`
## • `` -> `...50`
## • `` -> `...51`
## • `` -> `...52`
## • `` -> `...53`
## • `` -> `...54`
## • `` -> `...55`
## • `` -> `...56`
## • `` -> `...57`
## • `` -> `...58`
## • `` -> `...59`
## • `` -> `...60`
## • `` -> `...61`
## • `` -> `...62`
## • `` -> `...63`
## • `` -> `...64`
## • `` -> `...65`
## • `` -> `...66`igc <- read_xlsx("igc__goi.xlsx", sheet = "GOI & Indices", skip = 4)## New names:
## • `` -> `...3`colnames(fao)[2] <- "Value"
colnames(igc)[1:2] <- c("Date", "Value")p <- c("Henry Hub天然氣 現貨價", "日本天然氣進口報關價格", "國際奶粉價格WMP(拍賣價格)",
"聯合國農糧組織(FAO)食品價格指數", "國際穀物協會(IGC)穀物及油籽價格指數")
df_p <- list(hhng, jlng, milk, fao, igc)
df_p## [[1]]
## # A tibble: 6,510 × 2
## Date Value
## <dttm> <dbl>
## 1 1997-01-07 00:00:00 3.82
## 2 1997-01-08 00:00:00 3.8
## 3 1997-01-09 00:00:00 3.61
## 4 1997-01-10 00:00:00 3.92
## 5 1997-01-13 00:00:00 4
## 6 1997-01-14 00:00:00 4.01
## 7 1997-01-15 00:00:00 4.34
## 8 1997-01-16 00:00:00 4.71
## 9 1997-01-17 00:00:00 3.91
## 10 1997-01-20 00:00:00 3.26
## # … with 6,500 more rows
##
## [[2]]
## # A tibble: 50 × 2
## Date Value
## <date> <dbl>
## 1 2022-10-01 23.7
## 2 2022-09-01 23.7
## 3 2022-08-01 21.2
## 4 2022-07-01 18.9
## 5 2022-06-01 15.5
## 6 2022-05-01 16.7
## 7 2022-04-01 16.3
## 8 2022-03-01 15.1
## 9 2022-02-01 17
## 10 2022-01-01 14.7
## # … with 40 more rows
##
## [[3]]
## # A tibble: 45 × 2
## Date Value
## <dttm> <dbl>
## 1 2022-11-15 00:00:00 3397
## 2 2022-11-02 00:00:00 3297
## 3 2022-10-18 00:00:00 3421
## 4 2022-10-04 00:00:00 3573
## 5 2022-09-20 00:00:00 3733
## 6 2022-09-06 00:00:00 3610
## 7 2022-08-16 00:00:00 3417
## 8 2022-08-02 00:00:00 3544
## 9 2022-07-19 00:00:00 3557
## 10 2022-07-05 00:00:00 3961
## # … with 35 more rows
##
## [[4]]
## # A tibble: 395 × 66
## Date Value Meat Dairy Cereals Oils Sugar ...8 ...9 ...10
## <dttm> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <lgl> <lgl> <lgl>
## 1 NA NA NA NA NA NA NA NA NA NA
## 2 1990-01-01 00:00:00 64.1 73.4 53.5 64.1 44.6 87.9 NA NA NA
## 3 1990-02-01 00:00:00 64.5 76.0 52.2 62.2 44.5 90.7 NA NA NA
## 4 1990-03-01 00:00:00 63.8 77.8 41.4 61.3 45.7 95.1 NA NA NA
## 5 1990-04-01 00:00:00 65.8 80.4 48.4 62.8 44.0 94.3 NA NA NA
## 6 1990-05-01 00:00:00 64.4 81.0 39.2 62.0 45.5 90.4 NA NA NA
## 7 1990-06-01 00:00:00 63.7 83.1 39.2 60.7 43.8 80.3 NA NA NA
## 8 1990-07-01 00:00:00 62.5 83.4 39.2 57.9 43.7 74.2 NA NA NA
## 9 1990-08-01 00:00:00 61.5 83.7 36.8 55.7 45.4 67.6 NA NA NA
## 10 1990-09-01 00:00:00 61.0 84.5 38.1 52.5 44.8 68.5 NA NA NA
## # … with 385 more rows, and 56 more variables: ...11 <lgl>, ...12 <lgl>,
## # ...13 <lgl>, ...14 <lgl>, ...15 <lgl>, ...16 <lgl>, ...17 <lgl>,
## # ...18 <lgl>, ...19 <lgl>, ...20 <lgl>, ...21 <lgl>, ...22 <lgl>,
## # ...23 <lgl>, ...24 <lgl>, ...25 <lgl>, ...26 <lgl>, ...27 <lgl>,
## # ...28 <lgl>, ...29 <lgl>, ...30 <lgl>, ...31 <lgl>, ...32 <lgl>,
## # ...33 <lgl>, ...34 <lgl>, ...35 <lgl>, ...36 <lgl>, ...37 <dbl>,
## # ...38 <lgl>, ...39 <lgl>, ...40 <lgl>, ...41 <lgl>, ...42 <lgl>, …
##
## [[5]]
## # A tibble: 5,964 × 8
## Date Value ...3 Wheat Maize Soyabeans Rice Barley
## <dttm> <dbl> <lgl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 2000-01-03 00:00:00 96.7 NA 97.0 96.9 94.9 99.2 98.5
## 2 2000-01-04 00:00:00 97.0 NA 97.1 97.1 95.6 99.5 98.6
## 3 2000-01-05 00:00:00 97.1 NA 97.2 97.3 95.6 99.7 98.6
## 4 2000-01-06 00:00:00 97.4 NA 97.4 97.4 96.3 99.8 98.7
## 5 2000-01-07 00:00:00 97.4 NA 98.4 94.3 96.7 100. 98.8
## 6 2000-01-10 00:00:00 98.0 NA 98.9 95.9 97.2 100. 99.0
## 7 2000-01-11 00:00:00 98.6 NA 99.3 97.4 97.6 100. 99.3
## 8 2000-01-12 00:00:00 99.5 NA 100. 101. 98.1 100. 99.6
## 9 2000-01-13 00:00:00 100. NA 101. 102. 98.6 100. 99.8
## 10 2000-01-14 00:00:00 99.7 NA 101. 98.0 99.0 100. 100.
## # … with 5,954 more rowsunit_p <- c("價格(美元/百萬英熱單位)", "價格(美元/百萬英熱單位)", "價格(美元/噸)",
"指數 (2014-2016=100)", "指數 (Jan 2000=100)")
source_p <- c("U.S. Energy Information Administration", "YCHARTS",
"Global Dairy Trade", "FAO", "IGC")Loop for Plot
for (i in 1:5){
df_p[[i]] <- mutate(df_p[[i]], Date = as.Date(Date)) %>%
filter(Date >= as.Date("2021-01-01")) %>%
mutate(year = as.character(year(Date)),
Date.r = as.Date(ifelse(year == 2022, Date - 365, Date)))
# yearly plot
df_p[[i]] %>%
ggplot(aes(x = Date.r)) +
geom_line(mapping = aes(y = Value , color = year),
size = 1) +
labs(x = "月份", y = unit_p[i], color = "年份",
title = str_c(p[i], " 價格走勢圖"), caption = str_c("資料來源：", source_p[i],"，2022年11月")) +
theme_linedraw() +
theme(legend.position = "bottom",
legend.background = element_rect(size = 0.25, linetype = "solid",
colour = "black"),
text = element_text(face = "bold"),
plot.title = element_text(hjust = 0.5),
plot.caption = element_text(hjust = 0, face = "plain")) +
scale_x_date(breaks = date_breaks("months"),
labels = date_format("%B"),
limits = as.Date(c("2021-01-01", "2021-12-31"))) +
coord_cartesian(xlim = as.Date(c("2021-01-15", "2021-12-15")))
ggsave(paste0("Plots/year/", p[i], ".png"),
width = 8, height = 4, dpi = 600)
# quarterly plot
df_p[[i]] %>%
ggplot(aes(x = Date)) +
geom_line(mapping = aes(y = Value , color = p[i]),
size = 1.5) +
labs(x = "月份", y = unit_p[[i]], color = "",
title = str_c(p[i], " 近一季價格走勢圖"), caption = str_c("資料來源：", source_p[i],"，2022年11月")) +
theme_linedraw() +
theme(legend.position = "bottom",
legend.background = element_rect(size = 0.25, linetype = "solid",
colour = "black"),
text = element_text(face = "bold"),
plot.title = element_text(hjust = 0.5),
plot.caption = element_text(hjust = 0, face = "plain")) +
scale_x_date(limits = as.Date(c("2022-09-30", "2022-11-20")),
labels = date_format("%m/%d"))
ggsave(paste0("Plots/quarter/", p[i], ".png"),
width = 8, height = 4, dpi = 600)
}## Warning: Removed 440 row(s) containing missing values (geom_path).## Warning: Removed 21 row(s) containing missing values (geom_path).## geom_path: Each group consists of only one observation. Do you need to adjust
## the group aesthetic?## Warning: Removed 41 row(s) containing missing values (geom_path).
## Removed 21 row(s) containing missing values (geom_path).## geom_path: Each group consists of only one observation. Do you need to adjust
## the group aesthetic?## Warning: Removed 459 row(s) containing missing values (geom_path).Combine Statistics
stat_p <- lapply(df_p, function(x)
mutate(x, year = as.character(year(Date))) %>%
group_by(year) %>%
filter((Date < as.Date("2021-11-30") & year == 2021) |
(Date < as.Date("2022-11-20") & year == 2022)) %>%
summarise(Value = mean(Value)) %>%
melt() %>%
dcast(variable ~ year) %>%
mutate(gr2022 = (`2022` - `2021`) / `2021` * 100)) %>%
do.call(rbind, .) %>%
mutate(variable = p)## Using year as id variables
## Using year as id variables
## Using year as id variables
## Using year as id variables
## Using year as id variableshead(stat_p)## variable 2021 2022 gr2022
## 1 Henry Hub天然氣 現貨價 3.904737 6.535631 67.376981
## 2 日本天然氣進口報關價格 10.347000 18.281000 76.679231
## 3 國際奶粉價格WMP(拍賣價格) 3838.476190 3932.272727 2.443588
## 4 聯合國農糧組織(FAO)食品價格指數 125.004204 145.772804 16.614321
## 5 國際穀物協會(IGC)穀物及油籽價格指數 274.908349 323.861788 17.807185colnames(stat_p) <- c("Product", "year2021", "year2022", "gr2022")
rbind(stat, stat_p) %>%
write_xlsx("Growth Rate.xlsx")