どーも！
たかぽんです！
今回はCSVというデータから特定の情報だけ取り出したり、一定の条件に当てはまるものだけ集めたり...色々いじり倒してみたいと思います！
CSVで吐き出されるデータをGUI上でぽちぽちもまぁ...悪くはないんですが、CUIでサクッとガガっとまるごと整形してしまう方が早くて楽な場合も多々...(筆者はnumbersいまいちしらないですし...!）
かくいう僕もそんなに理解できていないので、改めてしっかりと学んでおこうと思います...!
目次
CSVをCUIでいい感じに整形する方法！
それではみていきます！
まず、検証のため、適当なCSVファイルを作っておきましょう。
任意のフォルダに以下のファイルを"test.csv"で作成します。
ID,Date,Sentence,Plice,Path
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
2,2020/11/15,これはてすとの文章です。,10000円,/hoge/huge/huga/test2
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
5,2020/08/15,このファイルはカンマ区切りでデータが入っています。,8500円,/hoge/huge/huga/test5CSVファイルは上記のようにカンマ区切りで作られているので、専用のアプリ等がなくても割とサクッとかけたりします。（量が多いと大変ですが...)
上記を保存できたら、Consoleにて、そのファイルがある場所で、以下のコマンドを実行します。
numbersというデフォルトのアプリケーションが入っていれば、CSVファイルが開かれるはずです。
open test.csv
データに関してはただただ出鱈目ですが、一般的によくありそうな感じ....と、僕の場合はログの確認...という側面から、ファイルパス的なものも混ぜています。
では次から色々と試そうと思います。
以降コマンドを解説していきますが、整形後のCSVは末尾に" > output.csv"とつけることで、同一フォルダ下に"output.csv"として保存することができるので、出力を別ファイルとして保存したい方は末尾につけるようにしてください。(名前は抽出内容に合わせて変えることをおすすめします）
CSVから特定の行を取り出す
まずは適当な行（縦）を出してみます...!
最初のコマンドは以下です。
awk 'NR == 2 {print $0}' sample.csv > output.csv
実際に実行をすると...
taka@Taka CUI-test % awk 'NR == 2 {print $0}' test.csv
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1このようになります。
ちなみに"NR == 2"という箇所でどの行かを指定していて、1行目は一番上の行の"ID, Data..."等になります。
配列のように0が最初...というわけではないので、気をつけましょう。
ちなみに、0を入れても何も出力されませんでした。(検索に引っかからないので当然ですね...!）
行を指定したら、awkコマンドの"$0"という変数にその列の情報が全て入っているので、$0をprintすることで指定した行の情報表示が可能です。
taka@Taka CUI-test % awk 'NR == 1 {print $0}' test.csv
ID,Date,Sentence,Plice,Path
taka@Taka CUI-test % awk 'NR == 0 {print $0}' test.csv
taka@Taka CUI-test %整形後のCSVは以下のような感じです。
複数行を表示するように
さて、先程一行だけだしましたが、各項目の情報がどんな情報なのかを表す一行目も合わせて出したいですね...
それを行うためには複数行を表示する必要があります。
今度はそれをやってみます。
awk 'NR == 1 || NR == 2 {print $0}' test.csv
上記を実行すると、1行目と2行目を表示することができます。
プログラムを書いている人ならわかりやすいかと思います。
条件で列1と、列2を指定してあげているわけですね。
また、条件の( A || B )の順序は出力の順序には関係なく、元のcsvの順番に出力されることにはご注意ください。
taka@Taka CUI-test % awk 'NR == 1 || NR == 2 {print $0}' test.csv
ID,Date,Sentence,Plice,Path
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
taka@Taka CUI-test % awk 'NR == 2 || NR == 1 {print $0}' test.csv
ID,Date,Sentence,Plice,Path
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
taka@Taka CUI-test %
特定の文字列を含む行を抽出
さて、次は特定の文字列を含む行を抽出します。
では、以下のコマンドを実行してください。
grep -E 'CSV' test.csvみんな大好きgrepコマンドですね。
すると...?
taka@Taka CUI-test % grep -E 'CSV' test.csv
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
taka@Taka CUI-test %上記のように、CSVという文字列をいずれかの列に含んでいる行だけ取得することができます。
注意しなければいけないのは、いずれかの列に含めばいいので、本当はSentence列の文字列に"CSV"が入っているものだけ取り出したい場合、Sentence列には含まずとも、Path列のパス名に"CSV"が入っているものがあと、抽出してしまいます。
大文字、小文字はちゃんと区別されるようです。
taka@Taka CUI-test % grep -E 'csv' test.csv
taka@Taka CUI-test % grep -E 'Csv' test.csv
taka@Taka CUI-test %ちなみに、大文字小文字を区別しない場合は以下でできます。(optionを-iで指定)
taka@Taka CUI-test % grep -i 'Csv' test.csv
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
taka@Taka CUI-test %(ちょっと例が悪いですが, もしcsVやCsv, csvがあっても検索に引っかかります。)
さて、抽出はできましたが、上部が気になりますね...
項目の説明行(ID, Date, Sentence等の記載)が消えて、データの一行目が項目名的なものになってしまっている感じです...
それも合わせていい感じにすると...?
echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`grep -E 'CSV' test.csv`"上記はawkコマンドで一行目だけ出力、そしてそのあとにgrepコマンドを使用してそのほかのデータ部を取得、最初の一行に続けて表示するようにしています。
grepの'CSV'の箇所に検索したい文字列を、そしてtest.csvは対象のファイルを指定します。
多分工夫すれば指定ファイルの書き換えも一箇所ですみそうな気もするけど...
また、いつか調べます...orz
taka@Taka CUI-test % echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`grep -E 'CSV' test.csv`"
ID,Date,Sentence,Plice,Path
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
taka@Taka CUI-test %
きれいになりましたね！
ファイル中で特定の列にしか存在しない文字列がある場合はこれだけで十分そうですね。
特定の列を指定し、ある文字列を含む行だけ表示する
さて、先程は全文からさがしていました...
ただ、やっぱり正確に抽出するなら、ある特定列に対してだけ検索をかけたいところです...
awkコマンドではそれが可能なので、やってみましょう！
以下の二つはやっていることは同じです。
cat test.csv | awk 'BEGIN{FS=","} $3 ~ /CSV/ {print$0}'
cat test.csv | awk '{NR == 3} /CSV/{print$0}'上記のコマンドにより、"test.csv"の三列目(今回はSentence)の値にCSVという文字列を含んでいる行を抽出することができます。
前述のコマンドは三列目なら$3, 二列目なら$2というふうに指定すれば大丈夫で、後者に関してはNRの値が列番号になるので、検索したい列の番号をNRに指定すればOKです。
taka@Taka CUI-test % cat test.csv | awk 'BEGIN{FS=","} $3 ~ /CSV/ {print$0}'
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
taka@Taka CUI-test % cat test.csv | awk '{NR == 3} /CSV/{print$0}'
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
taka@Taka CUI-test %また、どちらも大文字小文字は区別されます。(csv等でCSVを含む文字列は検索できません。)
taka@Taka CUI-test % cat test.csv | awk 'BEGIN{FS=","} $3 ~ /csv/ {print$0}'
taka@Taka CUI-test % cat test.csv | awk '{NR == 3} /csv/{print$0}'
taka@Taka CUI-test %項目の行(ID, Date....)を追加したら以下の形です。
echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`cat test.csv | awk 'BEGIN{FS=","} $3 ~ /CSV/ {print$0}'`"
echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`cat test.csv | awk '{NR == 3} /CSV/{print$0}'`"特定列に対して条件を指定して抽出(日付比較や数値比較)
さて！
先程は文字列だけの指定を特定の列に対しておこないました！
ただ、もっと細かいこともできるんです...!
早速以下にコマンドを貼ります。
cat test.csv | awk 'BEGIN{FS=","} {if($1 > 2)print$0}'
上記は一列目(ID列)が2よりも大きい物だけ抽出しています。
そうなんです...if文が使えてしまうんですね...w
taka@Taka CUI-test % cat test.csv | awk 'BEGIN{FS=","} {if($1 > 2)print$0}'
ID,Date,Sentence,Plice,Path
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
5,2020/08/15,このファイルはカンマ区切りでデータが入っています。,8500円,/hoge/huge/huga/test5
taka@Taka CUI-test %確かに2よりも大きな3以降のデータが表示されているのがわかりますね。
ただ、一点気をつけなければいけないことがあります。
一見何ら問題ないように見えますが、一行目のID...等も含まれているのがわかります。
試しに条件を反転させると...?
taka@Taka CUI-test % cat test.csv | awk 'BEGIN{FS=","} {if($1 <= 2)print$0}'
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
2,2020/11/15,これはてすとの文章です。,10000円,/hoge/huge/huga/test2
taka@Taka CUI-test %一行目はなくなりましたね...
ここの挙動については細かく調べられていませんが、おそらく文字列と数値の比較になってしまっていることによるのかなと思います。
そのため、一行目の"ID"という文字列はASCIIコードに対応する数値に変換され比較されている...と推測しています...。(詳しい方教えてください...!)
ただ、データ側の数値の比較に関して大小の関係は変わらないので、その点は問題なさそうな気がします。
また、if文なので、文字列の比較等ももちろん可能です。
taka@Taka CUI-test % cat test.csv | awk 'BEGIN{FS=","} {if($5 == "/hoge/huge/huga/test4")print$0}'
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4一行目も含めると以下のように可能です。
taka@Taka CUI-test % echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`cat test.csv | awk 'BEGIN{FS=","} {if($5 == "/hoge/huge/huga/test4")print$0}'`"
ID,Date,Sentence,Plice,Path
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4また、日付の比較も可能のようです...!
以下では"yyyy-mm-dd hh:mm:ss"の形式をそのまま文字列同士で比較すればいい感じにしてくれる...と書かれていますが、"/"区切りでもうまくできていそうです。（詳しく検証したわけではないので、重要なデータの場合、ある程度目視でも確認した方がいいかもしれません...!）
taka@Taka CUI-test % cat test.csv | awk 'BEGIN{FS=","} {if($2 > "2020/10/15")print$0}'
ID,Date,Sentence,Plice,Path
1,2020/12/15,これはてすとのCSVです。,5000円,/hoge/huge/test1
2,2020/11/15,これはてすとの文章です。,10000円,/hoge/huge/huga/test2
taka@Taka CUI-test % cat test.csv | awk 'BEGIN{FS=","} {if($2 <= "2020/10/15")print$0}'
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
5,2020/08/15,このファイルはカンマ区切りでデータが入っています。,8500円,/hoge/huge/huga/test5こちらも、文字列比較なので、最初の一行が入るかどうかは条件によって変わってしまいますね。
ついているならそのままで、ついていないなら以下のようにするとよさそうです。
taka@Taka CUI-test % echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`cat test.csv | awk 'BEGIN{FS=","} {if($2 <= "2020/10/15")print$0}'`"
ID,Date,Sentence,Plice,Path
3,2020/10/15,CSVファイルはご存知ですか？,150円,/hoge/huge/test3
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
5,2020/08/15,このファイルはカンマ区切りでデータが入っています。,8500円,/hoge/huge/huga/test5
taka@Taka CUI-test %複数列に対して条件を設定して抽出をする
複数列に対して条件を指定したい場合は以下のようにもできます。
cat test.csv | awk 'BEGIN{FS=","} { if($1 > 3 && $2 <= "2020/10/15")print$0}'if文の中で"$$"や"||"でそれぞれの列($1,$2...)を指定してあげればできるんですね。
例えば上記はtest.csvの1行目(ID)が3より大きく、かつ二行目(Date)が"2020/10/15"以前のデータを抽出しています。
taka@Taka CUI-test % cat test.csv | awk 'BEGIN{FS=","} { if($1 > 3 && $2 <= "2020/10/15")print$0}'
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
5,2020/08/15,このファイルはカンマ区切りでデータが入っています。,8500円,/hoge/huge/huga/test5一行目を追加すると...以下のようになります。
taka@Taka CUI-test % echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`cat test.csv | awk 'BEGIN{FS=","} { if($1 > 3 && $2 <= "2020/10/15")print$0}'`"
ID,Date,Sentence,Plice,Path
4,2020/09/15,CSVファイルはCUIから色々できます。,5000円,/hoge/huge/huga/test4
5,2020/08/15,このファイルはカンマ区切りでデータが入っています。,8500円,/hoge/huge/huga/test5抽出したデータの特定列だけ表示する
さて、最後に一通り欲しいデータを抽出したあと、さらにいらない列を消してしまいたい...
ということがあるかもしれません。
そちらもみていきます...!
せっかくなので、先程最後に出した複数列に条件指定したものに対して特定列を取り出してみます。
echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`cat test.csv | awk 'BEGIN{FS=","} { if($1 > 3 && $2 <= "2020/10/15")print$0}'`"| cut -d , -f 1,3,5
だいぶん長くなってきましたが、上記を実行すると...?
taka@Taka CUI-test % echo `awk 'NR == 1 {print $0}' test.csv` "\n" "`cat test.csv | awk 'BEGIN{FS=","} { if($1 > 3 && $2 <= "2020/10/15")print$0}'`"| cut -d , -f 1,3,5
ID,Sentence,Path
4,CSVファイルはCUIから色々できます。,/hoge/huge/huga/test4
5,このファイルはカンマ区切りでデータが入っています。,/hoge/huge/huga/test5ID、Sentence, Pathの三列文しか表示されていないのがわかりますね...!
行っていることは、先程解説してきたコマンドの末尾に " | cut -d , -f 1,3,5" という部分を付け加えただけです。
これによって、カンマ区切りで1, 3, 5列名のデータだけを抽出することが可能です。
元々cut無しだとcsvは以下のような感じになります。
それが、cutコマンドで列を指定すると...?
このように特定の列だけ抽出することができるんですね！
（後から気づいたけど、日付で比較してるのに日付出していないあたりとか変ですが許してください...!)
まとめ
さて、今回はCSVをいろんな方法でいい感じに整形する...
というとてもざっくばらんとしている目標のもと色々しらべましたが、意外と色々できそうでしたね...!
毎回調べる...というのはそれなりに骨が折れますが、ログや週次の集計データ等、一度うまく動くコマンドを作っておけばあとはコピペで済む...等にしておくととっても便利そう...
僕もつい先日簡単なCSVを調べる機会があったんですが、せっかくなのでそのログで使えるものは作っておきたいですね...!
今回あんまり細かいコマンドの解説等はできていませんが、一度一通り理解できたら色々と応用も効くので、是非皆さんもawkコマンドやgrepコマンド、パイプなどについて調べてみることをお勧めします！
それでわ！
