0

I have a comma',' FS filename as csv with n number of columns. I need to extract the unique value from colm.#1 with only corresponding values in colm.#10. So basically the column 10 is the date which is always unique for colm.#1 despite the other columns.

echo filename 


colm.#1 colm.#2 colm.#3 colm.#4 colm.#5 colm.#6 colm.#7 colm.#8 colm.#9 colm.#10    colm.#11
    a   231 412 30.84873962 3   1   1   2013    5/28/2013   6/6/2006    299
    c   12  41  66.80690765 3   1   1   2014    5/25/2014   4/4/2004    351
    d   35  6   25.91622925 3   1   2   2013    6/27/2013   3/3/2003    303
    d   352 55  33.91288757 3   1   2   2014    6/26/2014   3/3/2003    355
    a   86  3   30.58783722 3   1   3   2013    7/24/2013   6/6/2006    307
    c   15  3242    26.6435585  3   1   3   2014    7/24/2014   4/4/2004    359
    e   67  1   22.95526123 3   1   4   2013    8/21/2013   5/5/2005    311
    a   464 64  4.804824352 3   1   4   2014    8/20/2014   6/6/2006    363
    b   66  42  29.42435265 3   1   5   2014    9/18/2014   7/7/2007    367
    m   24  2   66.10663319 3   1   6   2014    10/13/2014  9/9/2009    371

I tried the following command but it is only for colm#1 and I do not know how to get the corresponding value of the colm#10.

 cut -d',' -f1 filename |uniq

The expected output would be:

a   6/6/2006
b   7/7/2007
c   4/4/2004
d   3/3/2003
e   5/5/2005
m   9/9/2009
  • That would be column 9, then? – jasonwryan Dec 12 '16 at 21:12
  • I am afraid not! It is column 10. the confusion comes from the shift of the data toward right as they are a bit compacted than the title. – Daniel Dec 12 '16 at 21:16
  • 1
    two caveats (1) where are the "commas" in your example input? and (2) this is nasty and presumes your first column is no wider than 5 characters: sort -k1 input_filename | uniq -w 5 | tr -s ' ' '\t' | cut -f 2,11 – Theophrastus Dec 12 '16 at 21:27
  • 2
    GNU datamash is nice for this kind of thing e.g. datamash --header-in -st, groupby 1 unique 10 < filename – steeldriver Dec 12 '16 at 22:08
  • 1
    OTOH if the Column 10 dates are indeed unique for a given Column 1, can't you just print the first occurrence of each? e.g. awk -F, '!a[$10]++ {print $1,$10}' filename – steeldriver Dec 12 '16 at 22:19
0
awk '{if ( ! ( $1 in Peers)) { Peers[$1]=$1 " " $10; print Peers[$1]} }' YourFile

this take the order as it come, if you need order the result, a sort in shel (input or output) or (with GNU awk)

awk '{if ( ! ( $1 in Peers)) Peers[$1]=$1 " " $10 } END{asort(Peers);for (Peer in Peers) print Peers[ Peer]}' YourFile

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.