topic Re: text manipulation challenge in Operating System - Linux

text manipulation challenge

Duncan Edmonstone — Fri, 27 Jul 2007 09:19:55 GMT

Hi all,

TRying to figure out a quick and easy solution to the following problem...

I have two sets of measureware data from 2 systems in different timezones (GMT0BST and EST5DST). Both sets of data looks like this:

e.g. UK data:

07/27/07 09:00 27.2
07/27/07 09:05 22.4

etc.

US data:

07/27/07 04:00 16.5
07/27/07 04:05 18.1

Now I need to take the two sets and combine the results together, but *taking into account the timezone differences* - so I need to add 5 hours onto the ESTDST time to match up with GMTBST and then match up the data with the data from the same time on the other system. I end up with data looking like this:

e.g.

07/27/07 09:00 27.2 16.5
07/27/07 09:05 22.4 18.1

unfortunately there may also be inconsistent gaps in the data (where systems have been rebooted)

Any ideas on how to do a quick timezone conversion and then match up each data item?

Thx

Duncan

Re: text manipulation challenge

Sandman! — Fri, 27 Jul 2007 09:54:33 GMT

Not sure if this can be done but how about getting the measureware output from both systems relative to a timezone? For example you could use UTC as the baseline for both systems and then have measureware output the data you want. This atleast will take care of the date and time conversion. Afterwards the task should be a simple shell or awk script.

Re: text manipulation challenge

Duncan Edmonstone — Fri, 27 Jul 2007 09:56:39 GMT

Sandman,

I knew I should have mentioned this in my original post:

I don't have access to the original systems!

That was my first idea, but it took an age to get the data in the first place...

HTH

Duncan

Re: text manipulation challenge

Chris Wilshaw — Fri, 27 Jul 2007 10:34:31 GMT

Hi Duncan,

Interesting little problem - and a sneaky awk solution;

#!/usr/bin/sh

awk -F"[ :]" '{printf "%s %.2d:%s %s\n",$1,$2+5,$3,$4}' us.txt > us.tmp

for LINE in `cat uk.txt | sed -e s/" "/_/g`
do
DATE_TIME=`echo $LINE | awk -F_ '{print $1" "$2}'`
METRIC=`echo $LINE | awk -F_ '{print $3}'`
METRIC2=`grep "$DATE_TIME" us.tmp | cut -c16-19`
echo "$DATE_TIME $METRIC $METRIC2"
done

It seems to work with your sample data - and should cope with missing bits to some extent (if the US part is missing, you'll just end up with the date/time and uk metric, if the UK part is missing, you get nothing)

I know the replacement of a space with an _ for the loop isn't the best way of doing it, but couldn't seem to get it to work setting the IFS variable.

Chris

Re: text manipulation challenge

Chris Wilshaw — Fri, 27 Jul 2007 10:39:13 GMT

Forgot to mention, I'd put the data into uk.txt and us.txt files for convenience, and although it's hard to see, there's a space before the : in the awk field separator brackets.

And just realised another problem - silly me, I've added 5 to whatever figure you have in the time field, so you could end up with 28:00 hours.

Oh well, it was worth a try.

Re: text manipulation challenge

James R. Ferguson — Fri, 27 Jul 2007 10:40:07 GMT

Hi Duncan:

Perl will handle all of this:

# cat .compareit
#!/usr/bin/perl

use strict;
use warnings;
use POSIX qw(strftime);
use Time::Local;

my $localdata = shift or die "LocalData expected\n";
my $gmtdata = shift or die "GMT Data expected\n";

my ( $mon, $mday, $year, $hours, $min, $metric );
my ( $time, $gmt );
my %results;

open( LD, "<", $localdata ) or die "Can't open '$localdata': $!\n";
while () {
( $mon, $mday, $year, $hours, $min, $metric )
= m{(\d\d)/(\d\d)/(\d\d)\s+(\d\d):(\d\d)\s+(.+)};
$time = timelocal( 0, $min, $hours, $mday, $mon - 1, $year );
$gmt = strftime "%m/%d/%y %H:%M", gmtime($time);
$results{$gmt} = $metric;
}
close LD;
open( LD, "<", $localdata ) or die "Can't open '$localdata': $!\n";
while () {
( $mon, $mday, $year, $hours, $min, $metric )
= m{(\d\d)/(\d\d)/(\d\d)\s+(\d\d):(\d\d)\s+(.+)};
$time = timelocal( 0, $min, $hours, $mday, $mon - 1, $year );
$gmt = strftime "%m/%d/%y %H:%M", gmtime($time);
$results{$gmt} = $metric;
}
close LD;

open( GD, "<", $gmtdata ) or die "Can't open '$gmtdata': $!\n";
while () {
( $mon, $mday, $year, $hours, $min, $metric )
= m{(\d\d)/(\d\d)/(\d\d)\s+(\d\d):(\d\d)\s+(.+)};
$time = timegm( 0, $min, $hours, $mday, $mon - 1, $year );
$gmt = strftime "%m/%d/%y %H:%M", gmtime($time);
$results{$gmt} .= " $metric";
}

for my $key ( sort keys %results ) {
print join " ", $key, $results{$key}, "\n";
}
1;

...run as passing your two files:

# ./compareit localtimedata gmttimedata

I rather quickly cobbled this together and it could be make a bit cleaner, but it should server your purpose.

Regards!

...JRF...

Re: text manipulation challenge

Duncan Edmonstone — Fri, 27 Jul 2007 10:55:35 GMT

James,

Thx - that did the trick - one quick question to get that extra point though - is the final output in GMT or EST? (My per is sooo rusty now, I can hardly even read the syntax any more!)

Duncan

Re: text manipulation challenge

James R. Ferguson — Fri, 27 Jul 2007 10:58:23 GMT

Hi (again) Duncan:

Yes, I should have mentioned that the final output is in GMT (UTC) units. I felt that that was the most appropriate units.

Regards!

...JRF...

Re: text manipulation challenge

Sandman! — Fri, 27 Jul 2007 13:45:44 GMT

You could use the source code pasted below to convert the US ESTDST date/time stamps into UTC; then concatenate the GMT & UTC files using the awk script as:

-- compile the source code pasted below
cc mysrcode.c -o mysrcode

-- generate UTC date/time stamps from the EST date/time stamp file
mysrcode UTC_file

-- run awk script to concatenate the files

awk '{
if (x[$1" "$2])
x[$1" "$2]=x[$1" "$2]" "$NF
else
x[$1" "$2]=$0
}END{for(i in x) print x[i]}' GMT_file UTC_file

