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 Feb 13 '20 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 Feb 13 '20 at 17:48
  • Yes I guess this will serve my purpose. Thank you. I thought I have use 'for loop' for this. – Arsala Feb 13 '20 at 18:19
  • 1
    Please add the input and expected output as text. – Bodo Feb 13 '20 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 Feb 14 '20 at 23:24
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 Feb 13 '20 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 Feb 13 '20 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 Feb 13 '20 at 18:20
  • Thank you. I will try this code. I am not exactly getting what this code will do. – Arsala Feb 13 '20 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 Feb 13 '20 at 18:31
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

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.