Formateur 326,
étude sur les organismes de formation (1)

Publié le 25 septembre 2020, par Richard Carlier
Série: Formateur 326
#formation #insee #OpenData #python #sql

Étant formateur indépendant, je me suis lancé dans une étude sur les centres de formations (avec l’idée, pourquoi pas, d’en contacter certains par la suite…)

L’open data nous permet facilement de retrouver la liste de ces centres, donc, allons y.

Dans cet épisode, voyons la préparation des données…

Récupérer les données

Mon premier point a été de récupérer le dataset de la liste publique des organismes de formation avec plusieurs idées en tête:

  • faire une petite étude statistiques sur la formation (en se limitant à ma spécialité, 326)
  • extraire ceux qui sont autour de chez moi à des fins de prospection

Un peu de téléchargement plus tard, je me retrouve avec le fichier 20200925_public_ofs.csv comprenant les 97.754 organismes, ainsi que le ListeNSF.xlsx qui lui comprend la liste des codes de spécialité (la mienne étant la 326, Informatique, traitement de l’information, réseaux de transmission des données).

NOTE: cette liste, téléchargée le 25/09/2020 semble dater de 27/04/2017… ce qui, pour une information déclarée « actualisé en temps réel » me laisse perplexe. Bref.

Analyser la structure du fichier

La structure du CSV est classique, mais réserve tout de même quelques surprises… Ce qui arrive tout le temps, ce n’est donc pas totalement une surprise en fait…

[
"raison_sociale",
" siren",
" num_etablissement",
" num_da",
" cfa",
" effectifs",
" adresse_voie",
" adresse_complement_adresse",
" adresse_code_postal",
" adresse_ville",
" adresse_etranger_voie",
" adresse_etranger_complement",
" adresse_etranger_code_postal",
" adresse_etranger_ville",
" adresse_etranger_pays",
" code_specialite_1",
" nb_stagiaires_1",
" nb_heures_stagiaires_1"
... etc jusque 12 ...
]

Déjà, chose assez étrange, à l’exception de la raison sociale, tous les champs commencent par un espace… Pas trop pratique, mais quand on le sait…

On remarque qu’il y a aussi 12 séries de code/nb de stagiaire/heures stagiaires, un centre de formation pouvant avoir plusieurs spécialités avec des volumes en conséquence… Trouver ceux qui sont en 326 va donc nécessiter de chercher dans les 12 colonnes…

Analyser les données du fichier

Comme souvent, c’est ici que les choses se corsent… Si l’on considère la ligne qui me concerne:

"RICHARD CARLIER";"442309142";"00036";"11754170975";Non;1;"";"";
"";"";"";"";"";"";"";"326";262;10000;; ; ; ; ; ; ; ; ; ; 
; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ; ;

On constate que, si les informations sont justes, il manque ce qui m’intéresse le plus : l’adresse. Ok, je connait la mienne (si, si). Ce qui me surprend, car lors de ma déclaration annuelle d’activité de formateur sur le site mesdemarches.emploi.gouv.fr, mon adresse y est correctement renseignée (j’ai vérifié), et y était déjà en 2017…

Et puis, via le code SIREN/Etablissement, on peut facilement croiser avec la base SIRENE et retrouver les données… Ce qui devrait être fait à mon sens pour ce fichier.

Bien sûr, je ne suis pas le seul concerné. En simplifiant mon analyse (j’ai étudié la présence du code postal supposant que l’adresse suit), je constate que sur les 97.754 centres présents, seuls 33.861 possèdent un code postal… Dans mon activité, 12.604 centres, mais uniquement 4.065 avec adresse.

Un tiers…

Complément, nettoyage…

J’ai pensé au départ utiliser l’API SIRENE pour récupérer en direct les informations manquantes, mais étant limitée à 30 interrogations par minutes… j’ai quand même 8.539 entreprises à récupérer (et plus 63.893 de plus si je pousse l’étude à tous les centres de formation…)

Donc, c’est parti, téléchargement des 5,3 Go de la base intégrale SIRENE, et me voici avec un fichier StockEtablissement_utf8.csv tout beau tout neuf.

Un petit coup de grep pour tester, et je fais ressortir en quelques instants :

442309142,00036,44230914200036,O,
2015-07-01,,,,2015-08-10T13:44:53,true,1,,
1,,BD,CHARLES DE GAULLE,92700,COLOMBES,,,92025,
,,,,,,,,,,,,,,,,,,2015-07-01,A,,,,,62.02A,NAFRev2,N

J’EXISTE !

Extraire les adresses des centres sur ce fichier de 30.127.826 de lignes pour les fusionner avec le précédent risque d’être un peu long. Le grep ayant pris presque une minute, on va tenter un import dans mysql de toute la base SIRENE.

Long, sans doute, mais il y aura moyen d’en faire quelque chose d’intéressant (pour cette étude ou une autre)…

LOAD DATA INFILE 'StockEtablissement_utf8.csv' IGNORE 
INTO TABLE sirene_20200925
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Query OK, 30127825 rows affected, 65535 warnings (2 min 57,01 sec)
Records: 30127825  Deleted: 0  Skipped: 0  Warnings: 30127825

Il y a du déchet (il en reste 29.626.755), mais cela constitue une bonne base de travail… et, j’en suis surpris, en moins de 3 minutes!

Après quelques essais en python (réussis, mais trop lents), je décide de tout travailler via quelques requêtes SQL bien senties. Question performance, on a beau dire, c’est quand même plus qu’efficace.

Donc:

  • chargement du fichier des organismes de formation, uniquement code 326 (python m’aide à automatiser les requêtes sur les 12 séries de champs – code_specialite_1, nb_stagiaires_1, nb_heures_stagiaires_1…)
  • jointure avec la table SIRENE pour récupérer les adresses (j’abandonne les infos de la table des organismes, trop de valeurs absentes, celles présentes faisant doublon à de rares exception ; de toutes façon, la table SIRENE est, elle, à jour).
  • suppression des éléments sans adresse du tout
  • jointure avec une table des communes que j’ai en stock pour récupérer la latitude et longitude des centres (du moins, des villes associées, ce qui est déjà pas mal)

Le temps de quelques essais…

Victoire !

Me voici désormais à la tête d’une table de 12.337 centres de formation code 326 qui semble propre.

Voici la ligne me correspondant (je note en passant que la date de création correspond à celle de l’établissement, pas à celle de l’entreprise… mais étant parti du StockEtablissement c’est logique, ça devrait quand même aller…)

Les analyses peuvent désormais commencer…

A suivre…

Top