Un petit coup de polish Nettoyage de fichiers Excel avec R

Rencontres R 2024

Thomas Vroylandt, Kantiles

Données et slides

https://github.com/tvroylandt/rr_2024

Qui suis-je ?

Thomas Vroylandt

Associé (Kantiles) thomas@kantiles.com

  • Rapports statistiques
  • Chaîne de production
  • Politiques sociales et de l’emploi

Dernière fois aux RR 2021

Cas réels, dans le cadre de production d’indicateurs pour un service statistique ministériel

Notre sujet du jour

Que demande le peuple ?

Source : Allison Horst

De l’autre côté du miroir

De l’autre côté du miroir

Une grande créativité dans les mises en forme !

Boîte à outils

  • readxl : import de fichiers rectangulaires simples

  • tidyxl : permet de transformer l’étape d’import en une étape de nettoyage de données

  • unpivotr : complément de tidyxl pour le nettoyage

  • A mentionner : openxlsx2 (sert aussi en écriture)

library(tidyverse)
library(readxl)
library(tidyxl)
library(unpivotr)

Cas pratiques

Insee - Taux d’activité

Population active et taux d’activité au sens du recensement selon le sexe et l’âge en 2020 : comparaisons départementales 

path_insee_activite <- "data/TCRD_015.xlsx"

Insee - Taux d’activité

Intitulés des colonnes fusionnées

Insee - Taux d’activité

Zone des données décalée

Insee - Taux d’activité - readxl

En spécifiant les colonnes à la main

read_xlsx(
  path_insee_activite,
  sheet = "DEP",
  skip = 5,
  col_names = c(
    "code_dep",
    "lib_dep",
    "pop_active_tot",
    "pop_activite_h",
    "pop_active_f",
    "tx_act_15_64",
    "tx_act_25_54",
    "tx_act_h_15_64",
    "tx_act_h_25_54",
    "tx_act_f_15_64",
    "tx_act_f_25_54"
  )
)

Insee - Taux d’activité - readxl

Insee - Taux d’activité - tidyxl

xlsx_cells donne un tibble de la localisation et du contenu des cellules

xlsx_cells(path_insee_activite)

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3)

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var")

up-left car une cellule fusionnée ne remplit que le haut à gauche

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var")

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep")

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep") |>
  behead("left", "lib_dep")

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep") |>
  behead("left", "lib_dep") |>
  select(typ_var, mod_var, code_dep, lib_dep, numeric)

Drees - ISD C24

Répartition des naissances par âge de la mère 

path_c24 <- "data/C24-ISD_Part_accouchements_selon_age_mere.xlsx"

Drees - ISD C24

Début des données décalées

Drees - ISD C24

Intitulés des colonnes fusionnées

Drees - ISD C24

Un onglet par année (mais tous pareil) + un onglet de documentation

Drees - ISD C24

Des données à la fin que l’on ne souhaite pas importer

Drees - ISD C24 - readxl

On prend les colonnes telles quelles

read_xlsx(path_c24,
          sheet = "2022",
          skip = 5,
          .name_repair = "unique_quiet") |>
  rename(code_dep = ...1,
         lib_dep = ...2) |>
  pivot_longer(-c(code_dep, lib_dep),
               names_to = "age_mere",
               values_to = "perc_naiss")

Drees - ISD C24 - readxl

Idem sur les onglets dans un map

excel_sheets(path_c24) |>
  set_names() |>
  _[-12] |>
  map(read_xlsx,
      path = path_c24,
      skip = 5,
      .name_repair = "unique_quiet") |>
  list_rbind(names_to = "annee") |>
  rename(code_dep = ...1,
         lib_dep = ...2) |>
  pivot_longer(-c(annee, code_dep, lib_dep),
               names_to = "age_mere",
               values_to = "perc_naiss") |>
  mutate(annee = as.numeric(annee)) |>
  filter(!is.na(code_dep) &
           !is.na(perc_naiss) & 
           !code_dep %in% c("F", "M"))

Drees - ISD C24 - readxl

Drees - ISD C24 - tidyxl

df_cells_c24 <- xlsx_cells(path_c24)

df_cells_c24

Drees - ISD C24 - tidyxl + dplyr/tidyr

df_cells_c24 |>
  filter(sheet != "Documentation") |>
  filter(row >= 4 & !is_blank & col != 2) |>
  select(sheet, row, col, character, numeric) |>
  mutate(header_row = case_when(row == 6 ~ character),
         code_dep = case_when(col == 1 ~ coalesce(as.character(numeric), character))) |>
  group_by(col) |>
  fill(header_row, .direction = "down") |>
  group_by(row) |>
  fill(code_dep, .direction = "down") |>
  ungroup() |>
  filter(!is.na(numeric) &
           !is.na(code_dep) &
           !is.na(header_row) & !code_dep %in% c("F", "M")) |>
  mutate(sheet = as.numeric(sheet),
         header_row = str_trim(header_row)) |>
  select(annee = sheet,
         code_dep,
         age_mere = header_row,
         perc_naiss = numeric)

