Linux Shell 前処理

join command: Header付きcsv fileのjoin

shell script preprocess 1/N

公開日: 2021-02-01
更新日: 2023-05-29

  Table of Contents

join command overview

Def: join command

  • joinコマンドは２つのファイルを比較し, キーに基づいて連結するコマンド
  • join対象キーはどちらのファイルもsortされていなければ, errorとなる
1
2

# Syntax
join [option] file1 file2

比較に関するオプション

option 説明
-i 大文字小文字を区別しない
-t セパレーターの指定
-1 数値 1つ目のファイルで比較に使用するcolumn number
-2 数値 2つ目のファイルで比較に使用するcolumn number
--header 各ファイルの1行目をヘッダとして扱う

出力に関するオプション

option 説明
-a 番号 指定しない場合: inner join
1: left join
2: right join
-v 番号 対応するフィールドがなかった行だけ出力
1: file1の内file2とマッチしなかった行
2: file2の内file1とマッチしなかった行
-o 番号 出力するフィールドを指定
-e 文字列 結合相手がいなかった場合の代替値

CSVファイルのjoin: sortエラーの確認と対処法 version 1

sortエラーの確認

student.csvという生徒マスターテーブルと, test_score.csvという生徒が直近受けたテストの点数を格納したファイルが以下のように与えられたとします.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

% cat test_score.csv
id,score
01,89
05,23
06,05
07,79
08,34
09,32
10,90
11,78
12,61
13,45
14,89
15,78
16,23

% cat student.csv
id,name,class
01,ryo,A
07,togo,A
08,sato,A
09,moka,A
10,shina,A
11,ringo,A
02,shun,B
03,takashi,B
04,bob,B
17,asada,B
18,billy,B
19,lio,B
20,son,B
05,sasuke,C
06,naruto,C
12,pop,C
13,felix,C
14,hezo,C
15,zoff,C
16,kirby,C

テストを受けた生徒のついて生徒マスターデータの情報を参照したいとします. このとき, キーカラムのsortに無頓着にjoinすると次のようなエラーが返ってきます.

1
2
3
4

% join -t, -1 1 -2 1 test_score.csv student.csv 
id,score,name,class
join: student.csv:6: is not sorted: 10,shina,A
join: input is not in sorted order

join keyがsortされていないとjoinコマンドはうまく動作してくれません. 今回のケースではstudent.csvがidカラムでsortされていないのでエラーが返ってきています.

どうやってsortする？

今回はheader付きcsv fileなのでheaderの位置をキープしたままsortさせたいです. sortコマンドを使うにあたって気をつけたい点は以下です:

  • -t: column seperator文字はなにかを指示する
  • -k: どのカラムでsortするか指示する & カラムは数値型かテキスト型かを指示する
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

## 文字列ベースでsort
% sort -t , -k1 student.csv | head -5
10,shina,A
11,ringo,A
12,pop,C
13,felix,C
14,hezo,C

## header位置をキープしながら文字列ベースでsort
% (head -n1 student.csv && tail -n+2 student.csv | sort -t , -k1) | head -5
id,name,class
10,shina,A
11,ringo,A
12,pop,C
13,felix,C

## 数値ベースでソート
% sort -t , -k1n student.csv | head -5
id,name,class
01,ryo,A
02,shun,B
03,takashi,B
04,bob,B

sort & joinをワンライナーで実現する

リダイレクトを用いることでワンライナーでsorted fileをinputとしてjoinすることができます.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

% join -t , --header -1 1 -2 1 <(head -n1 test_score.csv && (tail -n+2 test_score.csv | sort -t , -k1n)) <(head -n1 student.csv && (tail -n+2 student.csv | sort -t , -k1n))
id,score,name,class
01,89,ryo,A
05,23,sasuke,C
06,05,naruto,C
07,79,togo,A
08,34,sato,A
09,32,moka,A
10,90,shina,A
11,78,ringo,A
12,61,pop,C
13,45,felix,C
14,89,hezo,C
15,78,zoff,C
16,23,kirby,C

上の例では両方ともsortさせましたが, 今回の例ではtest_score.csvははじめからソートされているので以下のコマンドでも同じことができます.

1
2
3
4
5

## リダイレクトを用いる場合
% join -t , --header -j 1 test_score.csv <(head -n1 student.csv && (tail -n+2 student.csv | sort -t , -k1n)) 

## パイプでつなぐ場合
% (head -n1 student.csv && (tail -n+2 student.csv | sort -t , -k1n)) |join -t , --header -j 1 test_score.csv -

CSVファイルのjoin: sortエラーの確認と対処法 version 2

version 1で紹介した問題と似た以下のケースを考えます. idカラムが上の例では0埋めされていましたが 今回はされていません.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

% cat test_score.csv
id,score
1,89
5,23
6,05
7,79
...

