How do I print all lines of a file with duplicate values in a certain column

Question

Given a file like this:

1,768,12,46576457,7898
1,123,435,134,146
2,345,6756856,12312,1311
5,234,567465,12341,1341
1,3245,4356345,2442,13
9,423,2342,121,463
9,989,342,121,1212

I would like to list all rows (in bash terminal) such that the value in column 1 appears at least twice (in column 1). The result should be

1,768,12,46576457,7898
1,123,435,134,146
1,3245,4356345,2442,13
9,423,2342,121,463
9,989,342,121,1212

Answer 1

To try and avoid storing the whole file in memory, you could do:

awk -F , '
  !count[$1]++ {save[$1] = $0; next}
  count[$1] == 2 {
    print save[$1]
    delete save[$1]
  }
  {print}'

Answer 2

Perl solution:

perl -F, -ane ' $h{ $F[0] } .= $_
                }{
                $h{$_} =~ tr/\n// >= 2 and print $h{$_} for keys %h
              ' < input-file

• -n reads the input line by line
• -a splits each line on -F, i.e. comma, into the @F array.
• lines are stored in the %h hash keyed by the first field ($F[0]). They are concatenated together (.=).
• at the end of the file ("Eskimo greeting" }{), we loop over the keys and count the number of newlines (using the tr operator). If its at least 2, we print the stored lines.

You can feed the output to | sort -n if you want the first column to be numerically sorted.

Attention: if the last line didn't end in a newline, its group would report its size - 1. You can chomp each line and add the newlines yourself to fix it, or use array of arrays of lines instead of array of strings.

Answer 3

With awk (GNU awk for multi-dimensional arrays)

gawk -F, '
    { line[NR] = $0; count[$1]++; found[$1][NR] = 1}
    END {
        for (id in count)
            if (count[id] > 1)
                for (nr in found[id]) 
                    print line[nr]
    }
' file

The order of the output may not be the same as the input file.

Answer 4

Another variant (where test.txt is your input file):

FILE=test.txt ; for n in $(cat ${FILE} | awk -F"," '{count[$1]++} END {for (i in count) print i":"count[i]}'|grep -v ':1'|awk -F: '{print $1}');do grep ^${n} ${FILE} ;done

Answer 5

Using Python 3:

#!/usr/bin/env python3
import sys
from collections import defaultdict

column_delimiter = sys.argv[1]
column = int(sys.argv[2]) - 1

records = defaultdict(list)
for l in sys.stdin:
    l = l.rstrip('\n')
    r = l.split(column_delimiter)
    records[r[column]].append(l)

for ll in records.values():
    if len(ll) > 1:
        print(*ll, sep='\n')

Usage:

python3 duplicate-columns.py COLUMN-DELIMITER COLUMN

Example:

python3 duplicate-columns.py ',' 1 < data.csv

Answer 6

A pure shell script variant (only relying on sort):

FILE=your.file
last_key=""
last_tail=""
first_match=yes
sort -t, -k1,1 $FILE |
while IFS=, read key tail
do
  if [[ $key = $last_key ]]
  then
    if [[ $first_match = yes ]] 
    then
      echo $last_key,$last_tail
      first_match=no
    fi
    echo $key,$tail
  else
    first_match=yes
  fi
  last_key=$key
  last_tail=$tail
done

Answer 7

This is another pure bash script, built entirely with internal bash commands and operators. Associative arrays make the trickiest parts. This solution is very efficient, because the file is scanned just once and few data are stored in memory.

FILE=your.file
unset keys; declare -A keys
unset first_of; declare -A first_of
while IFS=, read key tail
do
  (( count = ++keys[$key] ))
  (( count==1 )) && { first_of[$key]="$tail"; continue; }
  (( count==2 )) && echo $key,${first_of[$key]}
  echo $key,$tail
done <$FILE

Answer 8

Using Miller (https://github.com/johnkerl/miller) and running

mlr --csv -N count-similar -g 1 -o count then filter '$count>1' then cut -x -f count ./input.csv >./output.csv

you will have

1,768,12,46576457,7898
1,123,435,134,146
1,3245,4356345,2442,13
9,423,2342,121,463
9,989,342,121,1212

Some notes:

• count-similar -g 1 to count grouping by field 1 values;
• -o count, to set the output count field;
• filter '$count>1', to filter records where count field is greater than one;
• then cut -x -f count, to remove the count field

Answer 9

for i in $(cat given | cut -d, -f1)
do
  linect=$(grep ^"${i}," given | wc -l)
  if [ ${linect} -gt 1 ]
  then
    grep ^"${i}," given >> result
  fi
done
sort result |uniq > desiredoutput

as long as the fields are delimited by comma and you are seeking duplicates in the column 1 and column 1 only, this should work.