Drees - ISD C24 - tidyxl + unpivotr

penser à grouper par onglet

df_cells_c24 |>
  filter(row >= 5) |>
  group_by(sheet) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep") |>
  behead("left", "lib_dep") |>
  ungroup() |>
  mutate(mod_var = str_trim(mod_var)) |>
  select(sheet, typ_var, mod_var, code_dep, lib_dep, numeric) |>
  filter(!is.na(code_dep) &
           !code_dep %in% c("F", "M") &
           !is.na(numeric))

Drees - ISD C24 - tidyxl + unpivotr

Astuces

  • on peut modifier le nombre de lignes à sauter par onglet (dans le filter), car les espacements ne sont pas toujours les mêmes d’une année sur l’autre
  • utiliser les fonctions de dplyr et tidyr comme fill ou coalesce en complément
  • on peut aussi appliquer à plusieurs fichiers Excel si les formats sont identiques

Astuces

  • récupérer d’autres informations avec str_detect, comme la date de mise à jour
df_cells_c24 |>
  select(sheet, character) |>
  filter(str_detect(character, "mise à jour")) |>
  mutate(
    date_maj = str_remove(character, "Date de mise à jour : "),
    date_maj = dmy(date_maj)
  )

Résultat, avec Observable

Drees - FAJ série

Fond d’aide aux jeunes

Et des espacements différents, à détecter

Drees - FAJ série

df_cells_faj <-
  xlsx_cells("data/FAJ Données annuelles 2007-2022.xlsx") |>
  filter(str_sub(sheet, 1, 6) == "FAJ 20" & sheet != "FAJ 2015")

Ligne de début - “Départements”

df_cells_faj_min <- df_cells_faj |> 
  filter(character == "Départements") |> 
  select(sheet, row_min = row)

Ligne de fin - ligne blanche vide

df_cells_faj_max <- df_cells_faj |> 
  filter(is_blank & col == 1) |> 
  inner_join(df_cells_faj_min, by = join_by(sheet)) |> 
  filter(row > row_min + 3) |> 
  group_by(sheet) |> 
  filter(row == min(row)) |>
  ungroup() |> 
  select(sheet, row_max = row) |>
  mutate(row_max = row_max - 1)

Drees - FAJ série

df_cells_faj |>
  inner_join(df_cells_faj_min, by = join_by(sheet)) |>
  inner_join(df_cells_faj_max, by = join_by(sheet)) |>
  filter(between(row, row_min, row_max)) |>
  group_by(sheet) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep") |>
  behead("left", "lib_dep") |>
  ungroup() |>
  select(sheet, typ_var, mod_var, code_dep, lib_dep, character, numeric)

Drees - FAJ 2022

Deux tableaux dans le même onglet

df_cells_faj_filtered <- df_cells_faj |>
  filter(sheet == "FAJ 2022" & row >= 9)

Drees - FAJ 2022

Détection des débuts de tableaux

df_title_cells_faj <- df_cells_faj_filtered |>
  filter(character %in% c("Départements", "Métropoles et départements hors métropoles"))

Partition ~ sorte de nest

partitions_faj <- partition(df_cells_faj_filtered,
                            df_title_cells_faj)

Drees - FAJ 2022

Puis map (ou traitement séparé)

map(
  partitions_faj$cells,
  \(data) data |>
    behead("up-left", "typ_var") |>
    behead("up", "mod_var") |>
    behead("left", "code_dep") |>
    behead("left", "lib_dep") |>
    select(typ_var, mod_var, code_dep, lib_dep, character, numeric)
) |>
  set_names("dep", "metro") |>
  list_rbind(names_to = "type_geo")

Drees - Panorama statistique

Etablissements pour personnes âgées, tableau 17

Drees - Panorama statistique

De l’information stockée sous une autre forme que le test :

  • gras
  • indentation

Drees - Panorama statistique

Chargement du contenu et du format de chaque cellule

path_panorama <- "data/PanoFrance2022.xlsx"

df_cells_panorama <- xlsx_cells(path_panorama) |> 
  filter(between(row, 330, 355))

format_panorama <- xlsx_formats(path_panorama)

Drees - Panorama statistique

On utilise unpivotr::behead_if

df_cells_panorama |>
  behead("up", "code_geo") |>
  behead_if(format_panorama$local$font$bold[local_format_id],
            direction = "left-up",
            name =  "type_etab") |>
  behead_if(
    format_panorama$local$alignment$indent[local_format_id] == 2,
    direction = "left-up",
    name =  "type_var"
  ) |>
  behead_if(
    format_panorama$local$alignment$indent[local_format_id] == 4,
    direction = "left",
    name =  "type_places"
  ) |>
  select(code_geo, type_etab, type_var, type_places, numeric, character) |>
  filter(!is.na(numeric))

Drees - Panorama statistique

Conclusion

Conclusion

  • L’import, avec tidyxl se résume souvent à de la manipulation de données

  • Chaque fichier Excel est unique !

  • Il faut rester pragmatique !

Quelles sont vos questions ?