I have many users. Each time a user uses their smartphone it register them. I am determining the last time each user used their smartphone each day. Additionally smartphone usages from 18:00 to 06:00, the next day, should be considered as an entry on the previous day. I have created a dummy example.

I did the following:

  1. First subtract the number of hours.
  2. Sort the data frame based on user and date time.
  3. Get the last row.

Is there a more efficient approach to this? Are there other tips I can follow to improve my code?

df_example = {'id': [1,1,1,1,1],
             'activity': [datetime.datetime(2019, 12, 1, 19, 30, 1),
                         datetime.datetime(2019, 12, 1, 20, 22, 2),
                         datetime.datetime(2019, 12, 2, 2, 13, 2),
                         datetime.datetime(2019, 12, 3, 19, 12, 2),
                         datetime.datetime(2019, 12, 3, 21, 3, 1)
df_example = pd.DataFrame(df_example, columns = ['id', 'activity'])
df_example['activity'] = df_example['activity'] - datetime.timedelta(hours=6, minutes=0)
df_example['date'] = df_example['activity'].apply(lambda x: x.date())
df_example.sort_values(by=['id', 'activity'])
df_example.groupby(['id', 'date']).tail(1)

1 Answer 1


Instead of using tail, if you only need one item there are the first and last methods, which do exactly what you think they would do with grouped dataframes:

df_example.groupby(['id', 'date']).last()

I doubt there is a faster way to create the activity column. And you have to create a new one because of your requirement with what counts to which day.

But you can speed up getting the date. Using apply with a lambda is about the second slowest way to work with pandas (manual Python for loops are slower). Instead use the vectorized datetime functions:

df_example['date'] = df_example['activity'].dt.date

Potential bug: Note that df_example.sort_values(by=['id', 'activity']) returns the sorted dataframe, it does not modify it inplace. Either assign it back to df_example, or use inplace=True.

The same is true for groupby, you probably want to assign the result to a variable as well in order to do something else with it afterwards.

Python has an official style-guide, PEP8. While it recommends using spaces around the = operator when using it for assignment, it recommends using no spaces when using it for keyword arguments.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.