[Python] Automatisiertes vergleichen/ID ändern/speichern

[Crys]

Hallo zusammen,

hier mal Problem, an dem ich schon seit Monaten sitze:

Ich möchte nachfolgendes:

1. csv-Dateien (3-30 Stück) einlesen
2. in der ersten (und bei einem Typ zweiten) Spalte steht eine "ID". Die Struktur ist immer anderes (Zahlen, Buchstaben, Minus; fünf bis 20 Ziffern) (groß und klein Buchstaben, deshalb geht das nicht in Excel selbst)
3. Dann soll eine neue Liste erstellt werden, mit "alter ID", "alter ID aus zweiter Spalte", und eventuell weiteren IDs ... plus eine neue einmalige ID
4. in den csv Dateien das ersetzten aller alter IDs mit den neuen IDs
5. Wenn in einer csv-Datei mehere Zellen die selbe ID haben soll der Inhalt der Wert Spalte summiert werden
6. abspeichern der csv Dateien als Blätter in einer xslx-Datei (kompribiert, da sonst sehr groß)

Es sind einfach sehr viele und vor allem große csv Dateien (von Mess- und Simulationswerten) sodass ich diese zum Teil nicht mal in Excel öffnen kann.

Was ich zuletzte versucht habe:

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import xlrd

# manuelles Umwandeln der csv in xlsx
book_pv = xlrd.open_workbook("test.xlsx")
book_heat = xlrd.open_workbook("test2.xlsx")

sh_pv = book_pv.sheet_by_index(0)
sh_heat = book_heat.sheet_by_index(0)

ids_pv = sh_pv.col(0, start_rowx=43)
ids_heat = sh_heat.col(0, start_rowx=22)
ids = ids_pv + ids_heat

import collections, numpy
neu_ids = numpy.array(ids)

from collections import Counter
Counter(ids).keys()
Counter(ids).values()
# feststellen, dass es angeblich jede Spalte nur einmal gibt, obwohl (siehe Beispiel) welche den selben Inhalt haben sollten

Spoiler: Beispiel Tabellen

Eingabe -
je eine CSV-Datei:

Tabelle1:
ID, Wert
A1, 15
B1, 23
E1, 14
B1, 3

Tabelle2:
ID, pID, Wert
A1, C1, 10
D1,   , 6
E1, C1, 11

Tabelle3:
ID, Wert
G1, 5
E1, 18
C1, 18
G1, 2


Ausgabe -
eine XLSX-Datei:

Blatt1:
ID, Wert
n1, 29
n2, 26

Blatt2:
ID, pID, Wert
n1, 21
n3, 6

Blatt3:
ID, Wert
n4, 7
n1, 36

Wenn Ihr mir weiter helfen könnt ... ein paar Skript fetzten oder etwas was schon tatsächlich lauffähig ist, dann wäre das super genial.

Viele Dank.

 

[el guapo]

Es würde sehr helfen, wenn Du Dein Problem nicht (nur) mit Worten, sondern auch als minimales Beispiel beschreibst. Also eine bzw. zwei minimale Beispielzeilen und das erwartete Ergebnis nach dem Vergleich. Die Beschreibung zu den IDs ist etwas verwirrend. Ich nehme an, dass Du einige voneinander unabhängige Schritte brauchst (bspw. zur Vorverarbeitung bei der horizontalen Summierung).

 

[BeBur]

groß und klein Buchstaben, deshalb geht das nicht in Excel selbst

CSV nicht als Datei öffnen, sondern eine leere Excel Datei erstellen, dann auf "Daten -> Import" oder so klicken, dann checkt Excel erst was du willst.

Was heißt 'groß'?

 

[Crys]

@el guapo Sorry, verstehe ich nicht. Code und Beispiel Tabelle ist und war schon immer in obiger Frage.

@BeBur Die IDs können nur per Funktion "IDENTISCH" verglichen werden (?). Diese ist aber nicht case sensitive, also wäre A1 und a1 identisch, was nicht der Fall ist.
Wie man eine csv in Excel importiert habe ich vor 20 Jahren gelernt
Wenn die Frage auf die Dateigröße bezogen ist: bis zu 1GB bzw. bis zu 1,2Mio. Zeilen je Datei sind möglich. Meist aber 50-100MB.

 

