1

I have a file with multiple columns and have identified lines where values in column 3 have been duplicated.

Example Input:

A B C
1 2 APPLE
3 4 PEAR
9 3 LEMON
8 3 ORANGE
8 2 APPLE
3 4 APPLE
9 3 LEMON
8 3 PEAR

I can count how often the word in Column 3 has been repeated using:

awk '{print $3}' [input filename] | sort | uniq -c > [output filename]

Output:

3 APPLE
2 PEAR
2 LEMON
1 ORANGE

What I would like to do is keep lines that are repeated 3 times:

Desired Output:

APPLE

or

1 2 APPLE
8 2 APPLE
3 4 APPLE

I don't mind if all of the columns are printed from the original input file or just the 3rd column value.

Using sort -u prints out any line that has appeared at least once which is not what I'm after.

marked as duplicate by αғsнιη, don_crissti, Stephen Rauch, Anthon, Satō Katsura Sep 27 '17 at 15:23

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    At least three times or exactly three times? – Philippos Sep 27 '17 at 10:49
  • 1
    If exactly 3 times, you can just pipe into grep '^3' before you send to file. – wvxvw Sep 27 '17 at 10:58
3

Another approach is to go over the file twice, first to build a reference and the second to filter as needed

$ awk 'NR==FNR{a[$3]++; next} a[$3]==3' ip.txt ip.txt 
1 2 APPLE
8 2 APPLE
3 4 APPLE

$ awk 'NR==FNR{a[$3]++; next} a[$3]==2' ip.txt ip.txt 
3 4 PEAR
9 3 LEMON
9 3 LEMON
8 3 PEAR

$ awk 'NR==FNR{a[$3]++; next} a[$3]<2' ip.txt ip.txt 
A B C
8 3 ORANGE
1

awk solution:

-- to output only entries which occur at least 3 times:

awk '++a[$3]==3{ print $3 }' file
  • ++a[$3] - consecutively incremented number of unique values of the 3rd field

-- to output only entries which occur exactly 3 times:

awk '{++a[$3]}END{ for(i in a) if(a[i]==3) print i }' file

The output:

APPLE
  • @Philippos, ok, see my update – RomanPerekhrest Sep 27 '17 at 12:02
  • The first version can be done more simple by slightly changing your first attempt. I hope you don't mind that I edited your answer in this way. – Philippos Sep 27 '17 at 12:27
  • @Philippos, ok, and your 1st comment is obsolete – RomanPerekhrest Sep 27 '17 at 12:31
  • Okay, removed it. – Philippos Sep 27 '17 at 13:03
0

Pass the output of your pipeline through awk '$1 == 3 { print $2 }' to get just APPLE.

Alternatively, count with awk from the start:

awk '{ c[$3]++; r[$3] = r[$3] ? r[$3] ORS $0 : $0 } END { for (i in c) { if (c[i] == 3) print r[i] } }' file
1 2 APPLE
8 2 APPLE
3 4 APPLE

The awk script will count, in c, how many times the values in column 3 occurs. Each input line is appended to r which is an associative array, like c, keyed on the third column.

At the end, only lines whose 3rd column occurs exactly three times are outputted.

0

Well, maybe not the most elegant way but what follows works. It extends what you already did by parsing each line to filter out those that have an occurrences count not strictly greater than 2.

awk '{print $3}' yourInputFile | sort | uniq -c | while read -r line
   do 
      echo $line | [ `awk '{print $1}'` -gt 2 ] && echo $line | awk '{print $2}'
   done

And should you wish to restrict the display to lines that have an occurrences count strictly equal to 3, it's even simpler. A grep can be used as @wvxvw suggested in comments. You don't even need to iterate in that case but you have to shield the grep not to get values that begin by 3 but that are greater, such as 30:

awk '{print $3}' yourInputFile | sort | uniq -c | grep '^\s*3\s' | awk '{print $2}'

In both cases the output is:

APPLE

Not the answer you're looking for? Browse other questions tagged or ask your own question.