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

[maloz]

Hallo zusammen,

ich habe zwei CSV Dateien. Die Zeilenwerte sind identisch bis auf einen Wert. Im Moment iteriere ich durch Datei A (Referenzdatei), suche mir diese Zeile in Datei B und übernehme den Wert, falls sich dieser unterscheidet.

Datei A:

OID,Caption,User,Mode,Passed
4,Der Schwarm,UserA,test,0
6,Das Auto,UserB,test,0
8,Die Bahn,UserC,test,0
12,Die Uhr,UserA,test,0
34,Der Computer,UserB,test,0
55,Das Internet,UserC,test,0
112,Die Tür,UserA,test,0
45,Das Dach,UserB,test,0
80,Der Strick,UserC,test,0

Datei B:

OID,Caption,User,Mode,Passed
4,Der Schwarm,UserA,test,1
55,Das Internet,UserC,test,1
80,Der Strick,UserC,test,1
8,Die Bahn,UserC,test,0
6,Das Auto,UserB,test,1
12,Die Uhr,UserA,test,0
34,Der Computer,UserB,test,1
45,Das Dach,UserB,test,0
112,Die Tür,UserA,test,1

Es geht um die Spalte "Passed". Der Wert muss, wenn er 1 ist, in die Zeile in Datei A übernommen werden. Die Zeilen stehen kreuz und quer jeweils in den Dateien, daher ist ein stupider 1:1 Abgleich nicht möglich.

Ich habe das schon im PowerShell geschrieben, weil ich das am besten kann. Problem: Es dauert eeeewig. Wir reden da von Stunden, trotz Optimierungsversuche. Datei A ist knapp 25 und Datei B 77 MB groß. Ich iteriere aktuell durch jede Zeile in Datei A und prüfe, ob diese in Datei B vorhanden ist - speziell prüfe ich, ob bestimmte Werte in einer Zeile in Datei B identisch sind und lese dann "Passed" aus. Das dauert natürlich.

Denkt ihr in Python bekommt man das Ganze schneller hin? ^^

Ein paar Tipps/Anhaltspunkte wie ich da vorgehen könnte - speziell wie ich die Zellenwerte in Datei B einfach "suchen" kann?
Bei PowerShell ist das einfach

DateiB.csv | where {$_.OID -eq $DateiA.OID -and ...}

In Python tue ich mich da schwer.

Ist übrigens keine Hausaufgabe sondern ein realer Fall den ich aktuell versuche zu lösen. ^^

Danke und Grüße

 

[00Zetti]

Ist es möglich die Dateien oder wenigstens B vorher zu sortieren?

 

[maloz]

Ich sag mal ja.

 

[DaysShadow]

Python mit Pandas sollte dir da weiterhelfen, wird bestimmt auch da einen Moment dauern, aber das macht ein paar Dinge mehr um schnell zu sein

 

[MisC]

Wäre es nicht am sinnvollsten einmal durch B zu gehen und alle OIDs in ein Array zu schreiben die eine 1 in passed haben und anschließend einmal durch A zu gehen schauen ob die OID im Array ist und dann entsprechend die Zeile aus A schreiben nur mit dem veränderten Wert?

 

[00Zetti]

Dann definitiv vorher lexikographisch sortieren. Wenn die Zeilen in beiden gleich vorhanden sind (also wirklich alle Einträge 1 zu 1 abgebildet sind), dann kannst du zeilenweise vergleichen. Ansonsten in eine Datenstruktur hauen und binäre Suche drauf , oder hashen, oder python bordmittel (wie oben beschrieben, das soltle schon deutlich schneller sein).

Edit: da A und B unterschiedlich groß sind, nehme ich mal an, die sind nicht 1 zu abgebildet. Wenn die mit 0 am Ende irrelevant sind und die mit einer 1 am ende (Passed) eine 1 zu 1 Abbildung zu A darstellen, musst du die Daten vorher einmal filtern, also alle mit 0 rauslöschen, und dann zeilenweise vergleichen. Aber ein paar mehr Informationen über die Datenstruktur wären hierfür hilfreich.

 

[maloz]

Da ist mein Beispiel blöd - die OID kann doppelt vorkommen. Nur die Kombination OID + User + Mode ist unique.
Aber grundsätzlich würde das sicher einen Geschwindigkeitsvorteil bringen, ich probiere es mal.

Python/Pandas muss ich mich echt mal einlesen. Bin halt nicht so bewandert mit Python, hab aber gelesen, dass es wesentlich schneller sein soll.

 

[mercury]

import pandas as pd

df1 = pd.read_csv(file1)
df2 = pd.read_csv(file2)

diff = pd.concat([df1, df2]).drop_duplicates()

 

[00Zetti]

Wäre es nicht am sinnvollsten einmal durch B zu gehen und alle OIDs in ein Array zu schreiben die eine 1 in passed haben und anschließend einmal durch A zu gehen schauen ob die OID im Array ist und dann entsprechend die Zeile aus A schreiben nur mit dem veränderten Wert?

