Acquisition de données depuis Excel

Lire des données depuis Excel

La première tâche du prétraitement des données consiste à obtenir les données. Il existe différents types de sources de données qui récupèrent des données, telles que des fichiers, des bases de données et des sites Web. Cette fois, je lis souvent des données,

Fichier Excel
Base de données

Apprenez à obtenir des données à partir de. En outre, l'acquisition de données à partir de CSV a été introduite dans le nettoyage des données.

Il est facile d'utiliser des pandas pour lire les données d'Excel. Si vous utilisez des pandas, vous pouvez obtenir des données au format pandas DataFrame après avoir lu les données. Le traitement ultérieur sera également plus facile à écrire. Pour les pandas, consultez À propos du traitement des données avec les pandas.

Pour lire des fichiers Excel avec des pandas
Fonction pd.read_excel(io, sheetname)Est utilisé.

Pour ce premier argument, io

Fonction pd.ExcelFile('Le nom du fichier que vous souhaitez lire')
Il est possible de transmettre l'objet ExcelFile créé dans.

Consultez la documentation officielle des pandas pour plus d'informations sur la fonction pd.read_excel ().

import pandas as pd

xlsx = pd.ExcelFile('Le nom du fichier que vous souhaitez lire')
#Création d'un objet ExcelFile

df = pd.read_excel(io, sheetname)
#Importer un fichier Excel avec des pandas

Combiner les données lues par Excel

Auparavant, j'ai chargé une feuille de fichier Excel Dans le prétraitement proprement dit, les données stockées dans plusieurs feuilles sont organisées et enregistrées. Vous souhaiterez peut-être l'utiliser pour l'analyse en tant qu'ensemble de données.

À titre d'exemple concret, considérons la situation où chaque feuille contient les données suivantes.

Dans cette situation, quel type de données faut-il créer lorsque vous souhaitez utiliser à la fois le prix de vente et le prix catalogue du produit vendu comme données d'analyse?

Pour ce faire, vous devez combiner les données des deux feuilles avec product_id pour créer les données suivantes.

#Cette opération de jointure est une fonction pandas
pd.merge(left, right, on)
#Est utilisé.

Si vous décrivez schématiquement les arguments de la fonction de fusion qui doivent être utilisés dans le cas de l'exemple précédent

pd.merge (cadre de données de la feuille de vente, cadre de données de la feuille de produit, on = 'product_id') On dirait.

Pour plus d'informations sur la fonction de fusion, consultez la documentation officielle. Ce qui suit est un exemple.

import pandas as pd
xlsx = pd.ExcelFile('nom de fichier')
df1 = pd.read_excel(xlsx, 'employees')
df2 = pd.read_excel(xlsx, 'projects')
df3 = pd.read_excel(xlsx, 'project_assigns')

#Veuillez écrire votre réponse ici
df13 = pd.merge(df1, df3, on='employee_id')
df123 = pd.merge(df13, df2, on='project_id')
df123

Affiner les données lues par Excel

Parmi les données suivantes Seuls les membres participants du "Projet de marketing" et du "Projet de développement de système" Et si je veux savoir?

Dans ce cas, vous devez affiner les données du tableau ci-dessus.

Il y a plusieurs moyens de le faire

#fonction pandas
DataFrame.query
#Cela rend le code plus facile à écrire et à lire.

Dans la fonction DataFrame.query, diverses conditions peuvent être spécifiées sous forme de chaînes comme suit.

df.query('employee_id > 1')
df.query('(employee_id > 1) & (project_id > 3)')
df.query('project_id in [1, 2]')
df.query('project_id == [1, 2]')

Si vous souhaitez connaître uniquement les membres participants de "Projet marketing" et "Projet de développement système" Puisque nous avons seulement besoin de restreindre le project_id à ceux avec 1 et 3, écrivez comme suit.

df.query('project_id in [1, 3]')
#Ou
df.query('project_id == [1, 3]')

Voir la documentation officielle pour divers exemples de DataFrame.query.

Agrégation des données lues par Excel