% cat student.csv
id,name,class
1,ryo,A
7,togo,A
8,sato,A
9,moka,A
10,shina,A
11,ringo,A
...

このとき, 普通にsort & joinを実行すると以下のようなエラーつき結果がリターンされます.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

## 数値ソートの場合
% (head -n1 student.csv && (tail -n+2 student.csv | sort -t , -k1n)) |join -t , --header -j 1 test_score.csv - 
id,score,name,class
1,89,ryo,A
5,23,sasuke,C
6,05,naruto,C
7,79,togo,A
8,34,sato,A
join: test_score.csv:8: is not sorted: 10,90
join: -:11: is not sorted: 10,shina,A
9,32,moka,A
10,90,shina,A
11,78,ringo,A
12,61,pop,C
13,45,felix,C
14,89,hezo,C
15,78,zoff,C
16,23,kirby,C
join: input is not in sorted order

## テキストソートの場合
% join -t , --header -1 1 -2 1 <(head -n1 test_score.csv && (tail -n+2 test_score.csv | sort -t , -k1)) <(head -n1 student.csv && (tail -n+2 student.csv | sort -t , -k1))  
id,score,name,class
10,90,shina,A
11,78,ringo,A
12,61,pop,C
13,45,felix,C
14,89,hezo,C
15,78,zoff,C
16,23,kirby,C
join: /proc/self/fd/18:12: is not sorted: 1,ryo,A
5,23,sasuke,C
6,05,naruto,C
7,79,togo,A
8,34,sato,A
9,32,moka,A
join: input is not in sorted order

ゼロ埋めしてjoinする

version 1ではエラーなく返ってきたので, 0埋めをしてjoinすれば良いという方針で以下の解答を作りました.

1

% awk -F"," -v 'OFS=,' 'NR==1; NR > 1{$1=sprintf("%02d",$1);print}' student.csv| sort -t , -k1n |join -t , --header -j 1 <(awk -F"," -v 'OFS=,' 'NR==1; NR > 1{$1=sprintf("%02d",$1);print}' test_score.csv) -

Outputのカラムを指定する

id, name, class, scoreの順番で出力したい場合は-oオプションを用います.

1
2
3
4
5
6

% awk -F"," -v 'OFS=,' 'NR==1; NR > 1{$1=sprintf("%02d",$1);print}' student.csv| sort -t , -k1n |join -t , --header -j 1 -o 0 2.2 2.3 1.2 <(awk -F"," -v 'OFS=,' 'NR==1; NR > 1{$1=sprintf("%02d",$1);print}' test_score.csv) - | head -5
id,name,class,score
01,ryo,A,89
05,sasuke,C,23
06,naruto,C,05
07,togo,A,79

-o 0 2.2 2.3 1.2の意味は

  • 0: join key
  • x.y: xはファイル番号, yはそのカラム番号

id, name, scoreの順番で出力したい場合は

1
2
3
4
5
6

% awk -F"," -v 'OFS=,' 'NR==1; NR > 1{$1=sprintf("%02d",$1);print}' student.csv| sort -t , -k1n |join -t , --header -j 1 -a 2 -e 0 -o 0 2.2 1.2 <(awk -F"," -v 'OFS=,' 'NR==1; NR > 1{$1=sprintf("%02d",$1);print}' test_score.csv) - | head -5 
id,name,score
01,ryo,89
05,sasuke,23
06,naruto,05
07,togo,79

joinの欠損値補完機能を使う

tes_scoreがない生徒については暫定的にスコアを0にして出力したい場合は, 主力範囲を指定する-aオプションと 欠損値の穴埋めをする-eオプションを用います.

  • -a 数値: 1ならばLEFT OUTER, 2ならばRIGHT OUTER
  • -e 文字列: 対応する値がない場合に文字列で補完する, 指定しない場合は欠損したまま出力される
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

% awk -F"," -v 'OFS=,' 'NR==1; NR > 1{$1=sprintf("%02d",$1);print}' student.csv| sort -t , -k1n |join -t , --header -j 1 -a 2 -e 0 -o 0 2.2 1.2 <(awk -F"," -v 'OFS=,' 'NR==1; NR > 1{$1=sprintf("%02d",$1);print}' test_score.csv) -
id,name,score
01,ryo,89
02,shun,0
03,takashi,0
04,bob,0
05,sasuke,23
06,naruto,05
07,togo,79
08,sato,34
09,moka,32
10,shina,90
11,ringo,78
12,pop,61
13,felix,45
14,hezo,89
15,zoff,78
16,kirby,23
17,asada,0
18,billy,0
19,lio,0
20,son,0

References

Share Buttons
 Share on:
Feature Tags
Leave a Comment
(注意：GitHub Accountが必要となります）
CONTENTS