J’utiliserai PostgreSQL pour ce projet ; la majorité de la syntaxe est en SQL standard, elle fonctionnera dans d’autres bases de données comme MySQL ou BigQuery.

Base de données SQL

L’ensemble de données concerne les offres d’emploi en science des données sur GlassDoor et a été supprimé directement du site. Il contient 15 colonnes décrivant l’offre d’emploi et l’entreprise. Disponible en tant qu’ensemble de données public sur Kaggle.

Vous pouvez trouver plus d’ensembles de données pour pratiquer vos compétences de nettoyage ici.

Le projet est divisé en deux parties, la première a pour objectif de comprendre les données et de savoir quelles données doivent être nettoyées, de faire une liste de tous les points à traiter, puis, dans la deuxième partie, nous allons faire tout le ménage.

Connaître les données SQL

Tout d’abord, nous devons explorer nos données et comprendre le type de problèmes auxquels nous sommes confrontés pour rendre les données propres et prêtes pour l’analyse. En regardant simplement les noms des colonnes et les valeurs qu’elles contiennent, nous pouvons réaliser qu’il sera pratique de modifier le type de données et le format des valeurs, comme la colonne d’estimation du salaire, qui est actuellement une chaîne et n’est pas vraiment utile dans de cette façon et avec ce format.

Chaînes mal orthographiées

L’ensemble de données est censé concerner les offres d’emploi de Data Scientist, exécutons une instruction DISTINCT pour savoir combien d’offres d’emploi distinctes sont réellement dans ce domaine particulier.

SELECT DISTINCT job_title 
FROM jobs_glassdoor ;

Eh bien, ce ne sera pas si facile, il y a 172 titres de poste distincts sur 672 lignes dans l’ensemble de notre ensemble de données, et nous devrons les ajouter à la liste.

Vérification des plages de valeurs numériques.

À l’heure actuelle, nous avons deux colonnes numériques, et les deux ont une contrainte de plage qui doit suivre, la colonne de notation doit être comprise entre 0 et 5, et la colonne fondée ne doit pas être avant Christ. Nous pouvons voir une tendance ici; ils utilisent probablement “-1” pour identifier les valeurs NULL, nous devons absolument vérifier cela.

Deux valeurs dans une colonne

La valeur d’évaluation est ajoutée à la colonne du nom de l’entreprise ; nous devons diviser cette chaîne en deux et conserver le nom de l’entreprise. Ensuite, nous devons également diviser les colonnes de localisation et de siège social, toutes deux contenant des données géographiques, sera important pour diviser ces données en ville et état, de cette façon dans la phase d’analyse pourrait approfondir la compréhension des données.

Je vérifierai dans le reste des colonnes les chaînes mal orthographiées ou les valeurs nulles.

Après ce processus, nous savons maintenant que nous devons effectuer les étapes suivantes pour nettoyer nos données :

1. Changer le format de la colonne d’estimation de salaire et son type de données en nombre.
2. Normalise la colonne du titre du poste et crée des étiquettes pour un niveau de poste, comme Jr, Mid, Senior.
3. Éliminez les valeurs “-1” de la note et des colonnes trouvées, et explorez s’il y en a plus dans d’autres colonnes.
4. Suppression des valeurs de notation dans la colonne du nom de l’entreprise.
5. Diviser la ville et l’état en deux colonnes différentes à partir de l’emplacement et du siège.

Créer une table de sauvegarde

Avant de commencer à modifier nos données, il est préférable de faire une sauvegarde de notre table, de cette façon si nous commettons une erreur, nous pouvons mettre à jour nos données d’origine dans l’ancienne table.

CREATE TABLE jobs_glassdoor_backup AS 
SELECT * FROM jobs_glassdoor;

Nettoyer les données

Après avoir fait notre sauvegarde, nous pouvons maintenant commencer à modifier nos données sans aucun problème. Nettoyons ces données.

1.Changer le format de la colonne d’estimation de salaire et son type de données en nombre.

