Python-Praxis: Excel-Daten nutzen

27.04.2024 09:15 Uhr Walter Saumweber

Die pandas-Bibliothek bietet die Möglichkeit, mit Python Daten aus Excel-Arbeitsmappen auszulesen, zu bearbeiten und zu ändern.

Programmseitige Zugriffe auf Excel sind für viele Anwendungsbereiche interessant. Daher verwundert es nicht, dass Python für diesen Zweck Bibliotheken zur Verfügung stellt. Außer pandas ist vor allem noch openpyxl zu nennen, wobei pandas selbst openpyxl in Teilen nutzt. Beide Bibliotheken unterstützen alle Excel-Formate, also auch den Makro-Dateityp .xlsm. Als Grundlage für die folgenden Codebeispiele dient eine Excel-Arbeitsmappe Demo.xlsx mit dem Arbeitsblatt Umsätze 2023. Die Tabelle ist mit je fünf Zeilen und Spalten bewusst einfach gehalten, aber natürlich lassen sich die folgenden Beispiele auch mit jeder anderen Excel-Arbeitsmappe nachvollziehen; die Größe des Tabellenblatts spielt dabei keine Rolle.

Walter Saumweber hat langjährige Erfahrung als Entwickler, Berater und Dozent. Er ist Autor von zahlreichen Fachbüchern und Beiträgen in Computer-Fachzeitschriften. Seine Tätigkeitsschwerpunkte sind die Realisierung von Unternehmenslösungen in Client-Server-Umgebungen.

Die Beispieldateien zum Download [1]

pandas installieren

Die pandas-Bibliothek ist nicht im Standardumfang von Python enthalten und lässt sich auf der Konsole mit dem Befehl pip3 install pandas für Python 3 nachinstallieren. Die IDE PyCharm macht das Installieren von Bibliotheken besonders einfach. Es erkennt, falls eine Bibliothek nicht vorhanden ist, und bietet die Installation automatisch an. Dazu schreibt man den import-Befehl so in den Editor, als ob die Bibliothek schon vorhanden wäre. Beim Hovern mit der Maus über den rot unterkringelten Namen erscheint dann ein Pop-up-Fenster mit dem install-Befehl.

Die Bibliothek pandas nutzt wiederum die Bibliotheken NumPy und openpyxl, die die Installation von pandas normalerweise automatisch enthält. Es ist jedoch sinnvoll, sich davon zu überzeugen und sie gegebenenfalls nachzuinstallieren.

Beispiel Excel — Die Excel-Tabelle, auf die sich die Codebeispiele beziehen, gibt die Umsätze eines Unternehmens pro Quartal wieder, aufgeteilt in vier verschiedene Bezirke (Abb. 1).

(Bild: Walter Saumweber)

Daten aus einem Excel-Tabellenblatt einlesen

Das Auslesen von Daten aus einem Excel-Tabellenblatt geschieht nach dem Import von pandas auf bequeme Weise mit der Funktion read_excel(). Als ersten Parameter übergibt man den Namen der auszulesenden Excel-Datei, gegebenenfalls mit Pfad. Standardmäßig, ohne weitere Parameter, liest read_excel() alle Daten des ersten Tabellenblatts ein.

import pandas
data_frame = pandas.read_excel('Demo.xlsx')

Die Funktion speichert die gelesenen Excel-Daten in einem DataFrame (Klasse pandas.core.frame.DataFrame), und zwar zweidimensional, also praktisch wie in einem Excel-Arbeitsblatt. Somit enthält beispielsweise data_frame['I. Quartal'][0] den Wert der ersten Zelle der Spalte I. Quartal. Im Arbeitsblatt von Demo.xlsx ist das der Umsatz des Bezirks A im ersten Quartal (Zelle B2).

Um sich einen Überblick über die Struktur der Daten zu verschaffen, kann man mit print(data_frame) den kompletten DataFrame ausgeben oder mit print(data_frame['<Spaltenname>']) nur die Daten einer Spalte, beispielsweise print(data_frame['I. Quartal']). Ausgegeben werden zum Beispiel auch die Indizes der Zeilen, beginnend bei 0 unter den Spaltennamen. Die Spalten müssen dagegen immer mit Namen, also durch Angabe eines Strings, referenziert werden. Falls eine Spalte nicht existiert, stellt sich ein KeyError ein. Für den Fall, dass eine Spalte im Excel-Tabellenblatt einmal umbenannt wird, ist eine diesbezügliche Ausnahmebehandlung sinnvoll:

try:
    print(data_frame['I. Quartal'])
except KeyError:
    print('Die Spalte existiert nicht')

