Dédoublonner et croiser des données avec Excel, Access, Notepad++

Tous les jours au bureau dans vos tâches quotidiennes vous êtes amenés à manipuler des listes de données : contacts, emails, résultats 2012 vs 2011, produits vendus par tel secteur vs tel autre secteur,…  Au moment de réconcilier vos données, vous pouvez avoir envie de dédoublonner , croiser, ou comparer vos listes pour en extraire l’info utile.

Toutes ces tâches font appel à des opérations basiques appartenant à la théorie des ensembles : Intersection, Réunion, Différence. Ce que je vous propose, c’est de voir concrètement comment on peut les effectuer avec des outils comme Excel, Access, Notepad++ simplement.

Au sommaire :

1. Prérequis
2. Les données sur lesquelles on va travailler
3. Intersection : Croiser des données
4. Réunion : Dédoublonner des données
5. Différence : Connaître les items exclusifs à une liste

Prérequis

Pour faire toutes nos manipulations, nous aurons besoin de 3 logiciels :

• Notepad++ pour retravailler nos données brutes
• Excel (inclus dans Office) pour croiser des listes avec moins de 65 000 lignes (Excel 2010 permet de gérer 1 M de lignes)
• Access (inclus dans le pack Office pro) au delà de 65 000 lignes

Et également de 2 fichiers de données pour faire les croisements, dédoublonnages, différences. Pour qu’on travaille sur les mêmes bases, je vais utiliser les données statistiques des articles de Wikipedia disponibles ici : http://dumps.wikimedia.org/other/pagecounts-raw/

Les données sur lesquelles on va travailler

Je vous propose de prendre les fichiers de stats wikipedia de ces 2 journées de 17h à 18h  :

1. 6 octobre 2011
2. 6 octobre 2012

Le 6 octobre, c’est le jour de ma naissance… Ca nous permettra de voir concrètement ce qui a progressé en 1 an en terme d’articles (croisement), combien d’articles différents on consulte en 1h et en 2h (dédoublonnage),  et quels nouveaux articles on a consulté en 2012 (différence).

Chaque fichier brut fait 400 méga une fois dezippé, Grâce à Notepad++, on va pouvoir garder que ce qui nous intéresse : les articles en français de wikipedia  (400 000 lignes, pour access donc) et les mots du wiktionnaire (30 000 lignes, pour excel)  sur 2011 et 2012.

Le plus simple c’est que je vous montre en vidéo comment je fais.

Intersection : Croiser des données

Soient 2 listes de données A et B contenant différents champs (mails, articles, produits,…), on cherche à trouver les champs qui sont à la fois dans A et dans B.

Comment croiser des données dans Excel ?

On le fait simplement grâce aux tableaux croisés dynamiques. On va le faire avec un cas concret : stats wiktionnaire 2012 (A) vs stats wiktionnaire 2011 (B)… Je vous propose de répondre à ces 2 questions :

1. Quels sont les articles du dico wikipedia qui sont à la fois consultés en 2012 et 2011 ? A^B
2. Quels sont ceux qui progressent le plus en 1 an ? (A/B -1 trié par ordre décroissant)

Illustration en vidéo

Comment croiser des données dans Access ?

Si vous avez plus de 65 000 lignes à croiser, mieux vaux utiliser Access. Il va nous falloir importer nos 2 fichiers CSV wikipedia 2012 et 2011, créer 2 tables associées, créer une requête croisée, et établir une jointure entre les articles 2012 et 2011. On ajoutera aussi un champ calculé pour analyser les progressions. On va répondre aux 2 mêmes questions qu’avant.

Ça peut paraître chaud à faire au 1er abord, alors on va encore vous illustrer ça avec une vidéo

Réunion : Dédoublonner des données

Soient 2 listes de données A et B contenant différents champs (mails, articles, produits,…), on cherche à avoir tous les champs qui sont  dans A ou dans B. La difficulté réside dans le fait de ne pas compter 2 fois un même champ.

Comment dédoublonner des données dans Excel ?

Là, les techniques sont nombreuses !

On peut par exemple faire un tableau croisée dynamique si on a moins de 10 000 lignes entre nos 2 listes et afficher le champ qu’on veut dédoublonner en ligne. On pourra mettre un contrôle à 1 dans une colonne affichage pour voir les mots qui sont présents dans 2 listes (valeur à 2)

On peut également faire un Filtre élaboré / avancé (2) sur la colonne de son choix(1)  en indiquant qu’on souhaite éliminer les doublons (3) comme ceci :

Et dans Excel 2010, on a même une touche magique « Supprimer les doublons »

Comment dédoublonner des données dans Access ?

Ayant une utilisation moins intensive d’ACCESS, je vais être moins bavard sur les exemples pour dédoublonner les données. J’en ai un assez simple qui me vient en tête, celui qui consiste à créer une requêtes Union en mode SQL :

SELECT [TableA.champ]
FROM [TableA]

UNION

SELECT [TableB.champ]
FROM [TableB]

Si on repart sur les tables que j’ai créé dans ma vidéo plus haut, ça donnerait ça en mode SQL (pas d’union possible en mode création) :

Différence : Connaître les items exclusifs à une liste

Soient 2 listes de données A et B contenant différents champs (mails, articles, produits,…), on cherche à avoir tous les champs qui sont  dans A et pas dans B. On s’intéresse donc à ce qu’il y a d’exclusif en A par rapport à B.

Comment connaître les items exclusifs dans Excel ?

On peut là encore  faire un tableau croisée dynamique si on a moins de 10 000 lignes entre nos 2 listes et  mettre un filtre « champs vides » sur la colonne B (ici 2011).

En 2012, on a cherché pour la 1ère fois Swag, Bifle, poke, point Godwin.

Comment connaître les items exclusifs dans Access ?

Comme dans la vidéo, on va faire une requête croisée. La jointure sera différente, vous allez choisir plutôt l’option 2, je veux tous Wikipedia 2012 et ceux de Wikipedia 2011 égaux :

Pour ne garder que les articles Wikipedia 2012 qu’on ne consultait pas en 2011, on va filtrer  la colonne 2011 en ne gardant que les champs vides (comme pour le tableau croisé dynamique excel précédent).

J’ai trié par hits décroissant pour qu’on voit les grosses nouvelles entrées 2012… sans ce traitement, je n’aurais vu apparaître le Gangnam Style qui n’existait pas en 2011.

Le mot de la fin

J’espère qu’avec tous ces petits trucs, vous ne passerez plus des heures au bureau à analyser vos listes de données à la mano. Pour bien comprendre comment on gère les ensemble avec Excel ou Access, je vous encourage vivement à bien regarder les 2 vidéos dans intersection…

• Share on Facebook.
• Share on Twitter.