CODEWITHSUNDEEP
pythonpandas
martba17
martba17

Reputation: 23

Best way to group datetime into days but done based on id

I have a dataframe that is similar to the one below:

time                                value1    value2    id
2020-07-20 00:00:00.621000+00:00    104       5         car1
2020-07-20 00:00:04.622000+00:00    105       10        car1
2020-07-21 00:00:09.341000+00:00    98        3         car2
2020-07-21 00:00:03.322000+00:00    23        9         car2
2020-07-22 00:00:00.201000+00:00    90        4         car3
2020-07-22 00:00:07.922000+00:00    49        7         car3

Is there a way to group these time datetime values into days, but that would be respective to the id? I am trying to find the average of these values.

So something like this:

time        value1    value2    id
2020-07-20  104.5     5         car1
2020-07-21  60.5      6         car2
2020-07-22  69.5      6.5      car3

Upvotes: 0

Views: 30

Answers (2)

wwnde
wwnde

Reputation: 26676

coerce time to datetime in format of YYYY-MM-DD and use it in the groupby

df.groupby([(pd.to_datetime(df['time']).dt.strftime('%Y-%M-%d')),'id']).mean().reset_index()


        time    id  value1  value2
0  2020-00-20  car1   104.5     7.5
1  2020-00-21  car2    60.5     6.0
2  2020-00-22  car3    69.5     5.5

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150785

You can do:

df.groupby([df['time'].dt.normalize(), 'id']).mean().reset_index()

Output:

                       time    id  value1  value2
0 2020-07-20 00:00:00+00:00  car1   104.5     7.5
1 2020-07-21 00:00:00+00:00  car2    60.5     6.0
2 2020-07-22 00:00:00+00:00  car3    69.5     5.5

Upvotes: 1

Related Questions