Standardmäßig interpretiert die Funktion read_excel() die Daten der ersten Zeile eines Excel-Tabellenblatts als Spaltenüberschriften. Für Zellen, die in dieser Zeile leer sind, speichert der DataFrame den Wert Unnamed: <Index>, für die erste Zelle in der Beispieltabelle folglich den Wert Unnamed: 0. data_frame['Unnamed: 0'][<Index>] referenziert die Zeilenüberschriften dieser Tabelle – mit data_frame['Unnamed: 0'][2] erhält man beispielsweise den Bezirk C.

Paketliste — Der Konsolenbefehl pip3 list listet alle Python-Bibliotheken auf, die aktuell auf dem System installiert sind; der Parameter -v zeigt zusätzlich die Speicherorte an (Abb. 2).

(Bild: Walter Saumweber)

Prüfen, ob eine Excel-Zelle leer ist

Einfache Datenzellen, die leer sind, werden im DataFrame mit nan (für „not a number“) gefüllt (in der Ausgabe erscheint jedoch das bekannte NaN). Daher erhalten Developer kein korrektes Ergebnis, wenn sie im Python-Code auf einen Leerstring prüfen. Beispielsweise ist die if-Bedingung if data_frame['I. Quartal'][1] == '' nicht geeignet, um über den erstellten DataFrame die zweite Zelle der Spalte I. Quartal dahin gehend zu prüfen. Aber auch Vergleiche mit 'nan' oder nan führen nicht zum Ziel.

Lösungen für dieses Problem gibt es mehrere. Zum einen stellt die pandas-Bibliothek für die Prüfung auf leere Zellen die Funktion isna zur Verfügung. Dieser übergibt man den Teil des DataFrames, der die Zelle repräsentiert. Die Inhaltsprüfung von Zelle B3 könnte so aussehen:

if pandas.isna(data_frame['I. Quartal'][1]):
    print('Die Zelle ist leer')

Die NumPy-Bibliothek bietet zum gleichen Zweck die Funktion isnan(). Der Aufruf unterscheidet sich nicht von dem der Funktion isna(). Es gibt noch eine weitere, recht elegante Möglichkeit: Entwicklerinnen und Entwickler machen sich den Umstand zunutze, dass nan das einzige Objekt ist, bei dem ein direkter Vergleich mit sich selbst ein False ergibt.

if data_frame['I. Quartal'][1] != data_frame['I. Quartal'][1]:
    print('Die Zelle ist leer')

Parameter von read_excel()

Für Bedingungen, die nicht dem Standard entsprechen, stellt die Funktion read_excel() verschiedene Parameter zur Verfügung. Der Parameter skiprows kommt zum Einsatz, wenn die relevanten Daten nicht in der ersten Zeile beginnen. Der an skiprows zugewiesene Wert steht für die Anzahl der Zeilen, die read_excel() überspringen soll, zum Beispiel:

data_frame = pandas.read_excel('Demo.xlsx', skiprows=3)

Der Parameter sheet_name bestimmt das einzulesende Tabellenblatt, mit der folgenden Anweisung das zweite Tabellenblatt der Beispiel-Arbeitsmappe.

data_frame = pandas.read_excel('Demo.xlsx', sheet_name='Umsätze 2024')

Anstelle des Namens eignet sich auch der Index, bei mehreren Tabellenblättern beispielsweise:

tabellenblaetter = [0, 1]
data_frames = pandas.read_excel('Demo.xlsx', sheet_name=tabellenblaetter)

In diesem Fall erzeugt read_excel() ein Dictionary, das einen DataFrame für jedes Tabellenblatt enthält. Die Schlüssel bilden die Tabellenblätter-Indizes bzw. die Tabellenblattnamen. Die oben erzeugten DataFrames lassen sich mit data_frames[0] und data_frames[1] ansprechen.Der Parameter header gibt die Zeile mit den Spaltenüberschriften an, was erforderlich ist, wenn sie nicht die erste Zeile des Tabellenblatts ist. Die Angabe header=2 bedeutet zum Beispiel, dass sich die Zeilenüberschriften in der dritten Zeile des Tabellenblatts befinden. header=None teilt der read_excel() mit, dass die Excel-Tabelle keine Spaltenüberschriften enthält. Unabhängig davon weist names einer Liste die gewünschten Spaltennamen zu:

spalten = ['', 'I/24', 'II/24', 'III/24', 'IV/24']
data_frame = pandas.read_excel('Demo.xlsx', names=spalten)
print(data_frame)

Die Ausgabe sieht so aus:

              I/24  II/24 III/24  IV/24
0  Bezirk A  19500  13500  17700  28000
1  Bezirk B  18400  25100  13600  21500
2  Bezirk C   9700  20000  29000  18500
3  Bezirk D  15800  14900  17200  16000

Wichtig ist dabei: read_excel() erwartet in der names-Liste für jede Spalte genau eine Überschrift, auch für Zellen, die in der Überschriftenzeile leer sind. Da die Überschriftenzeile in der Excel-Tabelle fünf Spalten umfasst, muss auch die names-Liste genau fünf Elemente enthalten, andernfalls würde sich ein ValueError einstellen („Number of passed names did not match number of header fields in the file“).

