Revision | edf9cab504194a44a97285533a5a9f868ccee6b3 (tree) |
---|---|
Time | 2020-07-17 00:15:35 |
Author | Lorenzo Isella <lorenzo.isella@gmai...> |
Commiter | Lorenzo Isella |
A script generating automatically some statistical tables. I use kableextra and some interesting options.
@@ -0,0 +1,202 @@ | ||
1 | +rm(list=ls()) | |
2 | + | |
3 | + | |
4 | + | |
5 | +library(tidyverse) | |
6 | +library(janitor) | |
7 | +library(fst) | |
8 | +library(lubridate) | |
9 | +library(kableExtra) | |
10 | +library(tikzDevice) | |
11 | +library(xtable) | |
12 | + | |
13 | +source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R") | |
14 | + | |
15 | + | |
16 | +iso_eu27 <- c("EU27","AT", "BE", "BG", "CY", "CZ", | |
17 | + "DK", "EE", "FI", "FR", "DE", "GR", "HU", "IE", | |
18 | + "IT", "LV", "LT", "LU", "MT", "NL", "PL", | |
19 | + "PT", "RO", "SK", "SI", "ES", "SE", "HR") | |
20 | + | |
21 | +country_list <- c("EU27","Austria", "Belgium", "Bulgaria", "Cyprus", | |
22 | + "Czech Republic", | |
23 | + "Denmark", "Estonia", "Finland","France", | |
24 | + "Germany", "Greece", | |
25 | + "Hungary", "Ireland", "Italy", "Latvia", "Lituania", | |
26 | + "Luxembourg", "Malta", "Netherlands", | |
27 | + "Poland", "Portugal", "Romania", "Slovakia", | |
28 | + "Slovenia", "Spain", "Sweden", "Croatia") | |
29 | + | |
30 | + | |
31 | +df_eu27 <- tibble(iso2=iso_eu27, country= country_list ) | |
32 | + | |
33 | + | |
34 | +weeks_to_discard <- 3 | |
35 | + | |
36 | +df_ini <- read_fst("../taxud_hs2_hs0/all_aggregations_and_cleaning_efficient.fst") %>% as_tibble | |
37 | + | |
38 | + | |
39 | +max_week <- df_ini %>% | |
40 | + filter(year==max(year)) %$% | |
41 | + week %>% max %>% - weeks_to_discard | |
42 | + | |
43 | + | |
44 | + | |
45 | +final_date <- ymd( "2020-01-01" ) + weeks( max_week ) -1 | |
46 | + | |
47 | +dd <- day(final_date) | |
48 | + | |
49 | +yy <- year(final_date) | |
50 | + | |
51 | +mm <- month.abb[month(final_date)] | |
52 | + | |
53 | +date1 <- paste("1 Jan - ", dd," ",mm, " ", yy-1, sep="" ) | |
54 | + | |
55 | +date2 <- paste("1 Jan - ", dd," ",mm, " ", yy, sep="" ) | |
56 | + | |
57 | + | |
58 | +coverage <- paste(dd," ",mm, " ", yy, sep="" ) | |
59 | + | |
60 | + | |
61 | + | |
62 | +last_up <- today() | |
63 | + | |
64 | + | |
65 | +dd2 <- day(last_up) | |
66 | + | |
67 | +yy2 <- year(last_up) | |
68 | + | |
69 | +mm2 <- month.abb[month(last_up)] | |
70 | + | |
71 | + | |
72 | +updated_on <- paste(dd2,mm2,yy2) | |
73 | + | |
74 | + | |
75 | +df <- df_ini %>% | |
76 | + filter(week<=max_week) | |
77 | + | |
78 | +rm(df_ini) | |
79 | + | |
80 | + | |
81 | +df_out1 <- df %>% | |
82 | + filter(reporterid %in% df_eu27$iso2, partnerid=="Extra-EU28", | |
83 | + productid=="all_products" , tag %in% c("common", "raw") ) %>% | |
84 | + group_by(reporterid, year) %>% | |
85 | + summarise(total_imports=sum(iv)) %>% | |
86 | + ungroup %>% | |
87 | + arrange(reporterid) %>% | |
88 | + pivot_wider(names_from=year, values_from=total_imports) %>% | |
89 | + clean_names() %>% | |
90 | + mutate(share_19=x2019/max(x2019)*100, | |
91 | + share_20=x2020/max(x2020)*100) %>% | |
92 | + arrange(desc(x2020)) %>% | |
93 | + mutate(growth=(x2020-x2019)/x2019*100) %>% | |
94 | + mutate(share_19=round_preserve_sum(share_19,0), | |
95 | + share_20=round_preserve_sum(share_20,0), | |
96 | + growth=round(growth,0), | |
97 | + difference=x2020-x2019 | |
98 | + ) %>% | |
99 | + left_join(y=df_eu27, by=c("reporterid"="iso2")) %>% | |
100 | + select(country, x2019, share_19, x2020, share_20,difference, growth) | |
101 | + | |
102 | + | |
103 | + | |
104 | +df_out1_print <- df_out1 %>% | |
105 | + format_all(0) %>% | |
106 | + mutate(across(starts_with("share"), ~paste(.x,"%", sep="")), | |
107 | + growth=paste(growth, "%", sep="")) | |
108 | + | |
109 | + | |
110 | + | |
111 | + | |
112 | +t1_plot <- df_out1_print %>% | |
113 | + search_replace( "%", "\\\\%" ) | |
114 | + | |
115 | +nn <- linebreak(c("Imports\nby reporter", "mio euro", "\\% total" | |
116 | + , "mio euro", "\\% total" | |
117 | + , "mio euro", "\\%" | |
118 | + ),align="c") | |
119 | + | |
120 | +pos <- which(t1_plot$country=="EU27") | |
121 | + | |
122 | +pos <- c(pos, pos+1) | |
123 | + | |
124 | +mycaption <- paste("Goods imports registered in TAXUD - Surveillance\\\\", | |
125 | + date1, " and ", yy, sep="") | |
126 | + | |
127 | + | |
128 | + | |
129 | + | |
130 | +## xtable2kable <- function(x) { | |
131 | +## out <- capture.output(print(x, table.placement = NULL))[-(1:2)] | |
132 | +## out <- paste(out, collapse = "\n") | |
133 | +## structure(out, format = "latex", class = "knitr_kable") | |
134 | +## } | |
135 | + | |
136 | + | |
137 | + | |
138 | +ll <- t1_plot %>% | |
139 | + kable("latex", booktabs = T, escape = F,align=c("l", rep("r",6)), | |
140 | + col.names = nn, | |
141 | + caption = mycaption , | |
142 | + linesep = c( "\\addlinespace","", " ", "", "")) %>% | |
143 | + | |
144 | + add_header_above(c(" ",setNames(2,date1), setNames(2,date2), "Growth"=2 )) %>% | |
145 | + add_header_above(c("Product group: Total Goods. Partner=Extra-EU27*"=7)) %>% | |
146 | + | |
147 | + kable_styling(position ="center", | |
148 | + latex_options = c("striped", "hold_position", | |
149 | + "scale_down" | |
150 | + ), | |
151 | + full_width = F) %>% | |
152 | + footnote(general=c( "\\\\textit{Source}: DG TAXUD Surveillance database.", | |
153 | + "* UK excluded.", | |
154 | + paste("Last day included: ",coverage,".", sep="" ), | |
155 | + paste("Last updated on ", updated_on, ".", sep="") ),general_title = " ", escape = FALSE) %>% | |
156 | + row_spec(pos, bold = T) | |
157 | + | |
158 | + | |
159 | + | |
160 | + | |
161 | + | |
162 | +writeLines( | |
163 | + c( | |
164 | + "\\documentclass{article}", | |
165 | + "\\usepackage[a4paper]{geometry}", | |
166 | + "\\usepackage{helvet}", | |
167 | + "\\usepackage[flushleft]{threeparttable}" , | |
168 | + "\\usepackage{graphicx}", | |
169 | + "\\renewcommand{\\familydefault}{\\sfdefault}", | |
170 | + "\\usepackage[justification=centering]{caption}", | |
171 | + "\\usepackage[labelfont=bf,font=large]{caption}", | |
172 | + "\\usepackage{makecell}", | |
173 | + "\\usepackage{booktabs}", | |
174 | +"\\usepackage{colortbl, xcolor}", | |
175 | +"\\begin{document}", | |
176 | +"\\thispagestyle{empty}", | |
177 | +"\\captionsetup[table]{labelformat=empty, textfont=bf}", | |
178 | + ll, | |
179 | + "\\end{document}" | |
180 | + ), | |
181 | + "page1.tex" | |
182 | +) | |
183 | + | |
184 | + | |
185 | + | |
186 | + | |
187 | + | |
188 | +tools::texi2pdf("page1.tex", clean = TRUE) | |
189 | + | |
190 | + | |
191 | +############################################################################### | |
192 | +############################################################################### | |
193 | +############################################################################### | |
194 | +############################################################################### | |
195 | +############################################################################### | |
196 | + | |
197 | + | |
198 | + | |
199 | + | |
200 | + | |
201 | + | |
202 | +print("So far so good") |