Dans la liste des membres du projet Vous voudrez peut-être connaître le nombre de membres dans chaque projet. Pour ce faire, nous allons agréger et regrouper les données pour chaque projet. Vous devez connaître le nombre de données dans chaque groupe.

#Ce sont des pandas
DataFrame.groupby
#Vous pouvez utiliser cette méthode.

DataFrame.groupby vous permet de spécifier les colonnes à regrouper au format suivant:

df.groupby('Nom de la colonne que vous souhaitez regrouper')
#Si vous souhaitez regrouper par plusieurs colonnes, procédez comme suit:

df.groupby(['Colonne 1', 'Colonne 2'])

La méthode groupby renvoie un objet GroupBy, pas un objet DataFrame. L'objet GroupBy définit les méthodes pratiques count (), mean (), sum (), max (), etc. que vous souhaitez appliquer à chaque groupe.

Consultez la documentation officielle pour les méthodes disponibles dans l'objet GroupBy.

Cliquez ici pour des exemples pratiques

import pandas as pd
xlsx = pd.ExcelFile('nom de fichier')
df1 = pd.read_excel(xlsx, 'employees')
df2 = pd.read_excel(xlsx, 'projects')
df3 = pd.read_excel(xlsx, 'project_assigns')

df13 = pd.merge(df1, df3, on='employee_id')
df = pd.merge(df13, df2, on='project_id')
df.groupby('project_name').count()['employee_id']

Résultat de sortie

Récupérer les données de la base de données

Lire les données de la base de données

Lors de la lecture des données de la base de données en prétraitement, il est pratique d'utiliser la fonction read_sql de pandas pour obtenir les données au format DataFrame. Les informations suivantes sont nécessaires pour se connecter à la base de données.