Da musst du nicht nur "einmal" durch A gehen sondern m*n wobei m die Anzahl der gefilterten Elemente in B sind und n die Anzahl aller Elemente in A, also quasi O(n²)

 

[maloz]

In Worten gesagt mache ich es im Moment so:

• Gehe durch jede Zeile in Datei A
• Ist in der aktuellen Zeile der Wert Passed = 0 dann
• Suche die Werte aus der aktuellen Zeile "OID and User and Mode" in Datei B (also Suche die Zeile in Datei B die zutrifft)
• Wenn dort in Datei B der Wert Passed = 1 ist, soll dieser in Datei A in der aktuellen Zeile übernommen werden

 

[Freezedevil]

Nochmal einen Schritt zurück, warum sind die Daten nicht in einer Datenbank?

 

[xammu]

Aus B stumpf alles raus was 0 ist, das ist in diesem Fall irrelevant, du willst nur 1 haben.
Das spart dir schonmal einen Vergleich
Dann aus B pro Zeile die Werte auslesen und in Datei A mit der 0 am Ende suchen. Wenn Treffer dann auf 1 setzen.
also
Lese B: "4,Der Schwarm,UserA,test,1"
Suche A: "4,Der Schwarm,UserA,test,0"
Wenn gefunden:
Ersetze A "4,Der Schwarm,UserA,test,0" > "4,Der Schwarm,UserA,test,1"
Ansonsten nächste Zeile.

Aber OT Frage: Warum wird keine Datenbank verwendet?

 

[sandreas]

Ich hab aus Spaß mal ne PHP Lösung gebaut :-) Müsste klappen, aber die Dateinamen müsste man anpassen. Ob das dir schnell genug ist, weiß ich aber nicht.

$firstFile = "data/file_a.csv";
$secondFile = "data/file_b.csv";
$outputFile = "data/file_merged.csv";


$fileA = new SplFileObject($firstFile);
$fileA->setFlags(SplFileObject::READ_CSV);
$fileB = new SplFileObject($secondFile);
$fileB->setFlags(SplFileObject::READ_CSV);
$fileC = new SplFileObject($outputFile, "w");

foreach ($fileA as $index => $row) {
    if($index === 0 || count($row) != 5) {
        $fileC->fputcsv($row);
        continue;
    }

    foreach($fileB as $indexB => $rowB) {
        if($row[0] === $rowB[0] && $row[1] === $rowB[1] && $row[2] === $rowB[2] && $row[2] === $rowB[2] && $row[3] === $rowB[3] && $row[4] !== $rowB[4]) {
            $fileC->fputcsv($rowB);
            printf("line %d has been updated, second file line %d contained a different value%s", $index, $indexB, PHP_EOL);
            break;
        }
    }

    $fileC->fputcsv($row);
}
$fileA = null;
$fileB = null;
$fileC = null;

 

[Poati]

CSV ist jetzt kein gutes Format, um da Werte zu suchen und/oder zu bearbeiten. Eignet sich besonders für Import/Export.

Unabhängig davon, ob man die Operationen noch optimieren können wird. Mit einer Datenbank läuft es so möglicherweise auch flott genug.

 

[Yuuri]

Mal ganz Quick'n'Dirty mit Bash und SQLite.

#!/usr/bin/env bash

rm db.db 2>/dev/null

sqlite3 -csv db.db ".import a.csv a"
sqlite3 -csv db.db ".import b.csv b"
sqlite3 db.db "CREATE UNIQUE INDEX 'idx_a' ON 'a' ('OID','User','Mode');
CREATE UNIQUE INDEX 'idx_b' ON 'b' ('OID','User','Mode');
CREATE TABLE 'merged'(
  'OID' TEXT,
  'Caption' TEXT,
  'User' TEXT,
  'Mode' TEXT,
  'Passed' TEXT,
  'Origin' TEXT
);
INSERT INTO merged
SELECT
    CASE WHEN
        a.Passed = 0 AND b.Passed = 1 THEN b.OID
        ELSE a.OID
    END OID,
    CASE WHEN
        a.Passed = 0 AND b.Passed = 1 THEN b.Caption
        ELSE a.Caption
    END Caption,
    CASE WHEN
        a.Passed = 0 AND b.Passed = 1 THEN b.User
        ELSE a.User
    END User,
    CASE WHEN
        a.Passed = 0 AND b.Passed = 1 THEN b.Mode
        ELSE a.Mode
    END Mode,
    CASE WHEN
        a.Passed = 0 AND b.Passed = 1 THEN b.Passed
        ELSE a.Passed
    END Passed,
    CASE WHEN
        a.Passed = 0 AND b.Passed = 1 THEN 'b'
        ELSE 'a'
    END Origin
FROM
    a
        LEFT JOIN b
            ON a.OID = b.OID
            AND a.User = b.User
            AND a.Mode = b.Mode;
CREATE UNIQUE INDEX 'idx_merged' ON 'merged' ('OID','User','Mode');"
sqlite3 -header -csv db.db "SELECT * FROM merged;" >merged.csv

 

