This is a test version of Biostars. For the public version, visit https://www.biostars.org.
select rows in a tab-delimited ed file

Hi all, I hope you are doing well. I have a tab-delimited file (about 60000 rows). The first column has information about the transposable element subfamily (eg: SINE, LINE, LTR or ERV). any Idea how can I sort them according to this information? I have attached an example to make it more clear.

I really appreciate any help you can provide.

Surar

ps: the file has three columns, one contains strings and two columns with numbers only.

chr6|42126072|42126376|AluSg:Alu:SINE|79|-,-    75.27258521 -1.598304168
chr6|106863597|106863885|THE1B:ERVL-MaLR:LTR|102|+,-    5.261992063 -4.333613608
chr7|73829665|73829806|L2a:L2:LINE|331|+,+  10.93709267 3.390930038
chr2|60928170|60929829|L1PA2:L1:LINE|18|+,+ 777.2062642 -0.943366885
chr15|42079918|42080207|MER33:hAT-Charlie:DNA|174|-,-   21.23243208 -3.026516149
chr12|14620131|14620527|MER57C2:ERV1:LTR|199|+,+    72.34162087 -1.262584508
chr15|66887807|66888452|LTR8:ERV1:LTR|65|-,-    25.45794673 -4.407397479
chr11|64988838|64988985|MIRb:MIR:SINE|288|+,-   16.14047449 -2.830192579
linux text
• 3,124 views • link
updated by Ram Ram • written by S.O.T.AL-HASHIMI2 • S.O.T.AL-HASHIMI2 
$ awk -v OFS="\t" -F "[:|]" '{print > $6".txt"}' test.txt
• 0 views • link
written by cpad0112 cpad0112

I'm not sure if OP changed their question after you added the comment, but this won't help sort anything.

• 0 views • link
written by Ram Ram

OP didn't change the post. It was my mistake in understanding. Code I posted will write each element (SINE,DNA etc) into individual files. Following code posted by you (@ Ram) will sort the file by elements.

• 0 views • link
written by cpad0112 cpad0112

Not a mistake, really. "Sort" can have two meaning and without examples from OP, understanding their requirement can be inexact.

• 0 views • link
written by Ram Ram

If you don't mind using R, here's a quick solution. For columns I didn't know what they were, I just named them Unknown 1 and Unknown 2. I did the same for the Transposon column (named the three One, Two, Three, with Three having the information you wanted to sort by.)

library(tidyverse)

read_lines("sample.txt") %>%
    map_dfr(~ str_split(.x, "\\|", simplify = TRUE)[1,] %>%
            as_tibble() %>%
            mutate(column = c("Chromosome", "Start", "End", "Transposon", "Unknown_1", "Unknown_2")) %>%
            pivot_wider(names_from = column, values_from = value)) %>%
    separate(Transposon, sep = ":", into = c("One", "Two",  "Three")) %>%
    arrange(Three)
• 0 views • link
written by bioinformatics2020 bioinformatics2020

Please mention that this is R 4.2+ - not everyone is using the latest R with the builtin pipe operator. Or, given that you're using tidyverse, use magrittr's %>% so your code becomes a little more backward compatible.

• 0 views • link
written by Ram Ram

Ah good point - I've gotten so used to the native pipe. Changed it here and will for the other post I just made!

• 0 views • link
written by bioinformatics2020 bioinformatics2020

Native pipe is 4.1 just FYI.

• 0 views • link
written by rpolicastro rpolicastro

Thank you very much for all the answers and sorry for the late reply. for some reason, I didn't get any notification. using sort -t: -k3,3 test.txt solved my problem.

Best regards Surar

• 0 views • link
written by S.O.T.AL-HASHIMI2 • S.O.T.AL-HASHIMI2

1 answer

I don't think so, as it would only have the LTR/SINE text. We would need something like sort -t: -k3,3 test.txt (unless by "sort" OP meant not "to order" but "to classify/partition" into different files)

• 0 views • link
written by Ram Ram

Thank you very much, using sort -t: -k3,3 test.txt has solved my problem.

• 0 views • link
written by S.O.T.AL-HASHIMI2 • S.O.T.AL-HASHIMI2

Please accept @Ram's answer to provide closure to this thread (green check mark),

• 0 views • link
written by GenoMax GenoMax

Log in to answer this question.

More posts like this

No similar posts found.