cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ToreVingare
Frequent Visitor

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! 

5 REPLIES 5
ToreVingare
Frequent Visitor

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

ziying35
Impactful Individual
Impactful Individual

@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
ziying35
Impactful Individual
Impactful Individual

Hi, @ToreVingare 

Do your simulated surgery time and post-surgical treatment time overlap?

Rocco_sprmnt21
Super User
Super User

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:

 

image.png

 

try this

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

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors