Flash reports with flexdashboard for the characterization and quality levels of available datasets

Paula Cruz, António Portugal, Bruno Lima, João Poças, Sofia Rodrigues, Salvador Gil, Jorge Magalhães, Cecília Santos, Almiro Moreira, Paulo Saraiva

uRos2021 November 24

Background

logotipo INE

For several years now, flash reports have been developed for all business surveys, with a view to monitor the data collection process and analyze the main indicators associated to each statistical operation;
In a top-down approach, it is thus possible to identify the main trends right away and to direct the analysis of the data towards the variables identified as most relevant;
It is also possible to breakdown information by region, economic activity or any other distribution that is considered relevant.

Current challenges

logotipo INE

The move into a more regular and efficient production of essential indicators, with a reduced burden on respondents, is one of our biggest challenges;
Under the cooperation established with external organizations, a very significant volume of administrative data from various sources is now internally available;
With a view to facilitate data analysis and exploration, the production and use of flash reports has also been adopted in this area;
However, due to the volume and nature of the data in consideration, it was necessary to adopt new tools.

The adopted solution

logotipo INE

Due to its ease of use and interactivity, the open-source package flexdashboard, based on R Markdown, has been chosen;
Within its main features, it is worth highlighting the following ones:
- Wide variety of components
- Flexible and easy to implement layouts
- Extensive support for text annotations
- Possibility to create storyboard layouts
- Possibility to include scrollable html tables
- Possibility to export data in standard formats
- Default dashboards as standard HTML documents
- Ease of reuse and parameterization
- Possibility to create a new report quickly

Flash report (FR) example

logotipo INE

E-Invoice - Electronic invoice data received from the Tax Authority (huge volume of data); the first FR to be implemented, composed of five main tabs:

Overview

Reference month

Sectoral and regional characterization

Related data universes and statistical operations

Technical note

Contents’ overview

logotipo INE

Overview:
- Trend analysis by year and month, since 2018 (no. of registers, total and average taxable amount, national and foreign consumption);
- Extreme outliers listing.
Reference month:
- No. of registers and total taxable amount in the reference month, compared with previous and homologous months;
- IQR analysis of consumption by companies and national and foreign individuals, in terms of taxable amount;
- TOP100+ and TOP100-;
- Companies common to both the reference and previous month, in 2020 and 2021;
- 50 biggest companies reporting in the reference or previous month (but not in both).
Sectoral and regional characterization:
- Total taxable amount by NACE, sector, NUTS3 region, market and country, in the reference and previous month;
- Breakdown by monthly variation and monthly year-over-year change rate.
Related data universes and statistical operations:
- Various comparisons with the most relevant statistical operations in the business surveys’ area.
Technical note:
- Description of original and enriched data.

Used packages

logotipo INE

flexdashboard (dashboards with R)
ROracle (Oracle Database interface driver for R)
ggplot2 (data visualization)
DT (interface to the JavaScript library DataTables based on the htmlwidgets framework)
dplyr (grammar of data manipulation)
sqldf (running SQL statements on R data frames)
scales (internal scaling infrastructure to ggplot2 )
gridExtra (arrange multiple grid-based plots)
reshape2 (restructure and aggregate data)
tibble (simple data frames)

Some important pieces of code

logotipo INE

Connection to database

"drv <- dbDriver("Oracle")

host <- "xxxx"

port <- xxxx

sid <-"xxxx"

connect.string <- paste ("(DESCRIPTION=","(ADDRESS=(PROTOCOL=tcp)

(HOST=",host, ")

(PORT=", port, "))",

"(CONNECT_DATA=(SID=", sid, ")))", sep= "")

con <- dbConnect(drv, username = "xxxx", password = "xxxx", dbname=connect.string)"

SQL query

"```{sql, connection=con,output.var="serie_corrigida", include=FALSE}

SELECT TO_DATE(ANO_MES,'YYYY/MM') AS ANO_MES,

SUBSTR(ANO_MES,1,4) AS ANO,

SUBSTR(ANO_MES,6,2) AS MES,

ROUND(COUNT_GERAL/1000,0) AS COUNT_REG,

ROUND(SOMA_VALOR_TRIB/1000000,2) AS SOMA_VT,

ROUND(SOMA_VALOR_TRIB/COUNT_GERAL,2) AS VT_MEDIO

FROM FNTAT.TF_EFAT_SERIES_CORRIGIDAS_FR```"

Multi-line chart

"```{r, fig.width=8, fig.height=5}

serie_corrigida_vt_saz<-data.frame(serie_corrigida$ANO,

serie_corrigida$MES,

serie_corrigida$SOMA_VT)

names(serie_corrigida_vt_saz)[1]<-"Ano"

names(serie_corrigida_vt_saz)[2]<-"Mes"

names(serie_corrigida_vt_saz)[3]<-"Soma_VT"

serie_corrigida_vt_saz$Mes <- as.numeric(serie_corrigida_vt_saz$Mes)

serie_corrigida_vt_saz$Ano<- factor(serie_corrigida_vt_saz$Ano)


ggplot() + 

  scale_y_continuous(labels = number_format(big.mark=" ")) + 

  geom_point(data=serie_corrigida_vt_saz[serie_corrigida_vt_saz$Ano==2021,],

  aes(x = Mes, y = Soma_VT, color = Ano)) +

  geom_line(data = serie_corrigida_vt_saz, 

  aes(x = Mes, y = Soma_VT, color = Ano), lwd=1.5) + 

  scale_x_continuous(breaks = 1:12) + 

  ylab("Valor Tributável (M€)") + 

  xlab("Mês") +

  theme(text = element_text(family = 'Gill Sans', color = "#444444")

    ,panel.background = element_rect(fill = '#f2f2f2')

    ,panel.grid.minor = element_line(color = '#f2f2f2')

    ,panel.grid.major = element_line(color = '#e6e6e6')

    ,plot.title = element_text(size = 10)

    ,axis.title = element_text(size = 10, color = '#555555')

    ,axis.title.y = element_text(vjust = 1, size = 10)

    ,axis.title.x = element_text(hjust = 0, size = 10)

    ,axis.text.x=element_text(size=8, angle=0)
  ) +

