Metadata are an essential part of a robust data science workflow ; they record the description of the tables and the meaning of each variable : its units, quality, allowed range, how we collect it, when it’s been recorded etc. Data without metadata are practically worthless. Here we will show how to transfer the metadata from PostgreSQL to R.
In PostgreSQL metadata can be stored in comments with the statements COMMENT ON TABLE ... IS '...' or COMMENT ON COLUMN ... IS '...'. So I hope your tables and columns have these nice comments and you can see them in psql or PgAdmin for example. But what about R ?
In R, metadata can be assigned as attributes of any object and mainly as « labels » for the columns. You may have seen labels when importing labelled data from SPSS for example.
We will use the {Hmisc} package which provides functions to manage labels. Another interesting package is {sjlabelled}.
library(Hmisc)
library(tidyverse)
library(RPostgreSQL)
library(glue)
library(httr)
library(rvest)
library(janitor)
cnx <- dbConnect(dbDriver("PostgreSQL"),
user = "***",
password = "***",
host = "***",
dbname = "***",
port = 5432
)
We’ll start by adding a table and its metadata in PostgreSQL. I chose to use the list of all the french communes from the code officiel géographique (COG). The data is provided as a zipped CSV file ; luckily a data dictionary appears on the page so we’ll scrape it.
# data from https://www.insee.fr/fr/information/3720946
# download
dl <- tempfile()
GET("https://www.insee.fr/fr/statistiques/fichier/3720946/commune2019-csv.zip",
write_disk(dl))
unzip(dl)
# import in PostgreSQL
read_csv("commune2019.csv",
col_types = cols(.default = col_character())) %>%
clean_names() %>%
dbWriteTable(cnx, c("ref_cog", "commune2019"), ., row.names = FALSE)
dbSendQuery(cnx, "ALTER TABLE ref_cog.commune2019 ADD PRIMARY KEY (com, typecom);")
# get the data dictionary from INSEE
page <- GET("https://www.insee.fr/fr/information/3720946") %>%
content()
table_info <- page %>%
html_node("#titre-bloc-3 + div > p") %>%
html_text() %>%
str_trim() %>%
str_replace_all("\\s+", " ")
columns_info <- page %>%
html_node("table") %>%
html_table() %>%
clean_names() %>%
mutate(designation_et_modalites_de_la_variable = str_trim(designation_et_modalites_de_la_variable),
designation_et_modalites_de_la_variable = str_replace_all(designation_et_modalites_de_la_variable, "\\s+", " "),
nom_de_la_variable = make_clean_names(nom_de_la_variable))
# add table metadata in PostgreSQL
dbSendQuery(cnx, glue_sql("COMMENT ON TABLE ref_cog.commune2019 IS {table_info}", .con = cnx))
# add columns metadata in PostgreSQL
walk2(columns_info$nom_de_la_variable,
columns_info$designation_et_modalites_de_la_variable,
~ dbSendQuery(cnx, glue_sql("COMMENT ON COLUMN ref_cog.commune2019.{`.x`} IS {.y}", .con = cnx)))
Now we have this nice table :
=> \dt+ ref_cog.*
List of relations
Schema | Name | Type | Owner | Size | Description
---------+-------------+-------+---------+---------+-------------------------------------------------------------------
ref_cog | commune2019 | table | xxxxxxx | 3936 kB | Liste des communes, arrondissements municipaux, communes déléguées et
communes associées au 1er janvier 2019, avec le code des niveaux
supérieurs (canton ou pseudo-canton, département, région)
(1 row)
=> \d+ ref_cog.commune2019
Table "ref_cog.commune2019"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+------+-----------+----------+--------------+----------------------------------------------------------------
typecom | text | | extended | | Type de commune
com | text | | extended | | Code commune
reg | text | | extended | | Code région
dep | text | | extended | | Code département
arr | text | | extended | | Code arrondissement
tncc | text | | extended | | Type de nom en clair
ncc | text | | extended | | Nom en clair (majuscules)
nccenr | text | | extended | | Nom en clair (typographie riche)
libelle | text | | extended | | Nom en clair (typographie riche) avec article
can | text | | extended | | Code canton. Pour les communes « multi-cantonales » code décliné
de 99 à 90 (pseudo-canton) ou de 89 à 80 (communes nouvelles)
comparent | text | | extended | | Code de la commune parente pour les arrondissements municipaux et
les communes associées ou déléguées.
Indexes:
"commune2019_pkey" PRIMARY KEY, btree (com, typecom)
Usually we query the data to R this way :
cog <- dbGetQuery(cnx, "SELECT * FROM ref_cog.commune2019 LIMIT 10")
We can create a function that will query the metadata of the table in information_schema.columns and add it to the data frame ; the function expects a data frame, the name of the schema.table from which we get the comments and a connection handler. It will return the data frame with labels and an attribute metadata with the description of the table.
#' Add attributes to a dataframe from metadata read in the PostgreSQL database
#'
#' @param df dataframe
#' @param schema_table "schema.table" from which to read the comments
#' @param cnx a database connexion from RPostgreSQL::dbConnect()
#'
#' @return a dataframe with attributes
#'
#' @examples \dontrun{add_metadata(iris, "public.iris", cnx)}
add_metadata <- function(df, schema_table, cnx) {
# get the table description and add it to a data frame attribute called "metadata"
attr(df, "metadata") <- dbGetQuery(cnx,
glue_sql("SELECT obj_description({schema_table}::regclass) AS table_description;",
.con = cnx)) %>%
pull(table_description)
# get colmumns comments
meta <- str_match(schema_table, "^(.*)\\.(.*)$") %>%
glue_sql(
"SELECT
column_name,
pg_catalog.col_description(
format('%s.%s', isc.table_schema, isc.table_name)::regclass::oid,
isc.ordinal_position) AS column_description
FROM information_schema.columns AS isc
WHERE isc.table_schema = {s[2]}
AND isc.table_name = {s[3]};",
s = .,
.con = cnx) %>%
dbGetQuery(cnx, .)
# match the columns comments to the variables
label(df, self = FALSE) <- colnames(df) %>%
enframe() %>%
left_join(meta, by = c("value" = "column_name")) %>%
pull(column_description)
df
}
Now we would do :
cog <- dbGetQuery(cnx,
"SELECT
*
FROM ref_cog.commune2019
LIMIT 10") %>%
add_metadata("ref_cog.commune2019", cnx)
The table description is available with :
attr(cog, "metadata") [1] "Liste des communes, arrondissements municipaux, communes déléguées et communes associées au 1er janvier 2019, avec le code des niveaux supérieurs (canton ou pseudo-canton, département, région)"
And you can see the metadata in the column headings of the RStudio viewer with View(cog) :
… the headings now show the metadata !
We can also use contents(cog) :
Data frame:cog 10 observations and 11 variables Maximum # NAs:10
- Labels Class Storage NAs
typecom Type de commune character character 0
com Code commune character character 0
reg Code région character character 0
dep Code département character character 0
arr Code arrondissement character character 0
tncc Type de nom en clair character character 0
ncc Nom en clair (majuscules) character character 0
nccenr Nom en clair (typographie riche) character character 0
libelle Nom en clair (typographie riche) avec article character character 0
can Code canton. Pour les communes « multi-cantonales » code décliné... character character 0
comparent Code de la commune parente pour les arrondissements municipaux... character character 10
Or :
cog %>% label() %>% enframe() # A tibble: 11 x 2 name value <chr> <chr> 1 typecom Type de commune 2 com Code commune 3 reg Code région 4 dep Code département 5 arr Code arrondissement 6 tncc Type de nom en clair 7 ncc Nom en clair (majuscules) 8 nccenr Nom en clair (typographie riche) 9 libelle Nom en clair (typographie riche) avec article 10 can Code canton. Pour les communes « multi-cantonales » code décliné de 99 à 90 (pseudo-canton) ou … 11 comparent Code de la commune parente pour les arrondissements municipaux et les communes associées ou dél…
Or lastly, for one column :
label(cog$tncc) [1] "Type de nom en clair"
We can also search for information in the variable names or in the labels with another function that can be helpful when we have a few hundred columns…
search_var <- function(df, keyword) {
df %>%
label() %>%
enframe() %>%
rename(variable = name,
metadata = value) %>%
filter_all(any_vars(str_detect(., regex(keyword, ignore_case = TRUE))))
}
search_var(cog, "canton")
# A tibble: 1 x 2
variable metadata
<chr> <chr>
1 can Code canton. Pour les communes « multi-cantonales » code décliné de 99 à 90 (pseudo-canton) ou de…
