pandas: Problem mit pivot_table und zwei Spalten für die Darstellung

[noisefloor]

Hallo,

die folgenden Daten sollen nach Jahr, Monat und Produktgruppe gruppiert und aufsummiert werden. Die Produktgruppe stellen die Buchstaben der Artikelnummer dar.

Beispieldaten (sind im Original in einer Excel-Datei):

Code: Alles auswählen

No_SalesLine	ShipmentDate_SalesLine	SalesOrderAmount
AL000660	01.04.26	10926,9
S_02	01.04.26	95
AL000113	01.04.26	20582,4
S_02	01.04.26	190
AL000020	01.06.26	22521,6
AL000112	01.10.26	19206
AL000632	01.10.26	19756,44
S_02	01.10.26	418
AL000113	01.12.26	22640,64
S_02	01.12.26	209
AL000679	02.02.26	5961,6
S_02	02.02.26	57
ZR000020	02.02.26	3907,2
FS001862	02.07.26	56595
S_02	02.07.26	1540
ANLB001015	03.02.26	7159,15
S_02	03.02.26	25
ANLB001252	03.03.26	29366,31
S_02	03.03.26	686,93
AL000632	03.03.26	8906,88
S_02	03.03.26	76
S_01	03.03.26	600
AL000359	03.03.26	18598,72
AL000786	03.03.26	11535,66
S_02	03.03.26	209
S_01	03.03.26	1650
NULL	NULL	NULL
NULL	NULL	NULL

Der folgende Code macht das auch:

Code: Alles auswählen

import pandas as pd

OPEN_ORDERS_FILE = 'daten_beispiel.xlsx'

df = pd.read_excel(OPEN_ORDERS_FILE, header=0,
                   usecols=['No_SalesLine', 'ShipmentDate_SalesLine',
                             'SalesOrderAmount'],
                   dtype={'SalesOrderAmount': float})

#Aufräumen des Data Frames:
# 1. alle Zeilen entfernen, wie alle Werte NaN sind
# 2. alle Zeilen entfernen, die einen MU-Artikel enthalten, weil der
#    Betrag hier Null (0) sein muss
# 3. Artikelnummer in String umwandeln
# 4. Datum ins ISO8601 Format konvertieren
# 5. Produktgruppe basierend auf der Artikelnummer hinzufügen
df = df.dropna(how='all')
indices_to_drop = df[df['No_SalesLine'].str.startswith('MU') == True].index
df = df.drop(indices_to_drop)
df = df.astype({'No_SalesLine': 'string'})
df['ShipmentDate_SalesLine'] = pd.to_datetime(df['ShipmentDate_SalesLine'], format='%d.%m.%y', yearfirst=True)
df['ItemGroup'] = df['No_SalesLine'].str.extract(r'([A-Z]{1,4})')
df_by_year_and_month = df.groupby([df['ShipmentDate_SalesLine'].dt.year, df['ShipmentDate_SalesLine'].dt.month, 'ItemGroup']).agg({'SalesOrderAmount':'sum'})
print('Umsatzvorschau, Gesamtübersicht, Summe in Euro')
print('----------------------------------------------')
print(df_by_year_and_month.to_string())
print('\n\n`')

Mir wäre nur eine Darstellung lieber, wo die Produktgruppe in den Zeilen steht und die Spalten nach Jahr und Monat gruppiert sind. Also so:

Code: Alles auswählen

	2026						
Zeilenbeschriftungen	2	3	4	6	7	10	12
AL	5961,6	39041,26	31509,3	22521,6		38962,44	22640,64
ANLB	7159,15	29366,31					
FS					56595		
S	82	3221,93	285		1540	418	209
ZR	3907,2						
Gesamtergebnis	17109,95	71629,5	31794,3	22521,6	58135	39380,44	22849,64

Geht theoretisch auch in Pandas mit einer Pivot Tabelle - kriege ich praktisch aber nicht hin.

Die Zeile:

Code: Alles auswählen

table = pd.pivot_table(df, values='SalesOrderAmount', index=['ItemGroup'],
                       columns=[df['ShipmentDate_SalesLine'].dt.year, df['ShipmentDate_SalesLine'].dt.month], 
                       aggfunc='sum')

wirft den Fehler:

Code: Alles auswählen

ValueError: The name ShipmentDate_SalesLine occurs multiple times, use a level number

Nur verstehe ich nicht, was man an `columns` übergeben muss, damit die Darstellung so ist wie gewünscht. Also ich verstehe nicht, wie und wo man einen Level übergeben könnte und diverse Experiment, `pd.Grouper` Objekte zu übergeben, waren auch erfolglos.

[Dennis89]

Ich habe keine Erfahrung damit, ich denke allerdings dass du kein Level übergeben musst, sondern über `MultiIndex.levels` darauf zugreifen musst bzw. das Level angeben.

Vielleicht hilft dir der Hinweis weiter.

Grüße
Dennis

[noisefloor]

Hallo,

