python - Sort xls file content by mutiple column with data-type -

-

i have sort xls file content 4 columns in ascending order.

i converted xls file content list of list. following input

input:

data = """abc, not consider1, 101, title , subtitle, not consider2, 30/12/2015 abc, not consider1, 100, title , subtitle, not consider2, 31/12/2015 abc, not consider1, 99, bic codes, not consider2, 31/12/2015 abc, not consider1, 98, title , subtitle, not consider2, 25/12/2015  abc, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 xyz, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 xyz, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 abc, not consider1, 100, title , subtitle, not consider2, 30/12/2015"""

respective output in string format:

 data = """abc, not consider1, 98, title , subtitle, not consider2, 25/12/2015  abc, not consider1, 99, bic codes, not consider2, 31/12/2015 abc, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 abc, not consider1, 100, title , subtitle, not consider2, 30/12/2015 abc, not consider1, 100, title , subtitle, not consider2, 31/12/2015 abc, not consider1, 101, title , subtitle, not consider2, 30/12/2015 xyz, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 xyz, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 """

first split data list format:

    # split data list. >>> data_list = [i.split(", ") in  data.split("\n")]  >>> print "\n".join([", ".join(i) in  data_list]) abc, not consider1, 101, title , subtitle, not consider2, 30/12/2015 abc, not consider1, 100, title , subtitle, not consider2, 31/12/2015 abc, not consider1, 99, bic codes, not consider2, 31/12/2015 abc, not consider1, 98, title , subtitle, not consider2, 25/12/2015  abc, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 xyz, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 xyz, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 abc, not consider1, 100, title , subtitle, not consider2, 30/12/2015

following sorting requirement:

- have sort index0 ,     if index0 have same values multiple items sort index2         if index0 , index2 same multiple items sort index3            if index0, index2 , index3 same multiple items sort index5

my logic

  1. to create string of index0, index2, index5 , index5
  2. create dictionary key step 1
  3. use sorted function sort key list
  4. again create xls file.

code:

>>> collections import defaultdict >>> data_dict = defaultdict(list) >>> in data_list: ...     key = "%s%s%s%s"%(i[0].strip(), i[2].strip(), i[3].strip(), i[5].strip()) ...     data_dict[key].append(i) ...  >>> sorted_keys = sorted(data_dict.keys()) >>>  >>> in sorted_keys: ...     j in data_dict[i]: ...         print j ...          ...  ['abc', 'do not consider1', '100', 'atitle , subtitle', 'do not consider2', '30/12/2015'] ['abc', 'do not consider1', '100', 'title , subtitle', 'do not consider2', '30/12/2015'] ['abc', 'do not consider1', '100', 'title , subtitle', 'do not consider2', '31/12/2015'] ['abc', 'do not consider1', '101', 'title , subtitle', 'do not consider2', '30/12/2015'] ['abc', 'do not consider1', '98', 'title , subtitle', 'do not consider2', '25/12/2015 '] ['abc', 'do not consider1', '99', 'bic codes', 'do not consider2', '31/12/2015'] ['xyz', 'do not consider1', '100', 'atitle , subtitle', 'do not consider2', '30/12/2015'] ['xyz', 'do not consider1', '100', 'atitle , subtitle', 'do not consider2', '30/12/2015']

but there numbers in index2 i.e. column 2 , date in index5 i.e. column5, not sorted data.

can me fix this?

you can sort multiple keys using sorted function follows:-

sorted_list = sorted(data_list, key=lambda item: (item[0], int(item[2]), item[3])) print "\n".join([", ".join(i) in  sorted_list])

returns

abc, not consider1, 98, title , subtitle, not consider2, 25/12/2015  abc, not consider1, 99, bic codes, not consider2, 31/12/2015 abc, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 abc, not consider1, 100, title , subtitle, not consider2, 31/12/2015 abc, not consider1, 100, title , subtitle, not consider2, 30/12/2015 abc, not consider1, 101, title , subtitle, not consider2, 30/12/2015 xyz, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015 xyz, not consider1, 100, atitle , subtitle, not consider2, 30/12/2015

the trick let key lambda return tuple containing values sort on, , convert third column's values integers using int() function.


Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5);
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:
Read more

Python Pig Latin Translator -

-
so have been working on @ codecademy.com while trying learn python. don't know if blind , can't see why won't work or what. here's code won't let me use. function print variable if had typed in , print "empty" if didn't type anything. print "welcome english pig latin translator!" original = raw_input("what's name?") if len(original) > 0 print original else print "empty" if len(original) > 0 else the above lines contains syntax errors. try: if len(original) > 0: else: note added colons, ( : ).
Read more