Dataframes kombinieren durch Vergleich einer Spalte

[ThomasS]

Hallo,

meine Ausgangssituation stellt sich vereinfacht wie folgt dar:

Code: Alles auswählen

import pandas as pd

dx = {
    'bezeichnung': ['Erlöse' , 'Aufwand' , 'Material_19' , 'Gewinn'] ,
    '2020': ['1001' , '900' , '90' , '11']
}

dx = {
    'bezeichnung': ['Aufwand' , 'Erlöse' , 'Material_16' , 'Gewinn'] ,
    '2019': ['1900' , '2001' , '80' , '21']
}

df1 = pd.DataFrame(dx)
df2 = pd.DataFrame(dy)

Es handelt sich hier um Auszüge aus Buchhaltungsdaten für verschiedene Zeiträume. Diesen Daten ist gemein, dass eine große Zahl der Elemente in Spalte 'Bezeichnung' in beiden dataFrames enthalten ist.

Was nun erreicht werden soll ist Folgendes:

• Unter Beibehaltung der Reihenfolge der Spalte 'Bezeichnung' in df1 soll getestet werden, ob für einen Eintrag in Spalte 'bezeichnung' in df2 ein korrespondierender Eintrag in df1 vorhanden ist. Ist dies der Fall, so soll der jeweilige Wert in Spalte '2019' in df2 in eine neue Spalte '2019' in df1 oder einem neuen df3 zum passenden Wert in Spalte 'bezeichnung' übernommen werden.

• Gibt es keinen korrespondierenden Eintrag in Spalte 'bezeichnung' in df1 so soll der jeweilige Eintrag als neuer Eintrag am Ende der Spalte 'bezeichnung' in df1 oder df3 hinzugefügt werden und der zugehörige Eintrag in Spalte '2019' in df1 bzw. df3 übernommen werden.

Das gewünschte Ergebnis würde vorliegend dann wie folgt aussehen:

Code: Alles auswählen

df1 = pd.DataFrame('bezeichnung': ['Erlöse' , 'Aufwand' , 'Material_19' , 'Gewinn', 'Material_16'] ,
    '2020': ['1001' , '900' , '90' , '11', '0'] ,
    '2019': ['2001' , '1900' , '0' , '21', '80'])

Wenn möglich, wäre ganz optimal aber Folgendes vorgehen:

Wenn es in Spalte 'bezeichnung' von df1 keinen exakten Treffer zum Eintrag in Spalte 'bezeichnung' des df2 gibt, aber zumindest der Anfang gleich ist, bei Buchstaben die ersten drei Zeichen und im Fall von Zahlen eine Ziffer, dann füge den Eintrag aus df2 unter den ersten Treffer nach vorgenannten Kriterien in df1 bzw. df3 ein.

Dies ist aber optional, da m.E. wohl nicht ganz trivial.

Vielen Dank im Voraus.

[Sirius3]

Ich verstehe zwar nicht genau, was Du da zu erklären zu versuchst, aber Du benutzt Dataframes falsch.
Du hast zweidimensionale Daten, wobei das, was Du `bezeichnung` nennest, eigentlich Spaltenüberschriften sind (column) und die Jahreszahlen ist die Index-Spalte des Dataframes.

Code: Alles auswählen

import pandas as pd

dx = {
    'bezeichnung': ['Erlöse' , 'Aufwand' , 'Material_19' , 'Gewinn'] ,
    '2020': ['1001' , '900' , '90' , '11']
}

dy = {
    'bezeichnung': ['Aufwand' , 'Erlöse' , 'Material_16' , 'Gewinn'] ,
    '2019': ['1900' , '2001' , '80' , '21']
}

df1 = pd.DataFrame(dx).set_index('bezeichnung').T
df2 = pd.DataFrame(dy).set_index('bezeichnung').T
df3 = df1.append(df2)

[ThomasS]

Danke für Deine Antwort. Aber ich denke, Deine Lösung funktioiert in meinem Fall nicht.

Ich spreche jetzt mal in Tabelle-Terminologie. Vielleicht wird es dann klarer:

