10

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

9 Answers 9

12

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.

1
  • I believe that's GNU AWK 4. Earlier versions handle (fake) multi-dimensional arrays differently. Jan 15, 2016 at 21:05
1

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
1

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
1

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
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.

4
  • can be written as grep -f <(cut -d, -f1 file | sort | uniq -c | awk '$1>1 {print "^"$2","}') file Jan 15, 2016 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, 2016 at 17:02
  • 2
    understood. Just making it more efficient: one grep call versus 2n calls. Jan 15, 2016 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, 2016 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
1
  • That's just about the ugliest thing I've ever seen. Jan 15, 2016 at 21:05
0

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

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.