CODEWITHSUNDEEP
bashsedawk
Paralife
Paralife

Reputation: 6236

In bash/sed how to merge 2 lines with a common field into one line

I have a file of the form 

KEY-A;START;<datetime>
KEY-B;START;<datetime>
KEY-B;END;<datetime>
KEY-A;END;<datetime>
.
.
.

and I would like some sed/awk/bash magic to transform it to

KEY-A;<datetime>;<datetime>;<duration in secs>
KEY-B;<datetime>;<datetime>;<duration in secs>
.
.
.

where the first <datetime> is the START and the second is the END. The datetimes are parseable by the date commmand.

I know it is two different questions( the merging and the duration calculation), I am most interested in the merging mainly.

EDIT: I can sort the file by the key field and then by datetime with the sort command if this eases the way merging can be done.

Thanks.

Upvotes: 0

Views: 95

Answers (2)

glenn jackman
glenn jackman

Reputation: 246807

The skeleton answer is

awk -F';' -v OFS=';' '
    function diff(strt, end) {
        # do stuff
    }
    $2 == "START" {start[$1]=$3; next} 
    {print $1, start[$1], $3, diff(start[$1],$3)}
'

This will print "KEY-B" first because it ends first. Pipe the answer through sort if you want.

Upvotes: 1

fedorqui
fedorqui

Reputation: 289725

Something like this can make it:

awk 'BEGIN{FS=OFS=";"}
     /START/{b[$1];a[$1,"st"]=$3}
     /END/{a[$1,"end"]=$3}
     END{for (i in b) print i, a[i,"st"], a[i,"end"], a[i,"end"]-a[i,"st"]}' file

For example given this file

$ cat a
key-a;START;1391521821
key-b;START;1391511821
key-a;END;1391521221
key-b;END;1391521831

It returns:

$ awk 'BEGIN{FS=OFS=";"} /START/{b[$1];a[$1,"st"]=$3} /END/{a[$1,"end"]=$3} END{for (i in b) print i, a[i,"st"], a[i,"end"], a[i,"end"]-a[i,"st"]}' a
key-a;1391521821;1391521221;-600
key-b;1391511821;1391521831;10010

Upvotes: 1

Related Questions