4.2 Croisement de données

L'Institut National de la Statistique et des Études Économiques (INSEE) est chargé de la production, de l'analyse et de la publication des statistiques officielles en France : comptabilité nationale annuelle et trimestrielle, évaluation de la démographie nationale, du taux de chômage, etc.

Le site web de l'INSEE fournit quantité de données ouvertes que tout citoyen peut récupérer et traiter suivant ses besoins. Il est par contre parfois nécessaire de récupérer les données de plusieurs fichiers pour obtenir l'information désirée.

Voici comment télécharger le fichier des données sur le découpage de la France en régions.

1. Dans un navigateur, taper l'URL du site web de l'INSEE [html, fr].

2. Naviguer dans le site en suivant le chemin suivant :
   Accueil > Définitions méthodes et qualités >Géographie administrative et d'étude >Téléchargement>Code Officiel Géographique (COG)

3. Cliquer sur "Téléchargement des fichiers" du dernier millésime.
   Pour ce module, on travaillera avec les données 2018.

4. Dans le sommaire, cliquer sur le lien "Liste des régions".

5. On arrive sur cela :

Ce qu'il y a entre parenthèses indique le format du fichier ainsi que sa taille.

Choisir le fichier txt et enregistrez le fichier reg2018-txt.zip [zip, fr].

Le format zip est un format de fichiers compressés : un algorithme est exécuté lors de la compression pour minimiser la taille des données.

Cliquer sur un fichier zip permet de voir son contenu, qui peut être un ou plusieurs fichiers.

Attention :

Cliquer sur un fichier zip permet de voir l'icône ou les icônes des fichiers qui ont été compressés pour créer ce fichier. Par contre, si vous cliquez sur les icônes de ces fichiers, vous ne pourrez pas les modifier, car ils sont décompressés "à la volée" dans un dossier temporaire dans lequel vous n'avez pas le droit d'écrire.

Il est donc impératif de décompresser le fichier zip avant de modifier les données.

Pour cela, faire un clic droit sur l'icône du fichier zip téléchargé et choisir "Extraire ici ...".

Télécharger les fichiers comsimp2018-txt.zip et depts2018-txt.zip sur les communes et les départements français.

On suppose que l'on a téléchargé sur le site de l'INSEE les fichiers comsimp2018-txt.zip, reg2018-txt.zip et dep2018-txt.zip [zip, fr]. On ne va d'abord utiliser que les deux premiers.

1. Décompressez ces trois fichiers dans un répertoire de travail (clic droit sur les icônes et choisir "Extraire tout ...".
   Ces fichiers ayant pour extension ".txt", mais qui sont en fait au format CSV avec pour séparateur des tabulations : on parle de format TSV.

2. Ouvrez un classeur vide avec LibreOffice Calc puis sauvegardez-le au format ODS avec le nom "Activite_1". Nous avons donc un nouveau fichier Activite_1.ods.

3. Pour importer le fichier reg2018.txt dans LibreOffice Calc :

   • Cliquez dans la zone à droite de "Feuille1 en bas de la feuille de calcul.

   • Dans la fenêtre "Insérer une feuille" qui vient d'apparaître, choisissez "A partir d'un fichier" puis cliquez sur "Parcourir".

   • Allez chercher dans votre répertoire de travail le fichier reg2018.txt (pas la version zip !).
     ATTENTION : dans la fenêtre "Import de texte", il y a des réglages à faire : le jeu de caractères et les options de séparateur.
     - choisir le même jeu de caractères que celui utilisé par l'INSEE pour coder ce fichier : ISO 8859-1, appelé aussi Latin-1 pour que les caractères accentués s'affichent bien,
     - dans les options de séparateur, cocher seulement "Tabulation".
     Vérifier que l'apparence des premières lignes du fichier est correct (on voit bien les différentes colonnes et tous les caractères s'affichent bien), puis appuyer sur le bouton OK.

   • Faites un clic droit en bas de la feuille sur le nom "reg2018" et renommez-le en "régions".

Importer de la même manière dans le classeur Activite_1 le fichier comsimp2018.txt ; renommez la feuille "comsimp2018" en "communes" puis sauvegarder l'ensemble.

1. Aller dans la feuille "communes" du classeur Activite_1.ods et tapez dans la cellule M1 le mot NREG (pour Nom Région)

