LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 05-19-2014, 08:38 AM   #1
santosh0782
Member
 
Registered: Nov 2013
Posts: 132

Rep: Reputation: Disabled
How to sort file with date column?


Hi,

i have a file with data as:

cat test.txt

2014/05/9|1220|141|1221|1336|1|2|3921|65.85|
2014/05/8|1607|136|3405|2053|1330|5|8536|60.30|
2014/05/7|1468|158|2014|1748|1|4|5393|67.49|
2014/05/6|1314|135|1308|1609|1|3|4370|63.08|
2014/05/5|852|111|874|933|55|1|2826|65.00|
2014/05/4|619|60|770|718|1|1|2169|66.80|
2014/05/3|878|67|874|980|0|1|2800|64.96|
2014/05/2|1105|100|1092|1246|3|3|3549|64.72|
2014/05/1|1328|150|1337|1475|0|1|4291|65.60|
2014/05/18|825|94|866|953|0|0|2738|65.19|
2014/05/17|879|99|1017|1455|0|1|3451|57.80|
2014/05/16|2565|157|2060|2517|4|2|7305|65.46|
2014/05/15|3134|236|1754|2373|6|1|7504|68.28|
2014/05/14|1523|142|1082|1566|68|1|4382|62.68|
2014/05/13|1417|189|1171|1682|0|2|4461|62.25|
2014/05/12|1511|192|1335|1733|70|3|4844|62.71|
2014/05/11|762|82|736|781|367|2|2730|57.87|
2014/05/10|966|87|1013|1319|0|1|3386|61.01|
2014/04/29|1261|119|1316|1461|8|4|4169|64.66|
2014/04/28|964|120|1165|1265|1098|1|4613|48.75|
2014/04/27|852|103|997|882|1|2|2837|68.80|
2014/04/26|1060|117|1087|1190|0|2|3456|65.50|
2014/04/25|1263|105|1190|1369|1|2|3930|65.08|
2014/04/24|1432|135|1341|1504|2|3|4417|65.83|


want to sort this file per date? how could we do that?
 
Old 05-19-2014, 08:40 AM   #2
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,027

Rep: Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845
does
Code:
sort test.txt
not work ?

man sort
 
Old 05-19-2014, 08:40 AM   #3
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,602

Rep: Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241
Use sort, with a record separator of "|", then sort by the first field.
 
Old 05-20-2014, 12:18 AM   #4
santosh0782
Member
 
Registered: Nov 2013
Posts: 132

Original Poster
Rep: Reputation: Disabled
tried this:

$ sort -t '|' -k1 test.txt
2014/04/24|1432|135|1341|1504|2|3|4417|65.83|
2014/04/25|1263|105|1190|1369|1|2|3930|65.08|
2014/04/26|1060|117|1087|1190|0|2|3456|65.50|
2014/04/27|852|103|997|882|1|2|2837|68.80|
2014/04/28|964|120|1165|1265|1098|1|4613|48.75|
2014/04/29|1261|119|1316|1461|8|4|4169|64.66|
2014/05/10|966|87|1013|1319|0|1|3386|61.01|
2014/05/1|1328|150|1337|1475|0|1|4291|65.60|
2014/05/11|762|82|736|781|367|2|2730|57.87|
2014/05/12|1511|192|1335|1733|70|3|4844|62.71|
2014/05/13|1417|189|1171|1682|0|2|4461|62.25|
2014/05/14|1523|142|1082|1566|68|1|4382|62.68|
2014/05/15|3134|236|1754|2373|6|1|7504|68.28|
2014/05/16|2565|157|2060|2517|4|2|7305|65.46|
2014/05/17|879|99|1017|1455|0|1|3451|57.80|
2014/05/18|825|94|866|953|0|0|2738|65.19|
2014/05/2|1105|100|1092|1246|3|3|3549|64.72|
2014/05/3|878|67|874|980|0|1|2800|64.96|
2014/05/4|619|60|770|718|1|1|2169|66.80|
2014/05/5|852|111|874|933|55|1|2826|65.00|
2014/05/6|1314|135|1308|1609|1|3|4370|63.08|
2014/05/7|1468|158|2014|1748|1|4|5393|67.49|
2014/05/8|1607|136|3405|2053|1330|5|8536|60.30|
2014/05/9|1220|141|1221|1336|1|2|3921|65.85|


it is not getting sorted properly..
 
Old 05-20-2014, 02:07 AM   #5
evo2
LQ Guru
 
Registered: Jan 2009
Location: Japan
Distribution: Mostly Debian and Scientific Linux
Posts: 5,753

Rep: Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288
Hi,

it looks to me like you just want to reverse the order, no need to actually sort. If so:
Code:
tac test.txt
Alternatively you could use a "natural" sort algorithm. Eg
Code:
sort -V test.txt
Evo2.
 