Es ist aber zulässig – wie hier beim ersten Element geschehen – einen Leerstring zuzuweisen. In diesem Fall enthält der DataFrame für diese Spalte tatsächlich keine Überschrift, und nicht etwa den Text „Unnamed: “ mit anschließendem Spaltenindex, den read_excel() bei fehlenden Spaltenüberschriften automatisch zuweist, wenn die Funktion ohne names-Parameter aufgerufen wird.

Allerdings definiert names auch für Spalten ohne Überschrift eine solche.

spalten = ['Verkaufsbezirke', 'I/24', 'II/24', 'III/24', 'IV/24']

Ein bisschen Vorsicht ist bei der gemeinsamen Verwendung der Parameter skiprows, names und header geboten, denn sie beeinflussen sich gegenseitig. Beispielsweise zählt die Überschriftenzeile der Excel-Tabelle beim skiprows-Wert nicht mit, wenn beim read_excel()-Aufruf gleichzeitig names verwendet wird, und bei einem skiprows-Wert von zum Beispiel 2 würde die Angabe header=0, ohne names, bedeuten, dass die Überschriftenzeile in der Excel-Tabelle nicht etwa die erste, sondern die dritte Zeile ist. Es ist also ratsam, sich in der Entwicklungsphase immer wieder davon zu überzeugen, dass das Ergebnis den Erwartungen entspricht.

Der Parameter nrows legt fest, wie viele Zeilen read_excel() in den DataFrame einliest. Beispielsweise speichert read_excel() mit nrows=2 im DataFrame zwei Datenzeilen plus Überschriftenzeile. Beim Standardwert für skiprows (None beziehungsweise 0) sind das die ersten drei Zeilen der Beispieltabelle (die Überschriftenzeile mitgerechnet). Falls ein Wert für skiprows angegeben ist, speichert read_excel() die Zeilen ab der entsprechenden Stelle.

Mit dem Parameter usecols lassen sich die einzulesenden Spalten beschränken. Diese müssen nicht unbedingt nebeneinanderliegen, sondern Entwicklerinnen und Entwickler können eine Liste mit Spaltennamen oder Indizes (aber nur eines von beiden) zuweisen. In Kombination mit names müssen die durch diesen Parameter definierten Spaltennamen angegeben werden.

spalten = ['Verkaufsbezirke', 'I/23', 'II/23', 'III/23', 'IV/23']
data_frame = pandas.read_excel('Demo.xlsx', names=spalten, usecols=['Verkaufsbezirke', 'II/23'])
print(data_frame)

Hier die Ausgabe des obigen Listings:

  Verkaufsbezirke   II/23
0        Bezirk A   13500
1        Bezirk B   25100
2        Bezirk C   20000
3        Bezirk D   14900

Mit pandas in ein Excel-Arbeitsblatt schreiben

Zu diesem Zweck stellt die pandas-Bibliothek die DataFrame-Methode to_excel() zur Verfügung. Die folgende Anweisung erstellt in der Arbeitsmappe Demo1.xlsx ein Arbeitsblatt mit dem Namen Umsätze 2. Quartal 2023 und schreibt in dieses die in dem zuvor erstellten DataFrame (siehe vorhergehendes Listing) gespeicherten Daten.

data_frame.to_excel('Demo1.xlsx', sheet_name='Umsätze 2. Quartal 2023', index=False)

Zu beachten ist, dass die Ziel-Arbeitsmappe bereits vorhanden sein muss und dass to_excel() einen bestehenden Inhalt dort überschreibt. Die Angabe index=False verhindert, dass die im DataFrame gespeicherten Datenzeilen-Indizes mitgeschrieben werden; die Standardeinstellung ist True.

Zwei weitere Parameter der to_excel()-Methode sind columns und startcol. columns ist das Gegenstück zum read_excel()-Parameter usecols. Falls Developer nicht bereits beim Einlesen der Excel-Daten in den DataFrame eine Vorauswahl getroffen haben, können sie usecols eine Liste mit den gewünschten Spalten zuweisen. Wenn die Spalten nebeneinanderliegen, bietet sich alternativ der Parameter startcol an. Der Integerwert legt die Spalte fest, ab der to_excel() in die Excel-Tabelle schreibt. Der Parameter startrow verhält sich analog und legt die erste Datenzeile fest. Um die Spaltennamen neu festzulegen, übergibt man dem header-Parameter eine Liste mit den gewünschten neuen Namen. Mit der Angabe header=False schreibt to_excel() die Daten ohne Spaltenüberschriften in die Excel-Tabelle.

Einen DataFrame erstellen

