How to extract unique values from column #x with its corresponding values of column #y?

Question

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

Answer 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

Answer 2

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

Answer 3

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.

Answer 4

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