1

I have a three-column table of data of the following form:

TIME         MPID    CPID
14:00:04.909 10048  370007
14:00:05.320 10048  370007
14:00:05.462 10048  370008
14:00:05.761 10048  370008
14:00:05.809 10048  370009
14:00:05.833 10048  370009
14:00:11.320 10048  370010
14:00:11.453 10048  370010
14:00:11.693 10048  370011
14:00:13.097 10048  370012
14:00:14.124 10048  370012

Here the TIME column consists of timestamps of the form HH:MM:SS.SSS The MPID and CPID columns are identification numbers; what they mean is not important for my question. The MPID values also don't play a role, except for the fact that they're present in the data set and need to be propagated to the output.

What I want is to identify pairs of rows which have matching CPID values and compute the difference between their corresponding times. So, for example, there are two rows (the third and the fourth) in the above example with CPID 77846. The corresponding times are 14:00:05.320 and 14:00:05.589, so I want to compute the difference: 14:00:04.909 - 14:00:05.320 = 00:00:00.589

I would also like to output this result in the following format:

MPID 10051 CPID 77846 Total time difference: 589 mili seconds

If a given CPID does not occur exactly twice in the data set then I want to ignore it.

The desired output for the given example data should look like this:

MPID 10051 CPID 77845 Total time difference: 1400 milli seconds
MPID 10051 CPID 77846 Total time difference: 1300  milli seconds
MPID 10051 CPID 77847 Total time difference: 800 milli seconds
MPID 10051 CPID 77848 Total time difference: 1800 milli seconds
MPID 10051 CPID 77849 Total time difference: 1900 milli seconds

Currently using Script:

uniq -D -f 2 "${1}" |
while read a b c && read d e f ; do
    g=$(( $(date -d $d +%s) - $(date -d $a +%s) ))
    printf "MPID %s CPID %s Total time difference: %02i seconds\n" $b $c $g
done

Output Giving

MPID 10051 CPID 77845 Total time difference: 00 seconds
MPID 10051 CPID 77846 Total time difference: 03 seconds
MPID 10051 CPID 77847 Total time difference: 12 seconds
MPID 10051 CPID 77848 Total time difference: 15 seconds
MPID 10051 CPID 77849 Total time difference: 19 seconds
2
  • for example, there are two rows (the third and the fourth) in the above example with CPID 77846; but there is no row with CPID: 77846 and what is the issue with your script? Aug 9 '19 at 4:38
  • 14:00:04.909 10048 370007 14:00:05.320 10048 370007 14:00:05.462 10048 370008 14:00:05.809 10048 370009 14:00:05.833 10048 370009 in above example CPID 370008 one row is missing , then script will ignore this & go to next . Aug 9 '19 at 4:47
0

I adjusted your sample data by adding three lines with an identical CPID that being 370013, to show that these are rejected, in accordance with the "occur exactly twice" requirement. I also added two more lines with CPID 370014 that are a pair we want:

TIME         MPID    CPID
14:00:04.909 10048  370007
14:00:05.320 10048  370007
14:00:05.462 10048  370008
14:00:05.761 10048  370008
14:00:05.809 10048  370009
14:00:05.833 10048  370009
14:00:11.320 10048  370010
14:00:11.453 10048  370010
14:00:11.693 10048  370011
14:00:13.097 10048  370012
14:00:14.124 10048  370012
14:00:14.189 10048  370013
14:00:14.320 10048  370013
14:00:15.020 10048  370013
14:00:16.123 10048  370014
14:00:16.790 10048  370014

Run:

$ txr data.txr data
MPID 10048 CPID 370007 Total time difference: 411 mili seconds
MPID 10048 CPID 370008 Total time difference: 299 mili seconds
MPID 10048 CPID 370009 Total time difference: 24 mili seconds
MPID 10048 CPID 370010 Total time difference: 133 mili seconds
MPID 10048 CPID 370012 Total time difference: 1027 mili seconds
MPID 10048 CPID 370014 Total time difference: 667 mili seconds

The single 370011 entry is not represented, and neither is the triplicated 370013.

Code:

@(do (defun mk-time-ms (date ms)
       (let ((tsec (time-parse-utc "%H:%M:%S" date)))
         (+ (* tsec 1000) ms))))
TIME         MPID    CPID
@(repeat)
@d0.@ms0 @mpid @cpid
@d1.@ms1 @mpid @cpid
@  (collect :gap 0)
@extra @mpid @cpid
@  (end)
@  (do (unless (boundp 'extra)
         (let ((t0 (mk-time-ms d0 (toint ms0)))
               (t1 (mk-time-ms d1 (toint ms1))))
           (put-line `MPID @mpid CPID @cpid Total time difference: @(- t1 t0) mili seconds`))))
@(end)

mk-time-ms is a function for parsing the date into an integer (time since the Unix Epoch), and combining it with the milliseconds value. We take the seconds time, multiply by 1000 and add the milliseconds.

We literally match the header lines:

TIME         MPID    CPID

then begin a @(repeat) match. We are looking for sequences of lines that begin with two consecutive lines that have the same cpid (and mpid). With an additional @(collect) we match an additional zero or more lines zero or more lines with the same mpid or cpid. From these, we collect the list of times as the extra variable. For each match, if the extra variable has not been bound in the pattern match, it means we have matched exactly two lines, and no extras: in that case we calculate the time differences and produce the required output.

@(repeat) skips any line where no match takes place, which takes care of the singles. Because @(collect) likewise, by default, skips non-matching lines, we have to make it strict with :gap 0: don't allow any gaps. Otherwise it will consume the entire data, leaving the outer @(repeat) with nothing.

If you don't actually have any triplicates in the data, only pairs or singletons, this can be:

@(do (defun mk-time-ms (date ms)
       (let ((tsec (time-parse-utc "%H:%M:%S" date)))
         (+ (* tsec 1000) ms))))
TIME         MPID    CPID
@(repeat)
@d0.@ms0 @mpid @cpid
@d1.@ms1 @mpid @cpid
@  (do (let ((t0 (mk-time-ms d0 (toint ms0)))
             (t1 (mk-time-ms d1 (toint ms1))))
         (put-line `MPID @mpid CPID @cpid Total time difference: @(- t1 t0) mili seconds`)))
@(end)

For our examaple data, this now includes this output:

MPID 10048 CPID 370013 Total time difference: 131 mili seconds

The first two 370013 lines are diffed, and consumed. The next 370013 looks like a singleton and is skipped. If we wanted to include both differences, we could make the following modification, consisting of adding the line @(trailer) in the right place:

@(do (defun mk-time-ms (date ms)
       (let ((tsec (time-parse-utc "%H:%M:%S" date)))
         (+ (* tsec 1000) ms))))
TIME         MPID    CPID
@(repeat)
@d0.@ms0 @mpid @cpid
@  (trailer)
@d1.@ms1 @mpid @cpid
@  (do (let ((t0 (mk-time-ms d0 (toint ms0)))
             (t1 (mk-time-ms d1 (toint ms1))))
         (put-line `MPID @mpid CPID @cpid Total time difference: @(- t1 t0) mili seconds`)))
@(end)

Now these lines appear in the output:

MPID 10048 CPID 370013 Total time difference: 131 mili seconds
MPID 10048 CPID 370013 Total time difference: 700 mili seconds

the difference between the first and second 370013 and the second and third.

@(trailer) means that whatever follows is a trailing context: it is matched, but not consumed. Thus the @(repeat) now consumes only one line in spite of matching two, causing the next iteration of @(repeat) to move ahead by one line even if it matched two lines and produced a time difference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.