nee, der Index bei der Pivot Table sind ja die Werte für die Zeilen, soweit ich das verstehe. columns gibt die Spalte(n) an. pd.Grouper kennt auch das `level` Argument - nur checke ich nicht, wie man das richtig einsetzt.

Gruß, noisefloor

[noisefloor]

Hallo,

so, die Lösung ist eigentlich ganz einfach... Nachdem ich die Frage bei SO stellen wollte, habe ich erst Mal die Frage an den AI Agent von SO gestellt. Hatte ich vorher noch nie genutzt. Der hat zwar nicht direkt die Lösung gegeben, aber in einem Codeschnipsel war der richtige Hinweis.

Wenn man erst im Dataframe Spalten für Monat und Jahr anlegt und die Name der Spalten dann an das `columns` Argument von `pd.pivot_table` übergibt, dann erhält man die gewünschte Darstellung. Also bezogen auf den Code aus dem 1. Post sieht der erweiterte Code dann so aus:

Code: Alles auswählen

import pandas as pd

OPEN_ORDERS_FILE = 'daten_beispiel.xlsx'

df = pd.read_excel(OPEN_ORDERS_FILE, header=0,
                   usecols=['No_SalesLine', 'ShipmentDate_SalesLine',
                             'SalesOrderAmount'],
                   dtype={'SalesOrderAmount': float})

#Aufräumen des Data Frames:
# 1. alle Zeilen entfernen, wie alle Werte NaN sind
# 2. alle Zeilen entfernen, die einen MU-Artikel enthalten, weil der
#    Betrag hier Null (0) sein muss
# 3. Artikelnummer in String umwandeln
# 4. Datum ins ISO8601 Format konvertieren
# 5. Produktgruppe basierend auf der Artikelnummer hinzufügen
df = df.dropna(how='all')
indices_to_drop = df[df['No_SalesLine'].str.startswith('MU') == True].index
df = df.drop(indices_to_drop)
df = df.astype({'No_SalesLine': 'string'})
df['ShipmentDate_SalesLine'] = pd.to_datetime(df['ShipmentDate_SalesLine'], format='%d.%m.%y', yearfirst=True)
df['ItemGroup'] = df['No_SalesLine'].str.extract(r'([A-Z]{1,4})')

df_by_year_and_month = df.groupby([df['ShipmentDate_SalesLine'].dt.year, df['ShipmentDate_SalesLine'].dt.month, 'ItemGroup']).agg({'SalesOrderAmount':'sum'})
print('Umsatzvorschau, Gesamtübersicht, Summe in Euro')
print('----------------------------------------------')
print(df_by_year_and_month.to_string())
print('\n\n`')

df['month'] = df['ShipmentDate_SalesLine'].dt.month
df['year'] = df['ShipmentDate_SalesLine'].dt.year
table = pd.pivot_table(df, values='SalesOrderAmount', index=['ItemGroup'],
                       columns=['year', 'month'], aggfunc='sum', fill_value=0)
print(table)

Ergebnis:

Code: Alles auswählen

year          2026
month           2         3        4        6        7         10        12
ItemGroup
AL         5961.60  39041.26  31509.3  22521.6      0.0  38962.44  22640.64
ANLB       7159.15  29366.31      0.0      0.0      0.0      0.00      0.00
FS            0.00      0.00      0.0      0.0  56595.0      0.00      0.00
S            82.00   3221.93    285.0      0.0   1540.0    418.00    209.00
ZR         3907.20      0.00      0.0      0.0      0.0      0.00      0.00

Die pandas Pivot Tabelle kann man dann auch nach Excel exportieren, wobei da halt "nur" die Daten/Werte in der Excel Datei drin sind und keine Excel Pivot Tabelle.
Gruß, noisefloor

[Sirius3]

Schon beim Laden sollten die Spalten die richtigen Typen haben. Warum wandelst Du ShipmentDate_SalesLine erst so spät in ein Datum um?
Das löschen der No_SalesLine mit "MU" ist reichlich kompliziert. Warum erzeugst Du erst eine Maske, die Du dann dazu benutzt, um Dein Dataframe zu selektieren, um dann den Index der übrig gebliebenen Einträge zu benutzen, um diese dann per drop zu löschen?
Einfach:

Code: Alles auswählen

df = df[~df['No_SalesLine'].str.startswith('MU')

[noisefloor]

Hallo,

Schon beim Laden sollten die Spalten die richtigen Typen haben. Warum wandelst Du ShipmentDate_SalesLine erst so spät in ein Datum um?

Das ist inzwischen korrigiert. Das Lesen der Excel-Datei sieht inzwischen so aus:

Code: Alles auswählen

df = pd.read_excel(in_file, header=0,
                       usecols=['No_SalesLine', 'ShipmentDate_SalesLine',
                             'SalesOrderAmount'],
                       dtype={'SalesOrderAmount': float,
                              'No_SalesLine': pd.StringDtype()},
                       parse_dates=['ShipmentDate_SalesLine'],
                       date_format='%d.%m.%y')

Danke für den Hinweis mit dem Negieren der Maske. Kannte ich so nicht, habe ich in den Code eingebaut.

Gruß, noisefloor