Sort CSV File Python/Linux Command

Question

I need to sort the CSV File by the Temp5 column which contain Following format.In my specific case,Temp5 column contain failed value.
In other words, it does not contain any value and present only Failed.

So, I need to perform sort operation on value in Temp5 and ignore Failed value.

I can write new csv File or modify exist one. I have looked into csv in python and sort command in linunx But I could not Find any solution. So In new/Existing CSV File,I have temp5 sorted value than after Failed value(i.e without loosing any row and Failed value any be any order)

Effort: I have tried into python code also which suggest me to make dictionary and store column as key(which you want to sort) and value make complete line than sort the keys and retrives data based on the keys.But I faced the problem,it did not included the Failed value.Please Find the Function Which I wrote into python

csv_s_mt0 = csv.reader(open("data.csv","rb"))
    s_mt0_map = {}
    s_mt1_map = {}
    line_escape = 0
    for line in csv_s_mt0:
        if(line_escape > 3):
            print line
            print line[4]
            s_mt0_map[line[4]] = line
        else:
            line_escape = line_escape + 1
    s_mt0_map_key = s_mt0_map.keys()
    s_mt0_map_key.sort()
    for key in s_mt0_map_key:
        print s_mt0_map_key[key]

    print len(s_mt0_map_key)


$Header Information
$Tool info=3
.TITLE '*****************************************************'
Temp1,Temp2,Temp3,Temp4,Temp5,Temp6,Temp6,Temp7,Temp8,Temp9
0.,   failed,   failed,-2.700e-10, 9.803e-11,-2.725e-11, 2.725e-11,-1.645e-06, -40.0000,1
1.000e-12,   failed,   failed,-2.689e-10, 9.805e-11,-2.731e-11, 2.731e-11, 6.571e-08, -40.0000,1
2.000e-12,   failed,   failed,-2.679e-10, 9.806e-11,-2.731e-11, 2.731e-11, 6.835e-08, -40.0000,1
3.000e-12,   failed,   failed,-2.669e-10, 9.805e-11,-2.729e-11, 2.729e-11, 1.376e-07, -40.0000,1
4.000e-12,   failed,   failed,-2.660e-10, 9.803e-11,-2.731e-11, 2.731e-11, 3.583e-08, -40.0000,1
5.000e-12,   failed,   failed,-2.649e-10, 9.807e-11,-2.725e-11, 2.725e-11,-1.646e-06, -40.0000,1
6.000e-12,   failed,   failed,-2.640e-10, 9.803e-11,-2.731e-11, 2.731e-11, 3.579e-08, -40.0000,1
7.000e-12,   failed,   failed,-2.630e-10, 9.801e-11,-2.728e-11, 2.728e-11, 1.828e-07, -40.0000,1
8.000e-12,   failed,   failed,-2.620e-10, 9.805e-11,-2.729e-11, 2.729e-11, 1.353e-07, -40.0000,1
4.940e-10,   failed,   failed, 2.241e-10,   failed,   failed,   failed,   0.8100, -40.0000,1
4.950e-10,   failed,   failed, 2.251e-10,   failed,   failed,   failed,   0.8100, -40.0000,1
4.960e-10,   failed,   failed, 2.261e-10,   failed,   failed,   failed,   0.8100, -40.0000,1
4.970e-10,   failed,   failed, 2.271e-10,   failed,   failed,   failed,   0.8100, -40.0000,1
4.980e-10,   failed,   failed, 2.280e-10,   failed,   failed,   failed,   0.8100, -40.0000,1
4.990e-10,   failed,   failed, 2.291e-10,   failed,   failed,   failed,   0.8100, -40.0000,1
5.000e-10,   failed,   failed, 2.301e-10,   failed,   failed,   failed,   0.8100, -40.0000,1

What have you tried? You'll get better responses by posting what you've tried and asking for assistance with a specific problem, rather than posting a specification and saying "do my work for me". — Jared Ng
– Jared Ng, Commented Apr 24, 2013 at 6:24
Which column of failed do you want to ignore? Just temp5 I presume — jamylak
– jamylak, Commented Apr 24, 2013 at 6:27
Yes But during the sorting.After Sorting the data,write the Failed value also — user765443
– user765443, Commented Apr 24, 2013 at 6:49

jamylak · Accepted Answer · 2013-04-24 08:06:34Z

1

The key function is used for sorting, which returns float('inf') for all failed so they all get put at the bottom of the list. Similarly you could use float('-inf') if you want them all at the top.

>>> import csv
>>> import sys # to print to sys.stdout for this example
>>> from itertools import islice
>>> def key(n):
        return float(n) if n != 'failed' else float('inf')

>>> with open('data.csv') as f:
        info = list(islice(f, 0, 3)) # first 3 lines
        r = csv.DictReader(f)
        w = csv.DictWriter(sys.stdout, r.fieldnames)
        rows = sorted(r, key=lambda row: key(row['Temp5']))    
        sys.stdout.writelines(info)
        w.writeheader()
        w.writerows(rows)


