Combining different tasks in one line of code in terminal

Question

[Za]

Hi,

I have many .xls files containing my read counts

I need to COMBINE these files while I must remove each row of each file containing "exon", removing columns 1, 2, 3, 5, 6, 8

and removing _mRNA from the end of each row of column 4

How I can do that in terminal?

I think doing so manually take too much time and would not be accurate enoughstrong text

[Bastien Hervé]

I don't think you can read xls using command line easily. basic unix command.

Try to export your xls to csv first.

You can find many topics of what you want to do over the web, but the use of awk should be the best answer.

Take a look at these links:

https://stackoverflow.com/questions/34682182/delete-rows-of-csv-file-based-on-the-value-of-a-column

http://www.tim-dennis.com/data/tech/2016/08/09/using-awk-filter-rows.html

https://unix.stackexchange.com/questions/97070/filter-a-csv-file-based-on-the-5th-column-values-of-a-file-and-print-those-reco

https://stackoverflow.com/questions/44441839/filter-csv-list-with-awk

https://stackoverflow.com/questions/17001849/awk-partly-string-match-if-column-word-partly-matches

[cpad0112]

Good description of data. Please post example data and expected output

Answer 1

[shenwei356]

Take a look at csvtk, a cross-platform, efficient and practical CSV/TSV toolkit in Golang. It provides many subcommands for lots of csv/tsv manipulations:

• csvtk xlsx2csv (usage), convert .xlsx (not .xls) to csv.
• csvtk grep (usage), search (invert-search with -v) specific columns using regular expression.
• csvtk cut (usage), select/unselect columns, e.g., csvtk cut -f -1,-2,-3,-5,-6,-8.
• csvtk replace (usage), edit specifc row using replace expression, csvtk replace -f 4 -p "_mRNA$"
• csvtk join (usage), to merge files according to key column(s).

csvtk supports reading from stdin, so you can use one line command pipe to finish all cleaning work.

Combining with shell for or parallel, you can easily parallelly handle multi files.

It's better if you can provide some sample data.

Answer 2

[lakhujanivijay]

Of course, if you want to do with your data being in the excel format and that too programatically (sensibly; synonym :D ) then may be you can use file parsers designed specially to manipulate excel files for e.g. this one.

Next option is transforming all data into simple text files and may be putting that here so that one can understand what you have and what you want. But before posting anything, try your best!