how to sort CSV columns files with Python

Question

I really need your help here: I am attaching an Excel file - and trying to sort its second column ("Time") in the right way (taking under consideration seconds). I am embarrassed to confess how much time I've been wasting on this..

PAPER   TIME    ACTION  PRICE   AMOUNT

1026    01/12/2013 9:03 BID 124 84,888

1026    18/04/2013 9:01 BID 120.5   14,888

1026    19/01/2013 9:02 BID 121 24,888

1026    21/04/2013 9:05 BID 122 44,888

1026    20/04/2013 9:04 BID 121.5   34,888

1026    15/10/2013 9:06 BID 123.5   74,888

1026    17/04/2013 9:00 BID 120 4,888

1026    22/04/2013 9:07 BID 122.5   54,888

1026    27/04/2013 9:08 BID 123 64,888

This data stored in a CSV file called yaniv123.csv and the columns are between A to E (only 10 rows). How do I import this file and sort the B column (TIME) in the right way.. PLEASE HELP ME OUT :-) many many thanks !

Can you post the code you have tried so far, and tell us more where it goes wrong ? -- as well as post the input as actual CSV, your example currently only has two columns, not 5. Even when we consider your input example as "space serperated" then the second column (or 'B') as you call it is not time, it is date. The third column is time. -- please update your question so we can answer meaningfull. — Edwin van Mierlo
– Edwin van Mierlo, Commented Oct 26, 2016 at 11:12
you right Adwin - my Bad! here is the corrected table: PAPER, TIME, ACTION, PRICE, AMOUNT, 1026, 01/12/2013 9:03, BID, 124, 84888 1026, 18/04/2013 9:01, BID, 120.5, 14888, 1026, 19/04/2013 9:02, BID, 121, 24888, 1026, 21/04/2013 9:05, BID, 122, 44888, 1026, 20/04/2013 9:04, BID, 121.5, 34888, 1026, 15/10/2013 9:06, BID, 123.5, 74888, 1026, 17/04/2013 9:00, BID, 120, 4888, 1026, 22/04/2013 9:07, BID, 122.5, 54888, 1026, 27/04/2013 9:08, BID, 123, 64888, — yaniv123
– yaniv123, Commented Oct 26, 2016 at 12:44

MikeAlbert · Accepted Answer · 2016-10-26 11:37:29Z

2

As @Edwin van Mierlo mentioned, the headers need to adjust. You can use comma separated but you need to make sure amount comma is removed. If not, then use semicolon instead:

PAPER;TIME;ACTION;PRICE;AMOUNT

1026;01/12/2013 9:03;BID;124;84,888

1026;18/04/2013 9:01;BID;120.5;14,888

1026;19/01/2013 9:02;BID;121;24,888

1026;21/04/2013 9:05;BID;122;44,888

1026;20/04/2013 9:04;BID;121.5;34,888

1026;15/10/2013 9:06;BID;123.5;74,888

1026;17/04/2013 9:00;BID;120;4,888

1026;22/04/2013 9:07;BID;122.5;54,888

1026;27/04/2013 9:08;BID;123;64,888

However if everything is adjusted correctly, then you can use python panda library.

import pandas as pd    
df = pd.read_csv('sample.csv', parse_dates=True, delimiter=";")
df['TIME'] = pd.to_datetime(df.TIME)
print(df.head())
print(df.sort('TIME'))

The output should be: PAPER TIME ACTION PRICE AMOUNT

1026 01/12/2013 9:03 BID 124.0 84,888

1026 15/10/2013 9:06 BID 123.5 74,888

1026 17/04/2013 9:00 BID 120.0 4,888

1026 18/04/2013 9:01 BID 120.5 14,888

1026 19/01/2013 9:02 BID 121.0 24,888

1026 20/04/2013 9:04 BID 121.5 34,888

1026 21/04/2013 9:05 BID 122.0 44,888

1026 22/04/2013 9:07 BID 122.5 54,888

1026 27/04/2013 9:08 BID 123.0 64,888

edited Oct 26, 2016 at 11:37

answered Oct 26, 2016 at 11:20

MikeAlbert

1743 silver badges11 bronze badges

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

2 Comments

yaniv123 Over a year ago

thanks a lot for finding the time to help me. please note that although you managed to sort the second column (starts at 01/12/2013 9:03) it DID NOT sorted correctly. we know that december 1st comes after October 15th. any idea how to sort columns which includes both Date and time in the right way ??

MikeAlbert Over a year ago

Sorry about that. Just update the time conversion line to df['TIME'] = pd.to_datetime(df.TIME, dayfirst = True)

Collectives™ on Stack Overflow

how to sort CSV columns files with Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related