Conversion Excel vers tsv des données sur les faits constatées

Le programme R ci-dessous est utilisé pour convertir les données concernant les faits constatés, disponibles uniquement en format Excel sur data.gouv.fr, en données utilisables pour les traitements de type data science avec R ou Python. On va donc les mettre sous forme de fichier texte tsv, c’est-à-dire utilisant le caractère tabulation comme séparateur, en s’inspirant des principes tidy data.

Le script est fait pour être exécuté quotidiennement, dans un cron par exemple. C’est pourquoi, dès le début, on récupère la date de dernière modification via l’API de data.gouv.fr. On n’exécute le traitement que si elle a été modifiée, c’est-à-dire quand une nouvelle version des données a été déposée, en général tous les mois.

Pour interroger l’API, on utilise le package R httr qui facilite l’utilisation de l’API en fournissant les commandes usuelles comme GET, POST... C’est un peu l’équivalent de la commande curl utilisé dans l’article Téléverser une contribution communautaire sur data.gouv.fr.

Ensuite, on utilise la fonction de conversion Excel vers R issue du package readxl. Et on fait également assez largement appel aux fonctions du package data.table, de @MattDowle, tout à fait adapté à ce type de traitement et très rapide.

Le programme est commenté pour expliquer les différents traitements.

A noter qu’à la fin, on utilise le script cr_upload qui est présenté dans l’article Téléverser une contribution communautaire sur data.gouv.fr.

library(data.table)
library(httr)
library(readxl)

# URL API data.gouv.fr
api <- 'https://www.data.gouv.fr/api/1'
# id du dataset "crimes et délits"
dataset <- '5617ad4dc751df6211cdbb49'

# récupère via API la date de dernière modification du dataset
GET(paste0(api, '/datasets/', dataset, '/')) -> rep
modif <- strptime(content(rep)$resources[[2]]$last_modified, "%Y-%m-%dT%H:%M:%S")
cur_date = strftime(modif, "%Y%m%d")

# si pas de fichier flag ou si dates différentes,
# alors on exécute le traitement de conversion excel -> csv
flag_file_name <- 'last_date.txt'
to_exec <- T
if (file.exists(flag_file_name)) {
  scan(flag_file_name, nlines=1) -> last_date
  to_exec <- (last_date != cur_date)
}

# traitement de conversion excel -> csv
if (to_exec) {
  # on sauve la nouvelle date de mise à jour
  flag_file <- file(flag_file_name, 'w')
  cat(cur_date, file=flag_file, sep='\n')
  close(flag_file)

  # nom du fichier en sortie
  file_out <- paste0("faits", cur_date, ".tsv")
  # URL du fichier excel à télécharger
  url <- content(rep)$resources[[2]]$url
  # fichier temporaire de destination
  path <- tempfile(fileext = '.xlsx')
  # on télécharge le fichier excel depuis data.gouv.fr
  download.file(url, path, quiet=T)

  # le champ des onglets excel (il y a un onglet par département)
  # à convertir correspond aux onglets 3 à 109, les 2 premiers
  # étant des récaps France
  champ <- excel_sheets(path)[3:109]

  # le coeur du traitement : pour chaque onglet du champ,
  # on applique la fonction de conversion qui prend l'onglet en
  # paramètre
  # la fonction R lapply part d'une liste pour créer une autre liste :
  # ici, on part de la liste des onglets et on va aboutir à
  # une liste de data.table (équivalent plus performant au data.frame)
  # On supprime la colonne qui a pour nom "Libellé index"

  faits <- lapply(
    champ,
    function(X) {
      dt <- data.table(readxl::read_excel(path, sheet=X))
      dt[,"Libellé index":=NULL]
    }
  )

  # on récupère le nom de chaque onglet qui correspond au nom de département
  names(faits) <- champ
  # fonction magique et très performante du package data.table qui permet
  # de transformer la liste de data.table en un data.table unique en
  # "recollant" par ligne
  # on utilise les noms (provenant du nom de l'onglet) pour alimenter la
  # colonne "dep"
  rbindlist(faits, use.names=T, fill=T, idcol="dep") -> faits

  # comme la date figure en nom de colonne, on va faire
  # un peu de mise en forme ('tidy data') en transformant le nom des
  # colonnes en une colonne 'date'
  # Avant :
  # Index dep 2016_05 2016_04 2016_03...
  #     1  01     123     125     132
  #
  # Après
  # Index dep date     nb
  #     1  01 2016_05 123
  #     1  01 2016_04 125
  #     1  01 2016_03 132
  faits <- melt(faits, variable.name='date', value.name='nb', id.vars=c('Index', 'dep'))
  # on ne garde que les lignes à effectif non nul
  faits <- faits[nb>0]
  # on crée la variable mois depuis onglet : type date au 1er du mois
  # et on supprime onglet
  faits[, `:=`(
          mois=as.IDate(paste(substr(onglet,1,4), substr(onglet,6,7), "01", sep="-")),
          onglet = NULL)
  ]
  # on renomme la colonne Index en i4001
  setnames(faits, 'Index', 'i4001')
  # on remet les colonne dans l'ordre
  setcolorder(faits, c('mois', 'dep', 'i4001', 'nb'))
  # on écrit le fichier tsv (séparateur tab)
  write.table(faits, file=file_out, row.names=F, sep='\t')
  # on supprime le fichier temporaire
  unlink(path)
  # et on transfère le fichier tsv vers data.gouv.fr
  system(paste('./cr_upload', file_out, dataset))
}

Le résultat de l’exécution de ce script apparait dans les ressources communautaires sur la page des données concernant les faits constatés du site data.gouv.fr.