[cloudman]

Zurück zu powershell

DateiB.csv | where {$_.OID -eq $DateiA.OID -and ...}

Das ist deswegen so extrem langsam weil für jede Zeile aus A jedesmal die komplette DateiB.csv von der Platte geladen wird.


Wenn du es schafft b.csv in den Speicher zu laden wird der Code deutlich schneller

$loopup = Import-csv 'C:\Users\dummie\SampleCSV.csv'  | where passed -eq 1  | select id,name |Group-Object -AsHashTable -AsString -Property id,name

Damit hast du alle Einträge aus b.csv als hashtable mit id und name als index im Speicher für die passed == 1 .
Das select dient dazu nur die Spalten im Speicher zu halten die relevant sind. Das müssen mindestens die Spalten sein, die auch als Index für die Hashtable benutzt werden.
Dann kannst du mit $loopup.Contains('4711, Meier') nachsehen ob der Eintrag enthalten ist.



Falls b.csv nicht mit import-csv in den Speicher passt wäre es vermutlich schneller on the fly eine datenbank zu erstellen

 

[BeBur]

Denkt ihr in Python bekommt man das Ganze schneller hin? ^^

Hauptsache, das Problem wird 'high level' formuliert (d.h. insbesondere ohne explizites Iterieren über Zeilen) wie bei SQL oder auch Pandas und ggf. aber auch normales CPython. Aber wie bei deiner PowerShell Lösung kannst du da auch immer aus versehen was machen, dass dann direkt um den Faktor 10 oder 100 weniger performant ist.

 

[cloudman]

Es liegt selten an der Sprache
Nur erlauben manche Sprachen Konstruktionen die man vermutlich nie einsetzen würde wenn man wüsste was dabei eigentlich passiert.

Wenn ich deinen Methode richtig verstanden habe funktioniert dein Code im Moment so :

Datei A öffnen
While ( more lines in A) 
   nächste Zeile aus a lesen
   Datei B öffnen
   Datei B komplett in ein Speicherobjekt lesen
   Speicherobjekt  linear nach key durchsuchen
   Datei B schließen
Datei A schließen

Das wäre so auch in C nicht wirklich schneller.

 

[cloudman]

Ich war jetzt mal neugierig und habe mir relativ große Testdaten von http://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/ heruntergeladen.

Mit 1.000.000 (119 MB Dateigröße) Zeilen dauert die Ausführung bei mir ca 1 min 20 sec und um die 3.5 GB Memory

Als eindeutiger Schlüssel fungiert hier "Order Id" + "Item Type"
Ziel ist es bei allen Zeilen Order Priority auf x zu setzen wenn in lookup (DateiB.csv) die Zeile enthalten ist ist dort das Feld Country auf Russia steht

$stats = Measure-Command {

## Beispieldaten von  http://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/
## Ziel im Quell CSV für alle Zeilen die Spalte 'Priority' auf X setzen wenn im lookup csv für diesen Eintrag bei Country 'Russia'  steht
## Eindeutiger Schlüssel sind die Spalten "Order Id" und "Item Type" kombiniert 
$srcpath =  "c:\tmp\1000000 Sales Records.csv"
## Nur um es einfacher zu machen
$lookuppath = $srcpath
$data = Import-csv $srcpath
## Lookup as Hashtable - alles einlesen mir Country == "Russia" 
$lookup = Import-csv $lookuppath | where Country -eq "Russia" | select "Order id","Item Type" |Group-Object -AsHashTable -AsString -Property "Order id","Item Type"
## In jeder Zeile die Spalte "Order Priority" auf x setzen wenn in lookup gefunden
$data | where { $lookup.ContainsKey( $_."Order id" + ', ' + $_."Item Type") } | ForEach-Object { $_."Order Priority" = "x" }

## Ausgabe erstellen
$data | Export-Csv "result.csv"
}

Write-Host "Total lines : $($data.Count)"
Write-Host $stats

 

[RalphS]

Können wir mal das Powershell-Fragment sehen? Wenn es nicht irgendwie unter Schutz steht.

Das ist viel VIEL zu lange mit den Stunden, immer vorausgesetzt es ist keine halbtote HDD im Backend und die CSV in Millionen Fragmenten verteilt.

PS sollte ein simpler Fall von Import-CSV, Where-Object und/oder Compare-Object sein.

Note - $Variable | Foreach-Object in Powershell ist tatsächlich performanter Albtraum.
Wo möglich, "immer" auf foreach($element in $list) {... } ausweichen UND Ausgaben innerhalb der Schleife(n) vermeiden.

Ein Datenbankbackend wie SQLite (oder ein echtes DBMS) kann natürlich immer sinnvoll sein. Aber da muß man dann auch schauen von wegen Indices und ob die Erzeugung der Datenstruktur(en) nicht länger dauert als die eigentliche Suche.
Wenn aber natürlich die Inputdaten insoweit statisch sind, daß sie eher ergänzt als ersetzt werden, dann macht es definitiv Sinn, darüber nachzudenken.