La dernière fois nous avons vu l’arrêté « prestations », cette fois-ci penchons-nous sur l’arrêté TARIFAIRE. (comme précédemment, je n’aborde que le MCO, mais le principe est le même pour les autres champs)
C’est celui qu’on attend le plus longtemps car il touche directement à la valorisation des séjours et un centime dans un sens ou dans l’autre modifie grandement l’équilibre économique que ce soit de la sécurité sociale que de chaque établissement.
Tout comme pour l’autre arrêté, l’ATIH nous fournit des fichiers mais cette fois-ci l’agence nous propose 2 formats : un excel et un « CSV ». Cependant, il y a une finesse.
Les fichiers sont disponibles sur la page https://www.atih.sante.fr/tarifs-mco-et-had.
Les CSV ou l’Excel ?
Si vous suivez le lien pour le format « CSV », vous allez vous retrouver avec un fichier… ZIP 🤔 mais heureusement contenant les-dits fichiers CSV.
Dedans se trouvent 6 fichiers répartis en 3 publics (exDG) et 3 privés (exOQN) reprenant respectivement les tarifs MCO (ghs_pub.csv ou ghs_pri.csv), HAD (ght_pub.csv ou ght_pri.csv) et les suppléments (sup_pub.csv ou sup_pri.csv).
Si par contre, on ouvre le fichier au format excel, on se retrouve avec pas moins de 61 tables ! Autant dire que les CSV sont loin d’être exhaustifs… Cependant pour le traitement du MCO/HAD elles restent suffisantes pour la valorisation des séjours si on ne veut pas rentrer dans les détails ou qu’on a pas besoin d’aborder les autres champs.
Voici à titre indicatif l’ensemble des noms des feuilles présentes dans le document xlsx (les noms ne sont pas dans l’ordre réel pour montrer la correspondance public/privé, la troisème colonne est un peu un fourre-tout où j’ai regroupé les feuilles non spécifiques et celles au contraire où le secteur n’est pas le seul critère (les outres-mers en particulier). En gras, les feuilles que nous allons utiliser dans l’article. Enfin, vous noterez au passage, des anomalies de systématisation typographique avec des disparitions de majuscule aléatoires selon le secteur qu’il va falloir respecter et complique l’automatisation…) :
Tarifs public
Suppléments public
Supp transport public TDE
Supp transport public TSE
Dialyse public
HAD public
HAD en EPHA public (-13%)
HAD en SSIAD public (-7%)
CPO public
CPO public2
CPO public3
CPO public4
PO public
Greffes public
Greffes public DV
Greffes public2
ATU public
FFM public
SEH public
APE public
FAI public
FAI2 public
FAI3 public
Tarifs privé
suppléments privé
Supp transport privé TDE
Supp transport privé TSE
dialyse privé
HAD privé
HAD en EPHA privé (-13%)
HAD en SSIAD privé (-7%)
CPO privé
CPO privé2
CPO privé3
CPO privé4
PO privé
ATU privé
FFM privé
SEH privé
APE privé
FAI privé
FAI2 privé
FAI3 privé
DTP
Coeff Geo
Coeff Segur
Forfait MRC
FU public met
FU public ant
FU privé met
FU privé ant
Sup urg public met
Sup urg public ant
Sup urg privé met
Sup urg privé ant
Bio urg public met
Bio urg public 971 972
Bio urg public 973 974
Bio urg privé met
Bio urg privé 971 972
Bio urg privé 973 974
Par ailleurs, certaines feuilles n’ont de « table » que le nom quand on voit par exemple l’onglet « DTP » :
En conclusion dans ce 2ème article, nous n’allons pas choisir la facilité et travailler les CSV mais voir ce qu’on pourrait faire de quelques tables (nous n’allons pas traiter les 61 pour ne pas faire un article trop long mais vous pouvez le faire pour vous ! ).
Enfin, si vous êtes un lecteur assidu de ce site, vous avez déjà vu la manipulation des fichiers CSV de tarifs car je les ai déjà utilisés par exemple dans Jouons avec les tarifs et ses suites.
Les tables ou les pages ?
Sous ces vocables je différencie deux types de feuilles Excel différentes :
Les tables
Elles contiennent des données sous forme de tableau avec une présentation orientée enregistrement, c’est à dire un nombre de colonnes représentant des facettes présentes dans tous les enregistrements et autant de lignes que nécessaire. Idéalement, elles disposent d’une « clé primaire » c’est à dire d’un champ dont la valeur est unique et sert de référence pour la table (par exemple le numéro de GHS pour la table GHS).
L’exemple typique dans ce Xlsx est la feuille « Tarifs publics » (ou « Tarifs privés » bien sûr) : 9 colonnes au contenu bien précisé et présent (ou pertinent) pour chacun des 5670 lignes représentant chacune un GHS. Une telle feuille se prête très bien à la lecture via read_excel() de la librairie readxl que nous avons mise en oeuvre dans l’article précédent.
Les pages
A côté de cela, il existe des pages qui contiennent des données sans structure. La plus typique étant celle pointée plus haut « DTP ». Dans un tel cas, il ne va pas être possible d’utiliser read_excel(). Heureusement, R a aussi une librairie pour cela tidyXl.
A mi-chemin, on peut aussi parler par exemple des tables TDE qui contiennent bien les tarifs mais pas le code officiel (bon, ça n’est pas non plus sorcier, dans le cas c’est TDE1/2/3/4/5).
Bref, n’oublions pas que toutes ces données sont avant tout une retranscription d’un texte de loi fait par des non-spécialistes pour publication au JO par initialement pour publication à visée de traitement informatique…
L’initialisation
A la différence du code dans l’article Arrêtés (I), si nous voulions utiliser les fichiers CSV, il nous faudrait les extraire d’une archive au format ZIP. Bien sûr, R sais faire cela. Je vous montre cependant comment le faire proprement :
library(dplyr)
library(readr)
library(tidyr)
library(stringr)
# on stocke la source dans une chaine de caractères pour la lisibilité future
# ci dessous l'adresse du xlsx puis le zip contenant les csv.
f_source_xls <- "https://www.atih.sante.fr/sites/default/files/public/content/1568/tarif_arrete_2025_2.xlsx"
f_source_csv <- "https://www.atih.sante.fr/sites/default/files/public/content/1568/ghs_web_20250301_1.zip"
date_effet = as.Date("2025-03-01")
# l'année à traiter
equiv_annee <- 2025
#Le chemin d'export des résultats
d_out <- sprintf("~/EXPORTS/REF/%4d/ARRETES/",equiv_annee)
##################################### Fin de la configuration
# Un endroit où sauvegarder les fichiers temporaires CSV
# mais cette fois c'est un répertoire
d_temp <- tempfile()
f_temp_zip <- tempfile(fileext = ".zip")
dir.create(d_temp)
# on télécharge le fichier et dézippe en un temps tout le répertoire
download.file(f_source_csv, f_temp_zip)
unzip(f_temp_zip, exdir = d_temp)
dir(d_temp)
# Nous n'avons plus besoin du fichier téléchargé
file.remove(f_temp_zip)
rm(f_temp_zip) # On supprime la variable qui n'a plus non plus d'utilité
# A partir d'içi nous pouvons accéder au contenu des différents fichiers
# dans le répertoire temporaire `d_temp`
Pour le retraitement à partir du fichier Excel, par contre, le code est similaire à celui de l’article précédent :
# On charge la librairie de manipulation des fichiers excel
library(readxl)
# mais aussi celle permettant de requêter de contenu
library(tidyxl)
# Idem pour le xls
f_temp_xls <- tempfile(fileext = ".xlsx")
download.file(f_source_xls,f_temp_xls, mode="wb")
# A partir d'içi nous pouvons accéder fichier xls
Les GHS
Je vous propose de produire 4 fichiers à partir de 2 des feuilles du documents Excel soit plus que ce que nous fourni l’ATIH :
- 2 CSV qui seront l’exacte copie de « ghs_pub.csv » et « ghs_pri.csv », uniquement dans un but d’exercice (vu qu’on les a potentiellement déjà dans le zip).
- 1 CSV regroupant ces 2 fichiers au sein d’un seul que j’appelle « ghs_tout.csv »
- et enfin 1 CSV tel que je l’utilise, regroupant les 2 secteurs, ne contenant que les champs strictement nécessaires et avec des noms de colonnes modifiés pour être plus facilement utilisables en R (en particulier en supprimant le signe <moins (-)> du nom, en le raccourcissant et le normalisant avec les dénominations simples que j’utilise habituellement dans mes traitements)
Tout d’abord la suite de retraitements (chaine ETL) pour produire un jeu de données unifié :
GHS <- bind_rows(
read_excel(f_temp_xls, # Le public
sheet="Tarifs public", skip=4,
col_names = c("GHS-NRO",
"GHM-NRO",
"GHS-LIB",
"SEU-BAS",
"SEU-HAU",
"GHS-PRI",
"EXB-FORFAIT",
"EXB-JOURNALIER",
"EXH-PRI")) %>%
mutate(
TYPE = "PUBLIC"),
read_excel(f_temp_xls, # Le privé
sheet="Tarifs privé", skip=4,
col_names = c("GHS-NRO",
"GHM-NRO",
"GHS-LIB",
"SEU-BAS",
"SEU-HAU",
"GHS-PRI",
"EXB-FORFAIT",
"EXB-JOURNALIER",
"EXH-PRI")) %>%
mutate(
TYPE = "PRIVE")) %>%
mutate(
`CMD-COD` = substr(`GHM-NRO`,1,2),
`DSC-MCO` = substr(`GHM-NRO`,3,3),
.after=`GHS-NRO`) %>%
mutate(`DATE-EFFET` = date_effet)Puis la sauvegarde spécifique de chacun des fichiers prévus :
# On sauvegarde, 4 versions : les 2 premières sont strictement les fichiers
# présents dans l'archive ATIH
# La 3ème correspond à la concaténation des 2
# La 4ème est la concaténation des 2 avec les noms de colonnes que j'utilise
# habituellement
# Les GHS du privé
write_csv2(GHS %>% filter(TYPE == "PRIVE") %>% select(-TYPE),
file = paste(d_out,"ghs_pri.csv", sep = ""), na = "")
# Les GHS du public
write_csv2(GHS %>% filter(TYPE == "PUBLIC") %>% select(-TYPE),
file = paste(d_out,"ghs_pub.csv", sep = ""), na = "")
# Les GHS public et privé en 1 fichier
write_csv2(GHS,
file = paste(d_out,"ghs_tout.csv", sep = ""), na = "")
# Ma version perso des GHS (retrait des "-" obligeant de mettre des backticks,
# suppression de certains champs pouvant être trompeurs ou à trouver ailleurs,
# simplification des noms)
write_csv2(GHS %>%
select(-`CMD-COD`, -`DSC-MCO`,-`GHM-NRO`, -`GHS-LIB`) %>%
rename(GHS = `GHS-NRO`,
BB = `SEU-BAS`,
BH = `SEU-HAU`,
TARIF = `GHS-PRI`,
FORFAITB = `EXB-FORFAIT`,
EXB = `EXB-JOURNALIER`,
EXH = `EXH-PRI`,
DEFFET =`DATE-EFFET`),
file = paste(d_out,"GHS.csv", sep = ""), na = "")Et voilà nous avons créé nous-même les fichiers GHS au format CSV fournis par l’ATIH et les avons augmentés de 2 autres versions.
Les données à code/valeur uniques
Si on regarde les autres feuilles, on peut noter que la plupart font correspondre un CODE à une VALEUR souvent en Euro mais parfois en %. Cependant, ces données sont souvent comparables dans leur structure : un code correspond à une valeur pour le secteur d’exercice.
Nous allons pour la plupart des autres feuilles tenter d’accumuler les données contenues dans un format standardisé du type :
- CODE : le code officiel de la prestation (ou a défaut un code personnel précédé d’un _ (trait de soulignement aussi appelé « tiret du 8 » par certains)
- LIB : un libellé éventuel
- VALEUR : la valeur numérique
- UNITE : par exemple « Euro » ou « % »
- COMMENTAIRE : un texte de commentaire présent dans le document original
- TYPE : qui correspondra à « PUBLIC » ou « PRIVE » comme pour les GHS si la différenciation existe dans le fichier Excel (même si les valeurs sont les mêmes, on va respecter les données sources).
Il ne sera par contre pas possible d’insérer dans ce tableau des données plus compliquées (qui ont plus d’une variable) comme par exemple l’échelle de FAI (celle-ci n’ayant pas de CODE différencié par niveau et de plus variant par palier en fonction d’une échelle de minimums et maximums).
Les SUPPLEMENTS
Nous regroupons les 2 tables de prix des suppléments (publiques/privés) en une seule
SUPPLEMENTS <- bind_rows(
read_excel(f_temp_xls, # Le public
sheet="Suppléments public", skip=4,
col_names = c("CODE", "LIB", "VALEUR")) %>%
mutate(
UNITE = "Euro",
TABLE = "SUPPLEMENTS",
TYPE = "PUBLIC"),
read_excel(f_temp_xls, # Le privé
sheet="suppléments privé", skip=4, # il y a un "s" minuscule...
col_names = c("CODE", "LIB", "VALEUR")) %>%
mutate(
UNITE = "Euro",
TABLE = "SUPPLEMENTS",
TYPE = "PRIVE")
)
Pour rappel, skip= indique combien de lignes sauter avant de commencer la lecture, col_names= fixe le nom des colonnes à importer.
Les TDE/TSE
Les TDE et TSE n’ont pas de code dans le tableau alors qu’il existe réellement et est composé de « TDE » (ou « TSE ») suivi d’un nombre. On doit donc le créer.
TDE <- bind_rows(
read_excel(f_temp_xls, # Le public
sheet="Supp transport public TDE", skip=4,
col_names = c("LIB", "VALEUR")) %>%
bind_cols(CODE = c("TDE1","TDE2","TDE3","TDE4","TDE5"), COMMENTAIRE=NA) %>%
mutate(
UNITE = "Euro",
TABLE = "TDE",
TYPE = "PUBLIC"),
read_excel(f_temp_xls, # Le privé
sheet="Supp transport public TDE", skip=4, # il y a un "s" minuscule...
col_names = c("LIB", "VALEUR")) %>%
bind_cols(CODE = c("TDE1","TDE2","TDE3","TDE4","TDE5"), COMMENTAIRE=NA) %>%
mutate(
UNITE = "Euro",
TABLE = "TDE",
TYPE = "PRIVE")
) %>%
relocate(CODE) %>% # CODE en première position
relocate(COMMENTAIRE, .before = TYPE) # COMMENTAIRE en avant-dernière, juste avant TYPE
Par ailleurs, vu que par défaut il est ajouté en fin de la liste de colonnes, il faut le déplacer par un relocate(). Une autre possibilité aurait été de spécifier tous les champs dans l’ordre au sein d’un select().
Les HAD
Il n’y a pas moins de 6 tables pour l’HAD (3 public, 3 privé) déclinées selon l’environnement du patient (domicile = plein tarif, EHPAD décoté de 13%, suivi par un SSIAD décoté de 7%)
HAD <- bind_rows(
read_excel(f_temp_xls, # Le public
sheet="HAD public",
range = "B5:C35",
col_names = c("CODE", "VALEUR")) %>%
mutate(
LIB = CODE,
UNITE = "Euro",
TABLE = "HAD PT",
COMMENTAIRE = NA,
TYPE = "PUBLIC"),
read_excel(f_temp_xls, # Le privé
sheet="HAD privé",
range = "B5:C35",
col_names = c("CODE", "VALEUR")) %>%
mutate(
LIB = CODE,
UNITE = "Euro",
TABLE = "HAD PT",
COMMENTAIRE = NA,
TYPE = "PRIVE"),
read_excel(f_temp_xls, # Le public -13
sheet="HAD en EPHA public (-13%)",
range = "B5:C35",
col_names = c("CODE", "VALEUR")) %>%
mutate(
LIB = CODE,
UNITE = "Euro",
TABLE = "HAD EPHA-13",
COMMENTAIRE = NA,
TYPE = "PUBLIC"),
read_excel(f_temp_xls, # Le public -7
sheet="HAD en SSIAD public (-7%)",
range = "B5:C35",
col_names = c("CODE", "VALEUR")) %>%
mutate(
LIB = CODE,
UNITE = "Euro",
TABLE = "HAD SSIAD-7",
COMMENTAIRE = NA,
TYPE = "PUBLIC"),
read_excel(f_temp_xls, # Le privé -13
sheet="HAD en EPHA privé (-13%)",
range = "B5:C35",
col_names = c("CODE", "VALEUR")) %>%
mutate(
LIB = CODE,
UNITE = "Euro",
TABLE = "HAD EPHA-13",
COMMENTAIRE = NA,
TYPE = "PRIVE"),
read_excel(f_temp_xls, # Le privé -7
sheet="HAD en SSIAD privé (-7%)",
range = "B5:C35",
col_names = c("CODE", "VALEUR")) %>%
mutate(
LIB = CODE,
UNITE = "Euro",
TABLE = "HAD SSIAD-7",
COMMENTAIRE = NA,
TYPE = "PRIVE"),
) %>%
select(CODE, LIB, VALEUR, UNITE, TABLE, COMMENTAIRE, TYPE)
Comme évoqué plus haut, ici j’ai utilisé un select() pour reclasser les champs. A titre personnel, je trouve cela plus visuel.
Notez par contre, que je n’utilise pas skip= mais range= qui attend le nom d’une plage au format Excel pour isoler les éléments à importer. La raison de cette utilisation est que la première colonne est vide dans la feuille d’origine. range= permet de l’exclure facilement. Cependant, range= ne peut pas contenir de notation de colonnes même si c’est une plage Excel valide. Ainsi il n’est pas possible de spécifier « B:C » pour « toutes les valeurs des 2 et 3èmes colonnes ». Donc cette notation est inadaptée pour des tables au nombre de lignes non connu lors de l’écriture du code. L’alternative est alors de passer par un skip= et un type particulier dans le paramètre col_types= :
read_excel(f_temp_xls,
sheet="HAD en SSIAD privé (-7%)",
range = "B5:C35",
col_names = c("CODE", "VALEUR"))
# peut aussi s'écrire :
read_excel(f_temp_xls,
sheet="HAD en SSIAD privé (-7%)",
skip= 4,
col_names = c("CODE", "VALEUR"),
col_types = c("skip", "text", "numeric"))
# On saute 4 lignes par le skip=
# Et on ne tient pas compte de la 1ère colonne par le type "skip"
# si vous voulez laisser read_excel() déduire le type des autres colonnes, vous
# pouvez utiliser c("skip", "guess", "guess")Dans la 2è version, tout le fichier sera importé, indépendamment du nombre de lignes.
La page DTP
Comme discuté plus haut, le format de la page DTP ne se prête pas du tout à un traitement tabulaire. Et plutôt que d’utiliser read_excel() (qui resterait possible mais imposerait un traitement bizarre), nous allons utiliser la librairie tidyXl pour charger les données.
Cela reste initialement assez similaire
library(tidyxl)
tmp <- xlsx_cells(f_temp_xls, sheets = "DTP")
Le résultat, par contre, n’est pas du tout une table de données mais une table reprenant le contenu « organisé » de la feuille :
> tmp
# A tibble: 14 × 24
sheet address row col is_blank content data_type
<chr> <chr> <int> <int> <lgl> <chr> <chr>
1 DTP A1 1 1 TRUE NA blank
2 DTP B1 1 2 TRUE NA blank
3 DTP A2 2 1 FALSE 125 character
4 DTP A4 4 1 FALSE 5654 character
5 DTP A5 5 1 TRUE NA blank
6 DTP A6 6 1 TRUE NA blank
7 DTP A7 7 1 TRUE NA blank
8 DTP A8 8 1 TRUE NA blank
9 DTP A9 9 1 TRUE NA blank
10 DTP A13 13 1 TRUE NA blank
11 DTP A14 14 1 TRUE NA blank
12 DTP A16 16 1 TRUE NA blank
13 DTP A20 20 1 TRUE NA blank
14 DTP A23 23 1 TRUE NA blank
# ℹ 17 more variables: error <chr>, logical <lgl>,
# numeric <dbl>, date <dttm>, character <chr>,
# character_formatted <list>, formula <chr>,
# is_array <lgl>, formula_ref <chr>,
# formula_group <int>, comment <chr>, height <dbl>,
# width <dbl>, row_outline_level <dbl>,
# col_outline_level <dbl>, style_format <chr>, …Ainsi vous pouvez requêter le contenu de la case qui nous intéresse par un :
> tmp %>% filter(address = "A4") %>% pull(character)
[1] "Lorsque l’établissement prend en charge un patient bénéficiant de l’assistance d’un proche dans le cadre de son traitement de l’insuffisance rénale chronique, un supplément dénommé « indemnité compensatrice à tierce personne » (DTP) peut être facturé par l’établissement dans les conditions suivantes :\r\n- un supplément pour chaque séance de traitement pour l’hémodialyse à domicile en sus du forfait d’hémodialyse à domicile ;\r\n- trois suppléments pour chaque semaine de traitement pour la dialyse péritonéale en sus du forfait de dialyse péritonéale automatisée et du forfait de dialyse péritonéale continue ambulatoire.\r\n Le tarif de ce supplément (DTP) est fixé à 25,05 €."Il ne nous reste plus qu’à extraire la donnée numérique qui s’y trouve grâce à une expression régulière (bientôt un article spécifique sur ce domaine qui est un langage dans le langage) :
tmp %>%
filter(address=="A4") %>% # on se concentre sur la case A4
pull(character) %>% # on extrait la chaine de caractères
sub(".*\\s(\\d+),(\\d*) €.*", # on ne garde que le prix
"\\1\\.\\2", # …
.) %>% # …
as.numeric()Pour faire simple le code ".*\\s(\\d+),(\\d*) €.*" se lit : Dans la chaine passée en paramètre, trouve autant de fois qu’il le faut n’importe quel caractère suivi d’un espace ou assimilé, suivi de plusieurs chiffres (sauvegarde la valeur dans « \1 ») puis une virgule, puis un autre ensemble éventuel de chiffres (sauvegarde la valeur dans « \2 »), suivi d’un espace et d’un symbole euro et n’importe quoi d’autre.
Puis le 2ème paramètre de sub() précise ce qui doit être retourné. Dans notre cas : retourne la valeur \1 précédemment sauvegardée puis un « . » (point) puis la valeur \2. (le 3è paramètre de sub() est la chaine en entrée, représentée ici par le point, propre à l’utilisation du %>% (pipe))
sub() travaillant sur des chaines de caractères, il faut alors convertir celle-ci en nombre avec as.numeric().
Pour la petite histoire, dans le cas spécifique de cette case, on aurait pu utiliser directement parse_number() de la librairie readr (que nous avons chargée pour utiliser write_csv2()) mais qui serait en échec si il y avait le moindre autre chiffre dans le texte avant le prix :
> "Le prix d'une carotte est de 2,04 €" %>% parse_number()
[1] 2.04
> "Le prix d'1 carotte est de 2,04 €" %>% parse_number()
[1] 1
# car parse_number s'est arrêté au premier groupe de chiffres trouvé et ignore le reste
#tandis que
> "Le prix d'1 carotte est de 2,04 €" %>%sub(".*\\W(\\d+),(\\d*) €.*",
+ "\\1\\.\\2",
+ .) %>% as.numeric()
[1] 2.04
# ou bien
> "Le prix d'1 carotte est de 2,04 €" %>%sub(".*\\W(\\d+),(\\d*) €.*",
+ "\\1\\.\\2",
+ .) %>% parse_number()
[1] 2.04Nous aurons à nouveau besoin des expressions régulières un peu plus loin.
In fine, le code pour DTP pourrait être :
DTP <- tibble(CODE="DTP",
LIB = "DTP",
VALEUR = # le traitement spécifique tidyxl
tmp %>%
filter(address=="A4") %>% # on se concentre sur la case A4
pull(character) %>% # on extrait la chaine de caractères
sub(".*\\s(\\d+),(\\d*) €.*", # on ne garde que le prix
"\\1\\.\\2", # ...
.) %>% # ...
as.numeric(), # et on convertit en numérique
UNITE = "Euro",
TABLE = "DTP",
COMMENTAIRE = NA,
TYPE = "TOUT")
Ce qui nous le met dans le même format que les autres données déjà récupérées plus haut.
Les coefficients Ségur et Géographiques
Ces deux tables sont lisibles avec read_excel() mais il n’y a pas de code officiel. Nous allons donc le créer de toute pièce mais aussi, il faut trouver comment le rattacher aux données.
Là aussi nous allons utiliser les expressions régulières, d’une part dans un grepl() pour le ségur et dans un sub() pour le géographique.
Pour le Ségur, nous cherchons le motif puis donnons un code adapté lorsque trouvé. Pour cela on se base sur l’alinea de l’article L.162-22 du code de la santé publique qui définit le type d’établissement.
C_SEGUR <- read_excel(f_temp_xls,
sheet="Coeff Segur",
skip = 4,
col_names = c("COMMENTAIRE", "VALEUR")) %>%
mutate(
CODE = case_when(grepl("a de l’article L\\.162-22", COMMENTAIRE) ~ "_SEGUR-PUB",
grepl("b et c de l’article L\\.162-22", COMMENTAIRE) ~ "_SEGUR-ESPIC",
grepl("d de l’article L\\.162-22", COMMENTAIRE) ~ "_SEGUR-PBNL",
grepl("à but lucratif", COMMENTAIRE) ~ "_SEGUR-PRI"),
LIB = case_when(grepl("a de l’article L\\.162-22", COMMENTAIRE) ~ "Ségur établissements publiques",
grepl("b et c de l’article L\\.162-22", COMMENTAIRE) ~ "Ségur ESPIC",
grepl("d de l’article L\\.162-22", COMMENTAIRE) ~ "Ségur Privé à But Non Lucratif (hors ESPIC)",
grepl("à but lucratif", COMMENTAIRE) ~ "Ségur Privé"),
UNITE = "%",
TABLE = "COEF_SEGUR",
TYPE = "TOUT") %>%
select(CODE, LIB, VALEUR, UNITE, TABLE, COMMENTAIRE, TYPE)Il y aurait une autre façon de référencer ces données en séparant public/privé et un sous-statut. Ce qui donnerait par exemple :
- CODE = « _SEGUR », TYPE = « PUBLIC »
- CODE = « _SEGUR », TYPE = « ESPIC »
- CODE = « _SEGUR », TYPE = « PRIVE-BNL »
- CODE = « _SEGUR », TYPE = « PRIVE »
Je vous laisse réécrire cette version à titre d’exercice.
Pour le coefficient géographique, nous allons réutiliser les données extraites grâce à un sub() :
C_GEO <- read_excel(f_temp_xls,
sheet="Coeff Geo",
skip = 4,
col_names = c("COMMENTAIRE", "VALEUR")) %>%
mutate(COMMENTAIRE = gsub("[…\\.]","", COMMENTAIRE)) %>% # On supprime les
# points simples et de suspension qui servent de continuation dans le document
mutate(
CODE = paste("_GEO", sub("(.*?)(\\s.*)", "\\1", COMMENTAIRE), sep = "_"),
LIB = paste ("Coefficient géographique pour : ", sub("(.*?)\\s(.*)", "\\2 (\\1)", COMMENTAIRE), sep = ""),
UNITE = "%",
TABLE = "COEF_GEO",
TYPE = "TOUT") %>%
select(CODE, LIB, VALEUR, UNITE, TABLE, COMMENTAIRE, TYPE)La transformation par le mutate() consiste à réécrire les données récupérées par les expressions régulières.
Vous pouvez vous demander pourquoi le paste(), car on aurait pu aussi écrire directement
CODE = sub("(.*?)(\\s.*)", "_GEO_\\1", COMMENTAIRE)
LIB = sub("(.*?)\\s(.*)", "Coefficient géographique pour : \\2 (\\1)", COMMENTAIRE)dans le mutate(). Ici, c’est uniquement pour la lisibilité. Cette version sans le paste() n’est que 50 microsecondes plus rapide (le plus long étant la lecture du fichier par le read_excel() qui prend à lui seul 45 millisecondes (soit 900 fois plus de temps) sur les 48 nécessaires à la totalité du traitement). Rappelez-vous que l’optimisation n’a d’intérêt que si elle porte sur une partie significative du temps de traitement.
Etc…
Je pourrais continuer pour les 61 pages, mais cet article deviendrait franchement indigeste. Le but était de vous illustrer les différents traitements qui peuvent être nécessaires pour normaliser des données de natures hétérogènes.
Finalement, nous pourrons regroupés tous ces éléments au sein d’une seule table via :
ELEMENTS <- bind_rows(
SUPPLEMENTS,
TDE,
TSE,
HAD,
DTP,
C_SEGUR,
C_GEO,
)
# Si plus de besoin on peut supprimer les tables intermédiaires
rm(SUPPLEMENTS, TDE, TSE, HAD, DTP, C_SEGUR, C_GEO)
write_csv2(ELEMENTS,
file = paste(d_out,"autres_tarifs.csv", sep = ""), na = "")
ou bien sur les sauvegarder séparément à votre préférence.
A titre d’exercice, je vous propose de continuer de faire quelques autres feuilles, par exemple les SEH, FFM, APE, etc. (assez similaires aux suppléments dans le format qui pourraient donc être ajoutés dans ELEMENTS) et les FAI (qui vont nécessiter l’utilisation d’expressions régulières pour extraire les bornes et les sauvegarder dans des variables différentes, ne seront donc pas compatibles avec le format de ELEMENTS et devront être sauvegardés dans une table à part)
Bon courage pour ces exercices.