title: Create a dataframe containing time spent in location based on a dataframe with trip information in Python
body: <p>I have a dataframe <code>df</code> that records the movement of people from one location to another. I would like to create a new dataframe <code>df2</code> that records the time when a location was entered and when the same location was left, based on the trip information in <code>df</code>.</p>
  <pre><code>import pandas as pd
  import numpy as np
  
  current_time = '2022-05-05 17:00'
  
  df = pd.DataFrame({&quot;person_id&quot;: [&quot;A&quot;, &quot;B&quot;, &quot;A&quot;, &quot;C&quot;, &quot;A&quot;, &quot;C&quot;],
                     &quot;start_location_id&quot;: [1, 5, 2, 7, 3, 8],
                     &quot;end_location_id&quot;: [2, 6, 3, 8, 2, 9],
                     &quot;start_ts&quot;: pd.to_datetime([&quot;2022-05-05 00:00&quot;, &quot;2022-05-05 00:00&quot;, &quot;2022-05-05 05:00&quot;, &quot;2022-05-05 00:00&quot;, &quot;2022-05-05 13:00&quot;, &quot;2022-05-05 11:00&quot;]),
                     &quot;end_ts&quot;: pd.to_datetime([&quot;2022-05-05 02:00&quot;, &quot;2022-05-05 03:00&quot;, &quot;2022-05-05 10:00&quot;, &quot;2022-05-05 04:00&quot;, &quot;2022-05-05 16:00&quot;, &quot;2022-05-05  12:00&quot;]),
  })
  </code></pre>
  <p><code>df</code> would look like this:</p>
  <pre><code>print(df.to_string())
  &gt;&gt;
    person_id  start_location_id  end_location_id            start_ts              end_ts
  0         A                  1                2 2022-05-05 00:00:00 2022-05-05 02:00:00
  1         B                  5                6 2022-05-05 00:00:00 2022-05-05 03:00:00
  2         A                  2                3 2022-05-05 05:00:00 2022-05-05 10:00:00
  3         C                  7                8 2022-05-05 00:00:00 2022-05-05 04:00:00
  4         A                  3                2 2022-05-05 13:00:00 2022-05-05 16:00:00
  5         C                  8                9 2022-05-05 11:00:00 2022-05-05 12:00:00
  </code></pre>
  <p>I then create <code>df2</code> using the following nested for loops. I would like to replace the code that sets up <code>df2</code> with code that uses pandas' groupby (or equivalent) functions. My current attempt is as follows:</p>
  <pre><code>df2 = pd.DataFrame(columns = [&quot;person_id&quot;, &quot;location_id&quot;, &quot;enter_timestamp&quot;, &quot;exit_timestamp&quot;])
  
  for i in np.unique(df[&quot;person_id&quot;]):
      df3 = df.loc[df[&quot;person_id&quot;] == i].reset_index(drop = True)
      for j in df3.index:
          try:
              df2 = df2.append({'person_id': i,
                                'location_id' : df3.loc[j, &quot;end_location_id&quot;],
                                'enter_timestamp' : df3.loc[j, &quot;end_ts&quot;],
                                'exit_timestamp' : df3.loc[j + 1, &quot;start_ts&quot;]}, ignore_index=True)
          except:
              df2 = df2.append({'person_id': i,
                            'location_id' : df3.loc[j, &quot;end_location_id&quot;],
                            'enter_timestamp' : df3.loc[j, &quot;end_ts&quot;]}, ignore_index=True)
  </code></pre>
  <p>My desired output would be:</p>
  <pre><code>print(df2.to_string())
  &gt;&gt;
    person_id location_id     enter_timestamp      exit_timestamp
  0         A           2 2022-05-05 02:00:00 2022-05-05 05:00:00
  1         A           3 2022-05-05 10:00:00 2022-05-05 13:00:00
  2         A           2 2022-05-05 16:00:00                 NaT
  3         B           6 2022-05-05 03:00:00                 NaT
  4         C           8 2022-05-05 04:00:00 2022-05-05 11:00:00
  5         C           9 2022-05-05 12:00:00                 NaT
  </code></pre>
  <p>How can I achieve this? Thanks</p>