International Trade Centre
Imported and Exproted Values and Quantities this month, this month last year, mean of this year, mean of last month percentage, difference in months, difference in years price is value divided by quantity
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(magrittr)
library(stringr)
library(writexl)
library(reshape2)
library(lubridate)##
## Attaching package: 'lubridate'## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, unionlibrary(lattice)
library(ggplot2)iterating variable
Product <- c("Copper", "Nickel", "Aluminium",
"Lead", "Zinc", "Tin", "Magnesium")
Trade <- c("Imports", "Exports")
Unit <- c("Values", "Quantities")set working direct
setwd("~/Desktop/R/CIER/International Trade Centre/Values, Quantities Using txt")
file <- list.files(path = "Data(txt)", pattern = ".txt")test
# paste0(i, j, k)
#i <- Product[1]
#j <- Trade[1]
#k <- Unit[1]
#str(df)looping
for (i in Product){
file.p <- file[str_detect(file, i)]
out.stat <- list()
out.data <- list()
out.p <- list()
for (j in Trade){
file.p.t <- file.p[str_detect(file.p, j)]
for (k in Unit){
file.p.t.u <- file.p.t[str_detect(file.p.t, k)]
df <- read.table(paste0("Data(txt)/", file.p.t.u), header = TRUE, sep = "\t")
df <- select(df, !contains("Unit") & !X) # drop X and Unit
df <- select(df, !length(df)) # drop newest month
df <- na_if(df, "")
df <- na_if(df, "No Quantity")
df <- na_if(df, "<NA>")
df[ ,2:length(df)] <- apply(df[ , 2:length(df)], 2,
function(x) as.numeric(as.character(x)))
# sapply(df, class)
# sub month (mm) which are different type in value and quantity
if (k == "Quantities"){
colnames(df)[-1] <- str_c(
colnames(df)[-1] %>% str_sub(start = 4, end = 5),
"/",
colnames(df)[-1] %>% str_sub(start = 8, end = 9))
# germany use tons
df[which(df[1] == "Germany"), -1] <- df[which(df[1] == "Germany"), -1] * 1000
}else{
colnames(df)[-1] <- str_c(
colnames(df)[-1] %>% str_sub(start = 21, end = 22),
"/",
colnames(df)[-1] %>% str_sub(start = 25, end = 26))
}
world <- sum(df[length(df)], na.rm = TRUE)
month <- colnames(df)[length(df)] %>%
str_sub(start = 4, end = 5)
# tb.p: percentage in this month
tb.p <- summarise(df,
percentage = round(df[[length(df)]] / world * 100, digits = 2))
# tb.month: month, month last year, difference in months, growth rate in months
tb.month <- select(df, contains(month))
tb.month <- mutate(tb.month, d_month = tb.month[[2]] - tb.month[[1]],
g_month = round(d_month / tb.month[[1]] * 100, 2))
# tb.mean: mean in this year, mean in last year, difference in means, growth rate in means
tb.mean <- df %>%
rowwise() %>%
summarise(
mean.21 = mean(c_across(contains("21"))),
mean.22 = mean(c_across(contains("22"))),
d_mean = mean.22 - mean.21,
g_mean = round(d_mean / mean.21 *100, 2))
# tb: combine tb.p, tb.month, tb.mean
tb <- cbind(df[1], tb.p, tb.month, tb.mean) %>%
arrange(desc(percentage))
out.p[[paste0(j, "_", k)]] <- tb
tb <- filter(tb, percentage > 2)
out.stat[[paste0(j, "_", k)]] <- tb
out.data[[paste0(j, "_", k)]] <- df
}
}
tb.price.import <- merge(out.stat[[1]], out.stat[[2]], by = "Importers") %>%
mutate(`21/06.p` = 1000 * `21/06.x`/`21/06.y`,
`22/06.p` = 1000 * `22/06.x`/`22/06.y`,
`mean.21.p` = 1000 * `mean.21.x` / `mean.21.y`,
`mean.22.p` = 1000 * `mean.22.x` / `mean.22.y`,) %>%
select("Importers", "21/06.p", "22/06.p", "mean.21.p", "mean.22.p") %>%
mutate(d_month = `22/06.p` - `21/06.p`,
g_month = d_month / `21/06.p`,
d_mean = mean.22.p - mean.21.p,
g_mean = d_mean / mean.21.p)
tb.price.export <- merge(out.stat[[3]], out.stat[[4]], by = "Exporters") %>%
mutate(`21/06.p` = 1000 * `21/06.x`/`21/06.y`,
`22/06.p` = 1000 * `22/06.x`/`22/06.y`,
`mean.21.p` = 1000 * `mean.21.x` / `mean.21.y`,
`mean.22.p` = 1000 * `mean.22.x` / `mean.22.y`,) %>%
select("Exporters", "21/06.p", "22/06.p", "mean.21.p", "mean.22.p") %>%
mutate(d_month = `22/06.p` - `21/06.p`,
g_month = d_month / `21/06.p`,
d_mean = mean.22.p - mean.21.p,
g_mean = d_mean / mean.21.p)
out.stat[["Imports_Price"]] <- tb.price.import
out.stat[["Exports_Price"]] <- tb.price.export
# output
write_xlsx(out.stat, paste0("Statistical Result/",i, "_stat.xlsx"))
write_xlsx(out.data, paste0("Data(xlsx)/", i, "_data.xlsx"))
}unit unit values(price) data
use copper imported unit price for example
file <- list.files(path = "Data(txt)", pattern = "Price")
df <- read.table(paste0("Data(txt)/", file[1]),
header = TRUE, sep = "\t")
str(df)## 'data.frame': 115 obs. of 45 variables:
## $ Importers : chr "United States of America" "Germany" "Korea, Republic of" "Taipei, Chinese" ...
## $ X2020.M12.Imported.unit.value : num 8.07 7604 7.33 8.65 7.62 ...
## $ X2020.M12.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M01.Imported.unit.value : num 8.63 7946 7.85 9 8.05 ...
## $ X2021.M01.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M02.Imported.unit.value : num 8.54 7991 8.17 9.55 8.29 ...
## $ X2021.M02.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M03.Imported.unit.value : num 9.01 8328 8.52 9.7 8.84 ...
## $ X2021.M03.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M04.Imported.unit.value : num 9.47 8835 8.98 10 9.07 ...
## $ X2021.M04.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M05.Imported.unit.value : num 10 9113 9.22 10 9.58 ...
## $ X2021.M05.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M06.Imported.unit.value : num 10 9349 9.67 11 10 ...
## $ X2021.M06.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M07.Imported.unit.value : num 10 9010 9.84 11 9.67 6.79 9.59 9.27 10 8.66 ...
## $ X2021.M07.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M08.Imported.unit.value : num 10 9075 9.84 11 10 ...
## $ X2021.M08.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M09.Imported.unit.value : num 10 9089 9.62 11 10 ...
## $ X2021.M09.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M10.Imported.unit.value : num 10 9253 9.45 11 9.98 ...
## $ X2021.M10.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M11.Imported.unit.value : num 10 9262 9.46 11 11 ...
## $ X2021.M11.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2021.M12.Imported.unit.value : num 10 9132 9.61 11 10 ...
## $ X2021.M12.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2022.M01.Imported.unit.value : num 10 9678 9.66 11 10 ...
## $ X2022.M01.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2022.M02.Imported.unit.value : num 11 9845 9.67 11 10 ...
## $ X2022.M02.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2022.M03.Imported.unit.value : num 11 9909 9.74 11 9.96 ...
## $ X2022.M03.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2022.M04.Imported.unit.value : num 11 10123 9.8 11 11 ...
## $ X2022.M04.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2022.M05.Imported.unit.value : num 11 9619 10 11 10 ...
## $ X2022.M05.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2022.M06.Imported.unit.value : num 11 9517 9.89 11 9.82 ...
## $ X2022.M06.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ X2022.M07.Imported.unit.value : num 10 8543 9.37 11 9.33 ...
## $ X2022.M07.Unit : chr "US Dollar/Kilograms" "US Dollar/Tons" "US Dollar/Kilograms" "US Dollar/Kilograms" ...
## $ Imported.value.in.2022.M07..US.Dollar.thousand: int 1465272 1212291 725725 517434 468786 409974 378559 362812 333303 299703 ...
## $ Imported.quantity.in.2022.M07 : int 145210878 141909 77469934 49166857 50262468 65942897 41488022 40570679 33457010 34361469 ...
## $ Quantity.Unit.in.2022.M07 : chr "Kilograms" "Tons" "Kilograms" "Kilograms" ...
## $ X : logi NA NA NA NA NA NA ...df <- select(df, Importers, contains("unit.value"))
df <- melt(df, id = "Importers")
top <- out.stat[[1]][["Importers"]]
df <- filter(df, Importers %in% top)
df <- mutate(df, year = as.numeric(str_sub(variable, 2, 5)),
month = as.numeric(str_sub(variable, 8, 9)))
df <- mutate(df, value = ifelse(Importers == "Germany",
value/1000, value))
df <- group_by(df, Importers) %>%
mutate(value.ts = ts(value,
start = c(2020, 12), frequency = 12),
date = paste0(as.character(year), "-",
as.character(month)),
date = ym(date))
str(df)## grouped_df [260 × 7] (S3: grouped_df/tbl_df/tbl/data.frame)
## $ Importers: chr [1:260] "United States of America" "Germany" "Korea, Republic of" "Taipei, Chinese" ...
## $ variable : Factor w/ 20 levels "X2020.M12.Imported.unit.value",..: 1 1 1 1 1 1 1 1 1 1 ...
## $ value : num [1:260] 8.07 7.6 7.33 8.65 7.68 ...
## $ year : num [1:260] 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 ...
## $ month : num [1:260] 12 12 12 12 12 12 12 12 12 12 ...
## $ value.ts : num [1:260] 8.07 7.6 7.33 8.65 7.68 ...
## $ date : Date[1:260], format: "2020-12-01" "2020-12-01" ...
## - attr(*, "groups")= tibble [13 × 2] (S3: tbl_df/tbl/data.frame)
## ..$ Importers: chr [1:13] "Bahrain" "Canada" "Czech Republic" "France" ...
## ..$ .rows : list<int> [1:13]
## .. ..$ : int [1:20] 11 24 37 50 63 76 89 102 115 128 ...
## .. ..$ : int [1:20] 8 21 34 47 60 73 86 99 112 125 ...
## .. ..$ : int [1:20] 9 22 35 48 61 74 87 100 113 126 ...
## .. ..$ : int [1:20] 5 18 31 44 57 70 83 96 109 122 ...
## .. ..$ : int [1:20] 2 15 28 41 54 67 80 93 106 119 ...
## .. ..$ : int [1:20] 13 26 39 52 65 78 91 104 117 130 ...
## .. ..$ : int [1:20] 7 20 33 46 59 72 85 98 111 124 ...
## .. ..$ : int [1:20] 3 16 29 42 55 68 81 94 107 120 ...
## .. ..$ : int [1:20] 12 25 38 51 64 77 90 103 116 129 ...
## .. ..$ : int [1:20] 10 23 36 49 62 75 88 101 114 127 ...
## .. ..$ : int [1:20] 4 17 30 43 56 69 82 95 108 121 ...
## .. ..$ : int [1:20] 6 19 32 45 58 71 84 97 110 123 ...
## .. ..$ : int [1:20] 1 14 27 40 53 66 79 92 105 118 ...
## .. ..@ ptype: int(0)
## ..- attr(*, ".drop")= logi TRUEPlot
plot(df$value.ts, type = "l", color = df$Importers)## Warning in plot.window(...): "color" is not a graphical parameter## Warning in plot.xy(xy, type, ...): "color" is not a graphical parameter## Warning in axis(side = side, at = at, labels = labels, ...): "color" is not a
## graphical parameter
## Warning in axis(side = side, at = at, labels = labels, ...): "color" is not a
## graphical parameter## Warning in box(...): "color" is not a graphical parameter## Warning in title(...): "color" is not a graphical parameter
ggplot(df, aes(x = date, y = value, color = Importers)) +
geom_line()## Warning: Removed 2 row(s) containing missing values (geom_path).