Forum Discussion

ToreVingare's avatar
ToreVingare
Frequent Visitor
5 years ago

Stop row based on next row, process mining

Hello!

 

I'm working on a process mining dashboard. In this dashboard I'm mapping a patient journey in our hospital. The process looks like this. Patient arrives at a ward -> Is rushed to surgery -> Needs post-surgical treatment (and or) => Intensive care -> Back to ward.

 

The process mining works great for all steps except the ward stay. The reason for this is that when surgery is over post-surgical treatment starts and when post-surgical treatment stop the intensive care takes over. But because the patient is rushed to surgery the ward stay continues parallel with the other events. 

 

Here is some example data. ID 1 arrives at avd 1 (ward) 10:00, the surgery starts at 10:31 but as you can see the ward stay continues even if the patient no longer is at the bed. After surgery he arrives at post-op (post surgical treatment) 12:51 and the intensive care 18:45.

IDStartEndLocation
12019-02-04 10:00                 2019-02-05 18:04                 Avd 1
12019-02-04 10:31                2019-02-04 13:01Surgery 1
12019-02-04 12:51                2019-02-04 18:45Post-op                 
12019-02-04 18:452019-02-04 19:45IVA
22019-03-11 18:392019-03-13 10:27Avd 1
22019-03-11 18:452019-03-11 21:00Surgery 1
22019-03-11 20:332019-03-12 14:49Post-op
22019-03-13 10:282019-03-30 21:36Avd 2
32019-10-31 10:422019-11-01 19:01Avd 1
32019-10-31 10:482019-10-31 12:15Surgery 1
32019-10-31 12:072019-10-31 15:29Post-op

 

Here is what I would like my data to look like:

IDStartEndLocation
12019-02-04 10:002019-02-04 10:31Avd 1
12019-02-04 10:312019-02-04 13:01Surgery 1
12019-02-04 12:512019-02-04 18:45Post-op
12019-02-04 18:452019-02-04 19:45IVA
12019-02-04 19:45                2019-02-05 18:04                  Avd 1                         
    
22019-03-11 18:392019-03-11 18:45Avd 1
22019-03-11 18:452019-03-11 21:00Surgery 1
22019-03-11 20:332019-03-12 14:49Post-op
22019-03-12 14:492019-03-13 10:27Avd 1
22019-03-13 10:282019-03-30 21:36Avd 2
    
32019-10-31 10:422019-10-31 10:48Avd 1
32019-10-31 10:482019-10-31 12:15Surgery 1
32019-10-31 12:072019-10-31 15:29Post-op
32019-10-31 15:292019-11-01 19:01Avd 1

 

Back to ID 1. The ward stay starts as before at 10:00 but is halted at 10:31 when the surgery starts. Then when the other two events is over the ward stay is resumed with a new row and start time is the end time of IVA (intensive care).

 

Is this possible?

 

Thank you so much in advanced! 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZG9DsIwDIRfJercSLaT0MRbRzYkJJaqG4ixqPxIvD1JW7VpUiDjxd/ZZzdNgUVZEKCTQBK0QGAAkb64xgi07Cs3aurXWWDRlluuCn+Z+hLFEKjjs79e+vcXH2Lzz8eyNl46dPeH7G75mFu2ExNLbpT2p3ogaP5WEjEQysWSChmpWi0hZ6Iug0Tot52FTjjyy1OxRAI1a7eEzKlxHBtJCkI3tZsmpIGZbRGkP5Bn9GyDKAHDHoazLKk2GJtIxGiyVClHDFUiGaZVqvYD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t, End = _t, Location = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start", type datetime}, {"End", type datetime}, {"Location", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Ids", each splitWard(_)}}),
    #"Expanded Ids" = Table.ExpandTableColumn(#"Grouped Rows", "Ids", {"ID", "Start", "End", "Location"}, {"Ids.ID", "Ids.Start", "Ids.End", "Ids.Location"})
in
    #"Expanded Ids"

     

     

    the splitWard function:

     

    let