df1 besteht in echt aus ca. 300 Zeilen und 6 Spalten. Spalte 1 ist die mit 'bezeichnung' bezeichnete Spalte. In den Spalten 2 bis 6 sind jeweils Zahlen für verschiedene Jahre eingetragen. Denselben Aufbau habe ich bei df2 nur, dass es in df1 Reihen in der Spalte 'bezeichnung' gibt, die es in df2 nicht gibt und umgekehrt. Wichtig ist, dass die Sortierung, d.h. die Reihenfolge wie in df1 erhalten bleibt. Anosnten möchte ich gerne die Werte sich entsprechenden Einträge der Zeilen zu 'bezeichnung' aus df2 in neue Spalten dem df1 hinzufügen. Und alle nicht entsprechenden unten der Spalte bezeichnung anfügen und die Werte aus den Spalten 2-6 aus df2 entsprechend in die neuen Spalten in df1 anfügen.

[Sirius3]

Für Dein Beispiel stimmt alles. Was also funktioniert nicht richtig? Gib ein Beispiel, das so einen Fall zeigt.

[ThomasS]

Für Dein Beispiel stimmt alles. Was also funktioniert nicht richtig? Gib ein Beispiel, das so einen Fall zeigt.

Die richtige Antwort sieht wie folgt aus:

Code: Alles auswählen

	import pandas as pd
	import numpy as np

	dx = {
	    'bezeichnung': ['Erlöse' , 'Aufwand' , 'Material_19' , 'Gewinn'] ,
	    '2020': ['1001' , '900' , '90' , '11']
	}

	dy = {
	    'bezeichnung': ['Aufwand' , 'Erlöse' , 'Material_16' , 'Gewinn'] ,
	    '2019': ['1900' , '2001' , '80' , '21']
	}

	# your expected results
	df = pd.DataFrame({'bezeichnung': ['Erlöse' , 'Aufwand' , 'Material_19' , 'Gewinn', 'Material_16'] ,
	    '2020': ['1001' , '900' , '90' , '11', '0'] ,
	    '2019': ['2001' , '1900' , '0' , '21', '80']})

	df1 = pd.DataFrame(dx)
	df2 = pd.DataFrame(dy)
	col = 'bezeichnung'
	temp = pd.merge(df1, df2, on=col, how='outer', indicator=False)
	temp = temp.replace(np.nan, '0')
	# you can check if two dfs are equal
	if temp.equals(df):
	    print('Two dfs are equal.\n')
	print(temp)

Mein Verständnis vom DataFrame hat schon gestimmt...

Dennoch danke.

[Sirius3]

@ThomasS: ich wiederhole mich. aber Du nutzt Dataframes falsch. Der Inhalt der bezeichnung-Spalte ist eindeutig das, was als Spaltenüberschriften da stehen sollte.
Du hast in Deiner Antwort nur wiederholt, was Du schon in Deinem ersten Beitrag geschrieben hast, aber die Frage war ja, warum die richtige Benutzung von Dataframes in Deinem Fall nicht funktioniert.

[ThomasS]

@ThomasS: ich wiederhole mich. aber Du nutzt Dataframes falsch. Der Inhalt der bezeichnung-Spalte ist eindeutig das, was als Spaltenüberschriften da stehen sollte.
Du hast in Deiner Antwort nur wiederholt, was Du schon in Deinem ersten Beitrag geschrieben hast, aber die Frage war ja, warum die richtige Benutzung von Dataframes in Deinem Fall nicht funktioniert.

Weil es durch die Benutzung wie in Deinem Beispiel un meinem Real World Szenario zu einer Doppelung der Spalte bzw. in Deiner Lösung der Zeile Bezeichnung kommt...

Dies ist bei der beigefügten Lösung nicht so, deshalb erscheint mir, dass es hier kein defintives Richtig oder Falsch gibt....

[einfachTobi]

Sirius3 hat schon Recht damit. Du machst Spalten zu Zeilen, die aber wiederum als Spalten fungieren sollen. Rein interessehalber: Wie sieht dein Input ursprünglich aus (gerne ein Beispiel mit mehr Daten, welches auch die Unterschiede bzw. Gemeinsamkeiten der einzelnen Datensätze deutlich macht)? Was willst du mit den Daten am Ende machen? Gemeint ist dabei die globale Fragestellung, nicht diese hier gestellte, konkrete. Vielleicht können wir da eine Lösungen finden, die früher ansetzt und dir nicht später auf die Füße fallen wird.

