Finding averages of cost over time interval-CodePudding

How do I please add every half hour up, find an average of the cost and make it a new hour. For example, I'd add up (the 1:30:00 and 2:00:00 costs)/2 and make the new cost 2:00:00. (2:30:00 and 3:00:00)/2 and make it 3:00:00, so on and so on......

I've tried

df1 = vcost.rolling(2).mean() 
df1 = vcost.iloc[::2, :]

though it reduces it to 8760 rows, it doesn't average it into a new hour. basically, I want to get rid of all the half-hourly intervals by averaging the cost and making the hour the new cost. Ending up with 8760 rows. Thanks

vcost = pd.read_excel('/content/drive/MyDrive/Diss/cleanedInputData/2019agile.xlsx')
vcost

    Time    Cost
0   2019-01-01 01:00:00 13.832
1   2019-01-01 01:30:00 12.348
2   2019-01-01 02:00:00 10.500
3   2019-01-01 02:30:00 10.416
4   2019-01-01 03:00:00 10.248
... ... ...
17514   2019-12-31 22:00:00 7.707
17515   2019-12-31 22:30:00 6.867
17516   2019-12-31 23:00:00 7.560
17517   2019-12-31 23:30:00 8.190
17518   2020-01-01 00:00:00 9.030

CodePudding user response：

Try this:

df.resample('30min', on='Time').mean()

CodePudding user response：

There are many ways to get your result, one of them (I'm not sure it's an optimized solution) is using FOR loops as follows:

for i in range(17518):
    if (i%2)==0:
        vcost_new.loc[i/2,'Time']=vcost.loc[i,'Time']
        vcost_new.loc[i/2,'Cost']=vcost.loc[i,'Cost']