CODEWITHSUNDEEP
pythonpandas
Ruben Alves
Ruben Alves

Reputation: 164

How to calculate certain values per hour using dataframes

i'm new at python and pandas library and i need help on solving this problem.

I've a dataframe that looks like this:

          timestamp       battery_level
0   2017-10-09 15:33:09   0.37
1   2017-10-09 15:36:17   0.38
2   2017-10-09 15:36:27   0.37
3   2017-10-09 15:38:08   0.38
4   2017-10-09 15:42:39   0.39
5   2017-10-09 15:45:30   0.40
6   2017-10-09 15:48:41   0.41
7   2017-10-09 15:51:52   0.42
8   2017-10-09 15:54:53   0.43
9   2017-10-09 15:57:54   0.44
10  2017-10-09 16:00:55   0.45
11  2017-10-09 16:05:47   0.46
12  2017-10-09 16:08:58   0.47
13  2017-10-09 16:12:09   0.48
14  2017-10-09 16:15:20   0.49
15  2017-10-09 16:18:11   0.50

How can i compute the timestamp difference and battery level per hour?

In this case i want to get something like this:

            timestamp_diff  battery_diff
15:00:00    00:24:45        0.07
16:00:00    00:17:26        0.05

Upvotes: 1

Views: 48

Answers (1)

jezrael
jezrael

Reputation: 862571

Use groupby by hours or floor for datetimes with diff and sum, rename is for avoid same columns names after reset_index:

#if necessary convert to datetimes
df['timestamp'] = pd.to_datetime(df['timestamp'])

df = df.groupby(df['timestamp'].dt.hour.rename('date')).apply(lambda x: x.diff().sum())
print (df)
     timestamp  battery_level
date                         
15    00:24:45           0.07
16    00:17:26           0.05

df = df.groupby(df['timestamp'].dt.floor('H').rename('date')).apply(lambda x: x.diff().sum())
print (df)
                    timestamp  battery_level
date                                        
2017-10-09 15:00:00  00:24:45           0.07
2017-10-09 16:00:00  00:17:26           0.05

df = df.reset_index()
print (df)
                 date timestamp  battery_level
0 2017-10-09 15:00:00  00:24:45           0.07
1 2017-10-09 16:00:00  00:17:26           0.05

Upvotes: 4

Related Questions