Matching 3 or more words from fields in separate csv files

Question

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.

Answer 1

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;