[el guapo]

@Crys Tut mir leid, ich habe am Handy die Tabellen übersehen. Aber ich verstehe sie nicht: warum werden bei Tabelle 1 A1 und E1 summiert und bei Tabelle 3 E1 und G1?

 

[Crys]

@el guapo kein Problem. G1 sollte nur mit sich selbst summiert werden. A1, C1 & E1 sind über Tabelle2 miteinander verbunden.

 

[galactix]

Du solltest dir genau überlegen welche Schritte notwendig sind und in welcher Reihenfolge diese ausgeführt werden müssen, um dein Problem zu lösen.
Soweit ich das verstanden habe müsste es in etwa so ablaufen:

1. Einlesen der Daten aus den CSV-Dateien (hier brauchst du noch keine .xlsx-Datei erstellen, speicher die Daten erstmal in einer Liste)
2. Aggregieren der Werte mit gleicher ID (würde hier eine Map* verwenden mit Key: Id und Value: Wert. Dann kannst du über die Liste mit den CSV Daten iterieren und wenn eine Id auftritt, die in der Map nicht enthalten ist diese eintragen, sonst den Wert dazuaddieren)
3. Für jede alte Id eine neue Id erzeugen (am besten hier auch wieder in einer Map speichern: alteId -> neueId)
4. In den CSV Dateien die alten Ids mit den neuen überschreiben
5. Die neuen Ids und die dazugehörigen aggregierten Werte in eine .xlsx-Datei schreiben

*edit: was ich mit Map meine heißt in Python dictionary

 

[el guapo]

Hier muss ich galactix widersprechen:

zu 1 und 2) Ich würde keine Liste nehmen (falls eine Python-Liste gemeint war), sondern unbedingt ein Pandas-Dataframe. Das erspart einem in Schritt 2 das Iterieren über die teilwweise großen Daten. Pandas bzw. die Dataframes bieten integrierte Gruppierungs- und Aggregierungsfunktionen.

zu 3) Ggf. kann man, wenn alles abgearbeitet ist (ja, hier fehlen leider noch ein paar Schritte), sich eine neue ID durch das Setzen eines neuen Dataframe-Index automatisch erzeugen.

4) Du kannst die alten IDs vielleicht(!) nicht überschreiben, da Du sie benötigst um zu erkennen, welche Daten auf ein Blatt gehören.

5) Das Schreiben in Excel(-Arbeitsblätter) wird von Pandas ebenfalls direkt unterstützt.

Ich hoffe, das war nicht verwirrend. Aber Hut ab, das ist algorithmisch sehr knackig.

 

[abcddcba]

Was genau geht jetzt mit Pandas nicht? Und wenn die Performance wirklich ein Problem sein sollte, du kannst jederzeit eine Datenbank nehmen und mit SQL arbeiten. Ob das notwendig ist kann ich nicht beurteilen, in der Regel kommt man mit Python und Pandas ganz gut hin. Und auch das kann Indices erstellen, also kann man super Joins etc. machen. Falls das nicht reicht, relationale DB deiner Wahl, die haelt zumindest nicht alles direkt im Speicher und kann bestimmte Datentypen gut optimieren.

 

[el guapo]

@Crys Noch mal ins Unreine (und iterativ) laut gedacht: ich verstehe es so, dass die letztlich zusammengesetzten Schlüssel aus Tabelle2 maßgeblich sind.

Idee: Du hältst während der Verarbeitung die Tabellen getrennt und prüfst zeilenweise, ob der Eintrag aus Tabelle1 in den IDs von Tabelle2 enthalten ist. Wenn nein, wird der Wert in Tabelle1-neu (ggf. aggregiert) übernommen, wenn ja werden die zugehörigen Werte der passenden IDs aggregiert und in Tabelle1-neu übernommen. Tabelle 3 würde genauso funktionieren. Tabelle2 ist - zumindest im Beispiel - eine simple Aggregation der ID2. Ist aber leider sehr iterativ gedacht und man muss aufpassen, bei Tabelle1 und Tabelle3 nichts doppelt zu zählen (aus Original-Tabelle löschen?)

 

