3

I have been using this perl script (thanks to Jeff Schaller) to match 3 or more words in the title fields of two separate csv files as answered here:

Matching 3 or more words from fields in separate csv files

The script is:

#!/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;

I have since realised that I would like to ignore certain words between the titles and not class them as matching words. I've been using sed to remove them before the csv files are compared but this isn't ideal as I lose data in the process. How can I add words which would be considered as exceptions to this perl script? For example, let's say if I wanted the script to ignore the three separate words and if and the when matching the titles so that they would be exceptions to the rule.

6
  • the perl script in my answer below also answers your recently deleted question. The get_intersection method from the List::Compare module eliminates duplicate entries (which is what you'd expect from set intersection), individual words are counted only once for each title. This is one of the huge benefits of using existing library modules rather than writing your own - they usually include useful features that would take a lot of effort to implement yourself. They also tend to have solved most or all of the common (and many not-so-common) problems related to the task at hand.
    – cas
    May 27, 2016 at 9:44
  • Hi cas, I tried your script yesterday, but I have one big problem unfortunately! I'm running this on a synology box which has a basic form of linux. On running the script my machine didn't recognise Class::CSV so I tried installing it through CPAN and I couldn't get it to install despite a lot of effort! Unfortunately because of this i couldn't test out your answer I'm afraid :(
    – nmh
    May 27, 2016 at 9:51
  • If you wanted to try it again, you could post a question about installing CPAN modules on a synology box, with examples of what you tried and what output & error messages you got. List::Compare would also need to be installed. btw does this link help?
    – cas
    May 27, 2016 at 10:27
  • Thanks, i'll give that a go. My synology box switches on at 15:30 so I'll be able to try it remotely after that time. Cheers for your help.
    – nmh
    May 27, 2016 at 10:49
  • Hi I tried your the recommendation in your link but no joy, i'm still getting 'make test had returned bad status, won't install without force'. I've added 'force' to the install command but it still doesn't end up installing. I'll keep searching for a solution though and maybe created a question on here at some point.
    – nmh
    May 27, 2016 at 16:42

2 Answers 2

3

After the line

my @titlewords = split /\s+/, $title;    #/ get words

add the code to remove the words from the array:

my @new;
foreach my $t (@titlewords){
    push(@new, $t) if $t !~ /^(and|if|the)$/i;
}
@titlewords = @new;
2
  • Hello, thanks for the response, Is there anything I can add to ignore the upper/lower case? I've just tested and here is what I found- if the three words are in upper case in csv1 then there is no match so this works as I wanted. If the three words are in upper case in csv2 then it still matches them. If the three words are in upper case in both files it still matches them. How do I get it to ignore case completely ? cheers!
    – nmh
    May 24, 2016 at 16:26
  • @nmh I should have thought of that. Simply add the i flag to the pattern. I edited my answer to show it.
    – meuh
    May 24, 2016 at 16:30
1

This is very similar to @meuh's answer, but instead of adding a foreach loop after the split line, you only need to add one line there, using either perl's grep function or its map function:

@titlewords = grep (!/^(and|if|the)$/i, @titlewords);

or

@titlewords = map { /^(and|if|the)$/i ? () : $_ } @titlewords;

See perldoc -f grep and perldoc -f map for more details on these functions and the differences between them. They are commonly used (especially map) in many perl scripts, so it is worth taking the time to understand what they do and learn how they work.


BTW, do NOT use #!/usr/bin/env perl. Using env like that is bad enough with python and ruby scripts (where it is, unfortunately, the convention), but it is completely broken for perl scripts and definitely NOT the conventional way to run them.

perl has many command-line options which alter its behaviour in significant ways, depending on what kind of program you're trying to write. Using env to run an interpreter like perl completely destroys the ability to pass command-line options to the interpreter (because env does not support it. env wasn't even designed to be used for this purpose, doing so is just an ugly hack that takes advantage of a side-effect of env's actual purpose - which is setting environment variables before running a program).

Use #!/usr/bin/perl instead (or whatever the path to your perl interpreter is).


Here's another perl script that does what you want - but this one uses the Class::CSV and List::Compare modules, as well as two Hashes-of-Arrays to compare the CSV files:

#! /usr/bin/perl

use strict;
use warnings;

use Class::CSV;
use List::Compare;

sub parse_csv($%) {
  my($filename,$tw) = @_;

  # exclude the following word list and the "empty word"
  my @exceptions = qw(and if the);
  my $exceptions = '^(\s*|' . join('|',@exceptions) . ')$';


  my $csv = Class::CSV->parse(
      filename => $filename,
      fields   => [qw/id title num1 num2/]
  );

  # build a hash-of-arrays (HoA), keyed by the CSV line. Each array
  # contains the individual words from each title for that line (except
  # for those matching $exceptions).  The words are all converted to
  # lowercase to enable case-insensitive matches.
  foreach my $line (@{$csv->lines()}) {

    # The following three lines are required because the input file has
    # fields separated by ', ' rather than just ',' which makes
    # Class::CSV interpret the numeric fields as strings.
    # It's easier/quicker to do this than to rewrite using Text::CSV.
    #
    # The final output will be properly-formed CSV, with only a comma as
    # field separator and quotes around the title string.
    my $key = join(',',$line->id,'"'.$line->title.'"',$line->num1,$line->num2);
    $key =~ s/([",])\s+/$1/g;   # trim whitespace immediately following " or ,
    $key =~ s/\s+([",])/$1/g;   # trim whitespace immediately preceding " or ,

    # If it wasn't for the not-quite-right CSV format, we could just use:
    #my $key = $line->string;

    push @{ $tw->{$key} }, grep (!/$exceptions/oi, split(/\s+/,$line->title));
  };
};

# two hashes to hold the titlewords HoAs
my %tw1=();
my %tw2=();

parse_csv('csv1',\%tw1);
parse_csv('csv2',\%tw2);

# now compare the HoAs
foreach my $k2 (sort keys %tw2) {
  my @matches = ();
  foreach my $k1 (sort keys %tw1) {
    my $lc = List::Compare->new('-u', \@{ $tw2{$k2} }, \@{ $tw1{$k1} });
    push @matches, $k1 if ($lc->get_intersection ge 3);
  };
  print join("\n",sort(@matches,$k2)),"\n\n" if (@matches);
};

Output:

11,"The Sun Still Shines in Reading",64312,464566
97,"Reading Still Shines",545464,16748967

Each group of matches is sorted and, even though the sample output doesn't show it (because there's only one group of matches), each group is printed as a separate paragraph (i.e. separated by a blank line)

BTW, if you don't want the double-quotes around the title fields, edit the my $key=join(...) line that adds them so that it doesn't.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .