How to aggregate pandas Dataframe by day

Question

I have got the following pandas DataFrame:

time,                value
2018-08-02 09:19:37, 2
2018-08-02 09:19:47, 3
2018-08-02 09:19:57, 6
......
2018-08-03 04:49:27, 2
2018-08-03 04:49:37, 4
2018-08-03 04:49:47, 5

I would like to build an output dataFrame as in the following:

time,                value
2018-08-02 11:59:59, AVG(2+3+6+..)
2018-08-03 11:59:59, AVG(2+4+5+..)

Please any help would very appreciated. Many Thanks.

Plafker

df.groupby(df.time.dt.date).value.mean() ?? – Cardiovascular

I tried but i get pandas.core.base.DataError: No numeric types to aggregate – Plafker

change the dtype of value, df.value=df.value.astype(int) and then try, check my answer for detailed explaination – Cardiovascular

Answer 1

You can use Resample

>>>df['time'] = df['time'].astype('datetime64[ns]')    
>>>df.resample('D', on='time').mean()
>>>        
time         value
2018-08-02  3.666667
2018-08-03  3.666667

Heterogynous

Answer 2

IIUC, use:

m=df.groupby(df.time.dt.date).value.mean().reset_index()
m.time=pd.to_datetime(m.time.astype(str)+' 11:59:59')
print(m)

                 time     value
0 2018-08-02 11:59:59  3.666667
1 2018-08-03 11:59:59  3.666667

Note : If the dtypes are string , use the below first before the above code:

df.time=pd.to_datetime(df.time)
df.value=df.value.astype(int)

Cardiovascular

Thank you very much. Before I vote. Please can you confirm that fact that your sol average all the value from 00:00:00 to 11:59:59? – Plafker

Sure. I will do it. Please can you confirm that fact that your sol average all the value from 00:00:00 to 11:59:59? – Plafker

@CarloAllocca this solution groups on the date and adds 11:59:59 as a string to each output. then converts to datetime so each row in time col will have 11:59:59 – Cardiovascular

so it does it automatically as it consider all the values within the same date. Thanks. – Plafker

@CarloAllocca yes. :) group on just the date and append the max time of the day to the time col – Cardiovascular