Vergleich zweier CSV Dateien - PowerShell zu langsam - Python als Alternative?

[maloz]

Erstmal Danke für die vielen guten und konstruktiven Vorschläge/Ideen bzw. "Komplettlösungen".
Ich möchte euch daher mehr Infos geben warum ich das so machen möchte wie eingangs erwähnt.

Um was geht es eigentlich?
Es geht um ein eLearning System. In diesem kann man einem User X eLearning-Module ("Schulungen") zuweisen. Davon hat er Y beendet (am Ende einer Schulung gibt es immer kurze Tests in Form von Multiple Choice Fragen).
Nun ist es für den Vorgesetzten interessant welcher MA schon welche Schulungen beendet hat und welche noch fehlen.
Dazu konnte man in der Vergangenheit einen Report generieren (bietet das eLearning System an), sämtliche Infos in ein CSV exportiert werden (entspricht aktuell DateiA).
Durch ein Update ist dieser Report fehlerhaft - sämtliche Schulungen, egal ob offen oder beendet, werden als offen angezeigt (Passed = 0).
Der Hersteller sagt, dass dieser Report nicht mehr supportet ist und verweist auf einen anderen Report.
In diesem sind aber nur alle beendeten Schulungen beinhaltet (Passed = 1).
Daher mein Angang die beiden Reports zu einem für uns brauchbaren Report zusammenzufassen.
Der bisherige Report wird dann von einem Kollegen in eine SQL DB geschrieben, dann gibt es noch eine Website die auf die DB zugreift und die Daten entsprechend aufbereitet und anzeigt (damit habe ich aber nichts zu tun und ist für das eigentliche Problem nicht wichtig).

Warum werden die Daten nicht gleich in eine DB exportiert?
Technisch ist das möglich und falls ich gar nicht weiterkomme durchaus eine Option.

Ja äääh warum dann überhaupt das Ganze irgendwie anders lösen?
Das liegt an mir - ich beschäftige mich gerne mir neuen Dingen und versuche, eigentlich recht simple Dinge wie in diesem Fall, sowas zu lösen/automatisieren.

Warum PowerShell?
Weil ich das am besten kann. ^^

Wie schaut das aktuelle PowerShell Script aus?
Das habe ich jetzt nicht zur Hand, ich kann nur die einzelnen Steps widergeben:

• Beide Reports (CSV Dateien) werden jeweils in eine Variable mit Import-Csv importiert (hier dachte ich eigentlich, dass die Daten dann schon im Speicher liegen...)
• DateiA (wo die falschen Werte drin stehen) wird durchiteriert
• Wenn in der aktuellen Zeile bestimmte Werte (Object UID and User and Mode) auch in DateiB gefunden werden ($DateiB | where...)...
• ...wird der Wert für "Passed" in der aktuellen Zeile auf 1 gesetzt
• Wenn das durch ist wird das ganze per Export-Csv weggeschrieben
• Dann kann diese weggeschriebene Datei für den weiteren Weg verwendet werden

---------

Auf Arbeit habe ich mich (heute) die restliche Zeit mit Python und Pandas befasst. Aber bin nicht weit gekommen - CSV einlesen und da drin nach bestimmten Werten suchen hat geklappt - und das sehr schnell.
Ggf. werde ich das auch nochmal mit den PowerShell Hashtables ausprobieren.

Ich bin kein Programmierer (wie man auch merkt) - ich bin SysAdmin und PowerShell gehört zu meinem täglich Brot, habe so eine Aufgabe aber auch das erste Mal. Grundsätzlich habe ich die Lösung schon in PowerShell, es ist nur arschlahm, deswegen hier die Nachfrage.
Wie schon erwähnt bin ich dankbar für Denkanstöße, bin ein Verfechter davon dann aus Eigeninitiative und -interesse mich weiter einzulesen und auszuprobieren.

Ich hoffe ich konnte etwas Licht ins Dunkle meines Arbeitsalltags bringen.

edit
@BeBur
Deinen Post habe ich ehrlich gesagt nicht verstanden. Worauf willst du hinaus?

 

