Process plain text data

Question

I've this plain text file that I need to be automatically examined and results written in another plain text file. Note that I'll use plain text and CSV interchangeably, since CSVs are plain text with common separator between data (commas, tabs, etc).

Data examples

ABD-01A
ABD-01B
ABD-01C
AL-25A
AL-25B

Process one - recognize the first two sections of the data (text-number) to repeat and count them in output CSV:

ABD-01,1
ABD-01,2
ABD-01,3
AL-25,1
AL-25,2

Process two - First part of the data gathered above has sort of a primary key assigned to them on a different file. I would like to get the primary keys mentioned in the newly generated files as well:

#primary key file
1,ABD-01
2,ABD-02
3,AL-02
20,AL-25

#newly generated file
1,ABD-01,1
1,ABD-01,2
1,ABD-01,3
20,AL-25,1
20,AL-25,2

So basically, what I'm asking her is if there's any software that can help me with this on either Windows or Linux-based operating systems. Bash can help me with simpler processing, but I don't think it can handle this much. Please advise.

Answer 1

1. Choose your preferred language – Perl works well with text; Python, PHP and Ruby are okay, if a little slower.
2. Read the key file first:
   • split each key,data line into key and data,
   • then store both in a dict/hash/array: keys[data] = key
   • also counts[data] = 0 if the language demands initialization
3. Read all data files:
   1. use a regexp to find the "TEXT-NUMBER" data at the beginning of every line,
   2. increment counts[data] by one,
   3. and immediately output keys[data], the line, and counts[data].

I wrote an example script in both Perl and Python, so your choice:

process.pl

#!/usr/bin/env perl
use strict;

sub usage {
    print "Usage: $0 <key_file> <data_file...>\n";
    return 2;
}

my $keys_file = shift(@ARGV) // exit(usage);
my @data_files = @ARGV;

my %keys;   # $data => $primarykey
my %counts; # $data => $count

# Read keys

open(my $fh, "<", $keys_file) or die "$!";
while (my $line = <$fh>) {
    chomp($line);
    my ($key, $datum) = split(/,/, $line, 2);
    if (!defined $datum) {
        warn "$keys_file: line $. not in KEY,DATA format: '$line'\n";
        next;
    }

    $keys{$datum} = $key;
    $counts{$datum} = 0;
}
close($fh);

# Read and output data

my $ex = qr/^(\w+-\d+)\w*/;
for my $data_file (@data_files) {
    open(my $fh, "<", $data_file) or die "$!";
    while (my $line = <$fh>) {
        chomp($line);
        if ($line =~ /$ex/) {
            my $datum = $1;
            if (!defined $keys{$datum}) {
                warn "no primary key found for data '$datum'\n";
                next;
            }

            # Increment count, then print processed line immediately
            $counts{$datum}++;
            print "$keys{$datum},$&,$counts{$datum}\n";
        }
        else {
            warn "$data_file: line $. does not begin with TEXT-NUMBER: '$_'\n";
        }
    }
    close($fh);
}

process.py

#!/usr/bin/env python
from __future__ import print_function
import sys
import re

def usage():
    print("Usage: %s <key_file> <data_file...>" % sys.argv[0])
    return 2

try:
    keys_file = sys.argv[1]
    data_files = sys.argv[2:]
except IndexError:
    sys.exit(usage())
except ValueError:
    sys.exit(usage())

keys = {}
counts = {}

# Read keys

for line in open(keys_file, "r"):
    try:
        key, datum = line.strip().split(",", 1)
    except ValueError:
        print("%s: line not in KEY,DATA format: %r" \
            % (keys_file, line.strip()), file=sys.stderr)
        continue

    keys[datum] = key
    counts[datum] = 0

# Read and output data

ex = re.compile(r'^(\w+-\d+)\w*')
for data_file in data_files:
    for line in open(data_file, "r"):
        line = line.strip()
        m = re.match(ex, line)
        if m:
            datum = m.group(1)
            if datum not in keys:
                print("no primary key found for data %r" % datum,
                    file=sys.stderr)
                continue

            # Increment count, then print processed line immediately
            counts[datum] += 1
            print("%s,%s,%d" % (keys[datum], m.group(0), counts[datum]))
        else:
            print("%s: line does not begin with TEXT-NUMBER: %r" \
                % (data_file, line.strip()), file=sys.stderr)

Answer 2

I would do it in Python using regular expressions. Just type python in your shell to see if it is installed.

Otherwise you can use Perl. Just type perl in your shell to see if it is installed. Is has built-in support of regular expressions.

Answer 3

Process 1

perl count.pl datafile …

where count.pl is something like

#!perl
use strict;
use warnings;

my %headwordcount;

while (<>) {
  if (/^([A-Z]+-\d+)/) { $headwordcount{$1}++; }
  # else { warn "Bad data: $_"; } # uncomment line for data warnings
}

END {
  foreach (sort keys %headwordcount) {
     print "$_,$headwordcount{$_}\n";
  }
}

Untested, caveat emptor.

Process 2

Add something like

   my %key;

   BEGIN {
     my $keyfilename = 'primary.key';
     open my $fh, '<', $keyfilename or die "Can't read '$keyfilename' - $!\n";
     while (<$fh>) {
        chomp;
        my ($key,$headword) = split(/,/, $_, 2);
        $key{$headword} = $key;        
     }
     close $fh;
   }

and change the print line in END {} to

   print "$key{$_},$_,$headwordcount{$_}\n";

Again, untested.

You'll generate warnings if the data file contains headwords not in the key file. You can check for $key{$_} being undefined and if so print w/o key.