Gestion, analyse et présentations de données

L'objectif de ce POK est d'apprendre comment on nettoie et on gère de la donnée, mais également de montrer comment on présente et traite cette donnée.

Lors de mes stages, j'ai pu constater que les données constituent un atout majeur pour une entreprise. Les données vont permettre de pouvoir faire une analyse des performances de l'entreprise, mais également de faire des prévisions afin d'ajuster les processus. L'enjeu majeur que constituent les données est également visible lorsque celles-ci sont incomplètes ou erronées, faussant voir paralysant certaines analyses et donc processus. C'est pourquoi j'ai décidé de m'intéresser à la gestion, la manipulation, l'analyse et la présentation de données. Ce sujet est très vaste mais je vais le découper en sous-thématiques :

• Manipulation et navigation de bases de données relationnelles (SQL)
• Création d'une base de données et analyse de son contenu en SQL

Tâches

Sprints

Le but final est la compréhension de la gestion des données, depuis la création d'une base de données, jusqu'à l aprésentation de celles-ci, en passant par leur analyse.

Sprint 1

• [x] Apprentissage du langage SQL pour naviguer au sein d'une base de données
• [x] Application à une base de données
• [x] Création d'une base de données
• [x] Analyse et tests sur la données de cette table

Sprint 2

• [ ] Apprentissage d'une méthode d'analyse de données (Python ?)
• [ ] Application afin d'analyser les données d'une table
• [ ] Présentation de cette données

Horodatage

Premier Sprint

Gestion de bases de données et de tables en SQL

Afin d'illustrer les différentes fonctions que nous allons présenter, nous utiliserons deux tables :

• villes_france_free comprenant les villes de France et leurs informations (nom, numéro de département, canton, population en 1999, 2010 et 2012, densité, longitude et latitude, surface,...).
• departement comprenant les départements de France et leurs numéros associés.

Extraire des données d'une table

Afin d'extraire des données d'une base de données relationnelle, on utilise des fonctions SQL :

Quelles sont les villes du Vaucluse (84) ?

SELECT * FROM villes_france_free WHERE ville_departement=84;

Quelles sont les 10 villes les moins peuplées de France en 2012 ?

SELECT * FROM villes_france_free
ORDER BY ville_population_2010 ASC
LIMIT 10;

Fonctions d'aggrégation

Une fonction d'aggrégation est une fonction qui réalise des calculs sur un groupe de données et qui renvoie une valeur unique.

Quelles est la population de la ville la plus peuplée du Vaucluse (84) ?

SELECT MAX(ville_population_2012)
FROM villes_france_free
WHERE ville_departement=84;

Combien de villes en France possède un nom débutant par 'Saint' ?

SELECT COUNT(ville_nom)
FROM villes_france_free
WHERE ville_nom LIKE 'SAINT%';

Quels sont les noms de ville donnés plusieurs fois, du plus au moins rare ?

SELECT ville_nom AS 'NOM VILLE', COUNT(ville_nom) AS 'NOMBRE DE VILLE'
FROM villes_france_free
GROUP BY ville_nom
HAVING COUNT(ville_nom)>1
ORDER BY COUNT(ville_nom) DESC;

Modifier une table

Il peut être nécessaire de modifier les données d'une table afin de la mettre à jour. Plusieurs fonctions peuvent être utiles :

Ajouter des données

La syntaxe à adopoter est dans ce cas la suivante :

INSERT INTO nom_de_la_table (colonne 1, colonne 2, colonne 3, ...)
VALUES 
(Value 1.1, Value 1.2, Value 1.3, ...),
(Value 2.1, Value 2.2, Value 2.3, ...);

NB: Si on ajoute une valeur pour chaque colonne, il n’est pas nécessaire de préciser le nom de chaque colonne dans les parenthèses après le nom de la table, mais il faut bien respecter l’ordre des données.

Ajouter la ville de New-York à la table villes_france_free.

INSERT INTO villes_france_free(ville_id,ville_code_commune,ville_nom) VALUES(000, 000, 'NEW YORK');
SELECT * FROM villes_france_free;

Supprimer des données

Exemple : Supprimer tous les éléments de la table villes_france_free se ferait avec la requête DELETE FROM villes_france_free;

Modifier des données

Mise à jour des données concernant la population de New-York

UPDATE villes_france_free 
SET ville_population_2012=8347000, ville_population_2010=819000, ville_population_1999=7428000
WHERE ville_nom='NEW YORK';

Combiner plusieurs tables