[Crys]

Danke @el guapo

Wie mache ich das konkrett?

import pandas as pd
df = pd.read_csv("test2.csv", sep=';', encoding='utf-8', skip_blank_lines=False, error_bad_lines=False, warn_bad_lines=False, skiprows=21)

# Dann weiß ich nicht mehr weiter ...
INDEX=1
while HÖHE_TABELLE
    if df(ZEILE SPALTE_EINS) IST NICHT IN ID_Tabelle
        SCHREIBE IN ID_Tabelle + INDEX++
    else
        TUE NICHTS
        
    if df(ZEILE SPALTE_ZWEI) IST NICHT IN ID_Tabelle
        SCHREIBE IN ID_Tabelle + INDEX
    else
        TUE NICHTS

 

[mercury]

ich bin mir nicht sicher, ob ich deine Anforderung richtig verstanden habe, aber ich versuche zu helfen:

import glob
import pandas as pd
from functools import reduce

files = glob.glob('./cb/*.csv')

dfs = []
for file in files:
    df = pd.read_csv(file)
    dfs.append(df)
    
new_df = reduce(lambda df1, df2: pd.merge(df1, df2, how='outer', on=['ID']), dfs)

new_df

als neue ID kannst zB einfach den Index von new_df nehmen.

 

[sandreas]

Ist das ganze ein wiederkehrender Import? Also rechnest du damit, dass du in ein paar Wochen wieder einen Satz von csv Dateien importieren musst, oder ist das eine einmalige Sache?

 

[Crys]

@mercury nein, aber trotzdem Danke.

@sandreas ja, ständig. Alle Woche ein paar neue Daten. Das Workaround mit Excel ist nur sehr kompliziert und kostet einfach sehr viel stupide Zeit.

 

[sandreas]

ja, ständig. Alle Woche ein paar neue Daten.

Dann solltest du dir dringend über einen anständigen Workflow Gedanken machen. Überführe deine Daten am besten in eine Datenbank (sqlite reicht hier meiner Ansicht nach völlig aus). Die sind nämlich für solche Vergleichs-, Sortier- und Abruf-Operationen ausgelegt.
Ich würde das so machen:

1. Automatisierter Import der Quelldaten in eine Datenbank, ohne die Quelldaten zu modifizieren (eine Tabelle pro CSV)
2. Erstellen einer "Export"-Tabelle (bzw. mehrere) für die Excel-Datei
3. Leeren und füllen (wahlweise ersetzen) der Export-Tabelle mit einem oder mehreren aufwändigeren Queries
4. Automatischer Export der Excel-Datei anhand der Export-Tabelle

Das klingt zwar erstmal aufwändig, aber es erspart dir eine Menge Scherereien und erlaubt es dir später vielleicht, die Datenerfassung direkt in der Datenbank zu machen.

Wenn du keine Ahnung von Datenbanken / SQL hast, wäre das natürlich nicht möglich. In diesem Fall wäre es vermutlich einfacher, es in Dateien zu implementieren.

In deinem speziellen Fall verstehe ich aber einfach die Datenstruktur (trotz deines Erklärungsversuchs) leider nicht. Woher kommt "n1", "n2" als ID im Excel?

 

[mercury]

@Crys kein problem. ABer nur zwecks verständnis weil mich das PRoblem fasziniert :
pro Zeile dann ID, ggf 2. ID und alle Werte.

Werte summieren und neue ID generieren wird dann mit der pandas tabelle auch noch drin sein. (den Teil hab ich weggelassen, da ich den jetzt bei dir als Wissen vorausgesetzt habe (kann man auch noch gern helfen!))

Was war mein Fehler?

 

[Crys]

Also, ich bin weiter gekommen und es läuft auch .. wenn nicht bei großen Dateien (und das sollte der hauptanwendungsfall sein).
Das df2 sind nur drei Spalten, die eben von anderen Dateien eingelesen werden.