2. Dans la cellule M2 de la feuille "communes", tapez l'instruction :
   =RECHERCHEV(C2;$régions.$A$2:$D$19;4;0)
   (attention à ce que le mot "régions" corresponde bien au nom de la deuxième feuille de calculs)
   Explication de la formule :
   Cette instruction effectue une recherche du contenu de la cellule C2 (c'est à dire 84) dans les cellules A2 à A19 de la feuille "régions". Elle trouve cette valeur 84 à la cellule A17. Elle affecte alors à la cellule M2 le contenu de la cellule D17, c'est à dire AUVERGNE-RHONE-ALPES, situé dans la 4^ème colonne de la zone de recherche .$A$2:$D$19 (d'où le "4" dans la formule). Le nom AUVERGNE-RHONE-ALPES devrait donc apparaître dans la cellule M2 si vous avez tapé correctement cette formule. Le dernier paramètre, 0, spécifie que la colonne A dans laquelle la recherche se fait n'est pas triée par ordre croissant.

3. A FAIRE VOUS MÊME : copier/coller la formule de la cellule M2 dans les cellules M3 à M35358 de façon à ce que le nom des régions de chaque commune soit renseigné (il va être très long d'étirer la formule, on pourras le faire que sur un millierde lignes...).

Recherche verticale avec référence aux cellules adjacentes à droite. Cette fonction vérifie si une valeur spécifique est contenue dans la première colonne d'une matrice. La fonction renvoie alors la valeur dans la même ligne de la colonne nommée par Index. Si le paramètre trié est omis ou défini sur VRAI ou un, on suppose que les données sont triées en ordre croissant. Dans ce cas, si le critère_de_recherche exact n'est pas trouvé, la dernière valeur qui est inférieure au critère est renvoyée. Si trié est paramétré sur FAUX ou zéro, une correspondance exacte doit être trouvée, sinon il en résulte l'erreur Erreur: valeur non disponible. Ainsi, avec une valeur de zéro, les données n'ont pas besoin d'être triées dans l'ordre croissant.

Syntaxe

=RECHERCHEV(critère_de_recherche;matrice;index;trié)

• critère_de_recherche est la valeur recherchée dans la première colonne de la matrice.

• matrice est la référence qui doit comprendre au moins deux colonnes.

• index est le numéro de la colonne dans la matrice qui contient les valeurs devant être renvoyées. La première colonne a le numéro 1.

• trié est un paramètre facultatif qui indique si la première colonne de la matrice est triée en ordre croissant. Saisissez la valeur logique FAUX ou zéro si la première colonne n'est pas triée en ordre croissant. Les colonnes triées peuvent être recherchées plus rapidement et la fonction renvoie toujours une valeur, même si la valeur de recherche ne correspond pas exactement. Dans les listes non triées, la valeur de recherche doit correspondre exactement. Sinon la fonction renvoie ce message Erreur : valeur non disponible.

Voir la liste des noms de chefs-lieux de régions

On souhaite appliquer un filtre pour ne voir que les chefs-lieux de régions dans la feuille "communes" du fichier "Activite_1.ods".

On suppose que vous avez déjà ajouté les noms des régions à ce fichier (voir travaux précédents).

Pour cela, on se base sur la description ci-dessous récupérée sur le site de l'INSEE du descripteur CHEFLIEU :

On voit donc qu'une commune chef-lieu de région doit avoir le nombre 4 comme donnée du descripteur CHEFLIEU.

Le principe va donc consister à appliquer un filtre sur la table "communes" afin que seules les communes ayant 4 dans la colonne CHEFLIEU apparaissent.

1. Ouvrir avec LibreOffice Calc le fichier "Activite_1.ods" et cliquer sur l'onglet "communes" pour voir la liste des communes de France.

2. Dans le menu "Données", cliquer sur "AutoFiltre". Cela fait apparaître des petites flèches à côté des noms des descripteurs de la table.

3. Cliquer sur la flèche de CHEFLIEU et ne laisser coché que la valeur 4 puis appuyez sur le bouton OK.

Affichage des communes qui sont chefs-lieux de département avec le nom de leur département

Mettez en pratique ce que vous avez appris pour :

1. Ajouter à la feuille "communes" le descripteur NDEP qui correspond au nom du département auquel appartient la commune.
   On effectuera pour cela un croisement des données avec la collection des départements (fichier dep2018.txt à importer dans le classeur Activite_1.ods) en utilisant le descripteur DEP qu'elle partage avec la collection des communes,

2. Faire afficher grâce à l'application d'un filtre uniquement les communes qui sont chefs-lieux de département avec le nom de leur département.