[ModellbahnerTT]

Die Daten beider Dateien in eine Datenbank zu importieren und dann auszuwerten wäre sicherlich eine schnelle Lösung braucht aber Kenntnisse zum programmieren des Datenabgleichs.

 

[cloudman]

@maloz hast du mein Beispiel gesehen? Bei 1.000.000 Zeilen etwas mehr als eine Minute Laufzeit.
Klar sind andere Daten aber der Code macht eigentlich das was du beschreibst.
Wie groß ist den DateiB ?

 

[RalphS]

In dem Fall möchte ich betonen: FINGER WEG von CSV.

Wenn man das muß und buchstäblich gar nichts anderes geht... oooo--kay, dann isses halt so.
Aber sobald man die Wahl hat, muß einem klar sein: CSV ist KEIN Standard. Da macht jeder alles mit. Es gibt keine zuverlässigen Parser - was ua. dem Umstand des fehlenden Standards geschuldet ist.

WENN Flatfiles, dann JSON oder XML.

Besser ist hier aber tatsächlich irgendein DBMS im Backend.

Fürs Frontend, wenn PS partout zu lahm ist, dann weiche ich auf C# und Lazy Load aus, via LINQ oder yield. Damit werte ich momentan hauptsächlich Serverlogs aus, wo täglich mindestens 30k Einträge anfallen. Parse mit purem PS dauert da mehrere Minuten, mit C# und yield nur noch Sekunden (note: es werden tatsächlich die Logeinträge in Objekte geparst, damit sie auswertbar werden.)

Eventuell wäre auch PS Core (also PS7+) eine Idee. Damit geht Foreach -Parallel.
Aber persönlich nie mit gearbeitet, wenn ich PS verwende, nutz ich dort Runspaces... wobei man auch da mit dem Einlesen der Logs teilweise etwas warten muß, nur die Auswertung geht schneller, wenn man sie parallelisieren kann.

 

[maloz]

@cloudman
Ich habe es mal stumpf übernommen:

$dataExistingValues = Import-Csv $fileExistingValues -Delimiter "`t" | where {$_.Mode -eq "test"} | select-object "Object UID","User","Mode","Caption" | Group-Object -AsHashTable -AsString -Property "Object UID","User","Mode","Caption"

Und seit 10 Minuten tut sich gar nichts. Klappt also irgendwie nicht so oder PowerShell ist mit der Struktur der CSV überfordert.

Kopiere ich nämlich nur 200 Zeilen aus dem CSV (in ein extra File) und versuche daraus eine HashTable zu erstellen geht das binnen Sekunden - da drin kann ich dann auch mit ContainsKey schön suchen.

 

[cloudman]

Ohne zu wissen von welchen Datenmengen wir hier reden ist es echt schwierig etwas dazu zu sagen.

Mit den Testdaten aus on http://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/ 1.000.000 Zeilen dauert es bei mir nur wenige Minuten.

Wie lange dauert bei dir den Import-Csv DateiB.csv?

Tipp : https://docs.microsoft.com/en-us/po...l.utility/measure-command?view=powershell-7.1

 

[maloz]

Es geht um knapp 139000 Zeilen in 78 MB. Also weniger als bei dir. Vorhin ist der Import-Csv fertig geworden, hat jketzt ca. 30 Minuten gedauert.
Ein reiner Import-Csv:
TotalMinutes : 1,064191565
TotalSeconds : 63,8514939

edit
Also ich bleibe nun bei der PS Hashtable Lösung. Nach weiteren Optimierungen der DateiB dauert der Import-Csv mit Group-Object ca. 10 Minuten.
Der eigentliche Abgleich zwischen DateiA und DateiB ist dann aber in Sekunden durch - da sist echt wahnsinn.
Danke dafür @cloudman!
Interessehalber werde ich aber versuchen das auch mal in Python/Pandas nachzustellen.

 

[cloudman]

Ich hab noch etwas getestet:

• select ... beim import der lookup table entfernen
• hashset statt hashtable