En entreprise, tous les employés n’ont pas accès à toutes les données, certaines étant classifiée sensibles. Ainsi, différentes bases de données (ou tables) peuvent être crées avec des niveaux de confidentialité différents. Afin d’extraire des données complètes, il peut être utile d’extraire des données provenant de différentes tables, c’est-ce qu’on appelle des jointures.

Jointures

Il existe différents types de jointure :

• (INNER) JOIN : renvoie les lignes ayant correspondances dans les deux tables
• LEFT (OUTER) JOIN : renvoie toutes les lignes de la table de gauche, et les données correspondantes dans la table de droite
• RIGHT (OUTER) JOIN : renvoie toutes les lignes de la table de droite, et les données correspondantes dans la table de gauche
• FULL (OUTER) JOIN : renvoie toutes les lignes quand il y a une correspondance dans la table de gauche ou de droite
• SELF JOIN : réalise une jointure d’une table avec elle-même

Source : w3schools

Dans notre exemple, nous avons dans la table villes_france_free les départements uniquement renseignés par numéro. Nous allons tenter de réaliser une jointure avec la table departement pour renseigner également le nom du département.

SELECT t1.ville_departement, t1.ville_nom, t2.departement_nom
FROM villes_france_free.villes_france_free AS t1
JOIN departement.departement AS t2 
ON t1.ville_departement = t2.departement_code
ORDER BY t1.ville_nom;

Création d'une base de données, d'une table

Fonctions pour créer une base de données, une table

Contraintes

Il est possible d’ajouter des contrainte sur une colonne lors de la création d’une table. Les contraintes possibles sont les suivantes :

Application : Création et analyse du contenu d'une base de données

Nous allons tenter de créer une base de données afin de pouvoir réaliser une manipulation des données en langage SQL. Pour cela, nous allons nous intéresser aux médailles remportées par pays, durant les jeux Olympiques d'hiver entre 1924 et 2010. Pour cela, nous allons tout d'abord créer un base de données et un table vide.

Création de la base de données et la table

Nous allons créer une base de données intitulée winter_medals, dans laquelle nous allons créer une table medals contenant les informations suivantes :

• Identifiant id
• Année de l'édition pendant laquelle la médaille a été obtenue, years
• Nom de la discipline, sport
• La couleur de la médaille, medal
• Le code du pays, country
• Le nom complet du pays, pays
• un boolen indiquant si le pays accueillait les jeux lorsqu'il a obtenu la médaille ou non, host

CREATE DATABASE winter_medals;
CREATE TABLE medals (
    id INT,
    years INT,
    sport VARCHAR(255),
    medal VARCHAR(255),
    country VARCHAR(255),
    pays VARCHAR(255),
    host VARCHAR(255)
);

Chargement des données

Afin de récolter les données, j'ai utilisé le site Data Gouv permattant d'accéder à un certain nombre de données. J'ai ensuite modifié quelques typographies sur Excel afin de pouvoir charger les données dans la table medals

INSERT INTO medals (id, years, sport, medal, country, pays, host) VALUES
(131666,1924,'biathlon','gold','SUI','Switzerland','FALSE'),
(231666,1924,'biathlon','silver','FIN','Finland','FALSE'),
(331666,1924,'biathlon','bronze','FRA','France','TRUE'),
(431666,1960,'biathlon','gold','SWE','Sweden','FALSE'),
(531666,1960,'biathlon','silver','FIN','Finland','FALSE'),
(631666,1960,'biathlon','bronze','URS','USSR','FALSE'),
(731666,1964,'biathlon','gold','URS','USSR','FALSE'),
(831666,1964,'biathlon','silver','URS','USSR','FALSE'),
(931666,1964,'biathlon','bronze','NOR','Norway','FALSE'),
[…]
(256732514,2010,'snowboard','silver','RUS','Russian Federation','FALSE'),
(256832514,2010,'snowboard','bronze','AUT','Austria','FALSE'),
(256932514,2010,'snowboard','gold','CAN','Canada','TRUE'),
(257032514,2010,'snowboard','silver','FRA','France','FALSE'),
(257132514,2010,'snowboard','bronze','SUI','Switzerland','FALSE');

On obtient ainsi une table contenant 2571 lignes.

On remarque que le la colonne id est en fait une clé primaire, dont les 5 derniers chiffres renvoient au type d’épreuve, et les chiffres avant correspond au numéro de la ligne. Il est donc intéressant de changer le type de la première colonne pour indiquer que c’est une clé primaire :

ALTER TABLE medals ADD PRIMARY KEY (id);