Wollen Entwicklerinnen und Entwickler berechnete Daten in eine Arbeitsmappe schreiben, müssen sie zuvor einen eigenen DataFrame erstellen. Dafür gibt es mehrere Wege, zum Beispiel übergibt man der __init__()-Methode der DataFrame-Klasse die gewünschten Daten. Der columns-Parameter definiert dabei die Spaltennamen. Die folgende Anweisung speichert im DataFrame die Umsätze der Bezirke A und B des ersten Halbjahrs (I. Quartal und II. Quartal).

data_frame = pandas.DataFrame([['Bezirk A', 19500, 13500], ['Bezirk B', 18400, 25100]], columns=['', 'I. Quartal', 'II. Quartal'])

Oder Developer speichern die Daten zunächst in einem Dictionary und übergeben dieses anschließend der __init__()-Methode. In diesem Fall bezieht __init__()- die Spaltennamen für den DataFrame aus den Schlüsseln und die Daten aus den Werten der Dictionary-Elemente. Die folgenden Anweisungen erstellen einen DataFrame mit den Gesamtumsätzen pro Quartal und schreiben diese anschließend in ein Arbeitsblatt Gesamtumsätze 2023 der Arbeitsmappe Demo1.xlsx.

import pandas

data_frame = pandas.read_excel('Demo.xlsx')

summe_quartal1_2023 = data_frame['I. Quartal'].sum()
summe_quartal2_2023 = data_frame['II. Quartal'].sum()
summe_quartal3_2023 = data_frame['III. Quartal'].sum()
summe_quartal4_2023 = data_frame['IV. Quartal'].sum()

umsaetze_dic = {
    'Umsatz 1. Quartal': [summe_quartal1_2023],
    'Umsatz 2. Quartal': [summe_quartal2_2023],
    'Umsatz 3. Quartal': [summe_quartal3_2023],
    'Umsatz 4. Quartal': [summe_quartal4_2023]
}

data_frame_umsaetze = pandas.DataFrame(umsaetze_dic)
data_frame_umsaetze.to_excel('Demo1.xlsx', sheet_name='Gesamtumsätze 2023', index=False)

Die Werte im Dictionary umsaetze_dic sind, obwohl einelementig, als Liste angegeben, da die Länge der Werte im Dictionary übereinstimmen muss, wenn man ein Dictionary an die __init__()-Methode übergibt. Das ist bei einfachen Werten wie hier bei den Umsätzen in der Regel nicht der Fall, da die Summen ja unterschiedliche Beträge ergeben.Es gibt noch eine Möglichkeit, den DataFrame zu erstellen, um auch einfache Werte im Dictionary zu notieren: from_dict() wandelt ein Dictionary direkt in einen DataFrame um. Der Parameter orient legt dabei fest, dass die Schlüssel des Dictionary, statt als Spaltenüberschriften als Index, also als Zeilenüberschriften des zu erstellenden DataFrame verwendet werden. Der obligatorische Parameter columns enthält die gewünschten Spaltenüberschriften:

…
umsaetze_dic = {
    'Umsatz 1. Quartal': summe_quartal1_2023,
    'Umsatz 2. Quartal': summe_quartal2_2023,
    'Umsatz 3. Quartal': summe_quartal3_2023,
    'Umsatz 4. Quartal': summe_quartal4_2023
}

data_frame_umsaetze = pandas.DataFrame.from_dict(umsaetze_dic, orient='index', columns=['Summe'])
data_frame_umsaetze.to_excel('Demo1.xlsx', sheet_name='Gesamtumsätze 2023')

Die Daten werden dann in folgender Anordnung nach Excel geschrieben:

                    Summe
Umsatz 1. Quartal   63400
Umsatz 2. Quartal   73500
Umsatz 3. Quartal   77500
Umsatz 4. Quartal   84000

Die Angabe index=False entfällt in dieser Variante beim Aufruf von to_excel(), da die Indizes, die hier ja aus den Quartalsangaben bestehen, ausdrücklich geschrieben werden sollen.

Fazit

Dank pandas funktioniert die Interaktion von Python-Prgorammen mit Excel einwandfrei. Allerdings kann es sein, dass manche Aufgaben doch einigen Programmieraufwand erfordern. Vor allem sollte man sich die Ergebnisse wie erwähnt während der Entwicklungsphase immer wieder anzeigen lassen, um sicherzugehen, dass das Programm erwartungsgemäß funktioniert. Im Übrigen sind die Möglichkeiten mit den hier gezeigten Beispielen bei Weitem noch nicht erschöpft. Wenn es zum Beispiel um Formatierungen geht, bietet sich die openpyxl-Bibliothek an.

(who [2])

URL dieses Artikels:
https://www.heise.de/-9689622

Links in diesem Artikel:
[1] https://www.heise.de/downloads/18/4/5/7/5/0/1/9/Excel_mit_Python.zip
[2] mailto:who@heise.de