expand_limits(y =  min(serie_corrigida_vt_saz$Soma_VT)-10000)```"

Dynamic tables

  "```{r}

  vt_cae2_2020<-merge(vt_cae2_2020,tvm_cae2_2020,by="CAE2",all.vt_cae2_2020 = TRUE)

  datatable(vt_cae2_2020,

      extensions = 'Buttons',

      rownames = FALSE, 

      options = list(  dom = 'Blfrtip',

      buttons = c('copy', 'csv', 'pdf', 'print'),

      lengthMenu = list(c(10,25,50,-1),c(10,25,50,"All")),

      language = list(url = 'https://(...)/Portuguese.json'),

      bPaginate = FALSE, 

      autoWidth = TRUE, 

      columnDefs = list(list(className ='dt-right', targets = 2:5),

      list(className = 'dt-right', targets =0)))) %>%

  formatCurrency(c('SETEMBRO_2021','OUTUBRO_2021'), 

      currency="€", mark = " ",

      dec.mark =",", digits = 2)  %>%

  formatPercentage(c('TX_VAR_MENSAL','TX_VAR_HOM'),  

      mark = " ", dec.mark =",",

      digits = 2) %>%

  formatStyle(c("SETEMBRO_2021","OUTUBRO_2021"),"white-space"="nowrap")```"

Grouped barplot

"```{r, fig.width=8, fig.height=5}

stats_vt_corr<-data.frame(stats_vt_corr_2019$MES,stats_vt_corr_2019$SOMA_VT,stats_vt_corr_2020$SOMA_VT)

names(stats_vt_corr)[1]<-"MES"

names(stats_vt_corr)[2]<-"N-1"

names(stats_vt_corr)[3]<-"N"

stats_vt_corr$MES <- factor(stats_vt_corr$MES, 

levels=c("08", "09", "10"))

stats_vt_corr <- melt(stats_vt_corr, id.vars='MES')

ggplot(stats_vt_corr, 

aes(x=MES, y=value, fill=variable)) + 

ylab("Valor tributável (M€) ")  +

labs(fill = "Período") + xlab("Mês") +

  geom_text(aes(label = format(value, big.mark = " ", scientific = FALSE)),

  position=position_dodge(width=0.9), vjust=-0.25, hjust="middle") +

  scale_y_continuous(

  labels = number_format(big.mark=" ")) +

  theme(legend.position="bottom",text = element_text(size=15)) +

  scale_fill_manual(values=c("olivedrab3","dodgerblue1")) +

  geom_bar(stat='identity', position='dodge')```"

Boxplot

"```{r, fig.width=8, fig.height=5, include=FALSE}

perc_emit_2020 <- data.frame(percentis_emit_2020)

perc_emit_2020$MES <- factor(perc_emit_2020$MES, levels=c("08", "09", "10"))

plot_emit<-ggplot(perc_emit_2020, aes(MES)) +

geom_boxplot(

  aes(ymin = LOWER_QUARTILE-1.5*(UPPERQUARTILE-LOWER_QUARTILE), 

  lower = LOWER_QUARTILE, 

  middle = MEDIAN_VAL, 

  upper = UPPERQUARTILE, 

  ymax = UPPERQUARTILE+1.5*(UPPERQUARTILE-LOWER_QUARTILE)), 

  stat = "identity") + xlab("Ano/Mês") + ylab("Valor tributável (€)") + 

  theme(text = element_text(size = 11),plot.title = element_text(hjust =

  0.5,size=11))+scale_y_continuous(

  labels = number_format(big.mark=" ")) + ggtitle("Companies") +

  scale_x_discrete(labels=c("2021/08", "2021/09", "2021/10"))```"

Value boxes

"```{r}
count_mes_ref <- sqldf("SELECT COUNT_REG FROM serie_corrigida where

ano='2021' and mes='10'")

count_mes_ref <-format(count_mes_ref, digits=2, decimal.mark=",",

big.mark=" ")

valueBox(value = count_mes_ref, caption = "October 2021: No. of

registers (thousands)",color = "orange")```"

Gauge

"```{r}

rateivne1<-comuns_ivne1$TX_COB

gauge(rateivne1, min = 0, max = 100, symbol = '%', gaugeSectors(

success = c(85, 100), warning = c(50, 84), danger = c(0, 49)))```"

Arrange graphs side by side
```
gridExtra::grid.arrange(plot3,plot4)
```

Feedback from users

logotipo INE

The obtained feedback from (internal) users was globally positive, resulting in the extension of this type of report to other administrative datasets;
Some suggestions for improvement were made, such as the possibility to download contents, automated refreshing, intranet publication, links to used datasets, among others;

Limitations and future improvements

logotipo INE

Currently, reports implemented in flexdashboard are restricted to internal users and a relatively low, although important, number of data sets;
However, we intend to extend its use and scope, by creating a common template that can be easily adapted to different datasets;
An upgrade to shinydashboard (R) is also being considered, with a view to better meet users’ needs.

Thank you!

logotipo INE

Questions?

paula.cruz@ine.pt

Flash reports with flexdashboard for the characterization and quality levels of available datasets

Background

Current challenges

The adopted solution

How it works

Flash report (FR) example

Contents’ overview

Used packages

Some important pieces of code

Feedback from users

Limitations and future improvements

Thank you!