r.iresmi.net - Metadata : from PostgreSQL comments to R labels

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 = Sys.getenv("PG_IRESMI_USER"),
                 password = Sys.getenv("PG_IRESMI_PASS"),
                 host = Sys.getenv("PG_IRESMI_HOST"),
                 dbname = Sys.getenv("PG_IRESMI_DB"),
                 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")

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):

                                                                                                                                                   -         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()

   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")

  variable metadata                                                                                          
  <chr>    <chr>                                                                                             
1 can      Code canton. Pour les communes « multi-cantonales » code décliné de 99 à 90 (pseudo-canton) ou de…

--- title: "Metadata : from PostgreSQL comments to R labels" description: "" author: "Michaël" date: "2019-04-18" date-modified: last-modified categories: - R - french - metadata - PostgreSQL draft: false freeze: true editor_options: chunk_output_type: console chunk_output_type: console execute: eval: false --- 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](https://www.postgresql.org/docs/current/sql-comment.html) `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](http://adv-r.had.co.nz/Data-structures.html#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}](https://rdrr.io/cran/Hmisc/man/label.html) package which provides functions to manage labels. Another interesting package is [{sjlabelled}](https://cran.r-project.org/web/packages/sjlabelled/vignettes/labelleddata.html). ```{r} library(Hmisc) library(tidyverse) library(RPostgreSQL) library(glue) library(httr) library(rvest) library(janitor) cnx <- dbConnect(dbDriver("PostgreSQL"), user = Sys.getenv("PG_IRESMI_USER"), password = Sys.getenv("PG_IRESMI_PASS"), host = Sys.getenv("PG_IRESMI_HOST"), dbname = Sys.getenv("PG_IRESMI_DB"), 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. ```{r} # 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: ```{r} 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. ```{r} #' 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: ```{r} cog <- dbGetQuery(cnx, "SELECT * FROM ref_cog.commune2019 LIMIT 10") %>% add_metadata("ref_cog.commune2019", cnx) ``` The table description is available with: ```{r} 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)`: ![](images/Capture-du-2019-04-28-20-38-48-1024x548.png) ... 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: ```{r} 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: ```{r} 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... ```{r} 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… ```