0

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.

  • The default field separator in awk is a space. Your data uses a comma as the field separator. Refer to the awk man page, particularly the -F option. – Jim L. Jun 11 at 15:22
  • If any of the answers solves your issue, please consider "accepting" the answer. This is the best way to show gratitude on this site. Accepting an answer not only marks the question as resolved, but also signals to future readers that the accepted answer actually solved the issue. More information about this is available here: unix.stackexchange.com/help/someone-answers – Kusalananda Jun 12 at 12:32
  • My previous comment applies to you other questions as well, none of which have accepted answers. If an answer is confusing or does not work, you should comment on it to say so to give the answerer a chance to clarify or correct it. – Kusalananda Jun 12 at 12:33
2

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
  • It works now, thanks! – Wendy Huang Jun 11 at 15:25
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
  • No need for the {print} either as that's awks default action given a true condition. – Ed Morton Jun 12 at 12:27
1

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.