====================================================================
#include
#include

int main(int argc, char **argv)
{
int mo, day, yr, hr, min;
time_t tloc, *ptloc = &tloc;
char buf[BUFSIZ]; struct tm *tmptr; float var;

while (gets(buf) != NULL)
{
sscanf(buf,"%2d%*c%2d%*c%2d%2d%*c%2d%f",&mo,&day,&yr,&hr,&min,&var);
time(ptloc); tmptr = gmtime(ptloc);
tmptr->tm_year = yr; tmptr->tm_mon = mo;
tmptr->tm_mday = day; tmptr->tm_hour = hr + 5;
tmptr->tm_min = min; mktime(tmptr);
printf("%.2d/%.2d/%.2d %.2d:%.2d %.1f\n",tmptr->tm_mon,tmptr->tm_mday,
tmptr->tm_year,tmptr->tm_hour,tmptr->tm_min, var);
}
}

Re: text manipulation challenge

James R. Ferguson — Fri, 27 Jul 2007 14:52:18 GMT

Hi (again) Duncan:

My post inadvertantly pasted a block of code twice (although it did work).

Here's a faster version that supports dates and times without leading zeros. Thus:

7/7/07 7:01 27.2

...is equivalent to:

07/07/07 07:01 27.2

Moreover, the output clearly shows where a sample on one system or the other is missing. You might see:

07/27/07 14:59 - GMT_metric
07/27/07 15:00 localmetric GMT_metric
07/27/07 15:01 localmetric

# cat .compareit
#!/usr/bin/perl
#!/usr/bin/perl

use strict;
use warnings;
use POSIX qw(strftime);
use Time::Local;

my $localdata = shift or die "LocalData expected\n";
my $gmtdata = shift or die "GMT Data expected\n";

my ( $mon, $mday, $year, $hours, $min, $metric );
my ( $time, $gmt );
my %results;

open( LD, "<", $localdata ) or die "Can't open '$localdata': $!\n";
while () {
( $mon, $mday, $year, $hours, $min, $metric )
= m{(\d\d?)/(\d\d?)/(\d\d)\s+(\d\d):(\d\d)\s+(.+)};
$time = timelocal( 0, $min, $hours, $mday, $mon - 1, $year );
$gmt = strftime "%m/%d/%y %H:%M", gmtime($time);
$results{$gmt} = $metric;
}
close LD;

open( GD, "<", $gmtdata ) or die "Can't open '$gmtdata': $!\n";
while () {
( $mon, $mday, $year, $hours, $min, $metric )
= m{(\d\d?)/(\d\d?)/(\d\d)\s+(\d\d?):(\d\d)\s+(.+)};
$gmt = sprintf "%02d/%02d/%02d %02d:%02d", $mon, $mday, $year, $hours,
$min;
if ( exists $results{$gmt} ) {
$results{$gmt} .= " " . $metric;
}
else {
$results{$gmt} = " - " . $metric;
}

}
close GD;

for my $key ( sort keys %results ) {
printf "%s %-s\n", $key, $results{$key};
}
1;

...as before, run as:

# .compareit local_time_data gm_time_data

Regards!

...JRF...

Re: text manipulation challenge

Duncan Edmonstone — Sat, 28 Jul 2007 11:47:02 GMT

Thx all