Extract data based on certain pattern in one column

Question

I have a dataset like this:

AAACCTGCAAGCGAGT-1,ENSG00000000419,DPM1,2
AAACCTGCAAGCGAGT-1,ENSG00000001497,LAS1L,1
AAACCTGCAAGCGAGT-1,ENSG00000002330,BAD,1
AAACCTGCAAGCGAGT-1,ENSG00000002549,MT-CO3,2
AAACCTGCAAGCGAGT-1,ENSG00000002586,CD99,5
AAACCTGCAAGCGAGT-1,ENSG00000002834,LASP1,1
AAACCTGCAAGCGAGT-1,ENSG00000003056,M6PR,1
AAACCTGCAAGCGAGT-1,ENSG00000003402,MT-CYB,2
AAACCTGCAAGCGAGT-1,ENSG00000004059,ARF5,2
AAACCTGCAAGCGAGT-1,ENSG00000004455,AK2,1
AAACCTGCAAGCGAGT-1,ENSG00000004468,CD38,8
AAACCTGCAAGCGAGT-1,ENSG00000004779,NDUFAB1,3
AAACCTGCAAGCGAGT-1,ENSG00000004975,DVL2,1
AAACCTGCAAGCGAGT-1,ENSG00000005022,SLC25A5,6

I want to get the data that third columns contains mitocondrial genes which is have pattern "MT-", for example, the dataset above, I would like to extract:

AAACCTGCAAGCGAGT-1,ENSG00000002549,MT-CO3,2
AAACCTGCAAGCGAGT-1,ENSG00000003402,MT-CYB,2

I use this command:

awk '$3 ~/^MT-/ {print $1, $2, $3, $4} final_matrix.csv

But it didn't work.

Answer 1

Awk expects whitespace separated fields by default - if your file is comma-separated, you will need to set the field separator accordingly, either using the -F command line option or by setting the internal FS awk variable.

If you want the output to be comma separated as well, you need to set OFS.

Ex.

$ awk -F, 'BEGIN{OFS=FS} $3 ~ /^MT-/ {print $1, $2, $3, $4}' final_matrix.csv
AAACCTGCAAGCGAGT-1,ENSG00000002549,MT-CO3,2
AAACCTGCAAGCGAGT-1,ENSG00000003402,MT-CYB,2

Answer 2

All answers are correct, in addition you can print the whole line.

And there is no need to get into field parsing.

awk  '/,MT-/{print}' final_matrix.csv

Answer 3

The idea is right, but you forgot to tell awk how to delimit the column entries such that $1 would be distinguished from $2 till the rest of the columns. Right now it is just looking for a single white-space to delimit your text on, but ended up finding the whole line on $1 and didn't find $3 at all. So $3 ~ .. would just be a false condition, resulting in none of the lines getting printed.

awk -v FS=, -v OFS=, '$3 ~/^MT-/ {print $1, $2, $3, $4}' final_matrix.csv

Answer 4

Given your posted example you could just do:

grep ',MT-/' final_matrix.csv

but if you wanted to use awk and focus on the 3rd field then it'd just be:

awk -F, '$3 ~ /^MT-/' final_matrix.csv

or:

awk -F, 'index($3,"MT-")==1' final_matrix.csv