CODEWITHSUNDEEP
pythonpandasdataframe
White cow
White cow

Reputation: 85

Change the 30 minutes of data to hourly data

I have a 30 minutes interval of the data and I want to change them to houlry data. I have date, id and the value of the data in one data frame. For example, in the following data frame: I have value for id=1 at 8 and 8:30, so I sum the val (10+10) for this id and keep the first time. My time shoud be integer, 8,9,10,11,12,13,14 and ... For id=2, I have the value for 8:00 and 9:00, so will keep those two. For id= 3, I have three values for one day, and I have another value for another day. So, for the first day I sum the values of 8:00 and 8:30. and keep the values for 9:00 and for the next day, I chose it values. Also for the id=4 we have different times and we do same as before. Here is the df:

import pandas as pd
df = pd.DataFrame()
df['id'] = [1, 1, 2, 2, 
 3,3,3,3,4,4,4,4,4,4]
df['date'] = ['2012-02-10 
 8:00:00','2012-02-10             8:30:00','2012- 
 02-10 8:00:00','2012-02- 
 10 9:00:00','2012-02-10 
 8:00:00', '2012-02-10 
 8:30:00', '2012-02-10 
 9:00:00','2012-02-11 
 9:30:00','2012-02-11 
 9:00:00','2012-02-11 
 9:30:00','2012-02-11 
 10:00:00','2012-02-11 
 11:00:00','2012-02-11 
 11:30:00','2012-02-11 
 12:00:00' ]
df [ 'val'] = [10,10, 2, 
2, 3,3, 4, 5,1,2,3,4,5,6]

And here is the output:

id      date          val               
1    2012-02-10 8:00:00    20
2    2012-02-10 8:00:00    2
2    2012-02-10 9:00:00    2
3    2012-02-10 8:00:00    6
3    2012-02-10 9:00:00    4
3    2012-02-11 9:30:00    5
4    2012-02-11 9:00:00    3
4    2012-02-11 10:00:00    3
4    2012-02-11 11:00:00    9
4    2012-02-11 12:00:00    6

Upvotes: 0

Views: 219

Answers (1)

ouroboros1
ouroboros1

Reputation: 14184

You can achieve the result as follows.

  • First, let's use pd.to_datetime to turn df['date'] into a pandas datetime object.
  • Next, we apply Series.dt.floor to reduce each timestamp to full hours.
  • We want to combine the resulting series with id inside df.groupby to group our data.
  • Finally, we apply DataFrameGroupBy.agg to retrieve the first value from the original date column, and the sum from the column val.
out = df.copy()
out['date'] = pd.to_datetime(out['date'])
out = out.groupby(['id',out['date'].\
                   dt.floor(freq='H')], as_index=False)\
    .agg({'date':'first','val':'sum'})

print(out)

   id                date  val
0   1 2012-02-10 08:00:00   20
1   2 2012-02-10 08:00:00    2
2   2 2012-02-10 09:00:00    2
3   3 2012-02-10 08:00:00    6
4   3 2012-02-10 09:00:00    4
5   3 2012-02-11 09:30:00    5
6   4 2012-02-11 09:00:00    3
7   4 2012-02-11 10:00:00    3
8   4 2012-02-11 11:00:00    9
9   4 2012-02-11 12:00:00    6

Upvotes: 2

Related Questions