How do I turn an event dataframe with "end" and "start" rows into a regrouped by event dataframe?

user_13618456 0
Viewed 197

I have a dataset of events sorted chronologically. I use pandas dataframes. This is what the dataframe looks like:

Time                         Event   Location    ID
2020-05-22 21:22:04.784622   start   UK          50
2020-05-22 21:43:07.060629   end     UK          50
2020-05-25 23:22:04.784622   start   UK          50
2020-05-25 23:43:07.060629   end     UK          50
2020-05-25 23:44:15.000566   start   US          30
2020-05-25 23:48:23.416348   start   Italy       70
2020-05-26 00:48:06.820164   end     US          30
2020-05-26 01:33:42.454450   end     Italy       70
2020-05-27 20:48:23.416348   start   Italy       30
2020-05-27 00:33:42.454450   end     Italy       30
etc

This is what I would like to make of it :

Start_Time                   End_Time                    Location    ID
2020-05-22 21:22:04.784622   2020-05-22 21:43:07.060629  UK          50
2020-05-25 23:22:04.784622   2020-05-25 23:43:07.060629  UK          50
2020-05-25 23:44:15.000566   2020-05-26 00:48:06.820164  US          30
2020-05-25 23:48:23.416348   2020-05-26 01:33:42.45445   Italy       70
2020-05-27 20:48:23.416348   2020-05-27 00:33:42.454450  Italy       30
etc

I've tried making separate dataframes (one for start, one for end) and merging them on Location and ID, but obviously it does not work. I've also looked at similar questions and wasn't able to figure it out from there. Would anyone have an idea of how I do that?

Edit : Also, there would be several events with same locations, or IDs in the dataframe. Edited the data in example to reflect my dataset more accurately

data-science pandas data-analysis datetime python
1 Answers

One way about it is to set index on the last three columns, and unstack the event column after.

df = pd.read_clipboard(sep='\s{2,}', engine='python', parse_dates=['Time'])

res = (df
       #appending Event,Location and ID with current index
       #prevents duplicate values when unstacking
       .set_index(['Event','Location','ID'], append=True)
       #get Event index as column
       .unstack('Event')
       #topmost column level redundant ... remove
       .droplevel(0,axis=1)
       #fill upwards on the end to align the dates to 
       #the appropriate positions
       .assign(end = lambda x: x['end'].bfill())
       .dropna()
       .add_suffix("_time")
       .reset_index()
       .drop("level_0", axis=1)
       .reindex(['start_time','end_time','Location','ID'], axis=1)
       .rename_axis(None,axis=1)
      )

res



          start_time                      end_time      Location    ID
0   2020-05-22 21:22:04.784622  2020-05-22 21:43:07.060629  UK      50
1   2020-05-25 23:22:04.784622  2020-05-25 23:43:07.060629  UK      50
2   2020-05-25 23:44:15.000566  2020-05-26 00:48:06.820164  US      30
3   2020-05-25 23:48:23.416348  2020-05-26 00:48:06.820164  Italy   70
4   2020-05-27 20:48:23.416348  2020-05-27 00:33:42.454450  Italy   30
user_71757130
Related