[ThomasS]

Sirius3 hat schon Recht damit. Du machst Spalten zu Zeilen, die aber wiederum als Spalten fungieren sollen. Rein interessehalber: Wie sieht dein Input ursprünglich aus (gerne ein Beispiel mit mehr Daten, welches auch die Unterschiede bzw. Gemeinsamkeiten der einzelnen Datensätze deutlich macht)? Was willst du mit den Daten am Ende machen? Gemeint ist dabei die globale Fragestellung, nicht diese hier gestellte, konkrete. Vielleicht können wir da eine Lösungen finden, die früher ansetzt und dir nicht später auf die Füße fallen wird.

Hi Tobi,

danke für Deine Antwort.

Anbei einmal tatsächliche Daten:

Code: Alles auswählen

# Import Module
import pandas as pd
import numpy as np

col = 'Bezeichnung'
tl1 = {'Bezeichnung': {0: 'Zimmer' , 1: '4906  Hotelkosten für Überbuchung' , 2: '8310  Logis 7% USt' ,
                       3: '8311  Sonstige Logis 7% USt' , 4: '8950  Nicht steuerbare Umsätze (In' ,
                       5: '83105  Logis 5% USt' , 6: '83115  Sonstige Logis 5% USt' , 7: 'Essen' ,
                       8: '8300  Cateringerlöse 7% USt' , 9: '8400  Room Service Food allg. Ste' ,
                       10: '8401  Restaurant 2 Food allg. Steu' , 11: '8402  Restaurant 1 Food allg.Steue' ,
                       12: '8403  Bar Food allg. Steuersatz' , 13: '8404  Tagung Food allg. Steuersatz' ,
                       14: '8421  Erlöse Food Frühstück allg. S' , 15: '84005  Room Service Food 5% US' ,
                       16: '84015  Restaurant 2 Food 5% UST' , 17: '84025  Restaurant 1 Food 5% UST' ,
                       18: '84035  Bar Food 5% UST' , 19: '84045  Tagung Food 5% UST' ,
                       20: '84055  Bankett Food 5% UST' , 21: '84215  Erlöse Food Frühstück 5%' , 22: 'Getränke' ,
                       23: '8406  Room Service Beverage allg.'} ,
       '2020': {0: '905.450' , 1: '0' , 2: '414.357' , 3: '5.319' , 4: '41.948' , 5: '439.539' , 6: '4.287' ,
                7: '181.595' , 8: '720' , 9: '2.475' , 10: '8.916' , 11: '20.421' , 12: '2.089' , 13: '21.901' ,
                14: '42.392' , 15: '2.829' , 16: '2.417' , 17: '14.106' , 18: '1.222' , 19: '15.574' , 20: '2.563' ,
                21: '43.972' , 22: '82.917' , 23: '244'} ,
       '2019': {0: '2.957.349' , 1: '-760' , 2: '2.922.236' , 3: '19.271' , 4: '16.602' , 5: '0' , 6: '0' ,
                7: '645.880' , 8: '2.102' , 9: '6.906' , 10: '112.768' , 11: '126.883' , 12: '17.172' , 13: '129.962' ,
                14: '250.087' , 15: '0' , 16: '0' , 17: '0' , 18: '0' , 19: '0' , 20: '0' , 21: '0' , 22: '318.459' ,
                23: '1.131'} ,
       '2018': {0: '2.797.634' , 1: '-92' , 2: '2.756.496' , 3: '18.437' , 4: '22.794' , 5: '0' , 6: '0' ,
                7: '602.267' , 8: '2.278' , 9: '5.038' , 10: '123.446' , 11: '92.245' , 12: '19.996' , 13: '114.940' ,
                14: '244.323' , 15: '0' , 16: '0' , 17: '0' , 18: '0' , 19: '0' , 20: '0' , 21: '0' , 22: '301.600' ,
                23: '1.030'} ,
       '2017': {0: '2.947.890' , 1: '0' , 2: '2.910.575' , 3: '14.839' , 4: '22.476' , 5: '0' , 6: '0' , 7: '688.323' ,
                8: '929' , 9: '8.624' , 10: '101.476' , 11: '95.393' , 12: '23.785' , 13: '187.388' , 14: '270.729' ,
                15: '0' , 16: '0' , 17: '0' , 18: '0' , 19: '0' , 20: '0' , 21: '0' , 22: '348.378' , 23: '1.631'}}

