☰

一致する列データに基づいて2つのCSVファイルを結合する

tag-icon • tag-icon text-processing awk sed csv join
一致する列データに基づいて2つのCSVファイルを結合する

一致する列情報に基づいて、2 つの CSV ファイルからデータを結合したいと思います。一致させるデータは、File1.csv の列 5 と File2 の列 1 からのもので、一致した場合に File2 の列 2 の情報を追加し、一致しない場合は空の二重引用符を残します。

ファイル1.csv

"Z","P","W","K","1","1.18.24.59"
"S","K","D","X","9","1.14.19.238"
"R","M","P","Y","8","1.15.11.21"
"B","D","0","U","5","1.9.20.159"
"R","E","W","Q","6","135.0.0.1"
"K","D","K","R","9","1.9.74.13"

ファイル2.csv

"65.9.7.19","374 22 53"
"1.9.74.13","123 256 51"
"1.18.24.59","23 25 41"
"1.15.11.21","98 77 8291"
"1.14.19.238","8827 145 8291"
"1.9.20.159","283 1 5734"

望ましい出力

"Z","P","W","K","1","1.18.24.59","23 25 41"
"S","K","D","X","9","1.14.19.238","8827 145 8291"
"R","M","P","Y","8","1.15.11.21","98 77 8291"
"B","D","0","U","5","1.9.20.159","283 1 5734"
"R","E","W","Q","6","135.0.0.1",""
"K","D","K","R","9","1.9.74.13","123 256 51"

答え1

を使用した 1 つの解決策を次に示しますawk。GNU awk 4.1.3 でテスト済み。

$ awk -F, 'NR==FNR{a[$1]=$2}NR!=FNR{print $0","(a[$6]?a[$6]:"\"\"")}' file2.csv file1.csv
"Z","P","W","K","1","1.18.24.59","23 25 41"
"S","K","D","X","9","1.14.19.238","8827 145 8291"
"R","M","P","Y","8","1.15.11.21","98 77 8291"
"B","D","0","U","5","1.9.20.159","283 1 5734"
"R","E","W","Q","6","135.0.0.1",""
"K","D","K","R","9","1.9.74.13","123 256 51"
$
  • これはNR==FNR{a[$1]=$2、file2 内の行と一致し、フィールド #1 をキーとして配列に追加します。
  • はNR!=FNR、file1 内の行と一致します。
  • print $0","ファイル1の行全体を出力し、その後にコンマを付けます。
  • (a[$6]?a[$6]:"\"\"")""エントリが見つからない場合にのみ、以前に構築された配列の対応する内容を出力します。

答え2

これは予想以上に困難でしたが、Python ベースのcsvkitバージョン 1.0.2 のツールを使用すれば、次のことが可能になります。

$ csvjoin -HI -c 6,1 --left File1.csv File2.csv 2>/dev/null | csvformat -K1 -U1
"Z","P","W","K","1","1.18.24.59","23 25 41"
"S","K","D","X","9","1.14.19.238","8827 145 8291"
"R","M","P","Y","8","1.15.11.21","98 77 8291"
"B","D","0","U","5","1.9.20.159","283 1 5734"
"R","E","W","Q","6","135.0.0.1",""
"K","D","K","R","9","1.9.74.13","123 256 51"

(少なくとも私のロケールでは、ドットで区切られたフィールドが日付時刻であると推測されるのを-I防ぐために、これは必要なようです。)csvjoin

答え3

for i in `cat file2.csv | awk -F "," '{print $1}'| sed 's/"//g'`; do j=`sed -n '/'$i'/p' file2.csv| awk -F "," '{print $2}'| sed 's/"//g'`;awk -v i="$i" -v j="$j" -F "," '$NF==i{$7=j;print $0}' file1.csv; done| sed 's/ /","/g'| sed 's/^/"/g'| sed 's/$/"/g'

出力

"Z","P","W","K","1","1.18.24.59","23 25 41"
"S","K","D","X","9","1.14.19.238","8827 145 8291"
"R","M","P","Y","8","1.15.11.21","98 77 8291"
"B","D","0","U","5","1.9.20.159","283 1 5734"
"R","E","W","Q","6","135.0.0.1",""
"K","D","K","R","9","1.9.74.13","123 256 51"

関連情報