Pandas Dataframes unter Bedingungen Kombinieren

[Freumel]

Guten Abend zusammen,

Folgendes Szenario:
Ich habe 3 Dataframes:
df_products, df_service, df_contract

Die Produkte haben eine einzigartige ID. Zu einem Produkt existieren verschiedene Serviceleistungen und teilweise auch verschiedene Verträge für den gleichen Kunden.

Eine Serviceleistung hat u.a. eine Produktzuordnung, Einnahmen und Ausgaben.
Innerhalb eines Vertrags sind Pauschalleistungen vereinbart (darunter ein einmaliger Grundpreis und ein pauschaler Wartungspreis pro Stunde.

Nun möchte ich den Gewinn pro Produkt bestimmen.

Das Problem:
Die Dataframes sind getrennt und ich betrachte ca. 100K Produkte zu jeweils 1 Mio Services und wieder 100K Verträgen.
Ich möchte gerne ein neues Dataframe erzeugen mit Folgenden Informationen:

ProductID, Einnahmen für alle Leistungen, Ausgaben für Alle Leistungen, Gewinn / Verlust für alle Leistungen, Basispreis im Vertrag, Pauschaler Stundenpreis, Erbrachte Stundenleistung für betrachtetes Kalenderjahr, Gesamtprofit, Wie viele Services werfen Gewinn ab, Wie viele Services werfen Verlust ab.

Ich habe mir hierzu zunächst ein Dictionary erstellt und fülle dies in einer Schleife. Anbei der Schleifendurchlauf:

Code: Alles auswählen

for index, row in df_product.iterrows():
    revenue = df_service[df_service['product_id']==row['product_id']]['revenue'].sum()
    costs = df_service[df_service['product_id']==row['product_id']]['costs'].sum()
    sum_ass = df_service[df_service['product_id']==row['product_id']]['sum'].sum()
    
    net = df_contract[df_contract['product_id']==row['product_id']]['net_price'].values[0]
    working_hours = df_contract[df_products['product_id']==row['product_id']]['hours'].values[0]
    price_per_working_hour = df_contract[df_products['product_id']==row['product_id']]['price_per_hour'].values[0]
    
    data_dict['product_id'].append(row['product_id'])
    
    data_dict['count_positive_services'].append(
        df_service[
            (df_service['product_id']==row['product_id']) &
            (df_service['current_total_sum'] < 0)
        ].shape[0]
    )
    
    data_dict['count_negative_services'].append(
        df_service[
            (df_service['product_id']==row['product_id']) &
            (df_service['current_total_sum'] >= 0)
        ].shape[0]
    )
    
    data_dict['revenue_services'].append(revenue)
    data_dict['costs_services'].append(costs)    
    data_dict['sum_services'].append(sum_ass)
    
    data_dict['net'].append(net)
    data_dict['working_hours'].append(working_hours)
    data_dict['price_per_working_hour'].append(price_per_working_hour)
    data_dict['total_profit'].append(
        working_hours * price_per_working_hour + net + sum_ass
    )

Das Problem: Das dauert ewig!
Das Programm läuft seit über einer Stunde und ist noch nicht am Ende.

Ich habe nur nicht die Erfahrung das zu optimieren.
Wäre dennoch schön es zu lernen

Kann mir vielleicht jemand helfen?

Vielen Dank und beste Grüße,

[peterpy]

Hallo Freumel,
ich finde, das ist keine gute Idee, die ganzen Datenmenge durchlaufen zu lassen.
Kannst Du nicht nur 10 Produkte zum Testen extrahieren?
Dann kannst Du die auch Zwischenergebnisse ausgeben lassen und siehst, wo's klemmt.
Gruss
Peter

[Freumel]

Hallo Freumel,
ich finde, das ist keine gute Idee, die ganzen Datenmenge durchlaufen zu lassen.
Kannst Du nicht nur 10 Produkte zum Testen extrahieren?
Dann kannst Du die auch Zwischenergebnisse ausgeben lassen und siehst, wo's klemmt.
Gruss
Peter

Ich glaube man muss komplett anders arbeiten.
Der Durchlauf alleine für den Teilbereich

Code: Alles auswählen

for index, row in df_product.iterrows():
    revenue = df_service[df_service['product_id']==row['product_id']]['revenue'].sum()

dauert ewig

Ich würde am liebsten folgende Logik übernehmen:

Code: Alles auswählen

df['c'] = df['a'] + df['b']

Nun ist meine Ausgangslage aber nicht ganz so trivial und scheitert sofort:

Code: Alles auswählen

df_profit['product_id'] = df_product['product_id']
df_profit['net_price'] = df_contract[df_contract['product_id'] == df_profit['product_id']]

Aber das funktioniert so leider nicht.

Dort bekomme ich dann einen Value Error. Dessen Ursprung ich auch verstehe und logisch finde.

Code: Alles auswählen

ValueError: Can only compare identically-labeled Series objects

[__blackjack__]

@Freumel: Das sieht aus als wenn Du versuchst DataFrames als Datenbank zu missbrauchen und das einiges davon vielleicht in DB-Abfragen besser aufgehoben wäre. In beiden Fällen muss man darauf achten das effizient gejoined werden kann. Bei einer Datenbank wo die IDs PRIMARY KEY-Spalten sind, ist das ja im Grunde garantiert, dass es da zumindest *irgend* eine Art von Index gibt, bei DataFrames muss man da selber aufpassen, das die SELECT-Umsetzung auf `df[<ausdruck> <vergleich> <ausdruck>]` nicht ganz doof einfach immer alle Zeilen durchgeht sondern irgendwie effizienter stattfindet. Denn das ist sonst eine Quelle für quadratische Laufzeit und würde dann bei 100K Produkten und 1M Services zu 100 Milliarden inneren Schleifendurchläufen führen. Das dauert dann tatsächlich ewig.

Wenn die Daten nicht in einer Datenbank sind und auch aus irgendwelchen Gründen auch in keine geschrieben werden können, müsste man sich das nachprogrammieren. Also beispielsweise Wörterbücher aufbauen die IDs auf die dazugehörigen Werte abbilden um *schnell* von einer ID zu den Daten eines Services zu kommen, ohne immer die ganze Million von Services nacheinander abzuklappern bis man die ID gefunden hat.

Man sollte sich auch immer überlegen was denn bei den einzelnen Schritten tatsächlich hinter den Kulissen von Pandas und Numpy gemacht werden muss. Du machst da einiges mehrfach und einiges was nicht nötig ist.

Der Teilausdruck ``df_service['product_id']==row['product_id']`` kommt im Schleifenkörper 5× mal vor. Jedes mal kostet das Rechenzeit und jedes mal wird das Objekt kurz danach wieder weggeworfen. Das Ergebnis ist aber immer das gleiche.

Dann das hier:

Code: Alles auswählen

    data_dict["count_positive_services"].append(
        df_service[
            (df_service["product_id"]==row["product_id"]) &
            (df_service["current_total_sum"] < 0)
        ].shape[0]
    )
    
    data_dict["count_negative_services"].append(
        df_service[
            (df_service["product_id"]==row["product_id"]) &
            (df_service["current_total_sum"] >= 0)
        ].shape[0]
    )

Da kommt der Ausdruck auch zweimal vor, den sollte man da also heraus ziehen:

Code: Alles auswählen

    product_mask = df_service["product_id"] == row["product_id"]

    ...

    data_dict["count_positive_services"].append(
        df_service[
            product_mask & (df_service["current_total_sum"] < 0)
        ].shape[0]
    )

    data_dict["count_negative_services"].append(
        df_service[
            product_mask & (df_service["current_total_sum"] >= 0)
        ].shape[0]
    )

Dann macht das wenig Sinn die entstehende ”Bitmaske” tatsächlich als Index in `df_service` zu verwenden wenn alles was man wissen will ist wie viele Ergebnisse das bringt. Das erstellt ja einen neuen Dataframe, kopiert da die Treffer rein, nur um die Länge abzufragen und die Daten dann wieder wegzuwerfen. Wobei das mit ``shape[0]`` auch recht kryptisch formuliert ist. Was man eigentlich will ist in der Maske die `True`-Werte zählen, also aufsummieren:

Code: Alles auswählen

    product_mask = df_service["product_id"] == row["product_id"]

    ...

    data_dict["count_positive_services"].append(
        (product_mask & (df_service["current_total_sum"] < 0)).sum()
    )

    data_dict["count_negative_services"].append(
        (product_mask & (df_service["current_total_sum"] >= 0)).sum()
    )

Jetzt sieht man aber auch, das bei ``df_service["current_total_sum"] < 0`` 100K Verträge in der Spalte auf <0 getestet werden. Und im zweiten Ausdruck dann 100K Verträge auf genau das Gegenteil getestet werden. Das zweite Ergebnis sollte man doch aber leicht aus dem ersten errechnen können, denn alle bei denen <0 nicht zutrifft müssen ja ≥0 sein (sofern hier kein NaN vorkommen kann).

Code: Alles auswählen

    positive_service_mask = df_service["current_total_sum"] < 0
    positive_service_count = (product_mask & positive_service_mask).sum()
    data_dict["count_positive_services"].append(positive_service_count)

    data_dict["count_negative_services"].append(
        product_mask.sum() - positive_service_count
    )

Und diese `positive_service_mask` wird für jede Iteration der ``for``-Schleife erstellt, der Wert ändert sich ja aber nie, also kann und sollte man das *einmal* *vor* der Schleife machen.