tl2 = {'Bezeichnung': {0: 'Zimmer' , 1: '8310  Logis USt erm.' , 2: '8311  Sonstige Logis USt erm.' ,
                       3: '8950  Nicht steuerbare Umsätze (Inn' , 4: 'Essen' , 5: '8300  Cateringerlöse 7% USt' ,
                       6: '8400  Room Service Food allg. Steue' , 7: '8401  Restaurant 2 Food allg. Steuer' ,
                       8: '8402  Restaurant 1 Food allg.Steuers' , 9: '8403  Bar Food allg. Steuersatz' ,
                       10: '8404  Tagung Food allg. Steuersatz' , 11: '8405  Bankett Food allg. Steuersatz' ,
                       12: '8421  Erlöse Food Frühstück allgem.' , 13: 'Getränke' ,
                       14: '8406  Room Service Beverage allg. S' , 15: '8407  Restaurant 2 Beverage allg. St' ,
                       16: '8408  Restaurant 1 Beverage allg. St' , 17: '8409  Catering Beverage allg. Steuer' ,
                       18: '8413  Bar Beverage allgem.Steuersat' , 19: '8419  Tagung Beverage allgem.Steue' ,
                       20: '8420  Bankett Beverage allg. Steuers' , 21: '8423  Getränke Frühstück allgem.Ste' ,
                       22: 'Minibar' , 23: '8414  Minibar allgem.Steuersatz'} ,
       '2017': {0: '2.976.479' , 1: '2.928.815' , 2: '21.797' , 3: '25.867' , 4: '622.322' , 5: '1.653' , 6: '6.597' ,
                7: '96.953' , 8: '98.265' , 9: '23.302' , 10: '141.156' , 11: '0' , 12: '254.396' , 13: '324.966' ,
                14: '1.080' , 15: '23.602' , 16: '44.395' , 17: '505' , 18: '67.266' , 19: '78.989' , 20: '55' ,
                21: '109.073' , 22: '19.547' , 23: '19.547'} ,
       '2016': {0: '2.947.890' , 1: '2.910.575' , 2: '14.839' , 3: '22.476' , 4: '688.323' , 5: '929' , 6: '8.624' ,
                7: '101.476' , 8: '95.393' , 9: '23.785' , 10: '187.388' , 11: '0' , 12: '270.729' , 13: '348.378' ,
                14: '1.631' , 15: '25.657' , 16: '37.459' , 17: '269' , 18: '65.944' , 19: '100.698' , 20: '691' ,
                21: '116.029' , 22: '19.719' , 23: '19.719'} ,
       '2015': {0: '2.376.582' , 1: '2.341.732' , 2: '18.540' , 3: '16.310' , 4: '551.994' , 5: '441' , 6: '6.802' ,
                7: '83.296' , 8: '87.639' , 9: '20.253' , 10: '142.562' , 11: '0' , 12: '211.002' , 13: '277.536' ,
                14: '986' , 15: '21.180' , 16: '33.064' , 17: '71' , 18: '49.610' , 19: '81.878' , 20: '318' ,
                21: '90.429' , 22: '16.818' , 23: '16.818'} ,
       '2014': {0: '2.425.115' , 1: '2.370.878' , 2: '15.909' , 3: '38.328' , 4: '568.161' , 5: '222' , 6: '4.950' ,
                7: '44.009' , 8: '90.003' , 9: '20.889' , 10: '148.670' , 11: '25.437' , 12: '233.981' , 13: '281.672' ,
                14: '1.009' , 15: '14.645' , 16: '31.167' , 17: '0' , 18: '51.831' , 19: '80.105' , 20: '2.647' ,
                21: '100.267' , 22: '17.189' , 23: '17.189'} ,
       }
df1 = pd.DataFrame(tl1)
df2 = pd.DataFrame(tl2)
df2 = df2.drop(['2017'], 1)
temp = pd.merge(df1, df2, on=col, how='outer', indicator=False)
temp = temp.replace(np.nan, '0')

