7

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
11

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}'
4

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.

1

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.

  • I believe that's GNU AWK 4. Earlier versions handle (fake) multi-dimensional arrays differently. – Paused until further notice. Jan 15 '16 at 21:05
1

Another awk approach to remove unique rows based on column#1 (or return duplicated rows based on column#1)

awk -F, 'NR==FNR{s[$1]++;next} (s[$1]>1)' infile infile
0
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.

  • can be written as grep -f <(cut -d, -f1 file | sort | uniq -c | awk '$1>1 {print "^"$2","}') file – glenn jackman Jan 15 '16 at 16:59
  • one of the many ways it can be written, is in my example. I just wanted to make it easier to understand. – MelBurslan Jan 15 '16 at 17:02
  • 2
    understood. Just making it more efficient: one grep call versus 2n calls. – glenn jackman Jan 15 '16 at 17:08
  • @glennjackman I like your solution. however, it appears to not work with huge files. I'm not sure if that's an issue with grep. – Bob Jan 18 '16 at 10:56
0

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
0

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

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.