Etude des données

Grâce à la création de cette base de données, nous allons pouvoir répondre de manière plus simple aux questions suivantes, qui nous permettent de débuter une analyse de la donnée :

• Q1 : Quel est le tableau des médailles pour l'ensemble des éditions de 1924 à 2010 ?
• Q2 : Quel pays est en tête du classement des médailles par édition ?
• Q3 : Quel est le classement dans le tableau des médailles de chaque pays par édition ?
• Q4 : Sur l'ensemble des éditions entre 1924 et 2010, quel pays est le meilleur dans chaque discipline ?
• Q5 : La performance du pays organisateur (en terme de nombre de médailles) est elle supérieur que lorsque le même pays n'est pas organisateur ?

Question 1 : Quel est le tableau des éditions entre 1924 et 2010, quel pays est le meilleur dans chaque discipline ?

Lors des JO, les pays sont classés selon un tableau des médailles, classant les pays en fonctions du nombre de médailles d'or, puis de médailes d'argent, et enfin de médailles de bronze.

SELECT pays AS 'Pays',
       COUNT(medal) AS 'Total Médailles',
       SUM(CASE WHEN medal = 'GOLD' THEN 1 ELSE 0 END) AS 'GOLD',
       SUM(CASE WHEN medal = 'SILVER' THEN 1 ELSE 0 END) AS 'SILVER',
       SUM(CASE WHEN medal = 'BRONZE' THEN 1 ELSE 0 END) AS 'BRONZE'
FROM medals
GROUP BY pays
ORDER BY GOLD DESC, SILVER DESC, BRONZE DESC;

Le résultat de la requête est donc le tableau des médailles suivant, réalisé sur l'ensemble des médailles reçues lors des éditions de 1924 à 2010 :

Question 2 : Quel pays est en tête du classement des médailles par édition ?

Nous avons réalisé un tableau des médailles sur l'ensemble des éditions entre 1924 et 2010, mais il est intéressant d'extraire le pays en tête du classement lors de chacune des éditions.

SELECT m.years AS 'Année', m.pays AS 'Meilleur Pays', m.nombre_or AS 'Nombre de Titres'
FROM (
    SELECT years, pays,
           SUM(CASE WHEN medal = 'GOLD' THEN 1 ELSE 0 END) AS nombre_or
    FROM medals
    GROUP BY years, pays
) AS m
JOIN (
    SELECT years, MAX(nombre_or) AS max_or
    FROM (
        SELECT years, pays,
               SUM(CASE WHEN medal = 'GOLD' THEN 1 ELSE 0 END) AS nombre_or
        FROM medals
        GROUP BY years, pays
    ) AS subquery
    GROUP BY years
) AS max_counts ON m.years = max_counts.years AND m.nombre_or = max_counts.max_or
ORDER BY m.years;

