Datenerfassung aus Excel

Daten aus Excel lesen

Die erste Aufgabe bei der Vorverarbeitung von Daten ist das Abrufen der Daten. Es gibt verschiedene Arten von Datenquellen, die Daten abrufen, z. B. Dateien, Datenbanken und Websites. Dieses Mal lese ich oft Daten,

Excel-Datei
Datenbank

Lernen Sie, Daten von zu erhalten. Darüber hinaus wurde die Datenerfassung von CSV in Data Cleansing eingeführt.

Es ist einfach, Pandas zu verwenden, um Daten aus Excel zu lesen. Wenn Sie Pandas verwenden, können Sie nach dem Lesen der Daten Daten im DataFrame-Format von Pandas abrufen. Die nachfolgende Verarbeitung ist auch einfacher zu schreiben. Informationen zu Pandas finden Sie unter Informationen zur Datenverarbeitung mit Pandas.

Eine Excel-Datei mit Pandas lesen
Funktion pd.read_excel(io, sheetname)Wird genutzt.

Für dieses erste Argument io

Funktion pd.ExcelFile('Der Name der Datei, die Sie lesen möchten')
Es ist möglich, das in erstellte ExcelFile-Objekt zu übergeben.

Weitere Informationen zur Funktion pd.read_excel () finden Sie in der offiziellen Pandas-Dokumentation.

import pandas as pd

xlsx = pd.ExcelFile('Der Name der Datei, die Sie lesen möchten')
#Erstellen eines ExcelFile-Objekts

df = pd.read_excel(io, sheetname)
#Importieren Sie eine Excel-Datei mit Pandas

Kombinieren Sie die von Excel gelesenen Daten

Zuvor habe ich ein Blatt Excel-Datei geladen Bei der eigentlichen Vorverarbeitung werden die in mehreren Blättern gespeicherten Daten organisiert und gespeichert. Möglicherweise möchten Sie es für die Analyse als Datensatz verwenden.

Betrachten Sie als konkretes Beispiel die Situation, in der jedes Blatt die folgenden Daten enthält.

Welche Art von Daten sollte in dieser Situation erstellt werden, wenn Sie sowohl den Verkaufspreis als auch den Listenpreis des verkauften Produkts als Analysedaten verwenden möchten?

Dazu müssen Sie die Daten aus den beiden Blättern mit product_id kombinieren, um die folgenden Daten zu erstellen.

#Diese Join-Operation ist eine Pandas-Funktion
pd.merge(left, right, on)
#Wird genutzt.

Wenn Sie die Argumente der Zusammenführungsfunktion, die im Fall des vorherigen Beispiels verwendet werden sollen, schematisch beschreiben

pd.merge (Verkaufsblattdatenrahmen, Produktblattdatenrahmen, on = 'product_id') Es sieht aus wie.

Weitere Informationen zur Zusammenführungsfunktion finden Sie in der offiziellen Dokumentation. Das Folgende ist ein Beispiel.

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

#Bitte schreibe deine Antwort hier
df13 = pd.merge(df1, df3, on='employee_id')
df123 = pd.merge(df13, df2, on='project_id')
df123

Eingrenzen der von Excel gelesenen Daten

Von den folgenden Daten Nur die teilnehmenden Mitglieder von "Marketing Project" und "System Development Project" Was ist, wenn ich es wissen will?

In diesem Fall müssen Sie die Daten in der obigen Tabelle eingrenzen.

Es gibt verschiedene Möglichkeiten, dies zu tun

#Pandas Funktion
DataFrame.query
#Dies erleichtert das Schreiben und Lesen des Codes.

In der Funktion DataFrame.query können verschiedene Bedingungen wie folgt als Zeichenfolgen angegeben werden.

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]')

Wenn Sie nur die teilnehmenden Mitglieder von "Marketing Project" und "System Development Project" kennenlernen möchten Da wir die project_id nur auf diejenigen mit 1 und 3 eingrenzen müssen, schreiben Sie wie folgt.

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

In der offiziellen Dokumentation finden Sie verschiedene Beispiele für DataFrame.query.

Aggregation von von Excel gelesenen Daten

