How to transform a table

[i111040d]

I have this table:

_time,id,src,dst
9:00,x,A,B
9:01,x,B,C
9:02,y,C,B
9:03,z,B,C
9:04,y,B,A
9:05,z,C,D

I wanna create this table:

_time,id,A-B,B-C,C-D
9:00,x,9:00,9:01,
9:02,y,9:04,9:02,
9:03,z,,9:03,9:05

How can I transform the table?

[somesoni2]

Try this

your current search giving _time id src dst | eval temp=src."-".dst."#".strftime(_time,"%H:%M")
| stats first(_time) as _time values(temp) as temp by id 
| mvexpand temp | rex field=temp "(?<direction>.+)#(?<time>.+)"
| eval temp=_time."#".id | chart values(time) over temp by direction | rex field=temp "(?<_time>.+)#(?<id>.+)" | fields - temp

Update#1
Thanks @woodcock for clarifying the requirement. This should take care of normalization as well.

 your current search giving _time id src dst | eval Nodes=mvsort(split(src."-".dst,"-")) | eval temp=mvindex(Nodes,0)."-".mvindex(Nodes,1)."#"._time | stats first(_time) as _time values(temp) as temp by id 
 | mvexpand temp | rex field=temp "(?<direction>.+)#(?<time>.+)"  | eval temp=_time."#".id | chart values(time) over temp by direction | rex field=temp "(?<_time>.+)#(?<id>.+)" | fields - temp| table _time id *

[woodcock]

Yours does not normalize B-A to be A-B so the results are wrong:

_time   id  A-B B-A B-C C-B C-D
9:00    x   9:00        9:01         
9:02    y       9:04        9:02     
9:03    z           9:03        9:05

Also, you must change strftime(_time, "%H,%M") to just _time. Use this to spoof test events:

|noop|stats count AS trash|eval trash="9:00,x,A,B::9:01,x,B,C::9:02,y,C,B::9:03,z,B,C::9:04,y,B,A::9:05,z,C,D"
|makemv delim="::" trash|mvexpand trash
| rex field=trash "(?<_time>.*?),(?<id>.*?),(?<src>.*?),(?<dst>.*)" | table _time id src dst
| rename DesiredResults AS "
_time,id,A-B,B-C,C-D
9:00,x,9:00,9:01,
9:02,y,9:04,9:02,
9:03,z,,9:03,9:05"

[somesoni2]

Not sure if that was the requirement (normalization). Would agree on strftime change if event though the field name is _time, use is storing string formatted time in it.

[woodcock]

Normalization is specified in the desired results given.

[somesoni2]

I did miss that. The answer updated to take care of both.

[woodcock]

Add this:

| rex field=_time mode=sed "s/://"
| eval transition = src . "-" . dst . "::" . dst . "-" . src | fields - src dst
| makemv delim="::" transition | mvexpand transition
| stats values(transition) AS transition BY _time id | eval transition = mvindex(transition, 0)
| chart first(_time) AS _time BY id transition
| eval time="9999" | foreach * [ eval time = min(time, $<<FIELD>>$) | rex field="<<FIELD>>" mode=sed "s/(\d{1,2})(\d{2})/\1:\2/" ]
| rex field=time mode=sed "s/(\d{1,2})(\d{2})/\1:\2/"
| table time id *