1

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.

Content of file 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
5
  • That would be column 9, then?
    – jasonwryan
    Dec 12, 2016 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, 2016 at 21:16
  • 2
    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 Dec 12, 2016 at 21:27
  • 2
    GNU datamash is nice for this kind of thing e.g. datamash --header-in -st, groupby 1 unique 10 < filename Dec 12, 2016 at 22:08
  • 2
    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 Dec 12, 2016 at 22:19

4 Answers 4

1
awk -F, 'NR > 1 && ! seen[$1 FS $10]++ {print $1, $10}' filename | sort -k1,1

output

a 6/6/2006
b 7/7/2007
c 4/4/2004
d 3/3/2003
e 5/5/2005
m 9/9/2009
1
  • 1
    Can you explain how the various parts of your recommended command work? Break it down into segments like the if clause, the print clause, why you're piping to sort, etc. Answers will always be better with explanations of how they work, so that people can understand -- and from there, modify -- the command for a particular purpose.
    – Doktor J
    Sep 8, 2021 at 19:46
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
0

If you'd like to print multiple fields with cut:

cut -d (SELECT DELIMITER) -f 1,10

-f 1,10 selects only the fields specified.

If you're aiming for a specific line, you could do: grep -w a filename | cut -d (SELECT DELIMITER) -f 1,10

In the above case, you're grepping for an exact match for the letter "a". Meaning, "apple" would not much, but an "a", would.

1
  • 1
    I'm afraid this is not what the OP wants. The relevant point is that the OP wants to disregard all lines with duplicate values in column 1, and among the remaining lines, print only columns 1 and 10.
    – AdminBee
    Oct 13, 2020 at 8:28
0

Using Raku (formerly known as Perl_6)

In Raku, the general approach to 'unique-ify' rows based upon a single 'column' is as follows:

raku -e '.put for lines.unique: :as(*.words[9]);'

The code above prints the entire row corresponding to unique values found in column 1, a.k.a. :as(*.words[0]). So if you only want columns 1 and 10, you simply select those (zero-indexed) words:

raku -e '.words[0,9].put for lines.unique: :as(*.words[9]);'

Calling words twice in the same one-liner might be inefficient, so we could abstract out that function call using the given topicalizer:

raku -e 'given lines.map(*.words) { .[0,9].put for .unique: :as(*.[9]) };' 

OR, just using the for iterator alone:

raku -e ' .[0,9].put for lines.map(*.words).unique( :as(*.[9]) ) ;' 

That pretty-much handles simple-tsv files, except for a header line. If you need to handle the header, output it first. Since lines is stateful, it will resume reading from the first data row after the header:

~$ raku -e 'lines.head(1).words.[0,9].put; \
          .[0,9].put for lines.map(*.words).unique( :as(*.[9]) ) ;'  file

Sample Input:

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

Sample Output (final code above):

colm.#1 colm.#10
a 6/6/2006
c 4/4/2004
d 3/3/2003
e 5/5/2005
b 7/7/2007
m 9/9/2009

Finally, you can add .sort to the very end of the final code above, to sort the rows into a,b,c,d,e,m alphabetic order, but (importantly) it's not required.

https://raku.org

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .