Sorting excel data using more than 1 column

Question

I am trying to sort excel data using 2 or 3 columns (so a nested sort if you will). Currently what occurs is I get a text file (some times more than 1) with log entries, I then have to convert the entries into a managed Excel spread sheet.

I have managed to do this, but now my manager wants me to also sort it by the 'date' and 'time' columns respectively.

So to break it down I am just trying to figure out how I can do this.

Requirements:

Sort a csv or excel spreadsheet via multiple columns and write them to a document (of the same type preferably).

CODE:

Currently the code I have is:

import csv, os
import operator

testcsv= open('test.csv', 'r')
csvSort = csv.reader(testcsv, delimiter=",")
sort = sorted(csvSort,key=operator.itemgetter(0,1))

for eachline in sort:
    print eachline

This works fine for "printing" and checking. But I can't get this to write the data back into an excel spreadsheet.

If I did that it goes all into 1 row instead of multiple rows. When I do the write lines to files this is the code I use:

dest = "C:/Test2.csv"
for eachline in sort:
    if not os.path.exists(dest):
        fileToCreate = open(dest, 'w')
        fileToCreate.writelines(eachline)
    else:
        fileToCreate = open(dest, 'a+')
        fileToCreate.writelines(eachline)
    fileToCreate.close()

Perhaps if someone has a better option for me to use or something to add that would help me, I'd be grateful.

Test input data file which is being used

05/02/2015,20:22:24,201534
07/02/2015,20:23:24,201534
05/02/2015,20:23:24,201534
06/02/2015,20:23:24,201534
05/02/2015,20:24:24,201534
13/02/2015,20:24:24,201534
13/02/2015,20:24:24,201534
12/02/2015,20:21:24,201534
01/02/2015,20:21:24,201534
21/02/2015,20:21:24,201534
21/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
13/02/2015,20:21:24,201534
05/02/2015,20:21:24,201534
05/02/2015,20:21:24,201534
05/02/2015,20:21:24,201534
05/02/2015,20:18:24,201534
05/02/2015,20:18:24,201534
05/02/2015,20:18:24,201534
05/02/2015,20:22:24,201534

Anand S Kumar · Accepted Answer · 2015-09-08 01:41:22Z

1

You can reuse csv.writer to write back the entire sorted list of lists. It has a function called writerows() for that. Example -

dest = "C:/Test2.csv"
with open(dest,'w') as f:
    writer = csv.writer(f)
    writer.writerows(sort)

Please note you do not need to specify delimiter as , , default delimiter is comma.

Currently when you use writelines with eachline list, it writes each element of the list simultaneously, but it would not add the required newline characters. So all of the data would appear to be in a single line. You should be using csv.writer for writing back csv/excel files.

answered Sep 8, 2015 at 1:41

Anand S Kumar

91.4k18 gold badges196 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

CFNZ_Techie Over a year ago

Thanks, this works for the most part, however seems to add an extra row for each entry, any way to avoid this? (it adds an extra row in excel, not notepad)

Anand S Kumar Over a year ago

hmm, is there any newlines in between the rows in notepad?

CFNZ_Techie Over a year ago

Not that I can tell, I think it's off the premise that 1 notepad new line is equivalent to 2 new lines in Microsoft office

Anand S Kumar Over a year ago

Hmm, I am not sure about that, can you show me the input file? Update that in the question.

CFNZ_Techie Over a year ago

Done - it's literally just some test dates, times and error codes

|

Collectives™ on Stack Overflow

Sorting excel data using more than 1 column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related