Old 05-20-2014, 02:09 AM   #6
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian i686 (solaris)
Posts: 8,104

Rep: Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267
use / as field separator, use numeric sort and use 3 different keys (first year, second month ....)
Code:
sort -t/ -k1,1n -k2,2n -k3,3n file test.txt
mark the text if you do not want to find the solution yourself....
 
Old 05-20-2014, 08:28 AM   #7
jpollard
Senior Member
 
Registered: Dec 2012
Location: Washington DC area
Distribution: Fedora, CentOS, Slackware
Posts: 4,602

Rep: Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241Reputation: 1241
No. His dates are not consistent.

He uses leading 0 on the month, but doesn't use leading 0 on the day.

I didn't notice that either.

Try putting the following into a script and running it by redirecting the input from your data, and redirecting the output to a new file

Code:
awk -F'|' '
BEGIN {OFS="|";}
{ patsplit($1,a,/[0-9]*/);
  a[3] = sprintf("%2.2d",a[3]);
  $1 = sprintf("%s/%s/%s",a[1],a[2],a[3]); 
  print $0;
}' | sort -t '|' -k1
The only issue I have with using sort is that the third key has the rest of the record. Now with a numeric sort that works as the number stops at the first nondigit, but it depends on a side effect of number conversion for it to work.

Last edited by jpollard; 05-20-2014 at 08:33 AM.
 
1 members found this post helpful.
Old 05-20-2014, 09:56 AM   #8
syg00
LQ Veteran
 
Registered: Aug 2003
Location: Australia
Distribution: Lots ...
Posts: 14,834

Rep: Reputation: 1820Reputation: 1820Reputation: 1820Reputation: 1820Reputation: 1820Reputation: 1820Reputation: 1820Reputation: 1820Reputation: 1820Reputation: 1820Reputation: 1820
I came up with the same solution as @pan64, and was happy enough with it.
If I was to bother reformatting the data I'd probably use "date" to do it with a format string and pipe it to getline to replace $1. Hardly seems worth the effort.
 
Old 05-27-2014, 05:43 AM   #9
santosh0782
Member
 
Registered: Nov 2013
Posts: 132

Original Poster
Rep: Reputation: Disabled
Thanks a lot :-)

its working fine.

---------- Post added 05-27-14 at 03:14 PM ----------

Quote:
Originally Posted by jpollard View Post
No. His dates are not consistent.

He uses leading 0 on the month, but doesn't use leading 0 on the day.

I didn't notice that either.

Try putting the following into a script and running it by redirecting the input from your data, and redirecting the output to a new file

Code:
awk -F'|' '
BEGIN {OFS="|";}
{ patsplit($1,a,/[0-9]*/);
  a[3] = sprintf("%2.2d",a[3]);
  $1 = sprintf("%s/%s/%s",a[1],a[2],a[3]); 
  print $0;
}' | sort -t '|' -k1
The only issue I have with using sort is that the third key has the rest of the record. Now with a numeric sort that works as the number stops at the first nondigit, but it depends on a side effect of number conversion for it to work.
Thanks a lot, it worked :-)
 
Old 05-27-2014, 06:31 AM   #10
evo2
LQ Guru
 
Registered: Jan 2009
Location: Japan
Distribution: Mostly Debian and Scientific Linux
Posts: 5,753

Rep: Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288
Hi,

am I missing something? Are there problems with the two trivial solutions I posted?

Evo2.
 
Old 05-27-2014, 07:42 AM   #11
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian i686 (solaris)
Posts: 8,104

Rep: Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267
Quote:
Originally Posted by evo2 View Post
Hi,

am I missing something? Are there problems with the two trivial solutions I posted?

Evo2.
I like them. Both....
 
Old 05-29-2014, 12:37 AM   #12
santosh0782
Member
 
Registered: Nov 2013
Posts: 132

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by evo2 View Post
Hi,

am I missing something? Are there problems with the two trivial solutions I posted?

Evo2.
sorry, i forgot to reply on this post, actually tac test.txt is working fine its reversing the file, but

$ sort -V test.txt
sort: invalid option -- V
Try `sort --help' for more information.

is giving error
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to sort a file according to a column having no. of days as well as time stamp Jhinukk General 2 01-17-2012 10:36 PM
how to sort the 2nd column on the basis of first column without repeating the value ? zediok Linux - Newbie 15 12-20-2011 12:48 PM
[SOLVED] how to sort two mysql table on their date or time column golden_boy615 Programming 3 07-27-2011 04:49 AM
[Perl] Sort a file by column Kunsheng Programming 4 04-24-2009 10:09 AM


All times are GMT -5. The time now is 06:39 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration