Calculate mean for all the columns every 12 hours

Question

I have a dataframe and i want to calculate every 12 hours mean for all the columns. dataframe has more than 200k rows.

          DateTime Speed   TRQ         ...    PtoP3  RMS3   Crest3
0       2016-07-01 00:00   994  35.4   ...       NA    NA       NA
1       2016-07-01 00:01   995  34.6   ...       NA    NA       NA
2       2016-07-01 00:02   995    34   ...       NA    NA       NA

i wrote this

Present_data.to_datetime(Present_data['DateTime'])
Total_12hravg_all = Present_data.groupby(pd.Grouper(freq='12H', key='DateTime')).mean()
print(Total_12hravg_all)

and got this error

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'

First try `Present_data['DateTime'] = pd.DatetimeIndex(Present_data['DateTime'])` — Alexander, Sep 19 '18 at 07:21
@Alexhttps://stackoverflow.com/users/2411802/alexanderander Still got the same error — sharma_re, Sep 19 '18 at 07:24

jezrael · Accepted Answer · 2018-09-19T08:47:20.003

1

If Datetime is column:

Your solution shoud be working nice:

Present_data['DateTime'] = pd.to_datetime(Present_data['DateTime'])
Total_12hravg_all = Present_data.groupby(pd.Grouper(freq='12H', key='DateTime')).mean()

Alternative solution is use resample with parameter on:

Present_data['DateTime'] = pd.to_datetime(Present_data['DateTime'])
Total_12hravg_all = Present_data.resample('12H', on='DateTime').mean()

Or create DatetimeIndex:

Present_data['DateTime'] = pd.to_datetime(Present_data['DateTime'])
Present_data = Present_data.set_index('DateTime')
Total_12hravg_all = Present_data.groupby(pd.Grouper(freq='12H')).mean()
#resample
#Total_12hravg_all = Present_data.resample('12H').mean()

If Datetime is index:

Present_data.index = pd.to_datetime(Present_data.index)

Total_12hravg_all = Present_data.groupby(pd.Grouper(freq='12H')).mean()
#resample
#Total_12hravg_all = Present_data.resample('12H').mean()

Final solution:

Present_data['DateTime'] = pd.to_datetime(Present_data['DateTime'])
Present_data = Present_data.set_index('DateTime')

#convert non numeri values to NaNs
Present_data = Present_data.apply(lambda x: pd.to_numeric(x, errors='coerce'))

Total_12hravg_all = Present_data.groupby(pd.Grouper(freq='12H')).mean()
#resample
#Total_12hravg_all = Present_data.resample('12H').mean()

edited Sep 19 '18 at 08:47

answered Sep 19 '18 at 07:46

jezrael

822,522
95
1,334
1,252

i have a dataframe Total_12hravg_all in which there are two columns PtoP3 and RMS3 and i need to replace RMS3 Values to nan if PtoP3 contains Nan for the same row. how can i do that? – sharma_re Sep 20 '18 at 05:11
@prashantsharma - Use `Present_data.loc[Present_data['PtoP3'].isnull(), 'RMS3'] = np.nan` – jezrael Sep 20 '18 at 05:12
how can i delete whole dataframe and some variable ?? will del work ?? – sharma_re Sep 20 '18 at 05:49
@prashantsharma - better is `drop` - https://stackoverflow.com/a/18145399 – jezrael Sep 20 '18 at 05:50
i want to change all of the columns of my dataframe to -1 wherever nan is present. just removing RMS3 from the above statement will work? – sharma_re Sep 20 '18 at 08:39
and i aso want to change my whole column names to be small letter – sharma_re Sep 20 '18 at 08:41
@prashantsharma - Please try create new question. – jezrael Sep 20 '18 at 10:21

Calculate mean for all the columns every 12 hours

1 Answers1