I have a data frame like:
import datetime as dt
import pandas as pd
s = pd.Series(
range(8),
pd.to_datetime(
[
'20130101 10:34',
'20130101 10:34:08',
'20130101 10:34:08',
'20130101 10:34:15',
'20130101 10:34:28',
'20130101 10:34:54',
'20130101 10:34:55',
'20130101 10:35:12'
]
)
)
df = s.to_frame()
df = df.reset_index()
df = df.rename(columns=
{
0 : 'value',
'index' : 'start'
}
)
df['ID'] = [1,2,1,2,1,2,1,2]
sec = dt.timedelta(seconds=30)
df['end'] = df['start'].map(lambda t: t + sec)
.
df
start val ID end
0 2013-01-01 10:34:00 0 1 2013-01-01 10:34:30
1 2013-01-01 10:34:08 1 2 2013-01-01 10:34:38
2 2013-01-01 10:34:08 2 1 2013-01-01 10:34:38
3 2013-01-01 10:34:15 3 2 2013-01-01 10:34:45
4 2013-01-01 10:34:28 4 1 2013-01-01 10:34:58
5 2013-01-01 10:34:54 5 2 2013-01-01 10:35:24
6 2013-01-01 10:34:55 6 1 2013-01-01 10:35:25
7 2013-01-01 10:35:12 7 2 2013-01-01 10:35:42
I have to sum the values of each ID for all rows between the start and end time stamps. To be accurate my result should have this meaning:
p_ = []
#CICLE IS a problem
for row in range(len(df)):
p_.append(
#USING LOC IS A PROBLEM
df.loc[
(df['start'] >= df['start'][row]) &
(df['start'] <= df['end'][row]) &
(df['ID'] == df['ID'][row])
]
['value']\
.sum()
)
df
start val ID end sum_of_values for_ID_in_time_period
0 2013-01-01 10:34:00 0 1 2013-01-01 10:34:30 6
1 2013-01-01 10:34:08 1 2 2013-01-01 10:34:38 4
2 2013-01-01 10:34:08 2 1 2013-01-01 10:34:38 6
3 2013-01-01 10:34:15 3 2 2013-01-01 10:34:45 3
4 2013-01-01 10:34:28 4 1 2013-01-01 10:34:58 10
5 2013-01-01 10:34:54 5 2 2013-01-01 10:35:24 12
6 2013-01-01 10:34:55 6 1 2013-01-01 10:35:25 6
7 2013-01-01 10:35:12 7 2 2013-01-01 10:35:42 7
Instead of the for
cycle and the loc
I would like to ask for help to transform this problem to some kind of groupby
, map
solution because my real data set is hardly fits into memory and I have to come up with something faster.
I have tried to use:
df.groupby(
[
df.start.map(lambda t: t.minute),
'ID'
]
)[['value']]\
.sum()
but this transforms my result something what is not depending on the end column.
value
start ID
34 1 12
2 9
35 2 7