Es handelt sich hier um Auszüge aus Betriebswirtschaftlichen Auswertungen für verschiedene Zeiträume. Aufgrund der verschiedenen Zeiträume unterscheiden sich die in Spalte Bezeichnung dargestellten Konten teilweise. Mein Ziel ist es, die beiden Datenbestände zusammenzuführen, so dass ich sie nach Excel exportieren kann. Wichtig ist dabei der Erhalt des grundlegenden Gliederungsschemas. Was mir an dem Ergebnis nicht 100% gefällt, ist die Tatsache, dass bspw. alle Konten die mit 84 beginnen entsprechend mit den jeweiligen Werten aufeinander folgen sollten. Man kann das aber nicht durch eine Sortierung der gesamten Spalte lösen, da dann der grundlegende Aufbau geändert würde.

Verstehst Du in etwa worauf ich hinaus will oder soll ich es noch mehr an einem Beispiel verdeutlichen?

[__blackjack__]

@ThomasS: Da ist für meinen Geschmack zu viel Unregelmässigkeit und ”optische Entscheidung” drin als das ich da Pandas für missbrauchen würde. Das in den Spalten beispielsweise Summen drin sind. Und auch das alles Zahlen sind die keine Zahlen sind, würde mich nervös machen.

[snafu]

Ich sehe da keine Unregelmäßigkeiten. Die "echten" Zahlen repräsentieren halt den Spaltenindex. Da kann man zur Vereinfachung auch Listen nehmen. In den Spaltenbezeichnungen sind die Kontonummern, gefolgt von den Kontenbezeichnungen. Hierbei wurde der Kontenplan anscheinend mitten in 2017 geändert, falls das jetzt wirklich die echten Angaben sind. Das ist ungewöhnlich, weil man solche Änderungen normalerweise nur mit Beginn des neuen Geschäftsjahres macht. Achso, und die Strings unter den Jahren dürften einfach Angaben in deutscher Schreibweise mit Tausender-Trennzeichen sein. Wahrscheinlich wurde da ein Jahresvergleich gemacht und dieser dann exportiert. Das Ganze einmal für die Jahre vor und einmal für die Jahre nach der Umstellung des Kontenplans. Und dass es sich um eine BWA handelt, wurde ja schon angesprochen.

Im ersten Schritt würde man also die Struktur der Darstellung anpassen und auch die besagen Strings bei den Jahren zu Integern machen (vermutlich sind das die Saldenwerte der Konten vom Jahresabschluss). Die Tausender-Trenner können dabei einfach wegfallen. Dann muss man halt schauen, wie man mit der Änderung ab 2017 umgehen will. So hundertprozentig kann man das ja nicht zusammenführen, wenn neue Konten dazukommen und alte wegfallen oder umbenannt wurden. Möglich wäre ein gemeinsamer Kontenplan, wo nicht genutzte Konten halt mit Null belegt werden. Ist später nur etwas verwirrend, weil man nicht erkennt, ob das tatsächlich ein ungenutztes Konto aus dem regulären Kontenplan war oder ein Konto, das in den Vorjahren schlichtweg noch nicht existiert hatte. Wie willst du da denn gerne vorgehen?

[snafu]

Sofern meine Annahmen zutreffen, käme ich für die Aufbereitung der Daten auf das hier:

Code: Alles auswählen

from pprint import pprint

