I need to count the number of unique values based on two columns in a spreadsheet.

Suppose the file looks like this, ordered by name, surname, company:

joe allen ibm
joe smith ibm
joe allen google
joe smith google
rachel allen google

And I need to count the number of unique first names for each company while ignoring the surname:

joe ibm 2
joe google 2
rachel google 1

I have this code:

sort file.tsv | uniq -ci | awk '{print $2,$1}'

If I simply delete the surname column, that code will work. But if I don't want to delete that column, just have awk ignore it, and save the output to a new file?

The data is separated by tabs \t

  • But your output doesn't contain the surname, so why bother keeping it? – muru Mar 3 '15 at 15:50

A GNU awk solution using two-dimensional arrays:

gawk -F $'\t' '{a[$1][$3]++} END {for (i in a) for (j in a[i]) print i, j, a[i][j]}' foo.txt
  • a[$1][$3]++ for each combination of first name and surname, increment the count
  • Then loop through the first names and the company names associated with each first name.

Another way that will work other awks using the older form of multidimensional arrays:

awk -F $'\t' '{a[$1, $3]++} END{for (i in a) {split (i, sep, SUBSEP); print sep[1], sep[2], a[i]}}' foo.txt
  • Since the old method actually uses a concatenation of the indices separated by SUBSEP, we have to split on SUBSEP to get back the original indices.
| improve this answer | |
  • Ah thanks so much for your help! The data is actually separated by \t, how can I split on \t in the second code you wrote? – Sveisa Mar 3 '15 at 16:27
  • You can specify the field separator: -F $'\t' – muru Mar 3 '15 at 16:28
  • Thanks, but how would the one-liner look like if I implement -F $'\t'? I tried to replace SUBSEP with this, but it didn't work. My apologies for all the questions, I'm new to awk! – Sveisa Mar 3 '15 at 16:39
  • See the update. I assume you meant a tab character not literally a backslash followed by t. – muru Mar 3 '15 at 16:40

Here is a Pythonic solution using the Counter class of collections module which will count the number of occurrences of each element in an iterable:

#!/usr/bin/env python2
import collections
with open('file.txt') as f:
    names = []
    for line in f:
        names.append(line.strip().split()[0] + ' ' + line.strip().split()[2])
    result_dict = collections.Counter(names)
    for person in result_dict:
        print person + ' ' + str(result_dict[person])
| improve this answer | |

You can make use of cut to select the columns you want to operate on first. So given that your columns are separated by a space, and are FNAME SNAME COMPANY where we are only interested in column 1 and 3 we can use:

cut -d' ' -f1,3 file.tsv | sort | uniq -ci

This tells cut to separate using a single space ' ' as a delimeter and to pass columns 1 and 3 into sort.

It will produce some output similar to:

 cut -d' ' -f1,3 file.tsv | sort | uniq -ci
      2 joe google
      2 joe ibm
      1 rachel google
| improve this answer | |

The following perl oneliner will extract the data for you:

perl -e '/(.*)\t.*\t(.*)/ and $a{"$1 $2"}++ for (<>); print "$_ $a{$_}\n" foreach (keys%a);' file.tsv


joe ibm 2
joe google 2
rachel google 1
| improve this answer | |
  • Thanks Sylvian! This uses regex, right? What would the oneliner look like if the data is separated by \t, not whitespace? – Sveisa Mar 3 '15 at 16:41
  • the regexp is using \t, I've updated the answer a few minutes ago – Sylvain Pineau Mar 3 '15 at 16:42
  • Awesome, thanks Sylvian! It works, I will come back to upvote your answer when I have a good enough rep. – Sveisa Mar 3 '15 at 16:56

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.