Den Filter beim Import der Lookup csv habe ich entfernt (ich gehe davon aus dass bei dir passed=1 für alle Einträge in lookup gesetzt sind) .
Statt passed von 0 auf 1 zu setzen ändere ich den Order Priority" wenn Land == Russia

Außerdem habe ich beim Import der Lookup Datei
Select-Object "Order id","Item Type" entfernt. Die Idee dahinter war die Datenmenge kleiner zu halten allerdings verdoppelt der select teil die Import Zeit

Das Erstellen der Hashtable dauert auch nochmal relativ lange.
Nachdem es nur darum geht zu testen ob die Daten enthalten sind reicht auch ein Hashset statt hashtable und spart nochmal deutlich Zeit

$srcpath =  "c:\tmp\1000000 Sales Records.csv"
$lookuppath = $srcpath
"Import data      Execution Seconds: " + (Measure-Command {$data = Import-csv $srcpath }).TotalSeconds
# "Import lookup    Execution Seconds: " + (Measure-Command { $lookuptable  = Import-csv $lookuppath |  Select-Object "Order id","Item Type" }).TotalSeconds
"Import lookup    Execution Seconds: " + (Measure-Command { $lookuptable  = Import-csv $lookuppath  }).TotalSeconds
"Create HashSet   Execution Seconds: " + (Measure-Command { $lookup = New-Object "System.Collections.Generic.HashSet[string]"; foreach ($l in $lookuptable ) { $null = $lookup.Add($l."Order id" + ", " +  $l."Item Type")} }).TotalSeconds
## not needed anymore release memory
$lookuptable = $null
"Update data      Execution Seconds: " + (Measure-Command {$data | Where-Object {  $_.Country -eq "Russia" -and  $lookup.Contains( $_."Order id" + ', ' + $_."Item Type") } | ForEach-Object  { $_."Order Priority" = "x" }}).TotalSeconds
"Export Data      Execution Seconds: " + (Measure-Command { $data | Export-Csv "result.csv" }).TotalSeconds

Die Zeiten :

Import data      Execution Seconds: 45.3249538
Import lookup    Execution Seconds: 43.2266093
Create HashSet   Execution Seconds: 5.058552
Update data      Execution Seconds: 39.3277395
Export Data      Execution Seconds: 17.3623436

Hashtable vs Hashset (netter Unterschied)

"Create HashTable Execution Seconds: " + (Measure-Command { $lookup2 = $lookuptable | Group-Object -AsHashTable -AsString -Property "Order id","Item Type" }).TotalSeconds
Create HashTable Execution Seconds: 32.2487891

"Create HashSet   Execution Seconds: " + (Measure-Command { $lookup = New-Object "System.Collections.Generic.HashSet[string]"; foreach ($l in $lookuptable ) { $null = $lookup.Add($l."Order id" + ", " +  $l."Item Type")} }).TotalSeconds
Create HashSet   Execution Seconds: 4.8086695

Meine Vermutung ist, dass Group-Object die HashTable Variante aussbremst

 

[T_55]

Noch mal ergänzend rein aus Sicht der Performance. Wenn man liest 100MB und > 1 Minuten dann ist da noch massiv Luft nach oben, dies kann man mind. um den Faktor 100 schneller bekommen. Mal ins Blaue geschätzt würde das mit dem folgenden beschriebenen Vorgehen und zB C++ eher eine Sache von Sekunden sein.
Wenn deine Files so "klein" sind also ca 100MB dann würde ich versuchen die zu vergleichenden Files zunächst möglichst effektiv komplett in den RAM schieben und erst dort zu vergleichen. Vergleich in RAM/CPU ist das schnellste.
Wenn Performance im Vordergrund steht, kann ich nur empfehlen die Daten als Flat File Binär zu speichern und dazu am besten noch in Chunks. Das spart auch ein Haufen überflüssige Zeichen und damit Speicherplatz.
Binär führt dazu, dass keinerlei Umwandlungen vom File zum Ziel-Datentyp im RAM nötig sind. Da kommt auch keine Datenbank mit. Chunks wiederum helfen den Festplattenzugriff besser auszunutzen durch auslesen größerer Blöcke. In Sachen Performance gehts kaum besser, hat aber auch seine Nachteile, dass man eben etwas mehr coden muss und zb File-Kompatibilität zu Excel verliert.

 

