# Count and keep duplicates in Column

**URL:** <https://community.unix.com/t/count-and-keep-duplicates-in-column/359354>\
**Category:** Shell Programming and Scripting\
**Created:** [March 23, 2016, 11:41am UTC](https://community.unix.com/t/count-and-keep-duplicates-in-column/359354 "2016-03-23T11:41:17Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pshields1984](https://community.unix.com/letter_avatar/pshields1984/32/5_5575768a8748004e209b776fc1b2916d.png) [@pshields1984](https://community.unix.com/u/pshields1984)\
**Post date:** [March 23, 2016, 11:41am UTC](https://community.unix.com/t/count-and-keep-duplicates-in-column/359354/1 "2016-03-23T11:41:17Z")

</div>

Hi folks,

I've got a csv file called test.csv

```nohighlight
Column A Column B
Apples 1900
Apples 1901
Pears 1902
Pears 1903

```

I want to count and keep duplicates in the first column. Desired output

```nohighlight
Column A Column B Column C
Apples 2 1900
Apples 2 1901
Pears 2 1902
Pears 2 1903

```

I have tried sort and uniq but to no avail, the uniq -c removes the duplicates. I need to keep them.

Any help would be great.

Thanks.

---

<div class="post-metadata">

**Author:** ![RudiC](https://community.unix.com/letter_avatar/rudic/32/5_5575768a8748004e209b776fc1b2916d.png) [@RudiC](https://community.unix.com/u/RudiC)\
**Post date:** [March 23, 2016, 12:05pm UTC](https://community.unix.com/t/count-and-keep-duplicates-in-column/359354/2 "2016-03-23T12:05:57Z")

</div>

Please use code tags as required by forum rules!

I guess the second column header should go with the column, no? Having fields with the field separator inside doesn't really help processing. Try

```nohighlight
awk 'NR == FNR {T[$1]++; next} FNR == 1 {print $1, $2, "CNT", $3, $4; next} {print $1, T[$1], $2}' file file
Column A CNT Column B
Apples 2 1900
Apples 2 1901
Pears 2 1902
Pears 2 1903

```

---

<div class="post-metadata">

**Author:** ![pshields1984](https://community.unix.com/letter_avatar/pshields1984/32/5_5575768a8748004e209b776fc1b2916d.png) [@pshields1984](https://community.unix.com/u/pshields1984)\
**Post date:** [March 23, 2016, 1:58pm UTC](https://community.unix.com/t/count-and-keep-duplicates-in-column/359354/3 "2016-03-23T13:58:37Z")

</div>

Thank you so much, I am almost there. Long time lurker first time poster, apologies about quoting code correctly. Can you explain the command? I don't really need column headers to make things more straight forward.

---

<div class="post-metadata">

**Author:** ![RudiC](https://community.unix.com/letter_avatar/rudic/32/5_5575768a8748004e209b776fc1b2916d.png) [@RudiC](https://community.unix.com/u/RudiC)\
**Post date:** [March 23, 2016, 2:24pm UTC](https://community.unix.com/t/count-and-keep-duplicates-in-column/359354/4 "2016-03-23T14:24:35Z")

</div>

It's two passes across the same file - first pass to count the occurrences, the second to print the fields plus the count.

---

<div class="post-metadata">

**Author:** ![Aia](https://community.unix.com/user_avatar/community.unix.com/aia/32/917_2.png) [@Aia](https://community.unix.com/u/Aia)\
**Post date:** [March 23, 2016, 2:39pm UTC](https://community.unix.com/t/count-and-keep-duplicates-in-column/359354/5 "2016-03-23T14:39:32Z")

</div>

If you do not need the header:

```nohighlight
awk 'NR == FNR {T[$1]++; next} FNR > 1{print $1, T[$1], $2}' pshields1984.input pshields1984.input

```

```nohighlight
NR == FNR {T[$1]++; next} # execute only in the first pass reading input
FNR > 1{print $1, T[$1], $2} # skip first line and insert the tally in from previous read after the first column

```

Some Perl code that could be more flexible.

```nohighlight
#!/usr/bin/perl

use strict;
use warnings;

my $filename = shift or die "Usage: $0 FILENAME\n";
my %tally;

open my $fh, '<', $filename or die "Could not open $filename: $!\n";

<$fh>;
my $data_position = tell $fh;

while (my $entry = <$fh>) {
    my ($id) = split '\s+', $entry;
    $tally{$id}++;
}
seek $fh, $data_position, 0;
while (my $entry = <$fh>) {
    my @fields = split '\s+', $entry;
    splice @fields, 1,0, $tally{$fields[0]};
    print "@fields\n";
}
close $fh;

```

Save as `tally.pl`  
Run as `perl tally.pl pshields1984.input`

---

<div class="post-metadata">

**Author:** ![pshields1984](https://community.unix.com/letter_avatar/pshields1984/32/5_5575768a8748004e209b776fc1b2916d.png) [@pshields1984](https://community.unix.com/u/pshields1984)\
**Post date:** [March 23, 2016, 7:34pm UTC](https://community.unix.com/t/count-and-keep-duplicates-in-column/359354/6 "2016-03-23T19:34:43Z")

</div>

Thank you RudiC. It worked a charm.

---------- Post updated at 06:34 PM ---------- Previous update was at 02:18 PM ----------

Thanks Aia I'll give the perl suggestion a go.