複数CSVを比較して重複行のリストを作成したい

実現したいこと

前提条件

Powershellを使用
CSVは同一フォルダに格納
各CSVは列数が異なるが、キー項目のインデックスは同じ
CSVのサイズは最大6GBほど

実現したいこと

csvファイル)
A、B、C、D、E
csv記載例）
A列、B列、名前、生年月日、住所、郵便番号......

各CSVを比較し、キー項目(名前、生年月日、郵便番号)が重複している項目を抽出し、リスト化したい
全CSV間での重複結果が必要なため、総当たりになると考えている。
比較パターン)
A-B B-C C-D D-E
A-C B-D C-E
A-D B-E
A-E

発生している問題・分からないこと

sort -Unique等での実装を考えたが、CSVの容量が重く実装が難しかった。
0. 番号リストimport-csvで読み込もうとしたところでメモリ不足が発生して処理が行えない
1.sort -Uniqueは重複を削除してしまい、今回得たい結果とは異なるので断念。

単純なスキル不足から実装方法が見えてこない。

該当のソースコード

PowerShell
1#比較するCSVらがあるフォルダ
2$work_path = "C:\重複リスト出力\データ"
3$work_path2 = "C:\重複リスト出力\キーファイル\keyFile.csv"
4$work_path3 = "C:\重複リスト出力\結果\重複リスト.csv"
5Set-Location -Path $work_path
6
7#作業フォルダ内で拡張子が.csvのみのファイルをフィルターして取得
8$csv_files = Get-ChildItem -File -Filter *.csv
9
10#キーファイル作成
11$keyfiles = @()
12$duplicate_list = @()
13$headers = @('KDB個人番号','氏名_カナ','生年月日','郵便番号') 
14
15
16Write-Host $csv_files
17
18foreach($csv_file in $csv_files){
19    
20    #CSV読み込み
21    $path = Join-Path $work_path $csv_file
22    $csvdata = Import-Csv $Path -Encoding unicode
23
24    if(Test-Path $work_path2){
25        $keyfiles = Import-Csv $work_path2 -Encoding unicode
26    }
27    #レコード分ループ
28    foreach($row in $csvdata){
29
30        #判定
31        $newdata = $keyfiles | Where-Object {($_.氏名_カナ -eq $row.氏名_カナ) -and ($_.生年月日 -eq $row.生年月日_西暦) -and ($_.郵便番号 -eq $row.郵便番号)}
32        if($newdata.Count -eq 0){
33
34            #重複していないデータの場合、キーファイルに追加
35            $record = New-Object PSObject | Select-Object $headers
36            $record.KDB個人番号 = $row.KDB個人番号
37            $record.氏名_カナ =  $row.氏名_カナ
38            $record.生年月日 = $row.生年月日_西暦
39            $record.郵便番号 = $row.郵便番号
40            $keyfiles += $record
41        }else{
42
43            if(($duplicate_list | Where-Object {($_.氏名_カナ -eq $row.氏名_カナ) -and ($_.生年月日 -eq $row.生年月日_西暦) -and ($_.郵便番号 -eq $row.郵便番号)}).Count -eq 0){
44
45                #既にキーファイルに存在するかつ重複リストに存在しない場合、重複リストに追加
46                $record = New-Object PSObject | Select-Object $headers
47                $record.KDB個人番号 = $row.KDB個人番号
48                $record.氏名_カナ =  $row.氏名_カナ
49                $record.生年月日 = $row.生年月日_西暦
50                $record.郵便番号 = $row.郵便番号
51                $duplicate_list += $record
52
53            }
54
55        }
56
57    }
58        #CSV読み終わるごとにCSV出力(メモリ削減のため)
59        $keyfiles | Export-Csv -Encoding unicode -NoTypeInformation -Path $work_path2
60}
61
62#重複リストに出力
63$duplicate_list | Export-Csv -Encoding unicode -NoTypeInformation -Path $work_path3

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

PowerShellでの重複削除方法
sort -Unique等で重複を削除できることは分かったが、今回は重複データを消したいわけではないため
成果は得られなかった

補足

特になし

melian

2024/02/14 14:08

> CSVの容量が重く具体的にはどの程度になるのでしょうか？(TB レベル？)

MIYABIIM

2024/02/14 23:23

最大で6gbほどになります。

ikedas

2024/02/15 03:07

> sort -Unique等での実装を考えたが、CSVの容量が重く実装が難しかった。具体的に**どのような実装**を考えて、「容量が重く」なることで具体的に**どのような問題**が起きて、「難しかった」と判断したのでしょうか。具体的に書いてください (このコメント欄に書くのではなく、質問文を編集して書いてください)。

ikedas

2024/02/15 05:31

取り急ぎ、「メモリリーク」という言葉の使い方がまちがっています。「メモリ不足」に訂正しておいてください。

melian

2024/02/15 06:01

Powershell の利用が絶対的な条件でなければ、Python + Pandas で処理してもよさそうです。

行動規範の内容に同意します

回答3件

ベストアンサー

2024-02-26 訂正あり

