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
  • 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? – αғsнιη Aug 9 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 . – Vivek Nigam Aug 9 at 4:47

Your Answer

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

Browse other questions tagged or ask your own question.