1

I have two csv file of the following format:

column1,column2,user,column4,column5,column...column14  

I would like to sort the files based on user as first key and then based on column14 in descending order which is a number representing a timestamp.

The result would be like:

file1:

A,AA,jim,XX,YY,....,1485771395    
A,AA,jim,XX,YY,....,1485771395    

etc

I would like then from these files to keep only the first row of each cluster i.e. only

A,AA,jim,XX,YY,....,1485771395  

How can I do that?

Update:
Example input:

"1/30/2017 11:14:55 AM",Valid customer,jim.smith,NY,1485771295      
"1/26/2017 5:06:11 AM",New customer,john.doe,CA,1485403571  
"1/26/2017 8:02:01 PM",Valid customer,jim.smith,NY,1485457321  
"1/30/2017 4:14:30 AM",New customer,tim.jones,CO,1485746070  
"1/30/2017 11:09:36 AM",New customer,tim.jones,CO,1485770976  
"1/30/2017 11:14:50 AM",Valid customer,jim.smith,NY,1485771290      
"1/22/2017 11:51:51 AM",New customer,tim.jones,CO,1485082311  

The last column is the epoch of the first column to be able to sort the rows using a number instead of a string.
So the expected output would be:
a) Sorting on name and on timestamp in reverse order so we get:

"1/30/2017 11:14:55 AM",Valid customer,jim.smith,NY,1485771295    
"1/30/2017 11:14:50 AM",Valid customer,jim.smith,NY,1485771290        
"1/26/2017 8:02:01 PM",Valid customer,jim.smith,NY,1485457321  
"1/26/2017 5:06:11 AM",New customer,john.doe,CA,1485403571  
"1/30/2017 11:09:36 AM",New customer,tim.jones,CO,1485770976  
"1/30/2017 4:14:30 AM",New customer,tim.jones,CO,1485746070  
"1/22/2017 11:51:51 AM",New customer,tim.jones,CO,1485082311  

So we have a cluster of rows for jim.smith another for tim.jones sorted by the last column in reverse order (i.e. the latest date of the first row is first in the cluster) and a row for john.doe that has only 1 record.

Then I would like to keep only the first row of each cluster. I.e.

"1/30/2017 11:14:55 AM",Valid customer,jim.smith,NY,1485771295    
"1/26/2017 5:06:11 AM",New customer,john.doe,CA,1485403571    
"1/30/2017 11:09:36 AM",New customer,tim.jones,CO,1485770976  
  • @don_crissti:I don't want the first line of the file. I am sorting on 2 columns and I will get groups in the cases that the first column has more than one occurrence. I want the top line of each group. – Jim Feb 2 '17 at 22:46
  • sort -u or uniq? – DopeGhoti Feb 2 '17 at 22:54
  • @DopeGhoti:How would that help? I am interested in the line with the max timestamp per user – Jim Feb 2 '17 at 22:56
  • 1
    So really you don't need to sort on the 14th field - except as a step to obtaining the max? Can you give us a dataset that is at least large enough to test (i.e. actually has at least one user with at least two different values of $14) – steeldriver Feb 2 '17 at 23:10
  • ... something like sort -t, -k3,3 -k14,14n file.csv | awk -F, '!seen[$3]++' maybe? – steeldriver Feb 2 '17 at 23:16
2

Given file input as

"1/30/2017 11:14:55 AM",Valid customer,jim.smith,NY,1485771295      
"1/26/2017 5:06:11 AM",New customer,john.doe,CA,1485403571  
"1/26/2017 8:02:01 PM",Valid customer,jim.smith,NY,1485457321  
"1/30/2017 4:14:30 AM",New customer,tim.jones,CO,1485746070  
"1/30/2017 11:09:36 AM",New customer,tim.jones,CO,1485770976  
"1/30/2017 11:14:50 AM",Valid customer,jim.smith,NY,1485771290      
"1/22/2017 11:51:51 AM",New customer,tim.jones,CO,1485082311  

then

sort -t, -k3,3 -k5,5rn input | awk -F, '!seen[$3]++'
"1/30/2017 11:14:55 AM",Valid customer,jim.smith,NY,1485771295      
"1/26/2017 5:06:11 AM",New customer,john.doe,CA,1485403571  
"1/30/2017 11:09:36 AM",New customer,tim.jones,CO,1485770976  
  • +1, but I have a preference for sort -t, -k3,3 -k5,5rn input | sort -t, -k3,3 -u – iruvar Feb 3 '17 at 18:15
  • How does the awk part work? It evaluates to true only once per user right? – Jim Feb 3 '17 at 19:34
  • @iruvar:How would that work? – Jim Feb 3 '17 at 19:34
1

With gnu datamash:

datamash -t, -s -f -g 3 max 5 <infile

but note that you need to remove all trailing blanks from each line in your input before running it.
With awk only, using two arrays:

awk -F, '!z[$3]{x[$3]=$0;z[$3]=$5;next}$5>z[$3]{x[$3]=$0}
END{for (i in z){print x[i]}}' infile
  • I am on OSX so I think this is not applicable right? – Jim Feb 3 '17 at 19:34
  • @Jim - afaik awk is available on osx , isn't it ? – don_crissti Feb 3 '17 at 20:09
  • I was referring to datamash – Jim Feb 3 '17 at 20:23
  • @Jim - it's not available on a default osx setup but you can install it via homebrew so whether it's applicable in your particular case or not - I don't know. – don_crissti Feb 3 '17 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.