[BeBur]

Frage aus Interesse: Warum sollten die Dateien nur 100MB groß sein um sie in den RAM zu schieben? Oder war das nur ein Beispiel und du siehst kein Problem bei 1GB großen Daten? 1GB passt ja heute in den RAM eines jeden normalen Rechners.

Weitere Frage aus Interesse: Wie ist es mit CPU Cache-misses beim Binär speichern (bei 100MB natürlich voraussichtlich irrelevant).

Binär führt dazu, dass keinerlei Umwandlungen vom File zum Ziel-Datentyp im RAM nötig sind. Da kommt auch keine Datenbank mit.

Das kann man so nicht sagen, da Datenbanken und auch andere Software gerne kluge Dinge tun, z.B. spezielle Datenstrukturen im Hintergrund verwenden um für Such-Operationen O(log(n)) oder auch O(1) zu erreichen, wo man eigentlich O(n) erwarten würde. Ein 'naives' Abgleichen (z.B. im RAM) hat so schnell O(n^2). Im übrigen arbeiten DBMS natürlich auch vorzugsweise im RAM.

 

[DaysShadow]

nur 100MB groß sein

Davon hat er doch mit keinem Wort etwas geschrieben?

 

[BeBur]

Davon hat er doch mit keinem Wort etwas geschrieben?

Mein Beitrag war an T_55, welcher schrieb:

Wenn deine Files so "klein" sind also ca 100MB dann würde ich versuchen die zu vergleichenden Files zunächst möglichst effektiv komplett in den RAM schieben und erst dort zu vergleichen.

Daher meine Nachfrage, ob bzw. wieso die so klein sollen, damit seine Aussage gilt.

 

[T_55]

Frage aus Interesse: Warum sollten die Dateien nur 100MB groß sein um sie in den RAM zu schieben? Oder war das nur ein Beispiel und du siehst kein Problem bei 1GB großen Daten? 1GB passt ja heute in den RAM eines jeden normalen Rechners.

Die hier genannten Dateigrößen waren um den Bereich 100MB darauf habe ich mich bezogen. 1GB wäre natürlich ebenso auch kein Problem. Je kleiner desto mehr ein No-Brainer erstmal alles in den RAM zu schieben.

Weitere Frage aus Interesse: Wie ist es mit CPU Cache-misses beim Binär speichern (bei 100MB natürlich voraussichtlich irrelevant).

Naja alles im L1 zu halten ist bei aktuellen CPUs (noch) nicht im Rahmen der Realität somit sind Cache-misses das täglich Brot einer CPU vor allem wenn nicht sequenziell gearbeitet wird. Das Entscheidende ist doch, dass alles im RAM liegt, das rumschieben von Daten zwischen CPU L1 L2 L3 RAM wird immer schneller sein als in CSVs Zeilen zu iterieren.

Das kann man so nicht sagen, da Datenbanken und auch andere Software gerne kluge Dinge tun, z.B. spezielle Datenstrukturen im Hintergrund verwenden um für Such-Operationen O(log(n)) oder auch O(1) zu erreichen, wo man eigentlich O(n) erwarten würde. Ein 'naives' Abgleichen (z.B. im RAM) hat so schnell O(n^2). Im übrigen arbeiten DBMS natürlich auch vorzugsweise im RAM.

Ich wollte damit sagen, dass der Transfer von Platte zu RAM nicht effektiver geht, als wenn das, was im RAM verarbeitet werden soll, exakt auch so schon auf der Platte liegt (Binär). Eine DBMS hat hier immer einen gewissen Overhead. Das heißt aber natürlich nicht das DBMS deswegen langsamer sind bei bestimmten Operationen, im Gegenteil beim random Suchen und vor allem auch random Einfügen neuer Daten sind natürlich DBMS verdammt effektiv. double linked list ist ja zb typisch für Cache-misses trotzdem ist einfügen und entfernen in konstanter Geschwindigkeit und kann so sequenziellen Dingen überlegen sein. Kommt am Ende immer auf den genauen Usecase an.

 

[cloudman]

@T_55 Nette Hintergrund Information.
Für jemanden der selbst schreibt kein Entwickler zu sein und vor allem powershell Kenntnisse zu haben allerdings schon eine heftige Hausnummer

Ich bin sicher das bekommt man schnell genug hin ohne alles zu ändern - so groß sind die Datenmengen jetzt auch nicht .
Dass die Verwendung von CSV nicht optimal ist wenn es um Performance sollte eh klar sein ....


Nachdem ich mich eh weiter in Python einarbeiten will habe ich jetzt meinen ersten Versuch mit pandas gemacht. Ziemlich mächtige Library
Das ist dann doch deutlich schneller als die Powershell csv Varianten


Voraussetzungen:

• Python installieren
• pandas library installieren (pip install pandas)

Die Python Variante meines Powershell Beispiels - geht bestimmt schneller und schöner aber das ist alles was ich mir auf die Schnelle an pandas skills aneignen konnte

import pandas as pd

data = pd.read_csv (r'C:\tmp\50000 Sales Records.csv',index_col=False)
dataidx = data.set_index(['Order ID', 'Item Type']).index
lookup = pd.read_csv (r'C:\tmp\1000000 Sales Records.csv',usecols=['Order ID','Item Type'],index_col=False)
lookupidx = lookup.set_index(['Order ID', 'Item Type']).index
data.loc[(dataidx.isin(lookupidx)) & (data['Country']=='Russia'),['Order Priority']] = 'x'
data.to_csv(r'c:\tmp\export.csv',index=False)

 

[mercury]

Oder nach cloudmans Beispiel:
alternativ einfach nur nach Spaltenwert filtern wenn man beide Tabellen gemerged hat.

df.loc[df['Passed'] == '1']

 

[KitKat::new()]

Weils so lustig ist, hier noch mal in Rust:

use std::collections::HashSet;
use csv::Error;

fn main() -> Result<(), Error> {
    let t0 = std::time::Instant::now();
    let set = read_hash_set("1000000 Sales Records.csv")?;
    println!("hash set created: {}", t0.elapsed().as_secs_f64());
    translate("1000000 Sales Records.csv", "1000000 Sales Records_new.csv", set)?;
    println!("translation complete: {}", t0.elapsed().as_secs_f64());
    Ok(())
}

fn read_hash_set(file_name: &str) -> Result<HashSet<(Vec<u8>, Vec<u8>)>, Error> {
    let mut reader = csv::Reader::from_path(file_name)?;
    let mut set = HashSet::new();
    let mut record = csv::ByteRecord::new();
    while reader.read_byte_record(&mut record)? {
        set.insert((record[2].to_owned(), record[6].to_owned()));
    }
    Ok(set)
}

fn translate(file_name: &str, file_name_new: &str, set: HashSet<(Vec<u8>, Vec<u8>)>) -> Result<(), Error> {
    let mut reader = csv::Reader::from_path(file_name)?;
    let mut writer = csv::Writer::from_path(file_name_new)?;
    let mut record = csv::ByteRecord::new();
    let header = reader.byte_headers()?;
    writer.write_record(header)?;
    while reader.read_byte_record(&mut record)? {
        if &record[1] == b"Russia" &&
            set.contains(&(record[2].to_owned(), record[6].to_owned())) {
            record.iter().enumerate().try_for_each(|(i, field)|
                if i == 4 { writer.write_field("X") } else { writer.write_field(field) }
            )?;
            writer.write_record(None::<&[u8]>)?;
            continue
        }
        writer.write_record(&record)?;
    }
    Ok(())
}

Output von cargo run --release:

hash set created: 0.8054024
translation complete: 1.9704887

(inkl. minimaler RAM Verbrauch)

 

[cloudman]

Nett was jetzt so kommt.
Wie sieht es mit Go aus?