UPDATE jobs_glassdoor j1 
SET estimation_salaire = ( 
    SELECT SPLIT_PART(estimation_salaire, '(',1) 
    FROM jobs_glassdoor j2 
    WHERE j1.id = j2.id 
)

Ici, nous mettons à jour la colonne entière à l’aide d’une sous-requête, nous utilisons la fonction SPLIT_PART , qui est utilisée pour diviser une chaîne par un délimiteur donné et conserver une partie spécifique de la chaîne. Il reçoit trois paramètres, le premier est la chaîne que nous voulons diviser, le second est le délimiteur et le troisième est la partie que nous voulons conserver.

Maintenant, nous devons lire la sous-chaîne Glassdoor entre parenthèses, mais encore, notre colonne est une chaîne, et nous ne pouvons pas effectuer de calculs dessus, je pense que la meilleure solution étant donné que nous ne savons pas exactement quel est le salaire pour chaque emploi, créons deux nouvelles colonnes : min_salary_estmate et max_salary_estimate.

Remarque : Je ne suis pas sûr que ce soit la meilleure option pour effectuer ces prochaines requêtes SQL, veuillez commenter si vous en connaissez une meilleure.

ALTER TABLE jobs_glassdoor AJOUTER UNE COLONNE min_salary_estimate int ; 
ALTER TABLE jobs_glassdoor AJOUTER UNE COLONNE max_salary_estimate int ;UPDATE jobs_glassdoor j1 
SET min_salary_estimate = ( 
        SELECT CAST( 
               CONCAT( 
               SPLIT_PART(SPLIT_PART(salary_estimate, '$',2), 'K',1)     
               ,'000') 
            AS int) 
        FROM jobs_glassdoor j2 
        WHERE j1.id = j2.id 
);UPDATE jobs_glassdoor j1 
SET max_salary_estimate = ( 
        SELECT CAST( 
               CONCAT( 
               SPLIT_PART(SPLIT_PART(salary_estimate, '$',3), 'K',1)     
               ,'000') 
            AS int) 
        FROM jobs_glassdoor j2 
        WHERE j1.id = j2.id 
);

Tout d’abord, nous créons les deux nouvelles colonnes. Nous avons deux instructions UPDATE, chacune pour chaque colonne ; nous utilisons une sous-requête pour mettre à jour toute la colonne où les identifiants de chaque requête correspondent, en utilisant un SPLIT_PART imbriqué, nous obtenons la valeur numérique de la plage inférieure du salaire, toujours sous forme de chaîne, nous concaténons ‘000’ puis CAST la chaîne entière à un type entier pour correspondre à notre contrainte de type de données. Répétez ensuite le processus pour la colonne max_salary_estimate.

2. Normalise la colonne du titre du poste et crée des étiquettes pour un niveau de poste, comme Jr, Mid, Senior.

La première chose que nous devons faire est d’éliminer les lignes qui ne correspondent pas aux postes de data scientist, celles comme Data Analyst ou Data Engineer ; certains postes sont affichés en tant que “Machine Learning Scientist” ou “Deep Learning Scientist”, en utilisant une clause WHERE avec plusieurs conditions, nous pouvons éliminer ces lignes.

DELETE FROM jobs_glassdoor 
WHERE ( 
        job_title NOT LIKE '%Data Scientist%' 
    AND job_title NOT LIKE '%Machine Learning%' 
    AND job_title NOT LIKE '%Data Science%' 
    AND job_Title NOT LIKE '%Deep Learning%' 
      );

Bien que nous n’ayons déjà que les emplois de data scientist, la colonne manque toujours d’uniformité, nous allons séparer le niveau d’emploi pour les différents emplois dans une nouvelle colonne et attribuer “Data Scientist” à toutes les valeurs de la colonne job_title.