Type de base de données à laquelle se connecter (MySQL, PostgreSQL, Oracle, etc.)
Informations de connexion à la base de données de destination de connexion (nom de la base de données, nom d'hôte, numéro de port, informations d'authentification de l'utilisateur de connexion, code de caractère)
Informations sur le pilote utilisé pour la connexion (mysqlclient, mysqlDB, mysql-connector-python, oursql, etc.)
Mysqlclient est un pilote MySQL exploité par SQL Alchemy et n'apparaît pas dans le code.

import sqlalchemy as sqla
engine = sqla.create_engine('mysql+mysqldb://ai:ai@mysql-service:3307/ai_mlprep?charset=utf8')
# create_Le format de la chaîne de connexion de l'argument du moteur est le suivant.

Base de données de connexion+Nom du conducteur://Nom d'utilisateur connecté:mot de passe@nom d'hôte:numéro de port/Nom de la base de données?charset=Code de caractère

Pour lire les données d'une table de base de données, utilisez l'instruction SQL SELECT suivante.

SELECT
Nom de la colonne de table 1,
Nom de la colonne de table 2
FROM nom de la table

Utilisez également la fonction pandas read_sql comme suit. L'écriture de la chaîne SQL avec '' '(guillemets triples) avec des sauts de ligne facilite la lecture du code.

pd.read_sql('''
SELECT
  column1,
  column2
FROM table1
''', engine)

Dans le deuxième argument de read_sql, transmettez le moteur de connexion à la base de données créé par sqlalchemy.

Combiner des données à l'aide de JOIN

Le tableau suivant est préparé.

À ce stade, que dois-je faire si je souhaite comparer le prix catalogue du produit réellement vendu avec le prix de vente? Pour ce faire, utilisez une colonne commune dans la table des produits qui inclut le prix de vente et la table des produits qui contient le prix catalogue. Vous devrez rejoindre les tables.

Après avoir lu chaque tableau au format DataFrame, comme lors de la lecture de données depuis Excel Vous pouvez également combiner chaque DataFrame avec la fonction de fusion pandas, Si vous avez une table dans votre base de données, utilisez SQL Le processus de jointure peut être écrit de manière concise et seules les données de résultat de la jointure peuvent être obtenues.

Pour joindre la table A et la table B de la base de données en utilisant chaque colonne 1 comme clé Écrivez SQL en utilisant JOIN ON comme indiqué ci-dessous.

SELECT
Tableau A.Colonne 1,
Tableau B.Colonne 1
FROM
Tableau A
JOIN table B ON table A.Colonne 1=Tableau B.Colonne 1

Lors de la jonction de trois tables ou plus comme indiqué ci-dessous, JOIN ON est décrit successivement.

SELECT
Tableau A.Colonne 1,
Tableau B.Colonne 1
FROM
Tableau A
JOIN table B ON table A.Colonne 1=Tableau B.Colonne 1
JOIN table C ON table B.Colonne 2=Tableau C.Colonne 2

Filtrer les données avec WHERE

Il a la même structure de table.

Lorsque vous effectuez une analyse, par exemple, vous souhaitez connaître les tendances concernant la vente des boissons. Et si j'ai une exigence?

Pour ce faire, vous devez effectuer les deux opérations suivantes.

Les informations sur la date de vente sont incluses dans le tableau des ventes, et Les informations sur les produits vendus étant incluses dans le tableau des produits vendus, les deux tableaux sont joints.

Étant donné que je souhaite limiter les résultats de sortie aux boissons uniquement, je me limite uniquement au product_id des boissons.

Parmi les produits inclus dans le tableau des produits, les seules boissons sont le thé avec product_id 4 et le jus d'orange avec product_id 6.

Dans ce cas, dans la clause WHERE de SQL
product_id =4 et produit_id =Spécifiez 6 pour affiner la sortie.

Voici comment écrire un SQL qui répond aux exigences ci-dessus et inclut une clause WHERE pour filtrer la sortie:

SELECT
  product_id,
  sales_year,
  sales_month
FROM
  mlprep_sales_products JOIN mlprep_sales ON mlprep_sales_products.sales_id = mlprep_sales.sales_id
WHERE
  mlprep_sales_products.product_id = 4 OR
  mlprep_sales_products.product_id = 6

Comme mentionné ci-dessus, vous pouvez utiliser OR pour spécifier plusieurs conditions dans la clause WHERE. De plus, dans ce qui précède, la condition est décrite en utilisant =, mais des nombres d'inégalité tels que peuvent également être utilisés dans la description de la condition. Bien que cela ne soit pas montré dans l'exemple, il est possible de signaler plusieurs conditions en utilisant AND en plus de OR. Lorsque AND est utilisé, seules les données qui remplissent toutes les conditions multiples sont sorties.

Agréger les données à l'aide de GROUP BY

Il a à nouveau la même configuration.

Compte tenu de ces données, le prix de vente total de tous les produits par année et par mois Et si je veux l'utiliser pour l'analyse?

Vous pouvez utiliser GroupBy de pandas, mais vous pouvez faire de même avec SQL.

Pour calculer le prix de vente total pour chaque année Il ne peut pas être réalisé en combinant avec JOIN ou en réduisant avec WHERE. Les deux points suivants doivent être abordés.

Étant donné que le tableau des ventes contient les données pour 1, l'année et le mois et que le tableau des produits de vente contient les données pour les informations sur le prix de vente, les deux tableaux sont combinés et les données à agréger sont combinées en un seul tableau.

2, agréger les données par année et par mois pour calculer la valeur totale

Le premier peut être obtenu en se joignant à JOIN, mais le second utilise la clause SQL GROUP BY.

La clause SQL GROUP BY spécifie les colonnes à agréger dans la clause GROUP BY pour la table finale après avoir joint les tables (si nécessaire), comme suit: Si l'agrégation comporte deux niveaux, tels que l'année et le mois, écrivez les deux colonnes séparées par des virgules.

Vous pouvez également spécifier une fonction à agréger pour les données agrégées. Ici, je veux calculer le prix de vente total pour chaque année et mois. Le prix de vente XX quantité des ventes (sales_price * sales_amount) est calculé à l'aide de SUM.

SELECT
  sales_year,
  sales_month,
  SUM(sales_price * sales_amount)
FROM
  mlprep_sales
  JOIN mlprep_sales_products ON mlprep_sales.sales_id = mlprep_sales_products.sales_id
GROUP BY sales_year, sales_month

Python: prétraitement en machine learning: acquisition de données