Import-CSVコマンドレットでCSVファイル全部を読み込んで処理しようとすると、メモリ不足になって失敗するということですね。

であれば、CSVファイルを少しずつ読み込んで処理できる方法を考えなければなりません。理想は一度に1レコード (1行) しか読み込まないことです。

以下、それを実現する方法の一案です。なお以下では、個々のCSVファイルにはキーの重複はないものとします。

まず、複数のCSVファイルの内容をまとめてキーのフィールドでソートします。

sortコマンドでは特定のフィールドでソートすることはできませんから工夫が必要ですね。たとえばキーフィールドをレコードの先頭に持ってくれば、キーでソートできます。

X-key.csv

“差之 寸世蔵”,”2001-03-11”,”9862200”,...
“阿以 宇江雄”,”1970-01-01”,”5010634”,...
“加幾 久計子”,”1989-11-09”,”0850245”,...

Y-key.csv

“ラリー ルレロ”,”2011-09-01”,”5010634”,...
“差之 寸世蔵”,”2001-03-11”,”9862200”,...
“耶 由代”,”2023-04-25”,”0850245”,...

ps1
1sort.exe XY-key.csv > XY.csv

なお、XY-key.csvは上のX-key.csv、Y-key.csv、…の内容を連結したものです。連結の順番は問いませんし、ファイルの数も自由です。

sortコマンドはメモリ不足にならないのか心配でしょうか。sortコマンドはすべてのレコードを一度にメモリ上でソートできない場合、データを小部分に分けてソートした結果を一時的にディスクに保存しその後マージすることで、メモリ不足を起こさずにすべてのレコードがソートされた結果を出力します (詳しくはsortコマンドの文書を参照)。

※拡張子なしのsortを実行するとsortコマンドの代わりにSort-Content コマンドレットが実行されてしまうので、sort.exeとして実行する必要があります。

さて、ソートの結果できるファイルはこんなふうになります (名前のソート結果が実際とはちがうかもしれませんが、例ということで)。
XY.csv

“ラリー ルレロ”,”2011-09-01”,”5010634”,...
“阿以 宇江雄”,”1970-01-01”,”5010634”,...
“加幾 久計子”,”1989-11-09”,”0850245”,...
“差之 寸世蔵”,”2001-03-11”,”9862200”,...
“差之 寸世蔵”,”2001-03-11”,”9862200”,...
“耶 由代”,”2023-04-25”,”0850245”,...

レコードがキーの順に並んでいるため、キーが重複したレコードがあれば続けて出力されます。したがって、最初から1レコードずつ読み取っていき、同じキーのレコードが続いたら重複キーの行として出力すればいいです。この処理に必要なメモリの量は、ひとつ前のレコードと次のレコードの分だけです。

実際のコードは考えてみてください。

なお、上記の方式ではメモリの使用量は抑えられますが、処理中に一時的に使用されるディスクの空き容量を考慮しなければなりません。

キーフィールドがレコードの最初にくるCSVファイル。元のCSVファイルと同じ大きさです。
sortコマンドが作成する一時ファイル。ソート対象のCSVファイルの総量と同じくらいで、多くても2倍を超えることはないでしょう。
ソート結果のファイル。元のCSVファイルの総量と同じ大きさです。

これらのファイルは最終的な出力が得られれば必要なくなるため削除できますが、作業のためには元のCSVファイルの総量の3倍から4倍の空きディスク容量が必要だということになります。

投稿2024/02/15 10:02

編集2024/02/26 09:30

ikedas

総合スコア4443

MIYABIIM

2024/02/19 00:30

回答ありがとうございます。個々のCSVに重複はありますが 1.各CSVの重複削除、重複リスト作成以下ループ 2.CSV1とCSV2をマージ 3.重複チェック、リスト出力ループ終わり 4.重複リスト内の重複削除上記のような形で実装可能だと感じました。一つの解決策として検討いたします。ありがとうございます。

ikedas

2024/02/19 10:14

あるいは、キー項目をレコードの最初に持ってきたときに、キー項目のあとにファイル名の項目を足すことも考えられます。そうすれば同一ファイル内も含め、どのファイルに重複行があるかがわかります。 “ラリールレロ”,”2011-09-01”,”5010634”,”Y.csv”,... “阿以宇江雄”,”1970-01-01”,”5010634”,”X.csv”,... “加幾久計子”,”1989-11-09”,”0850245”,”X.csv”,... “差之寸世蔵”,”2001-03-11”,”9862200”,”X.csv”,... “差之寸世蔵”,”2001-03-11”,”9862200”,”Y.csv”,... “耶由代”,”2023-04-25”,”0850245”,”Y.csv”,... ちなみに、ファイル2つずつを比較するのではなく全部のファイルを一度に処理することもできると思います。

ikedas

2024/02/26 09:31

回答を訂正しました。これで動くと思います。

行動規範の内容に同意します

キーが(名前、生年月日、郵便番号)
値が「ファイルや行位置＝リスト出力したい情報」

である辞書を作成すればよいでしょう。
このような辞書を利用すれば、総当たりの必要はなく、おおむね以下のような処理をすればよいと思います。

