indicators

# install.packages("devtools")
devtools::install_github("dataobservatory-eu/indicator")

library(indicators)
#> Registered S3 method overwritten by 'quantmod':
#>   method            from
#>   as.zoo.data.frame zoo
require(DBI)
#> Loading required package: DBI
#> Warning: package 'DBI' was built under R version 4.0.3
require(dplyr)
#> Loading required package: dplyr
#> Warning: package 'dplyr' was built under R version 4.0.4
#> 
#> 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
# Make sure that it will work in a vignette, too:
data_raw_dir <- ifelse (dir.exists('data-raw'), 'data-raw', file.path('..', 'data-raw'))

create_eurostat_database (
  ids = c("ISOC_R_BLT12_I", "isoc_cicce_use", "teicp090", "tin00028"),
  db_path = file.path(data_raw_dir, "test_db.db")
)
#> Warning: Closing open result set, pending rows
#> Warning: Closing open result set, pending rows

#> Warning: Closing open result set, pending rows
#> Table isoc_r_blt12_i cached at C:\Users\DANIEL~1\AppData\Local\Temp\RtmpsrwOkq/eurostat/isoc_r_blt12_i_date_code_FF.rds
#> Table isoc_cicce_use cached at C:\Users\DANIEL~1\AppData\Local\Temp\RtmpsrwOkq/eurostat/isoc_cicce_use_date_code_FF.rds
#> Table teicp090 cached at C:\Users\DANIEL~1\AppData\Local\Temp\RtmpsrwOkq/eurostat/teicp090_date_code_FF.rds
#> Table tin00028 cached at C:\Users\DANIEL~1\AppData\Local\Temp\RtmpsrwOkq/eurostat/tin00028_date_code_FF.rds

disc_con <- dbConnect(RSQLite::SQLite(), file.path(data_raw_dir, "test_db.db") )

message  ("The following tables are present in the database: ")
#> The following tables are present in the database:
DBI::dbListTables(disc_con)
#> [1] "indicator" "labelling" "metadata"

db_metadata <- DBI::dbReadTable(disc_con, "metadata")

internet_use_indicators <- db_metadata %>% 
  select ( all_of(c("indicator_code", "description_indicator", "actual", "missing"))) %>%
  filter ( grepl("internet use", tolower(.data$description_indicator)) ) %>%
  relocate ( .data$indicator_code, .after = .data$missing )

test_indicators <- DBI::dbGetQuery(
  conn = disc_con, 
  "SELECT indicator_code, value, geo, time, estimate FROM indicator 
   WHERE indicator_code = ?", 
  params = list(internet_use_indicators$indicator_code)
)

head(test_indicators,12)
#>                         indicator_code value       geo  time estimate
#> 1  eurostat_tin00028_i_ilt12_ind_total    73        AT 14245   actual
#> 2  eurostat_tin00028_i_ilt12_ind_total    76        BE 14245   actual
#> 3  eurostat_tin00028_i_ilt12_ind_total    45        BG 14245   actual
#> 4  eurostat_tin00028_i_ilt12_ind_total    50        CY 14245   actual
#> 5  eurostat_tin00028_i_ilt12_ind_total    64        CZ 14245   actual
#> 6  eurostat_tin00028_i_ilt12_ind_total    79        DE 14245   actual
#> 7  eurostat_tin00028_i_ilt12_ind_total    87        DK 14245   actual
#> 8  eurostat_tin00028_i_ilt12_ind_total    68        EA 14245   actual
#> 9  eurostat_tin00028_i_ilt12_ind_total    72        EE 14245   actual
#> 10 eurostat_tin00028_i_ilt12_ind_total    45        EL 14245   actual
#> 11 eurostat_tin00028_i_ilt12_ind_total    62        ES 14245   actual
#> 12 eurostat_tin00028_i_ilt12_ind_total    68 EU27_2007 14245   actual

DBI::dbGetQuery(conn = disc_con, 
                "SELECT COUNT(*) FROM indicator 
                WHERE indicator_code = ?", 
                params = list(internet_use_indicators$indicator_code)
                )
#>   COUNT(*)
#> 1      445
#> 2      445
#> 3      445

DBI::dbDisconnect(disc_con)

vignette( package="indicators")
#> no vignettes found