CODEWITHSUNDEEP
pythonpandasdataframe
Vincent
Vincent

Reputation: 183

Dataframe - find last time of the day, grouped by day

I have a simple dataframe with datetime and their date

df = pd.DataFrame( [['2021-01-01 10:10', '2021-01-01'],
                    ['2021-01-03 13:33', '2021-01-03'],
                    ['2021-01-03 14:44', '2021-01-03'],
                    ['2021-01-07 17:17', '2021-01-07'],
                    ['2021-01-07 07:07', '2021-01-07'],
                    ['2021-01-07 01:07', '2021-01-07'],
                    ['2021-01-09 09:09', '2021-01-09']],
                    columns=['datetime', 'date'])

I would like to create a new column containing the last datetime of each day. I have something quite close, but the last datetime of the day is only filled on the last datetime of the day... A weird NaT (Not a Time) is filled on all other cells. Can you suggest something better?

df['eod']=df.groupby('date')['datetime'].tail(1)

Upvotes: 0

Views: 31

Answers (2)

Chris
Chris

Reputation: 16147

You are probably looking for transform which will return the result to every row in the group.

df['eod'] = df.groupby('date').transform('last')

Output

           datetime        date               eod
0  2021-01-01 10:10  2021-01-01  2021-01-01 10:10
1  2021-01-03 13:33  2021-01-03  2021-01-03 14:44
2  2021-01-03 14:44  2021-01-03  2021-01-03 14:44
3  2021-01-07 17:17  2021-01-07  2021-01-07 01:07
4  2021-01-07 07:07  2021-01-07  2021-01-07 01:07
5  2021-01-07 01:07  2021-01-07  2021-01-07 01:07
6  2021-01-09 09:09  2021-01-09  2021-01-09 09:09

Upvotes: 1

ThePyGuy
ThePyGuy

Reputation: 18406

You don't really need another date column if the date part is coming from the datetime column. You can group by dt.day of the datetime column, then call last for the datetime value:

>>> df['datetime'] = pd.to_datetime(df['datetime'])
>>> df.groupby(df['datetime'].dt.day)['datetime'].last()

datetime
1   2021-01-01 10:10:00
3   2021-01-03 14:44:00
7   2021-01-07 01:07:00
9   2021-01-09 09:09:00
Name: datetime, dtype: datetime64[ns]

Upvotes: 0

Related Questions