df2 = pd.DataFrame(columns = ["newID", "oldID1", "oldID2"])

df2["oldID1"] = df4["GMLId"].append(df_pv4["Building ID"], ignore_index=True)
df2["oldID2"] = df4["ParentGMLId"]
df2["newID"] = "NaN"

counter = 0
for n in range(len(df2)):
    
    if pd.isna(df2["oldID2"].iloc[n]):
        df2["oldID2"].iloc[n] = df2["oldID1"].iloc[n]
        
    if df2["newID"].iloc[n] != "NaN":
        continue
        
    for z in range(len(df2)):
        
        if df2["oldID2"].iloc[n] == df2["oldID2"].iloc[z]:
            #print(df2["oldID2"].iloc[n], " und ", df2["oldID2"].iloc[z], "sind gleich!")
            
            if n != z:
                if df2["newID"].iloc[z] == "NaN":
                    counter += 1
                    df2["newID"].iloc[n] = counter
                    df2["newID"].iloc[z] = counter
                    #print("haben beide jetzt den selben Index bekommen!")

                else:
                    df2["newID"].iloc[n] = df2["newID"].iloc[z]
                    #print("hat einen schon vorhanden Index bekommen!")
                
                break
            
            #continue

        # falls nichts gefunden wird
        elif z == len(df2)-1:
            counter += 1
            df2["newID"].iloc[n] = counter

Wenn ich das so ausführe mit aktuell nur ca. 100 MB csv Dateien, dann braucht das ewig und fünf Tage (bei größeren habe ich das abgebrochen, weiß nicht ob das wirklich durchläuft) und ich bekomme eine Warnung:

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py:670: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self._setitem_with_indexer(indexer, value)

Was mache ich falsch? Wie mache ich das Richtig?

 

[Crys]

Ich habe eine Progressbar gefunden, jetzt kann ich wenigstens anzeigen, dass es noch läuft.
Wie erwartet hängt es an den for-Schleifen. Meine 100MB Datei mit ca. 270T Zeilen würde ca. 18 Tage brauchen.

Wie kann ich das suchen und ersetzten ohne (langsame) Schleifen lösen?

 

[tackleberry]

Ich habe eine Progressbar gefunden, jetzt kann ich wenigstens anzeigen, dass es noch läuft.
Wie erwartet hängt es an den for-Schleifen. Meine 100MB Datei mit ca. 270T Zeilen würde ca. 18 Tage brauchen.

Wie kann ich das suchen und ersetzten ohne (langsame) Schleifen lösen?

Ohne den ganzen Thread gelesen zu haben. Mal überlegt für jede einzelne Datei in einen eigenen Thread / Prozess zu öffnen? Das dürfte ja alles schonmal ordentlich beschleunigen wenn das Skript nicht gerade auf einer 1 Kern CPU läuft. Da läuft aber einiges schief, wenn ne 100 MB Datei 18 Tage brauchen soll. Auf was für Hardware läuft Dein Skript? CPU, RAM Frequenz + Latenz, SSD Modell bitte.

Pandas finde ich übrigens arschlahm... Wenn Ich die Datenstruktur Deiner CSVs sehen könnte, könnte ich evtl. direkt eine Lösung finden.

Wenn Du übrigens Text in der CLI ausgibst, braucht das Skript auch ein vielfaches länger als wenn Du nichts ausgibst. Zum testen habe ich in Schleifen auch gerne diverse Prints drin aber im Production Mode, laufen die Skripte still und loggen max in eine Logfile.

edt

Habe jetzt die Tabellen gefunden. Dein Code den ich hier sehe, sieht mir auch viel zu kompliziert aus für die simple Ausgabe, die Du automatisieren willst.

Ich würde ein dict erstellen, key is dateiname, value sind die eingelesenen Werte aus dem CSV. Das ist ist eine globale Variable.

Ich würde ggf. auch eine Liste mit möglichen Headern für die ID Spalten erstellen und eine Liste für mögliche Headernamen für die Wertspalten. Alternativ als dict mit Key Priorität in Form einer Nummer damit das Programm bei mehrfachen ID-Spalten weiß welche ID als Grundlage genutzt werden sollen. Beispiel: Wenn ID (prio = 8) und IDZ (prio = 5) in CSV, nimm Spalte ID weil 8 > 5.