On obtient ainsi le table suivante, donnant le meilleur pays par année en nombre de titre (médailles d'or) : Remarque : On pourrait être plus précis en prenant en compte égalelement le nombre de médailles d'argent et de bronze

Question 3 : Quel est le classement dans le tableau des médailles de chaque pays par édition ?

Toujours afin d'étudier les performances des pays par édition, nous pouvons essayer d'extraire le classement dans le tableau des médailles par année.

WITH classement AS (
    SELECT pays AS 'Pays',
           years AS 'Année',
           RANK() OVER (
               PARTITION BY years
               ORDER BY 
                   SUM(CASE WHEN medal = 'GOLD' THEN 1 ELSE 0 END) DESC,
                   SUM(CASE WHEN medal = 'SILVER' THEN 1 ELSE 0 END) DESC,
                   SUM(CASE WHEN medal = 'BRONZE' THEN 1 ELSE 0 END) DESC
           ) AS 'Classement'
    FROM medals
    GROUP BY years, pays
)
SELECT Pays, Année, Classement
FROM classement
ORDER BY Pays, Année;

Le résultat obtenu est le suivant :

Question 4 : Sur l'ensemble des éditions entre 1924 et 2010, quel pays est le meilleur dans chaque discipline ?

La base de données nous permet également de pouvoir réaliser une étude sur le pays le meilleur (en nombre de titre) en fonction du sport.

SELECT m.sport AS 'Discipline', m.pays AS 'Meilleur Pays', m.nombre_or AS 'Nombre de Titres'
FROM (
    SELECT sport, pays,
           SUM(CASE WHEN medal = 'GOLD' THEN 1 ELSE 0 END) AS nombre_or
    FROM medals
    GROUP BY sport, pays
) AS m
JOIN (
    SELECT sport, MAX(nombre_or) AS max_or
    FROM (
        SELECT sport, pays,
               SUM(CASE WHEN medal = 'GOLD' THEN 1 ELSE 0 END) AS nombre_or
        FROM medals
        GROUP BY sport, pays
    ) AS subquery
    GROUP BY sport
) AS max_counts ON m.sport = max_counts.sport AND m.nombre_or = max_counts.max_or
ORDER BY m.sport;

On obtient ainsi la table suivante :

Question 5 : La performance du pays organisateur (en termes de nombre de médailles) est-elle supérieur que lorsque le même pays n'est pas organisateur ?

Lors de différents évènements sportifs, il existe un match aller, et un match retour. Ceci est organisé car lorsqu'une équipe joue à domicile, ses performances sont souvent meilleures. Il est donc intéressant de regarder si ce phénomène est visible dans les résultats des différents pays aux JO d'hiver.

SELECT pays AS 'Pays', COUNT(medal) AS 'Nombre_Médailles'
FROM medals
WHERE host = 'FALSE'
GROUP BY pays
ORDER BY pays;

SELECT pays AS 'Pays',
       COUNT(years) AS 'Nombre_Organisateur',
       SUM(nombre_medaille) AS 'Total_Médailles'
FROM (
    SELECT years, pays, COUNT(medal) AS nombre_medaille
    FROM medals
    WHERE host = 'TRUE'
    GROUP BY years, pays
    ORDER BY years
) AS sous_requete
GROUP BY pays
ORDER BY 'Nombre Organisateur' DESC;

SELECT not_host.Pays AS Pays, not_host.Nombre_Médailles AS medailles_ext, host.Nombre_Organisateur AS Nbre_orga, host.Total_Médailles AS medailles_dom
FROM(
    SELECT pays AS 'Pays', COUNT(medal) AS 'Nombre_Médailles'
    FROM medals
    WHERE host = 'FALSE'
    GROUP BY pays
    ORDER BY pays) AS not_host
JOIN (
    SELECT pays AS 'Pays',
       COUNT(years) AS 'Nombre_Organisateur',
       SUM(nombre_medaille) AS 'Total_Médailles'
    FROM (
        SELECT years, pays, COUNT(medal) AS nombre_medaille
        FROM medals
        WHERE host = 'TRUE'
        GROUP BY years, pays
        ORDER BY years
    ) AS sous_requete
    GROUP BY pays
    ORDER BY 'Nombre Organisateur' DESC) AS host
ON not_host.Pays=host.Pays;

SELECT Pays,
    ROUND(medailles_dom/Nbre_orga,1) AS Moyenne_médailles_domicile,
    ROUND(medailles_ext/(21-Nbre_orga),1) AS Moyenne_médailles_extérieur
FROM(
    SELECT not_host.Pays AS Pays, not_host.Nombre_Médailles AS medailles_ext, host.Nombre_Organisateur AS Nbre_orga, host.Total_Médailles AS medailles_dom
    FROM(
        SELECT pays AS 'Pays', COUNT(medal) AS 'Nombre_Médailles'
        FROM medals
        WHERE host = 'FALSE'
        GROUP BY pays
        ORDER BY pays) AS not_host
    JOIN (
        SELECT pays AS 'Pays',
        COUNT(years) AS 'Nombre_Organisateur',
        SUM(nombre_medaille) AS 'Total_Médailles'
        FROM (
            SELECT years, pays, COUNT(medal) AS nombre_medaille
            FROM medals
            WHERE host = 'TRUE'
            GROUP BY years, pays
            ORDER BY years
        ) AS sous_requete
        GROUP BY pays
        ORDER BY 'Nombre Organisateur' DESC) AS host
    ON not_host.Pays=host.Pays) AS under_query;

Le résultat obtenu est la table suivante :

On remarque à l'aide de cette table que pour la totalité des pays ayant organisé les JO d'hiver (excepté pour la Suisse et l'Allemagne), les performances à domicile sont, au pire, égales à celles à l'extérieur, mais pour la plupart, les performances à domicile sont significativement meilleures (en termes de nombre total de médailles obtenues).

Conclusion Sprint 1

Ce premier sprint nous a permis de comprendre comment créer une base de données relationnelles et comment extraire des tables afin de réaliser les prémices d'une analyse de données en SQL. Cependant, le langage SQL n'est pas le plus adapté pour réaliser l'analyse de données. Nous explorerons plus en profondeur ceci lors du second sprint.

Second Sprint