This is a test version of Biostars. For the public version, visit https://www.biostars.org.
how to change and exchange columns or row order in csv-file

Dear all!

Sorry, this question probably was many times discussed,

but I have not found a clear answer.

I have a single Excel-file. I have either zero or some number in each cell.

I've transformed it to csv-file.

I know about some Python-modules, but I failed to apply them.

Does any clear program package exist to change column order or row order?

I need to shuffle both ones separately.

Randomly or in some fixed order. I am interested in both variants.

Sorry again if it is a repeat,

Many thanks!

Natasha

genome sequence csv
• 7,763 views • link
written by natasha.sernova natasha.sernova

2 answers

Awk can do this, so can R. I'm sure python can do this as well, but it's not what i'm familiar with.

If you give us some example data, and let us know what exactly you want to do we might be able to help you further.

• 1 views • link
written by cbio cbio

Dear Carlos,

Thank you for your answer! I could not download full excel.csv file, here is just a short fragment of the initial excel-file.

How can I mix (or change the positions of its columns) randomly or depending on the number of zeros in the column?

Is it possible to save somewhere all possible pairs of columns from this file? Many-many thanks!

Natasha

pamp_8    paq_9    pay_4    pjs_46    pqy_17    psh_57
0         0        0        0         0         0
1691      1648     1596     1766      1656      1726
1799      1743     1707     0         1871      0
2170      2172     2078     0         2277      0
1688      1645     1593     1763      0         1723
1689      1646     1594     1764      1654      1724
1690      1647     1595     1765      1655      1725
2173      0        2081     0         0         0
1430      1385     1331     0         1352      0
1800      1745     1708     0         1872      0
1827      1770     1733     1883      0         1836
303       303      313      346       301       346
304       304      314      347       0         347
0         0        2615     0         0         0
1432      1387     1333     0         1354      0
1798      1742     1706     0         1870      0
0         0        0        0         0         0
2169      2171     2077     0         2275      0
1829      1772     1735     1885      1900      1838
• 1 views • link
updated by Ram Ram • written by natasha.sernova natasha.sernova

You will need to save the file in some delimited format. Add -d option if you use a different delimiter (-d',') You can put this command in a loop to get all column combinations.

$ cut -f 1,2 your_file > col_1_2
$ cut -f 1,3 your_file > col_1_3
$ cut -f 5,6 your_file > col_5_6

You can use awk to print the columns in any order you wish (if file is not tab delimited change \t delimiter)

$ awk -F '\t' '{print $6"\t"$3"\t"$2}' your_file > new_file_col_6_3_2
• 1 views • link
updated by Ram Ram • written by GenoMax GenoMax

If this works for you, I suggest you accept genomax2's answer as the answer since he did all the work. Hope it works out!

• 1 views • link
written by cbio cbio

@Carlos: I did not address the number of zeros part. If you can extend the solution above for that then that should address everything.

• 1 views • link
written by GenoMax GenoMax

Thank you very much, Carlos!

Sincerely yours,

Natasha

• 1 views • link
written by natasha.sernova natasha.sernova

Perfect! Thousand thanks!

cut -f 1,2 your_file > col_1_2

What computer language did you use for this?

Or this is just bash-command, isn't it? I've never seen it, sorry.

THANK YOU AGAIN!!!

Sincerely yours,

Natasha

• 1 views • link
updated by Ram Ram • written by natasha.sernova natasha.sernova

Log in to answer this question.

More posts like this