0

I have two csv files:

This is csv1:

11, The Sun Still Shines in Reading, 64312, 464566
13, You and Me Together Again London, 564564, 131355
12, What's the Story Now Your Gone, 4545646, 1124545
17, Hello I love you, 456899, 1259898

This is csv2:

69, The Sun Shines, 6464, 52364
12, Tell me your name, 56456, 21345
17, From London to New York, 897944, 234655
97, Reading Still Shines, 545464, 16748967

I have this code which allows the matching of titles (which are in field 2).

cat $csv1 |cut -d, -f2 | while read p; do
  grep -i "$p" $csv2
  if [ $? -eq 0 ];then
    grep -i "$p" $csv1
  fi
done

At the moment this code looks at each line in csv1, and if there is a line in csv2 which has a matching title, then it prints the matching lines together. This works really well.

However, I would now like to adapt the script so that rather than searching for an exact title, it checks if there are 3 or more words matching.

So the output in the case of the csv data on this page would be:

11, The Sun Still Shines in Reading, 64312, 464566
69, The Sun Shines, 6464, 52364
97, Reading Still Shines, 545464, 16748967

Which consists of the line from csv1 at the top, followed by the two lines from csv2 which have 3 or more matching words in field 2 (the title). How can I specify the number of matching words?

EDIT: One thing I forgot to mention is that csv1 will be significantly smaller in size in terms on lines than csv2 (like 10's compare to thousands), although thinking about it I guess this doesn't really matter as I can define the largest data set as either csv1 or csv2.

1
  • Time to learn perl.
    – tniles
    May 18 '16 at 15:57
2

There may be a more shell-centric solution (awk?) but I usually turn to perl when the problem gets this complex. Here's a perl script that reads all of csv2 into memory, collecting the lines as keys into a hash whose values are the corresponding titles.

It then loops through csv1, pulls out the title, then for each title from csv2, counts the number of times each word in the title is seen. If it's more than desired, then the matching title is printed, along with its "source" line from csv1.

#!/usr/bin/env perl

my @csv2 = ();
open CSV2, "<csv2" or die;
@csv2=<CSV2>;
close CSV2;

my %csv2hash = ();
for (@csv2) {
  chomp;
  my ($title) = $_ =~ /^.+?,\s*([^,]+?),/; #/ match the title 
  $csv2hash{$_} = $title;
}

open CSV1, "<csv1" or die;
while (<CSV1>) {
  chomp;
  my ($title) = $_ =~ /^.+?,\s*([^,]+?),/; #/ match the title 
  my @titlewords = split /\s+/, $title;    #/ get words
  my $desired = 3;
  my $matched = 0;
  foreach my $csv2 (keys %csv2hash) {
    my $count = 0;
    my $value = $csv2hash{$csv2};
    foreach my $word (@titlewords) {
      ++$count if $value =~ /\b$word\b/i;
      last if $count >= $desired;
    }
    if ($count >= $desired) {
      print "$csv2\n";
      ++$matched;
    }
  }
  print "$_\n" if $matched;
}
close CSV1;
6
  • Thanks, I just tested this which worked with 3 words, but I then changed my $desired = 3 to my $desired = 4 which I assume sets the number of words which must match (as I may want to alter the number of matches depending on my needs), and it produced the same output as with 3 words. Why is this? thanks
    – nmh
    May 18 '16 at 16:21
  • Just to add, my expected output when matching words is set to 4, would be no matches (based on the same csv example data) as none of the lines in csv2 contain 4 or more matching words in the title, but this script still matches two of the lines when when my $desired is set to my $desired = 4;
    – nmh
    May 18 '16 at 16:29
  • ahhh, I see what's happening - I'm not enforcing the matches to be on a word boundary; one moment...
    – Jeff Schaller
    May 18 '16 at 17:04
  • updated with 2 improvements: the word-searching now enforces a word boundary (\b) and is also now case-insensitive to match your grep -i
    – Jeff Schaller
    May 18 '16 at 17:06
  • Nice work, just tested it and that's excellent, yes I do need to learn perl it seems!! very powerful
    – nmh
    May 19 '16 at 9:45

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.