$HeaderInformation
$Toolinfo=3
.TITLE'*****************************************************'
Temp1,Temp2,Temp3,Temp4,Temp5,Temp6,Temp6,Temp7,Temp8,Temp9    
7.000e-12,failed,failed,-2.630e-10,9.801e-11,2.728e-11,2.728e-11,1.828e-07,-40.0000,1    
0.,failed,failed,-2.700e-10,9.803e-11,2.725e-11,2.725e-11,-1.645e-06,-40.0000,1    
4.000e-12,failed,failed,-2.660e-10,9.803e-11,2.731e-11,2.731e-11,3.583e-08,-40.0000,1    
6.000e-12,failed,failed,-2.640e-10,9.803e-11,2.731e-11,2.731e-11,3.579e-08,-40.0000,1    
1.000e-12,failed,failed,-2.689e-10,9.805e-11,2.731e-11,2.731e-11,6.571e-08,-40.0000,1    
3.000e-12,failed,failed,-2.669e-10,9.805e-11,2.729e-11,2.729e-11,1.376e-07,-40.0000,1    
8.000e-12,failed,failed,-2.620e-10,9.805e-11,2.729e-11,2.729e-11,1.353e-07,-40.0000,1    
2.000e-12,failed,failed,-2.679e-10,9.806e-11,2.731e-11,2.731e-11,6.835e-08,-40.0000,1    
5.000e-12,failed,failed,-2.649e-10,9.807e-11,2.725e-11,2.725e-11,-1.646e-06,-40.0000,1    
4.940e-10,failed,failed,2.241e-10,failed,failed,failed,0.8100,-40.0000,1    
4.950e-10,failed,failed,2.251e-10,failed,failed,failed,0.8100,-40.0000,1    
4.960e-10,failed,failed,2.261e-10,failed,failed,failed,0.8100,-40.0000,1    
4.970e-10,failed,failed,2.271e-10,failed,failed,failed,0.8100,-40.0000,1    
4.980e-10,failed,failed,2.280e-10,failed,failed,failed,0.8100,-40.0000,1    
4.990e-10,failed,failed,2.291e-10,failed,failed,failed,0.8100,-40.0000,1    
5.000e-10,failed,failed,2.301e-10,failed,failed,failed,0.8100,-40.0000,1

edited Apr 24, 2013 at 8:06

answered Apr 24, 2013 at 6:34

jamylak

134k30 gold badges238 silver badges240 bronze badges

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

4 Comments

user765443 Over a year ago

Thanks For reply.After sorting Data,I also need Failed value . The reason is,In that row,Temp5 contain Failed but in other column it contain value (same row)

user765443 Over a year ago

I run this program and endup with following error. Traceback (most recent call last): File "temp.py", line 10, in <module> rows = sorted(filtered_r, key=lambda row: float(row['Temp5'])) File "temp.py", line 10, in <lambda> rows = sorted(filtered_r, key=lambda row: float(row['Temp5'])) ValueError: could not convert string to float: failed

jamylak Over a year ago

@AbhishekGoswami Please explain what you want to do with failed

jamylak Over a year ago

@AbhishekGoswami I've changed it to put all failed at the bottom

pradyunsg · Accepted Answer · 2013-04-24 06:42:42Z

Try this:

>>> c = list(csv.reader(s)) # here s is the file with the headers skipped
>>> final = []
>>> for row in c:
...    row = map(lambda x: x.strip(), row)
...    Temp5 = row[4]
...    if Temp5 != 'failed':
...        final.append(row)
>>> myprint.tabular(final) # just a pretty-printing function...
+ --------- + ------ + ------ + ---------- + --------- + ---------- + --------- + ---------- + -------- + ----- +
|   Temp1   | Temp2  | Temp3  |   Temp4    |   Temp5   |   Temp6    |   Temp6   |   Temp7    |  Temp8   | Temp9 |
+ --------- + ------ + ------ + ---------- + --------- + ---------- + --------- + ---------- + -------- + ----- +
|     0.    | failed | failed | -2.700e-10 | 9.803e-11 | -2.725e-11 | 2.725e-11 | -1.645e-06 | -40.0000 |   1   |
| 1.000e-12 | failed | failed | -2.689e-10 | 9.805e-11 | -2.731e-11 | 2.731e-11 | 6.571e-08  | -40.0000 |   1   |
| 2.000e-12 | failed | failed | -2.679e-10 | 9.806e-11 | -2.731e-11 | 2.731e-11 | 6.835e-08  | -40.0000 |   1   |
| 3.000e-12 | failed | failed | -2.669e-10 | 9.805e-11 | -2.729e-11 | 2.729e-11 | 1.376e-07  | -40.0000 |   1   |
| 4.000e-12 | failed | failed | -2.660e-10 | 9.803e-11 | -2.731e-11 | 2.731e-11 | 3.583e-08  | -40.0000 |   1   |
| 5.000e-12 | failed | failed | -2.649e-10 | 9.807e-11 | -2.725e-11 | 2.725e-11 | -1.646e-06 | -40.0000 |   1   |
| 6.000e-12 | failed | failed | -2.640e-10 | 9.803e-11 | -2.731e-11 | 2.731e-11 | 3.579e-08  | -40.0000 |   1   |
| 7.000e-12 | failed | failed | -2.630e-10 | 9.801e-11 | -2.728e-11 | 2.728e-11 | 1.828e-07  | -40.0000 |   1   |
| 8.000e-12 | failed | failed | -2.620e-10 | 9.805e-11 | -2.729e-11 | 2.729e-11 | 1.353e-07  | -40.0000 |   1   |
+ --------- + ------ + ------ + ---------- + --------- + ---------- + --------- + ---------- + -------- + ----- +

Collectives™ on Stack Overflow

Sort CSV File Python/Linux Command

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related