Ich wollte mir eigentlich auch noch das hier ansehen https://dfinke.github.io/powershell/2020/01/01/DataFrames-PowerShell-and-Excel.html aber bin nicht dazu gekommen.

 

[maloz]

Ich komme mit Pandas irgendwie gar nicht klar.

Die Abfrage zu basteln wie ich sie brauche bekomme ich hin:

dataframeMissingValues.query('User == "User1" and Mode == "test"')

(ich weiß, dass es andere, ggf. bessere Wege gibt das zu ermitteln, query ist aber mein letzter Test/Stand)

Zurückgegeben wird immer ein DataFrame mit den Ergebnissen, in diesem Fall genau ein Treffer.

Wie kann ich das nun weiterverwenden in einem if?
Einfach...

if dataframeMissingValues.query('User == "User1" and Mode == "test"'):

...gibt mir immer als Fehler, dass das DataFrame, welches zurückgegeben wird, nicht für einen boolschen Vergleich verwendet werden kann.
Der Fehler ist so richtig und mir klar, aber wie schaffe ich es, dass ich in einem DataFrame nach was suchen kann und falls es min. einen Treffer gibt, einfach nur True returned wird?

 

[KitKat::new()]

Der Fehler ist so richtig und mir klar, aber wie schaffe ich es, dass ich in einem DataFrame nach was suchen kann und falls es min. einen Treffer gibt, einfach nur True returned wird?

Die Menge der Elemente im Query-Ergebnis auf !=0 pruefen?
Hier, gibt ne Funktion, die angibt ob das Dataframe leer ist: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.empty.html

df = dataframeMissingValues.query('User == "User1" and Mode == "test"')
if not df.empty():
    ...

 

[BeBur]

Zurückgegeben wird immer ein DataFrame mit den Ergebnissen, in diesem Fall genau ein Treffer.

Wie kann ich das nun weiterverwenden in einem if?

Das ist ein XY-Problem, vermute ich. Du willst etwas bestimmtes tun gerade, aber danach fragst du nicht, sondern setzt das was du machen willst voraus und hast innerhalb dessen nun ein Problem.
Das ist schlecht, weil das eigentliche Problem eventuell darin begründet ist, was du ursächlich machen willst.
Wenn ich dich richtig verstehe willst du gerade sehr kleinteilig einzelne Schritte in Code abbilden. So wird pandas voraussichtlich auch eher langsam sein.
Du willst generell immer auf dem gesamten Dataframe arbeiten, also nicht einzelne Zeilen rausgreifen und die dann einzeln editieren. Ein Beispiel wie das aussehen könnte:

Schau mal das hier: Link1 oder auch speziell das hier Link2 ob das nicht das ist was du willst und ggf. mit wenigen sehr simplen Zeile Code zu bewerkstelligen ist. Bei Der Doc zu pandas.DataFrame.update speziell schau mal den overwrite Parameter. Macht das nicht genau das was du machen willst?
1. Beide Dateien als Dataframe laden (DF-A und DF-B)
2. Beide sortieren nach OID Wende `set_index('OID', inplace = True) auf beide an
3. In DF-A setze alles auf NA außer Spalte Passed
4. In DF-B ersetze in der Spalte Passed alle Nullen durch NA
5. Rufe `DF-B.update(DF-A) auf
6. In DF-B ersetze in der Spalte Passed alle NA durch Nullen
7. Fertig

Erklärung
In DF-B stehen ja nur die Nullen in Passed zur Debatte, dass die geändert werden, deswegen werden die auf NA gesetzt. Ein NA Wert heißt: Trage das ein, was in DF-A steht. Steht in DF-A Null, dann steht hinterher auch wieder in DF-A eine Null, so wie vorher. Steht da in DF-A aber eine Eins, dann wird diese eingetragen.

Das geht noch eleganter / schneller / kürzer aber ist vllt ein Kompromiss aus Verständlichkeit, Geschwindigkeit und Kürze. Vermutlich kann man das noch deutlich beschleunigen, indem man DF-A auf die Spalte Passed reduziert.