Sort csv columns?

Question

I have a CSV file that looks like:

Timestamp;A;O;P;M;
22:05;5;7;2;1
22:10;2;3,4;7
22:15;7;4;3;2

With a python script I would like to order the header (except the timestamp. it has to be the first column). What I mean is:

Timestamp;A;M;O;P;
22:05;5;1;7;2
22:10;2;7;3;4
22:15;7;2;4;3

How can I do it? I don't have any code because I can't figure out how to do it.

Why is some of your data comma-separated and some semi-colon? — John Zwinck, Aug 26 '14 at 12:27

score 2 · Accepted Answer · answered Aug 26 '14 at 12:37

2

You can do it with NumPy!

import numpy as np

a = np.loadtxt('a.txt', dtype=str, delimiter=';')
s = a[0].argsort() # produces the indexes which would sort the header
s = np.append(0, s[s!=0]) # put 0 at the front again, that's Timestamp
final = a[:,s]
np.savetxt('final.txt', final, fmt='%s', delimiter=';')

answered Aug 26 '14 at 12:37

John Zwinck

239,568
38
324
436

Nice +1 for numpy even though I think its overkill in this situation. – Burhan Khalid Aug 26 '14 at 12:43
File "C:\Users\ButoeruG\Desktop\xelena\prova\sort.py", line 7, in a = np.loadtxt('finale.txt', dtype=str, delimiter=';') File "C:\Python27\lib\site-packages\numpy\lib\npyio.py", line 856, in loadtxt X = np.array(X, dtype) ValueError: cannot set an array element with a sequence. What does this mean? – Gabriel Butoeru Aug 27 '14 at 06:53
I think it's because your header lines have a trailing delimiter. Try removing that. – John Zwinck Aug 27 '14 at 07:03
What do you mean? An extra ';' at the end? – Gabriel Butoeru Aug 27 '14 at 13:07
Yes, you should have the same number of `;` on each line, but the first line has one more. – John Zwinck Aug 27 '14 at 13:59
I tried addign ';' at the end of each row and I tried to remove the ';' too but it doesn't worked. My csv file has some empty rows (;;;;;;;;;;;;) but I don't think that this is the problem. The script doesn't work even if I use a csv that doesn't has empty rows/cells. – Gabriel Butoeru Aug 28 '14 at 07:56
I only get the error you're reporting if I have mixed delimiters in the file. For example in your question you still have one stray comma (`3,4;7`). – John Zwinck Aug 28 '14 at 08:16

score 1 · Answer 2 · answered Aug 26 '14 at 12:34

1

You mean something like this?

import csv

with open('old.csv', 'r') as i, open('new.csv', 'w') as o:
   reader = csv.reader(i, delimiter=';')
   writer = csv.writer(o, delimiter=';')
   for row in reader:
      writer.writerow([row[0], row[1], row[4], row[2], row[3]])

answered Aug 26 '14 at 12:34

Burhan Khalid

169,990
18
245
284

This doesn't actually "sort" anything, it just hard-coded moves the columns. If that's all the OP needs, it might be faster/easier to just use awk or something. Anyway, simple, idiomatic Python is always good. :) – John Zwinck Aug 26 '14 at 13:03

score 0 · Answer 3 · edited May 23 '17 at 12:08

0

Hi this post might help you. Check this link. sort csv by column

https://stackoverflow.com/questions/2100353/sort-csv-by-column

edited May 23 '17 at 12:08

Community

1
1

answered Sep 25 '14 at 06:35

akbsmile

1,119
2
11
15

Sort csv columns?

3 Answers3