DATA = {'Bezeichnung': {0: 'Zimmer' , 1: '4906  Hotelkosten für Überbuchung' , 2: '8310  Logis 7% USt' ,
                       3: '8311  Sonstige Logis 7% USt' , 4: '8950  Nicht steuerbare Umsätze (In' ,
                       5: '83105  Logis 5% USt' , 6: '83115  Sonstige Logis 5% USt' , 7: 'Essen' ,
                       8: '8300  Cateringerlöse 7% USt' , 9: '8400  Room Service Food allg. Ste' ,
                       10: '8401  Restaurant 2 Food allg. Steu' , 11: '8402  Restaurant 1 Food allg.Steue' ,
                       12: '8403  Bar Food allg. Steuersatz' , 13: '8404  Tagung Food allg. Steuersatz' ,
                       14: '8421  Erlöse Food Frühstück allg. S' , 15: '84005  Room Service Food 5% US' ,
                       16: '84015  Restaurant 2 Food 5% UST' , 17: '84025  Restaurant 1 Food 5% UST' ,
                       18: '84035  Bar Food 5% UST' , 19: '84045  Tagung Food 5% UST' ,
                       20: '84055  Bankett Food 5% UST' , 21: '84215  Erlöse Food Frühstück 5%' , 22: 'Getränke' ,
                       23: '8406  Room Service Beverage allg.'} ,
       '2020': {0: '905.450' , 1: '0' , 2: '414.357' , 3: '5.319' , 4: '41.948' , 5: '439.539' , 6: '4.287' ,
                7: '181.595' , 8: '720' , 9: '2.475' , 10: '8.916' , 11: '20.421' , 12: '2.089' , 13: '21.901' ,
                14: '42.392' , 15: '2.829' , 16: '2.417' , 17: '14.106' , 18: '1.222' , 19: '15.574' , 20: '2.563' ,
                21: '43.972' , 22: '82.917' , 23: '244'} ,
       '2019': {0: '2.957.349' , 1: '-760' , 2: '2.922.236' , 3: '19.271' , 4: '16.602' , 5: '0' , 6: '0' ,
                7: '645.880' , 8: '2.102' , 9: '6.906' , 10: '112.768' , 11: '126.883' , 12: '17.172' , 13: '129.962' ,
                14: '250.087' , 15: '0' , 16: '0' , 17: '0' , 18: '0' , 19: '0' , 20: '0' , 21: '0' , 22: '318.459' ,
                23: '1.131'} ,
       '2018': {0: '2.797.634' , 1: '-92' , 2: '2.756.496' , 3: '18.437' , 4: '22.794' , 5: '0' , 6: '0' ,
                7: '602.267' , 8: '2.278' , 9: '5.038' , 10: '123.446' , 11: '92.245' , 12: '19.996' , 13: '114.940' ,
                14: '244.323' , 15: '0' , 16: '0' , 17: '0' , 18: '0' , 19: '0' , 20: '0' , 21: '0' , 22: '301.600' ,
                23: '1.030'} ,
       '2017': {0: '2.947.890' , 1: '0' , 2: '2.910.575' , 3: '14.839' , 4: '22.476' , 5: '0' , 6: '0' , 7: '688.323' ,
                8: '929' , 9: '8.624' , 10: '101.476' , 11: '95.393' , 12: '23.785' , 13: '187.388' , 14: '270.729' ,
                15: '0' , 16: '0' , 17: '0' , 18: '0' , 19: '0' , 20: '0' , 21: '0' , 22: '348.378' , 23: '1.631'}}
                
def main():
    bwa = {}
    for jahr in range(2017, 2020 + 1):
        konten = DATA["Bezeichnung"].values()
        salden = [
            int(saldo.replace(".", ""))
            for saldo in DATA[str(jahr)].values()
        ]
        bwa[jahr] = dict(zip(konten, salden))
    pprint(bwa)

if __name__ == "__main__":
    main()

[__blackjack__]

@snafu: Die Unregelmässigkeit die mich da stört sind die Zwischensummen. Die müsste man rausfiltern, wenn man damit dann tatsächlich arbeiten/rechnen will. Die dürften auch der Grund sein, warum die Reihenfolge ja nicht geändert werden darf, denn sonst wäre die ja erst mal egal. Um das regelmässig zu machen, müssten die Zeilen mit den Zwischensummen raus und die Bezeichnung dieser Zeilen in eine eigene Spalte für den jeweiligen Block der folgt, denn die Bezeichnung der Zwischensumme ist ja eigentlich eine Kategorie zu der eine Gruppe von Konten gehört.

[snafu]

Scheinbar soll ja auch die ursprüngliche Struktur erhalten bleiben. Das meintest du vielleicht mit "optische Entscheidung": Sieht nett aus für die Darstellung, aber nicht für den Zugriff, weil man dabei nur mit den Indexwerten arbeiten kann.

Jedenfalls zur Frage der gemeinsamen Spalten: Das kann man mit Sets lösen. Basierend auf dem Code des TEs weiter oben:

Code: Alles auswählen

print(set(dx["bezeichnung"]) | set(dy["bezeichnung"]))

Oder exakt auf die Musterlösung gemünzt:

Code: Alles auswählen

bezeichungen = dx["bezeichnung"] + list(set(dy["bezeichnung"]) - set(dx["bezeichnung"]))