0

I have a tsv file. For a specific value in a column 5, I want to extract all the rows, and then cut three columns and then count the unique lines. For example for string 'abc' in column 5, I want

awk '$5 == "abc"' file.tsv | cut -f 1-3 | sort -u | wc -l

but I want to do this for all the unique strings in column 5 and not just 'abc'. There should be something like "for i in $5" but I am not exactly getting this 'for loop' thing. I can't give individual commands because there are so many strings.

enter image description here

5
  • 1
    Please edit your question and show the (example) input and the expected output corresponding to the input.
    – Bodo
    Commented Feb 13, 2020 at 17:37
  • Does this do what you need? cut -f 1-3,5 file.tsv | sort -u | cut -f 4 | sort | uniq -c
    – Bodo
    Commented Feb 13, 2020 at 17:48
  • Yes I guess this will serve my purpose. Thank you. I thought I have use 'for loop' for this.
    – Arsala
    Commented Feb 13, 2020 at 18:19
  • 1
    Please add the input and expected output as text.
    – Bodo
    Commented Feb 13, 2020 at 18:22
  • Don't post images, post text so we can copy/paste it to use as the data to test a potential solution against, Had you do that you'd have had more answers posted to choose from.
    – Ed Morton
    Commented Feb 14, 2020 at 23:24

2 Answers 2

2

This will print the expected result

cut -f 1-3,5 file.tsv | sort -u | cut -f 4 | sort | uniq -c | awk '{ print $2, $1; }'

Explanation:

cut -f 1-3,5 file.tsv extract relevant columns 1, 2, 3, 5
sort -u get unique combinations
cut -f 4 extract only original column 5 value which is now in column 4
sort | uniq -c sort and count the unique values
awk '{ print $2 "\t" $1; }' exchange columns and format output

0

It looks like you want something like

awk '{test[$5" "$1" "$2" "$3]++}END{for (t in test) print t}' file1 | cut -d' ' -f1 | sort | uniq -c

Walk through

test[$5" "$1" "$2" "$3]++ #populates an array with unique combinations of these fields
for (t in test) print t   #print each unique array index (field combination) once to STDOUT
cut -d' ' -f1             #extract what was the original 5th field
sort                      #yes, yes OK @Bodo
uniq -c                   #count the number of times it appears

Output

2 abc
1 def

Edit

While admitting defeat at the hands of @Bodo the determination to find a viable awk solution remains, so I offer this ugly beast.....

awk '!test[$5" "$1" "$2" "$3]{out[$5]++;test[$5" "$1" "$2" "$3]++}
  END
  {for (o in out) print o, out[o]}' file1
6
  • I'm not sure, but I think the OP wants to count the number of unique/different combinations of fields 1, 2, 3 for every value of field 5. This code code counts how often every individual combination occurs.
    – Bodo
    Commented Feb 13, 2020 at 18:08
  • True, but field 5 is part of the unique pattern of fields 5, 1, 2 & 3. Result is the same but will edit if OP gives an MWE.
    – bu5hman
    Commented Feb 13, 2020 at 18:10
  • My comment was not clear. The code in the question prints exactly one number which is the count of unique combinations of columns 1, 2, 3 for the case $5 == "abc". Your code would result in as many numbers as there are unique combinations. If there are 3 unique combinations your code would print 3 numbers while the OP's code would print the single number 3.
    – Bodo
    Commented Feb 13, 2020 at 18:20
  • Thank you. I will try this code. I am not exactly getting what this code will do.
    – Arsala
    Commented Feb 13, 2020 at 18:22
  • You should add sort before uniq -c to make it work with input that is not already sorted as the example from the question.
    – Bodo
    Commented Feb 13, 2020 at 18:31

You must log in to answer this question.

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