xlrd / xlutils reordering spreadsheet rows

Question

So I have an excel spreadsheet that has a specific column I want to sort the spreadsheet by. The column has 3 different combinations of starting letters, B,C,E. I want to order by B then E then all the rows starting with C. I do need to sort the whole table from the column

Anyone know how to do this in Python?

Michael · Accepted Answer · 2012-09-28T12:42:06.167

1

Read your sheet into a list of rows lst, the column index of your specific column being index.

import xlrd
wb = xlrd.open_workbook('myworkbook.xls')
sh = wb.sheet_by_index(0)
lst = [sh.row(n) for n in xrange(sh.nrows)]
searchdict = dict(B=1, E=2, C=3)
lst.sort(key=lambda row: searchdict.get(row[index][0], 4))

The [0] in the last line accesses the first character of your field, which is looked up in a dictionary then. The default value of 4 in the dict.get arguments puts all not fitting lines to the end of your new list. Not tested in detail, but should get you the idea.

edited Sep 28 '12 at 12:42

answered Sep 28 '12 at 03:02

Michael

7,316
1
37
63

So I think the problem here is that I have like 10 columns, and when I reorder I need all columns reordered, right? – user1610719 Sep 28 '12 at 03:04
Well, please be more precise about your requirements. Do you only want to reorder this one single column or the whole table according to this one column? – Michael Sep 28 '12 at 03:07
Then this should work for you as is. I was also not 100% sure what you meant with combinations of starting letters. Did you mean combination A='asdf', E='sdfsd' etc. or Axxx, Exxx, Cxxx? The answer would have to be slightly modified according to that. – Michael Sep 28 '12 at 03:12
They all start with the same 3 letters, but yea the column is Axxx, Exxx, Cxxxx – user1610719 Sep 28 '12 at 03:29
I keep getting int object is no iterable when trying this, any idea where I'm off? – user1610719 Sep 28 '12 at 12:27
I actually found a better work around, my logic was just bad and once I fixed that I didn't need to, but this did fix the problem at hand! Thanks guys – user1610719 Sep 30 '12 at 20:24

score 0 · Answer 2 · edited May 23 '17 at 10:28

You could use something like how to sort xls file column wise and write it to another file with entire row using python?

And in your sorting function (key), I would do something like this (not tested, add error checking) for the comparison. If you want to implement __gt__ and __lt__ then they're basically the same with the > switched to < .

    order = ['B','E','C'] #Set the order for the first letter here
    def gt( a, b ):

        if a[0] == b[0]:
          return a > b   #Same letter, use normal sorting
        else:
          return order.index(a[0]) > order.index(b[0])   #Sort using first letter

Note: If your question was NOT asking to sort passed the first letter, then go with the other answer.

xlrd / xlutils reordering spreadsheet rows

2 Answers2