CSVファイルループ
    CSV行ループ
        その行のキーが辞書に存在する
            値（＝最初に出現した情報）がある
                値を出力して、辞書の値だけを空にする
            この行の情報を出力する
        存在しない
            このファイル、行に関する情報を辞書に追加する

投稿2024/02/15 04:37

8524ba23

総合スコア38352

MIYABIIM

2024/02/15 05:17

辞書というのは、キー項目+出力内容を積み上げていく重複のないデータ群という認識でよろしいでしょうか。・辞書に存在する場合に、辞書の値を消すのはなぜでしょう。存在する場合はデータを追加しないでは問題ありますか？・辞書にデータを積み上げすぎると、メモリリークをおこさないでしょうか？

8524ba23

2024/02/15 05:56

辞書はPowerShellいう「ハッシュテーブル」です。 https://learn.microsoft.com/ja-jp/powershell/scripting/learn/deep-dives/everything-about-hashtable?view=powershell-7.4 > ・辞書に存在する場合に、辞書の値を消すのはなぜでしょう。存在する場合はデータを追加しないでは問題ありますか？ > ・辞書にデータを積み上げすぎると、メモリリークをおこさないでしょうか？やってみてください。

行動規範の内容に同意します

回答を削除します

PowerShell

投稿2024/02/16 02:22

編集2024/02/17 08:50

ccc-

総合スコア356

ikedas

2024/02/16 09:30

すみません。確認したいのですが、ご回答のコードは実際に動作しますか。PowerShellのことはよく知らないので勘違いかもしれませんが、なんだか動作しそうにないようにも見えるのです。また動作するのだとして、今回の質問のような状況でも実用になるのですか (論理的には、重複行をすべて洗い出すためにはCSVファイル全体を読み取る必要があると思います。ご提示のコードでは質問にあるような6GB程度のファイルは「小さいサイズ」にあたるので問題ないということなのでしょうか)。ついでに、本質的な問題ではないですが、行数が10の28乗行を超すような天文学的に巨大なCSVファイルを作成し保存するなどということが現実にあり得る/なし得るものなのでしょうか。

ccc-

2024/02/17 03:41

ikedas様ご質問への回答です。 ■本サンプルプログラムはCSVファイル全体を1行1行読み取っております。 Whileループ＋ReadLine()命令 ■PowerShellの変数はカウントを続けるとエラーになります。本サンプルプログラムではSystem.Decimalを使用し変数の範囲を指定しています。最大の行番号が分からないまま処理すると「PowerShellでは途中でエラー」になります。エラー防止の為に指定しています。天文学的に巨大なCSVファイルの作成保存の件につきましては当方では存じておりません。 ■実用になるかどうか、6GBが小さいサイズにあたるかどうか、は各自の認識によるものと思います。ただ、PowerShellで6GBのファイルを処理するのは時間が掛かると思います。 ※PowerShellはインタプリタ言語（逐次解釈しながら実行するため） ikedas様ご質問ありがとうございました。

ikedas

2024/02/17 07:02

> ■本サンプルプログラムはCSVファイル全体を1行1行読み取っております。 > Whileループ＋ReadLine()命令そのようにも見えますが、一行一行について別々に「重複があるかどうか」を調べているということでしょうか。そこが気になりました。添付されているテストデータと結果らしきものは、実際にコードを実行して得られたものでしょうか、それとも想定でしょうか。

ikedas

2024/02/17 08:58 編集

ええと、もしかして、Shift_JIS.csvの中の > 10,010,高山,高山,群馬,000-0010 > 20,020,北野,1920/01/01,長野,1920/01/01 > 30,030,000-0030,1930/01/01,和歌山,000-0030 > 40,040,安藤,安藤,福岡,安藤こういったものは、それぞれのレコード中に「高山」「1920/01/01」「000-0030」「安藤」といった内容の項目が複数あるから「キー項目(名前、生年月日、郵便番号)が重複している」ということなのですか。もしもそうならば、ひとつのCSV内でレコード毎に項目の定義が異なるということになってしまいます (そういうCSVもあり得ますが、それではキー項目の意味がない)。またこれは質問にある「複数CSVを比較」という状況を無視しています。そもそも、こんな処理をするだけのことでどこをどうしたらメモリ不足が問題になるというのでしょうか。失礼ながら、質問をよく読んでおられないために、質問への回答になっていないコードを「回答」として提示しておられると考えます。回答を最初から考え直すか、それが不可能なら回答の削除をお勧めします。 --- ついでに、System.Decimalの最大値までの行数ですと、1秒間に100万行処理したとしてもその行数に達するには10の15乗年 (秒ではないですよ) くらいかかります。そのころまでCSVファイルが使われているとは思えません (というか我々の宇宙そのものがそのころまで存続していないと思われます)。この行数に達してしまわないようにコードで考慮するというのは (それよりはるか前にほかのリソースの枯渇が起きますから) 衒学趣味でしかないでしょう。

ccc-

2024/02/17 11:45

お返事ありがとうございます。先程、削除致しましたご迷惑をお掛け致しまして誠に申し訳ございませんでした

行動規範の内容に同意します

あなたの回答