how to change and exchange columns or row order in csv-file
2
0
Entering edit mode
6.4 years ago
natasha.sernova ★ 3.9k

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 • 5.5k views
ADD COMMENT
1
Entering edit mode
6.4 years ago
cbio ▴ 450

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.

ADD COMMENT
0
Entering edit mode
6.4 years ago
natasha.sernova ★ 3.9k

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
ADD COMMENT
1
Entering edit mode

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
ADD REPLY
1
Entering edit mode

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!

ADD REPLY
1
Entering edit mode

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

ADD REPLY
0
Entering edit mode

Thank you very much, Carlos!

Sincerely yours,

Natasha

ADD REPLY
0
Entering edit mode

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

ADD REPLY

Login before adding your answer.

Traffic: 1718 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6