splitWard=(tab)=>
let
    minFoll=List.Min(List.Select(tab[Start], each _>tab{[Location="Avd 1"]}[Start])),
    maxPrec=List.Max(List.Select(tab[End], each _<tab{[Location="Avd 1"]}[End])),
    newTab=Table.ReplaceRows(tab, 0,1,{tab{0}&[End=minFoll], tab{0}&[Start=maxPrec]}),
    #"Sorted Rows" = Table.Sort(newTab,{{"Start", Order.Ascending}})
in
    #"Sorted Rows"
in splitWard

    a possible result:

     

     

    try this

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      this version of function splitWard is valid even if the original table is not well ordered (in case where the first row of group with same ID is not the "Avd 1" one)

       

       

      let

splitWard=(tab)=>
let
    minFoll=List.Min(List.Select(tab[Start], each _>tab{[Location="Avd 1"]}[Start])),
    maxPrec=List.Max(List.Select(tab[End], each _<tab{[Location="Avd 1"]}[End])),
    pos=List.PositionOf(tab[Location], "Avd 1"),
    newTab=Table.ReplaceRows(tab, pos,1,{tab{pos}&[End=minFoll], tab{pos}&[Start=maxPrec]}),
    #"Sorted Rows" = Table.Sort(newTab,{{"Start", Order.Ascending}})
in
    #"Sorted Rows"
in splitWard

       

       

  • ziying35's avatar
    ziying35
    Impactful Individual
    5 years ago

    ToreVingare 

    I have partially modified the raw data you provided, using the source data, which I think is fine, The following is the code I wrote, the code uses the List.Accumulate function, currently can handle the amount of data 8000 lines, if you form the amount of data more, you can modify my code into a custom function, and then you split the source data by 8000 lines of the base, and finally iterate through the List.Transform function on it!

    // output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("jdK9asMwFAXgVymaLbg/cmPfzZAMgQ4Fmyyhg2lKyWIHxwmE0nevVJektaSooO3ofkhH2n6o9VIJZqoe22FUogiw1EAaTIMg4JbK1Krb/c7yBgsBM2VP/Ws77vvObqjOuwdUn1lCZQyqNiMh9NT6NLy/DZe0TJJH5UJM7snP/XHU/SHl3mZ9twy56011NWluskZ0Jpe+aTN2DdEi0W1UDZ30O6Pba97tNizbaeagTA0aMWWy25A73bUIuQyN/Qr8GOyBrirPVATN6FRDnoqoAd2Lgf/H/rYbV/2z/mT0v3Yjsp1exORc6E67L18=", BinaryEncoding.Base64),Compression.Deflate))),
    rows = Table.ToRows(Source)&{{""}},
    acc = List.Accumulate(
               rows, 
               {{},"","",{},{}},
               (s,c)=>
               if s{1}<>c{0} then { if Text.StartsWith(s{4}?{3}? ??c{3}, "Avd") then s{0} 
                                    else s{0}&{{s{4}?{0}?, s{4}?{2}?, s{3}?{2}?, s{3}?{3}?  }}, c{0}, c{3}, c, c 
                                  } 
               else if Text.StartsWith(s{4}?{3}? ??c{3}, "Avd") then { s{0}&{{s{4}{0}, s{4}{1}, c{1}, s{4}{3}}}&{c}, c{0}, c{3}, s{3}, c } 
                    else if c{1}>s{3}{2} then { s{0}&{{s{4}{0}, s{4}{2}, s{3}{2}, s{3}{3}}}&{c}, c{0}, c{3}, s{3}, c }   
                         else   { s{0}&{c}, c{0}, c{3}, s{3}, c }
                    
          ),
    toTbl = Table.FromRows(acc{0}, Table.ColumnNames(Source)),
    chType = Table.TransformColumnTypes(toTbl,{{"Start", type datetime}, {"End", type datetime}})
in
    chType
  • ToreVingare's avatar
    ToreVingare
    Frequent Visitor
    5 years ago

    Hello! Thank you so much for your time. I will try the solutions on monday! I report back how it goes!