DÉMARRER LA TRANSACTION ;METTRE À JOUR jobs_glassdoor j1SET job_level = (                  SÉLECTIONNER                  CAS                  WHEN job_title LIKE ANY (array['%Sr%', '%Senior%', 
                 '%Lead%', '%Principal%','%Experienced%', 
                 '%Director%','%Manager%' ]) ALORS 'Senior'                  AUTREMENT 'Inconnu'                  END AS new_column                  DE jobs_glassdoor j2                  OÙ j1.id = j2.id);METTRE À JOUR jobs_glassdoorSET job_title = 'Scientifique des données' ;COMMETTRE;

Pour des raisons de sécurité, je recommande de toujours écrire nos mises à jour dans un bloc Transaction, de cette façon nous pouvons revenir en arrière si nous nous sommes trompés. Comme les seuls intitulés de poste qui précisent le niveau du poste sont les plus élevés, celui-ci sera le seul à avoir un niveau, les autres auront une valeur par défaut.

3. Éliminez les valeurs « -1 » de la note et les colonnes fondées

UPDATE jobs_glassdoor 
SET rating =( 
               SELECT 
               avg(rating) 
               FROM jobs_glassdoor) 
WHERE rating = -1 
RETURNING job_title, rating ;

J’ai défini la valeur moyenne de la colonne de notation sur toutes les valeurs manquantes, de cette façon, nous pouvons toujours effectuer des calculs dans une analyse plus approfondie.

Nous allons passer aux valeurs NULL dans le reste des colonnes qui contiennent des valeurs “-1”.

METTRE À JOUR jobs_glassdoorSET taille =NULLWHERE taille = '-1';

J’ai trouvé une meilleure solution que de répliquer ces trois lignes de code pour chaque colonne de l’ensemble de données, si vous connaissez une meilleure option, faites-le moi savoir.

4. Suppression des valeurs d’évaluation dans la colonne du nom de l’entreprise.

Comme certaines entreprises n’ont pas de note, toutes les valeurs de cette colonne n’ont pas de chiffres à la fin, nous ne pouvons donc pas utiliser la fonction SUBSTRING(), à la place, nous utiliserons des expressions régulières avec REGEXP_REPLACE().

UPDATE jobs_glassdoor as j1 
SET company_name = ( 
                 SELECT 
                 REGEXP_REPLACE(company_name, '\d\D\d', ' ') 
                 FROM jobs_glassdoor as j2 
                 WHERE j1.id = j2.id 
);

5. Diviser la ville et l’état en deux colonnes différentes à partir de l’emplacement et du siège social.

ALTER TABLE jobs_glassdoor ADD COLUMN city_location text;ALTER TABLE jobs_glassdoor ADD COLUMN state_location text ;METTRE À JOUR jobs_glassdoor j1SET city_location =(                   SÉLECTIONNER                   SPLIT_PART(lieu,',',1)                   DE jobs_glassdoor j2                   OÙ j1.id = j2.id);METTRE À JOUR jobs_glassdoor j1                   SET state_location =(                   SÉLECTIONNER                   SPLIT_PART(lieu,',',2)                   DE jobs_glassdoor j2                  OÙ j1.id = j2.id);

Tout d’abord, nous ajoutons quelques colonnes pour séparer nos valeurs de la colonne de localisation, une pour la ville et une pour l’état. Ensuite, nous utilisons la clause UPDATE pour définir les valeurs de notre nouvelle colonne city_location, en utilisant la fonction SPLIT_PART, nous pouvons séparer chaque chaîne par “,” et conserver simplement la première partie de la chaîne, qui est la ville de l’emplacement , nous avons répété ce processus pour remplir la colonne d’état, cette fois en prenant la deuxième partie de la chaîne après la scission. Nous effectuons également le processus de trou pour la colonne du siège.

Conclusion

Il est certainement plus facile de nettoyer des données dans un langage de programmation comme Python ou R, c’est probablement aussi plus facile dans Excel ou Google Sheets. Pourtant, savoir comment nettoyer les données en SQL est une compétence intéressante à avoir.

Si vous souhaitez vérifier l’intégralité du code, vous pouvez le consulter sur mon Github.

Votez : (7 votes, average: 1,29 out of 5)

Loading...