Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Date::Calc and comparing across arrays?

by rcotten (Initiate)
on Apr 09, 2013 at 04:46 UTC ( #1027642=perlquestion: print w/replies, xml ) Need Help??
rcotten has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks... been using perl for a while now but this one has got me stumped. I have multiple lines in a .csv they all have start stop dates and total time on each line. I need to look for dates (across multiple lines) that are consecutive. accounting for weekend and holidays.

IE. input.csv employid, start, stop, total 1234, 01/04/2013, 05/04/2013, 5 1234, 08/04/2013, 12/04/2013, 5 1234, 18/04/2013, 18/04/2013, 1 5678, 01/01/2013, 01/01/2013, 1 output.csv employid, start, stop, total 1234, 01/04/2013, 12/04/2013, 10 1234, 18/04/2013, 18/04/2013, 1

I have the open/close and csv reading fine with CSV::Text. I am pretty confident on the looping etc.. I'm just not sure the best way to look through file for a matching employid and then take into account weekends and holidays. Suggestions on modules and how to proceed?

Was seeing a lot of talk about Date::Calc..

Thanks ray

Replies are listed 'Best First'.
Re: Date::Calc and comparing across arrays?
by hdb (Prior) on Apr 09, 2013 at 08:12 UTC

    What the helpful Anonymous Monk probably wanted to say (but I might be wrong) is that a bit more evidence of effort on your side would get you more help here. If you had posted some code snippets like

    use strict; use warnings; use Date::Calendar::Profiles qw( $Profiles ); use Date::Calendar; my $cal = Date::Calendar->new( $Profiles->{'US-AZ'} ); print "Number of work days between 5 Apr 13 and 8 Apr 13:"; print $cal->delta_workdays( 2013, 4, 5, 2013, 4, 8, 0, 0 );

    people would be providing more detailed help. Otherwise, there is often the assumption that you had not bothered to look up the massive amount of documentation on Perl modules available on CPAN and to spend some time experimenting.

    With Date::Calendar and Date::Calc you seem to be on the safe side.


      This is what I have so far...

      #!/usr/bin/perl use strict; use warnings; use Text::CSV; use Date::Calc qw(:all); use Date::Calendar; #------------------------------------------- # #------------------------------------------- #--------------------------------------- # Edit Custom Variables Below #--------------------------------------- #-------------------------------- # Input and Output files #-------------------------------- #IN is the directory/filename.csv that the input csv is being written +to #OUT is the directory/filename.csv that the output csv #my $in = '/opt/data/input.csv'; #my $out = '/opt/data/output.csv'; my $in = 'c:/Users/cottenr/test.csv'; my $out = 'c:/Users/cottenr/foo.csv'; my $employee_number; my $employee_id; my $start_date; my $end_date; my $total_days; #--------------------------------------- # Program and Loop Section #--------------------------------------- #Looking for csv file to be written #START: while ( -e $in){ my $csv = Text::CSV->new ({ binary => 1, auto_diag => 1, sep_char => ',' # not really needed as this is the default }); #Open IN and OUT files open(my $data, '<:encoding(utf8)', $in) or die "Could not open '$in' f +ile \n"; <$data>; #skips header line of .csv file open(OUT, '>encoding(utf8)', $out) or die "Could not open '$out' file\ +n"; #Loop through the file and process data while (my $fields = $csv->getline( $data )) { $employee_number = $fields->[2]; $employee_id = $fields->[4]; $start_date = $fields->[17]; $end_date = $fields->[18]; $total_days = $fields->[21]; #Conditional eval for Date Comparison if($end_date > $highnumber) { } elsif ($count > $mednumber) { } else { } #Layout the format and write to OUT file print OUT "$employee_number, $employee_id, $start_date, $end_date, $to +tal_days\n"; } #While loop if (not $csv->eof) { $csv->error_diag(); } #--------------------------------------- # Finish it up! #--------------------------------------- #Print out the footer #Close the IN and OUT files close $data; close $in; close OUT; #Delete the INPUT csv file #unlink $in; } #--------------------------------------- # Take a Nap and do it all over again #--------------------------------------- #sleep 60; #goto START;

      Not sure how to go about pulling the first line of the Array and then searching for other matches to see if the Start date -2days of line 2 is the enddate of line 1. If it matches then add total time and update my enddate on the OUT.

      Thanks, Ray

        Hope this is helpful!

        use strict; use warnings; use Date::Calendar::Profiles qw( $Profiles ); use Date::Calendar; sub to_date { my ($d,$m,$y) = split /\//, shift; return [$y,$m,$d]; } sub from_date { my $date = shift; $date =~ s/(\d{4})(\d{2})(\d{2})/$3\/$2\/$1/; return $date; } my $calendar = Date::Calendar->new( $Profiles->{'DE-SN'} ); # first read in the whole file and # record start and end dates for each employee my %dates; # simplified data reading, pls stick to Text::CSV while (<DATA>) { next if /^empl/; chomp; my( $employee_id,$start_date,$end_date,$total_days) = split /, +/; $dates{$employee_id}{$start_date}{'end'} = $end_date; $dates{$employee_id}{$start_date}{'days'} = $total_days; } # now we have all the data # and can process it for my $employid (sort keys %dates) { my $data = $dates{$employid}; for my $startdate (keys %{$data}) { my $enddate = $$data{$startdate}{'end'}; next if $enddate eq 'invalid'; # redundant, see below # find next businessday my $next = from_date( $calendar->add_delta_workdays(to +_date($enddate),1) ); # do we have a record for the next business day ? if( exists $$data{$next} ) { # merge two records $$data{$startdate}{'end'} = $$data{$next}{'end +'}; $$data{$startdate}{'days'} += $$data{$next}{'d +ays'}; $$data{$next}{'end'} = 'invalid'; # mark redun +dant } } } for my $employid (sort keys %dates) { my $data = $dates{$employid}; for my $startdate (keys %{$data}) { my $enddate = $$data{$startdate}{'end'}; next if $enddate eq 'invalid'; print "$employid,$startdate,$enddate,"; print $$data{$startdate}{'days'},"\n"; } } __DATA__ employid, start, stop, total 1234,01/04/2013,05/04/2013,5 1234,08/04/2013,12/04/2013,5 1234,18/04/2013,18/04/2013,1 1234,22/04/2013,22/04/2013,1 1234,23/04/2013,23/04/2013,1 5678,01/01/2013,01/01/2013,1
Re: Date::Calc and comparing across arrays?
by Anonymous Monk on Apr 09, 2013 at 07:01 UTC

    Suggestions on modules and how to proceed?

    What I would do? If you got the CSV::Text/looping going, then perlintro explains the matching, and sure, Date::Calc can help you generate a list of things to match, DateTime can too, does that work for you?

    Hi, I'm lil'the-great-sarcasmo, here are my jokes

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1027642]
Approved by Lady_Aleena
[thepkd]: Yes this is my first time in cb. Trying to work my way around.
[JohnCub]: "A ref too far" would make a good title for anyone's perl memoirs
[thepkd]: Anyways it works with $aohoaoh[0]->{'foo '}[0]->{'bar'}
[GotToBTru]: "I did it my $way"
[JohnCub]: I hope $way isn't a hashref. ;)
[GotToBTru]: many times i've been told I made a real hash of thiings
[GotToBTru]: so .. perhaps it should b e

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (10)
As of 2016-12-06 13:48 GMT
Find Nodes?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:

    Results (105 votes). Check out past polls.