Field data missing; copying from other field

pbassnote has asked for the wisdom of the Perl Monks concerning the following question:

I have a csv file that among other data, for this particular problem, contain these fields: 1) employee_name, 2) employee_group, 3) manager_name . For some of the employee_name entries, the group entries are empty (null). So, since the managers are also listed as employees in this file, I would like to be able to do a look up on their entries to copy their group information into the null entries of their corresponding employees, and I don't know how to code this. I'm thinking the pseudo-code might be something like:

if $element->[employee_group] = ""
then (go to $element->[manager_name]
copy $element->[manager's_group] to $element->[employee_group]);
[download]

So, this is an idea of what it might look like.

Sam Malone,Cheers,Rebecca Howe
Woody Boyd,,Rebecca Howe
Rebecca Howe,Cheers,Rebecca's Manager
[download]

You can see that Woody's group info is missing - null. So I'd like the code to go to Woody's manager, Rebecca Howe, and copy her group info to Woody's missing group field to make the file as such:

Sam Malone,Cheers,Rebecca Howe
Woody Boyd,Cheers,Rebecca Howe
Rebecca Howe,Cheers,Rebecca's Manager
[download]

Thanks, Gurus!

Comment on Field data missing; copying from other field Select or Download Code

Replies are listed 'Best First'.

Re: Field data missing; copying from other field
by Athanasius (Archbishop) on Oct 13, 2014 at 16:48 UTC

Hello pbassnote,

I would suggest that you proceed in three steps:

read the csv file into a suitable data structure, such as an array of arrays (AoA);
create a hash matching employee names to employee groups;
work through the AoA, filling in any missing employee groups from the data in the hash.

For example:

#! perl
use strict;
use warnings;
use Data::Dump;
use Text::CSV_XS qw( csv );

my $aoa = csv(in => \*DATA);

my %employees;

# Make an employee => group hash

for my $record (@$aoa)
{
    $employees{ $record->[0] } = $record->[1];
}

# Fill in the missing groups

for my $record (@$aoa)
{
    $record->[1] = $employees{ $record->[2] } if $record->[1] eq '';
}

dd $aoa;

__DATA__
Sam Malone,Cheers,Rebecca Howe
Woody Boyd,,Rebecca Howe
Rebecca Howe,Cheers,Rebecca's Manager
[download]

Output:

 2:46 >perl 1057_SoPW.pl
[
  ["Sam Malone", "Cheers", "Rebecca Howe"],
  ["Woody Boyd", "Cheers", "Rebecca Howe"],
  ["Rebecca Howe", "Cheers", "Rebecca's Manager"],
]

 2:47 >
[download]

Hope that helps,

Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]

Re: Field data missing; copying from other field
by Eily (Monsignor) on Oct 13, 2014 at 17:00 UTC

To jump to the line of the manager you need to know where it is, this means that you have to loop through the whole data once to find the manager's index. So if you do that, you might as well go through the file once, and fill the holes as you loop through the data to print it in a file. Something like:

for (@line)
{
  $employee = GetElement($_);
  $groups{$employee->[name]} = $employee->[group];
  push @list, $employee;
}

for (@list)
{
  $_->[group] = $groups{$_->[manager]} unless $_->[group];
  print ToCsv($_);
}
[download]

Or, in the spirit of TIMTOWTDI, you can use references so that the group of an employee and their manager actually refer to the same scalar, even if it is still unknown:

use Data::Dumper;

my %people;
while (<DATA>)
{
  chomp;
  my ($name, $group, $manager) = split/,/; # Don't do that, use Text::
+CSV instead
  if ($group)
  {
    ${ $people{$name}{Group} } = $group; # Autovivification means: if 
+the reference exists, change the value, else, create a new reference
  }
  else
  {
    $people{$name}{Group} = \${ $people{$manager}{Group} }; # Autovivi
+fication again
  }
}
print Dumper \%people;
print ${ $people{"Woody Boyd"}{Group} };
__DATA__
Sam Malone,Cheers,Rebecca Howe
Woody Boyd,,Rebecca Howe
Rebecca Howe,Cheers,Rebecca's Manager
[download]

Edit: You all recognized TIMTOWTDI desguised as TIMTOWTDIT :)

[reply]
[d/l]
[select]