In der Liste der Projektmitglieder Möglicherweise möchten Sie die Anzahl der Mitglieder in jedem Projekt wissen. Dazu aggregieren und gruppieren wir die Daten für jedes Projekt. Sie müssen die Anzahl der Daten in jeder Gruppe herausfinden.

#Das sind Pandas
DataFrame.groupby
#Sie können diese Methode verwenden.

Mit DataFrame.groupby können Sie Spalten angeben, die im folgenden Format gruppiert werden sollen:

df.groupby('Spaltenname, den Sie gruppieren möchten')
#Wenn Sie nach mehreren Spalten gruppieren möchten, gehen Sie wie folgt vor:

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

Die groupby-Methode gibt ein GroupBy-Objekt zurück, kein DataFrame-Objekt. Das GroupBy-Objekt definiert die praktischen Methoden count (), mean (), sum (), max () usw., die Sie auf jede Gruppe anwenden möchten.

In der offiziellen Dokumentation finden Sie Informationen zu den im GroupBy-Objekt verfügbaren Methoden.

Klicken Sie hier für praktische Beispiele

import pandas as pd
xlsx = pd.ExcelFile('Dateiname')
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']

Ausgabeergebnis

Daten aus der Datenbank abrufen

Daten aus der Datenbank lesen

Beim Lesen von Daten aus der Datenbank in der Vorverarbeitung ist es zweckmäßig, die Funktion read_sql von Pandas zu verwenden, um die Daten im DataFrame-Format abzurufen. Die folgenden Informationen sind erforderlich, um eine Verbindung zur Datenbank herzustellen.

Art der Datenbank, zu der eine Verbindung hergestellt werden soll (MySQL, PostgreSQL, Oracle usw.)
Verbindungsinformationen zur Verbindungszieldatenbank (Datenbankname, Hostname, Portnummer, Authentifizierungsinformationen des Verbindungsbenutzers, Zeichencode)
Für die Verbindung verwendete Treiberinformationen (mysqlclient, mysqlDB, mysql-connector-python, ourql usw.)
Mysqlclient ist ein MySQL-Treiber, der von SQL Alchemy betrieben wird und nicht im Code enthalten ist.

import sqlalchemy as sqla
engine = sqla.create_engine('mysql+mysqldb://ai:ai@mysql-service:3307/ai_mlprep?charset=utf8')
# create_Das Format der Verbindungszeichenfolge des Engine-Arguments lautet wie folgt.

Verbindungsdatenbank+Fahrername://Verbundener Benutzername:Passwort@Hostname:Port-Nummer/Name der Datenbank?charset=Zeichencode

Verwenden Sie die folgende SQL SELECT-Anweisung, um Daten aus einer Datenbanktabelle zu lesen.

SELECT
Tabellenspaltenname 1,
Tabellenspaltenname 2
FROM Tabellenname

Verwenden Sie außerdem die Funktion pandas read_sql wie folgt. Das Schreiben der SQL-Zeichenfolge mit '' '(dreifache Anführungszeichen) mit Zeilenumbrüchen erleichtert das Lesen des Codes.

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

Übergeben Sie im zweiten Argument von read_sql die Engine für die von sqlalchemy erstellte Datenbankverbindung.

Kombinieren Sie Daten mit JOIN

Die folgende Tabelle wird erstellt.

Was kann ich zu diesem Zeitpunkt tun, wenn ich den Listenpreis des tatsächlich verkauften Produkts mit dem Verkaufspreis vergleichen möchte? Verwenden Sie dazu eine gemeinsame Spalte in der Produkttabelle, die den Verkaufspreis enthält, und die Produkttabelle, die den Listenpreis enthält. Sie müssen die Tabellen verbinden.

Nach dem Lesen jeder Tabelle im DataFrame-Format, genau wie beim Lesen von Daten aus Excel Sie können jeden DataFrame auch mit der Pandas-Merge-Funktion kombinieren. Wenn Ihre Datenbank Tabellen enthält, verwenden Sie SQL Der Join-Prozess kann präzise geschrieben werden, und es können nur die Ergebnisdaten des Joins abgerufen werden.

So verbinden Sie Tabelle A und Tabelle B der Datenbank mit jeder Spalte 1 als Schlüssel Schreiben Sie SQL mit JOIN ON wie unten gezeigt.

SELECT
Tabelle A..Spalte 1,
Tabelle B..Spalte 1
FROM
Tabelle A.
JOIN Tabelle B ON Tabelle A..Spalte 1=Tabelle B..Spalte 1

Wenn Sie drei oder mehr Tabellen wie unten gezeigt verbinden, wird JOIN ON nacheinander beschrieben.

SELECT
Tabelle A..Spalte 1,
Tabelle B..Spalte 1
FROM
Tabelle A.
JOIN Tabelle B ON Tabelle A..Spalte 1=Tabelle B..Spalte 1
JOIN Tabelle C ON Tabelle B..Spalte 2=Tabelle C..Spalte 2

Filtern von Daten mit WHERE

Es hat die gleiche Tabellenstruktur.

Wenn Sie beispielsweise eine Analyse durchführen, möchten Sie Trends herausfinden, wann Getränke verkauft werden. Was ist, wenn ich eine Anforderung habe?

Dazu müssen Sie die folgenden zwei Dinge tun.

Informationen zum Verkaufsdatum sind in der Verkaufstabelle enthalten, und Da die Informationen der verkauften Produkte in der Tabelle der verkauften Produkte enthalten sind, werden die beiden Tabellen zusammengefügt.

Da ich die Ausgabeergebnisse auf nur Getränke eingrenzen möchte, beschränke ich mich nur auf die product_id von Getränken.

Von den in der Produkttabelle enthaltenen Produkten sind die einzigen Getränke Tee mit product_id 4 und Orangensaft mit product_id 6.

In diesem Fall in der WHERE-Klausel von SQL
product_id =4 und Produkt_id =Geben Sie 6 an, um die Ausgabe einzugrenzen.

So schreiben Sie eine SQL, die die oben genannten Anforderungen erfüllt und eine WHERE-Klausel zum Filtern der Ausgabe enthält:

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

Wie oben erwähnt, können Sie mit OR mehrere Bedingungen in der WHERE-Klausel angeben. Oben wird die Bedingung auch mit = beschrieben, aber Ungleichungszahlen wie können auch in der Bedingungsbeschreibung verwendet werden. Obwohl im Beispiel nicht gezeigt, ist es möglich, zusätzlich zu ODER mit AND auf mehrere Bedingungen hinzuweisen. Wenn AND verwendet wird, werden nur Daten ausgegeben, die alle verschiedenen Bedingungen erfüllen.

Aggregieren Sie Daten mit GROUP BY

Es hat wieder die gleiche Konfiguration.

Angesichts dieser Daten der Gesamtverkaufspreis aller Produkte nach Jahr und Monat Was ist, wenn ich es für die Analyse verwenden möchte?

Sie können GroupBy von pandas verwenden, aber Sie können dasselbe mit SQL tun.

Berechnung des Gesamtverkaufspreises für jedes Jahr Dies kann nicht durch Kombinieren mit JOIN oder Eingrenzen mit WHERE erreicht werden. Die folgenden zwei Punkte müssen angesprochen werden.

Da die Verkaufstabelle die Daten für 1, Jahr und Monat enthält und die Verkaufsprodukttabelle die Daten für die Verkaufspreisinformationen enthält, werden die beiden Tabellen kombiniert und die zu aggregierenden Daten in einer Tabelle zusammengefasst.

2, Aggregieren Sie Daten nach Jahr und Monat, um den Gesamtwert zu berechnen

Die 1 kann durch Verbinden mit JOIN erreicht werden, die 2 verwendet jedoch die SQL GROUP BY-Klausel.

Die SQL GROUP BY-Klausel gibt die Spalten an, die in der GROUP BY-Klausel für die endgültige Tabelle nach dem Zusammenfügen der Tabellen (falls erforderlich) wie folgt aggregiert werden sollen: Wenn die Aggregation zwei Ebenen hat, z. B. Jahr und Monat, schreiben Sie die beiden durch Kommas getrennten Spalten.

Sie können auch eine Funktion angeben, die für die aggregierten Daten aggregiert werden soll. Hier möchte ich den Gesamtverkaufspreis für jedes Jahr und jeden Monat berechnen. Die Verkaufspreis XX Verkaufsmenge (sales_price * sales_amount) wird mit SUM berechnet.

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: Vorverarbeitung beim maschinellen Lernen: Datenerfassung