Für jede CSV Datei würde ich nun einen eigenen Thread / Prozess starten.

Der Prozess macht folgendes:

1. CSV Datei öffnen, deren Path + Name über den Call der Threading Funktion überleben wurde
2. Checks per if(): einbauen (Irgendein ein ID Header doppelt etc.)

Wenn alle Threads durch sind wird eine komplett neue Tabelle aus den vorhandenen Einträgen erstellt. Von mir aus kannst Du an dieser Stelle nun Pandas nutzen um das Excel mit mehreren Blättern zu schreiben. Du fährst einfach mit einer For Schleife durch das Dict. K ist der Dateiname, V ist der Inhalt aus dem CSV.

for csvdatei,inhalt in in CSVDICT:
   Mach_halt_was()

Nachdem die neue Liste erstellt wurde, werden in den alten CSVs die neuen IDs eintragen. Am besten wieder jeweils ein Thread pro Datei. Vorher hast du ja die neuen IDs nicht.

Insgesamt haben wir also drei Schleifen.
Schleife #1: Dateien einlesen und Checks und ggf. Werte anpassen.
Schleife #2: Neue Liste erstellen
Schleifen #3: Neue IDs in alte CSVs schreiben

Pandas dürfte wohl erstmal jede Datei komplett einlesen und in ein DF umwandeln. Wenn Du einfach nur das CSV öffnest und Zeile für Zeile einließt und ggf. die Werte bearbeitet, dürfte das schomal massiv Zeit sparen. Die Werte der CSV hast Du dann eh im RAM wenn Du sie in einer Variable schreibst.

Wenn ein Durchlauf mehr als 10 Minuten frisst, falle ich vom Glauben ab.

Und wie gesagt, am besten Finger weg von Pandas, das braucht bei nem 10MByte 700 x 20 Datensatz gefühlt schon ewig bis mal der DF erstellt ist und zwar auf nem 5950X mit 128 GB RAM und Datacenter SSDs.

edit2

Man könnte auch die Werte aus allen CSVs in eine Variable schreiben und zusätzlich noch die Spalte einfügen aus welcher Datei die Werte stammen ("Source"). Ich würde das wieder SQL Style machen mit einem dict wo ich als key die id der datei und als value den namen des CSVs verwendet. In die Spalte Source des CSV kommt dann nur die ID der Datei. Übrigens behalten entweder list() oder set() in python die Reihenfolge nicht ein wenn man die Variable ändert. Evtl. sorgt das auch für Probleme.

Wenn das aber größere Datenmengen sind, die regelmässig bearbeitet (Lesen / Schreiben) werden müssen, würde ich mir überlegen ob ich das nicht per SQL (z.B. Progres) umstellen wollen würde.

edit3

Male Dir mal in Zukunft Prozessdiagramme für solche I/O Aufgaben. Da findet man dann schnell den Flaschenhals.

 

[KitKat::new()]

Wie erwartet hängt es an den for-Schleifen. Meine 100MB Datei mit ca. 270T Zeilen würde ca. 18 Tage brauchen.

Wie kann ich das suchen und ersetzten ohne (langsame) Schleifen lösen?

1. Schleifen in Python (statt Pandas oder gleich ne andere Sprache)
2. Quadratische Laufzeit durch die verschachtelten Schleifen. Wenn ichs richtig verstanden habe, sind gar keine verschachtelten notwendig:

1. Aggregieren der Werte mit gleicher ID (würde hier eine Map* verwenden mit Key: Id und Value: Wert. Dann kannst du über die Liste mit den CSV Daten iterieren und wenn eine Id auftritt, die in der Map nicht enthalten ist diese eintragen, sonst den Wert dazuaddieren)

Durch die Verschachtelung hast du statt ein paar hundert Tausend Durchläufen 270.000^2 = 72.900.000.000 - joa Python braucht da ne Weile 😀