I am working with a list with billions rows of data.

I have data like this: enter image description here

Like you see, in fourth column (gene column) there exist names of genes but not all rows have a "gene name". I need to get the complete list of "gene name" from fourth column.

How I can get what I need?

  • Is the "gene column" the one labelled "product"? Would it be enough to output entries from that column (only) that is not "hypothetical protein", or do you need data from one of the other columns too? – Kusalananda Mar 10 at 17:07
  • Yes, it is. Almost "gene column" labeled with "product name" but not all. Yaaa, you're right. I can remove line with "hypothetical protein" to minimize my data. I only need whole "gene name" and product name except "hypothetical protein". Do you have an advice for me? – Lulu' Nisrina Mar 10 at 17:19
  • 1
    @Lulu'Nisrina Could you rephrase the text in your question in such a way that no biology training is necessary to understand it? In particular, use the names from the headers in the data that you show rather than referring to interpretations of the data. I might be a bioinformatician (sort of), but most people here are not, and it would be nice to avoid silly misunderstandings. – Kusalananda Mar 10 at 17:21
  • 1
    Oke. I'll rephrase it. – Lulu' Nisrina Mar 10 at 17:23
  • 5
    Please edit your question to get rid of that image (or leave it as something extra if you think it'd add value) and instead provide concise, testable textual sample input/output so we can better understand your problem and have something we could test a potential solution against. See How to Ask if that's not clear. And make sure the column headers have the values you refer to in the text - don't make us have to mentally map "gene column" to "product name" or whatever. – Ed Morton Mar 10 at 18:12

Try this one-liner:

cut -f4 in.tsv | tail -n +2 | grep -P '\S'

cut -f4 in.tsv: output the 4th TAB-delimited column of the input file in.tsv.
tail -n +2: remove the first line (header).
grep -P '\S': keep only the lines that have non-whitespace characters, that is, remove blank lines. -P tells grep to use Perl regular expressions.

If you need only the unique gene names, add sort -u like so:

cut -f4 in.tsv | tail -n +2 | grep -P '\S' | sort -u
| improve this answer | |

It is not clear what is your requirement. Assuming that, excluding the first row, it is only the values of the fourth column (labelled "gene"), whose value in sixth column (labelled "product") is different from "hypothetical protein"

grep -v "hypothetical protein" < <(tail -n +2 file.tsv) | cut -f4 -d$'\t'


tail -n +2 file.tsv

excludes the first row ("locus_tag", "type", etc.)

grep -v "hypothetical protein"

excludes all rows containing the "hypothetical protein" string

cut -f4 -d$'\t'

prints the fourth column.

| improve this answer | |

This looks like a task for awk. You might try:

awk '{if ($4); print $4 $7}' filename.tsv

Following the useful suggestion from the comments:

awk 'BEGIN { FS = "\t" } ; $4 != "" { print $4 "\t" $7}'
| improve this answer | |
  • This would concatenate the 4th and 7th fields with no delimiter between them. They want tab-delimited output. Theoretically, it would also skip genes whose names are 0 (there no such genes, but it would be better to test with $4 != "", and to set FS to a tab). – Kusalananda Mar 10 at 17:06
  • 1
    also this will wrongly process column #5 in row #18 as column #4 and wrongly column #NF as column #4 or #5 for other rows, since awk resolves repeated delimiter (default tab/space) as one. [ edit]: still has same issue after your update – αғsнιη Mar 10 at 17:18
  • I will try like Alex mention. Actually to get product name except "hypothetical protein" is optional. It is enough to only get the whole name of the gene from "gene column" – Lulu' Nisrina Mar 10 at 17:29
  • I already try command awk '{if ($4); print $4 $7}' filename.tsv but I get not only the list of gene name, I also get print of hypothetical protein – Lulu' Nisrina Mar 10 at 17:42

Using csvcut:

csvcut -t -c gene -x file.tsv

The -x will remove blank lines.

| improve this answer | |
  • Glad it helped. Please read this. – agc Mar 12 at 14:42

Using awk:

awk -F'\t' '$4 != "" {arr[$4] = 1} END {for (idx in arr) print idx}' file.tsv
  • -F'\t': Split on tab.
  • $4 != "": If the 4th field is not empty…
  • {arr[$4] = 1}: …use it as the index in an array-assignment.
    • Subsequent instances of the same index will overwrite the array-entry, no duplicates are stored.
    • The value assigned (1) is arbitrary, 0 or "blergh" would work just as well.
  • END: When all lines have been read…
  • {for (idx in arr) print idx}: …print all the indexes.
| improve this answer | |

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.