macでインフォマティクス

macでインフォマティクス

HTS (NGS) 関連のインフォマティクス情報についてまとめています。

インフォマティクス解析に使えるコマンドの紹介1 excelからのデータ抽出

 

バイオインフォマティクス解析の初心者の方が、ターミナル環境を扱う際に知ってると便利そうなコマンド、tipsなどを紹介していきます。

 

Excelの重たい解析データも、ターミナルで操作すればサクサク扱うことができます。ということで、最初は

 

 1、Excelファイルからデータを抽出し、ついでに並べ替え、抽出なども高速で行う。

excelファイルからcsvを抽出できるxlsx2csvを使います。

Githubリンク

GitHub - dilshod/xlsx2csv: Convert xslx to csv, it is fast, and works for huge xlsx files

 

pipでインストールします。 

pip install xlsx2csv

インストールしたライブラリの確認

pip freeze

出てくるリストの中にxlsx2csvがあることを確認します。

 

ランは

xlsx2csv

で行います。

以下のような内容のExcelファイル (data.xlsx) があるとします。

  name read
1 gene1 42
2 gene2 3333
3 gene3 363
4 gene4 73
5 gene5 846
6 gene6 200
7 gene7 36464
8 gene8 452
9 gene9 842
10 gene10 462

 

csvへの変換は以下のコマンドを打ちます。

xlsx2csv data.xlsx > output.csv
cat output.csv

home$ xlsx2csv 1.xlsx > output.csv

home$ cat output.csv 

,name,read

1,gene1,42

2,gene2,3333

3,gene3,363

4,gene4,73

5,gene5,846

6,gene6,200

7,gene7,36464

8,gene8,452

9,gene9,842

10,gene10,462

白文字部分がcsvファイルに変換された出力結果です。オリジナルのexcelデータの並びが崩れていないことを確認します。

ただ抽出するだけならこれで終わりですが、パイプを使い、様々なコマンドで内容をソートしたり文字変換して、1発で都合の良い形式で出力する方法を学びます。複数のコマンドが出てきてややこしいですが、UNIXのコマンドに不慣れな方はこの流れを真似して一緒に覚えてしまえば楽になります。

 

1列目はただの通し番号なので変換時に消します。それにはパイプ(縦棒)を使い、cutコマンドと組み合わせます。2-3列目だけ出力するなら、

xlsx2csv data.xlsx |cut -d "," -f 2,3 > output.csv

-fで出力する列を指定。

-dでフィールドセパレータを指定、ここでは ' で区切られてるので-d "'"。例えばスペースで仕切られているなら-d " "

出力は

name,read

gene1,42

gene2,3333

gene3,363

gene4,73

gene5,846

gene6,200

gene7,36464

gene8,452

gene9,842

gene10,462

 1行目が消えていることが分かります。

 

さらに2列目のreadの数値の降順で並べ替えもすることにします。再びパイプでつなぎ、cutの結果をsortに繋げます。

xlsx2csv data.xlsx |cut -d "," -f 2,3 |sort -t "," -k 2 -n -r > output.csv

sortのオプションは

-tでフィールドセパレータを指定。

-k出力する列を指定。

-nで数値でソート。

-rで降順ソート。-昇順ソートなら-rを消す。

出力は

gene7,36464

gene2,3333

gene5,846

gene9,842

gene10,462

gene8,452

gene3,363

gene6,200

gene4,73

gene1,42

name,read

2列目の数値の降順でソートされています。

 

名前に重複がないか確認もしておきます。uniqコマンドを使います。

xlsx2csv data.xlsx |cut -d "," -f 2,3 |sort -t "," -k 2 -n -r |uniq -c > output.csv

-cをつけると出力の左端に登場回数が出ます。重複行があれば、1つを残して消されて出力されます。

   1 gene7,36464

   1 gene2,3333

   1 gene5,846

   1 gene9,842

   1 gene10,462

   1 gene8,452

   1 gene3,363

   1 gene6,200

   1 gene4,73

   1 gene1,42

   1 name,read

uniqの注意点ですが、uniqは連続した行にしか効果がないので、必ず先に重複チェックしたい列に対してsortをかけておきます。

 

1番下は必要ないのでcutします。"haed -番号"、で先頭から指定行だけ抽出します。

xlsx2csv data.xlsx |cut -d "," -f 2,3 |sort -t "," -k 2 -n -r |uniq -c |head -10 > output.csv

出力は

gene7,36464

gene2,3333

gene5,846

gene9,842

gene10,462

gene8,452

gene3,363

gene6,200

gene4,73

gene1,42

末尾から指定するなら"tail -番号" を使います。途中を抜くならsedを使います(10-100行目を抜くならsed -e "10,100d")。

 

検索文字がある行だけ出力したいならgrepを使います。

xlsx2csv data.xlsx |cut -d "," -f 2,3 |sort -t "," -k 2 -n -r |uniq -c |grep --color=auto "gene" > output.csv

grepをつけると、検索対象に合致した行だけ出力されます。--color=autoで検索文字に色がつきます。 出力は

   1 gene7,36464

   1 gene2,3333

   1 gene5,846

   1 gene9,842

   1 gene10,462

   1 gene8,452

   1 gene3,363

   1 gene6,200

   1 gene4,73

   1 gene1,42

-vをつけると、ヒットしなかった行だけ出力されます。-oをつけると検索文字のみ出力され、-iをつけると大文字小文字の区別なく検索します。

 

sedも組み合わせ、,をスペースに置換します。

xlsx2csv data.xlsx |cut -d "," -f 2,3 |sort -t "," -k 2 -n -r |uniq -c |grep "gene" |sed -e 's/,/ /g' > output.csv

 出力は

   1 gene7 36464

   1 gene2 3333

   1 gene5 846

   1 gene9 842

   1 gene10 462

   1 gene8 452

   1 gene3 363

   1 gene6 200

   1 gene4 73

   1 gene1 42

区切りが,からスペースに変わっています。

 

このあたりのコマンドを組み合わせれば、ある程度複雑な変換もワンライナーのコマンドでできて便利です。

 

 本当はもっと体系的に説明すべきなので、時間があればまとめたいと思います。