基于groupby和condition对列求和

Question

我有一个数据帧和一些列。我想对“间隙”列求和，其中时间在某些时隙中。你知道吗

   region.    date.   time.     gap
0   1   2016-01-01  00:00:08    1
1   1   2016-01-01  00:00:48    0
2   1   2016-01-01  00:02:50    1
3   1   2016-01-01  00:00:52    0
4   1   2016-01-01  00:10:01    0
5   1   2016-01-01  00:10:03    1
6   1   2016-01-01  00:10:05    0
7   1   2016-01-01  00:10:08    0

我想和gap列相加。我在dict里有这样的时间段。你知道吗

'slot1': '00:00:00', 'slot2': '00:10:00', 'slot3': '00:20:00'

现在求和之后，上面的数据帧应该是这样的。你知道吗

 region.    date.       time.      gap
0   1   2016-01-01  00:10:00/slot1  2
1   1   2016-01-01  00:20:00/slot2  1

我有许多地区和144个时段从00:00:00到23:59:49。 我试过这个。你知道吗

regres=reg.groupby(['start_region_hash','Date','Time'])['Time'].apply(lambda x: (x >= hoursdict['slot1']) & (x <= hoursdict['slot2'])).sum()

但它不起作用。你知道吗

我们将非常感谢您的帮助。谢谢

Answer 1

解决这个问题的方法是首先将time列转换为所需的值，然后对time列执行groupby sum。你知道吗

下面的代码显示了我使用的方法。我使用np.select来包含任意多个条件和条件选项。在我把time转换成我想要的值之后，我做了一个简单的groupby sum 不必为格式化时间或转换字符串等操心。简单地让数据帧直观地处理它。你知道吗

#Just creating the DataFrame using a dictionary here
regdict = {
        'time': ['00:00:08','00:00:48','00:02:50','00:00:52','00:10:01','00:10:03','00:10:05','00:10:08'],
        'gap': [1,0,1,0,0,1,0,0],}

df = pd.DataFrame(regdict)


import pandas as pd
import numpy as np #This is the library you require for np.select function    

#Add in all your conditions and options here
condlist = [df['time']<'00:10:00',df['time']<'00:20:00'] 
choicelist = ['00:10:00/slot1','00:20:00/slot2'] 

#Use np.select after you have defined all your conditions and options
answerlist = np.select(condlist, choicelist)
print (answerlist)
['00:10:00/slot1' '00:10:00/slot1' '00:10:00/slot1' '00:10:00/slot1'
'00:20:00/slot2' '00:20:00/slot2' '00:20:00/slot2' '00:20:00/slot2']

#Assign answerlist to df['time']
df['time'] = answerlist
print (df)
       time  gap
0  00:10:00    1
1  00:10:00    0
2  00:10:00    1
3  00:10:00    0
4  00:20:00    0
5  00:20:00    1
6  00:20:00    0
7  00:20:00    0

df = df.groupby('time', as_index=False)['gap'].sum()
print (df) 
       time  gap
0  00:10:00    2
1  00:20:00    1

如果你想保持原来的时间，你可以做df['timeNew'] = answerlist，然后从那里过滤。你知道吗

df['timeNew'] = answerlist
print (df)
       time  gap         timeNew
0  00:00:08    1  00:10:00/slot1
1  00:00:48    0  00:10:00/slot1
2  00:02:50    1  00:10:00/slot1
3  00:00:52    0  00:10:00/slot1
4  00:10:01    0  00:20:00/slot2
5  00:10:03    1  00:20:00/slot2
6  00:10:05    0  00:20:00/slot2
7  00:10:08    0  00:20:00/slot2

#Use transform function here to retain all prior values
df['aggregate sum of gap'] = df.groupby('timeNew')['gap'].transform(sum)
print (df) 
       time  gap         timeNew  aggregate sum of gap
0  00:00:08    1  00:10:00/slot1                     2
1  00:00:48    0  00:10:00/slot1                     2
2  00:02:50    1  00:10:00/slot1                     2
3  00:00:52    0  00:10:00/slot1                     2
4  00:10:01    0  00:20:00/slot2                     1
5  00:10:03    1  00:20:00/slot2                     1
6  00:10:05    0  00:20:00/slot2                     1
7  00:10:08    0  00:20:00/slot2                     1

Answer 2

为了避免Datetime比较的复杂性（除非这是你的全部观点，在这种情况下忽略我的答案），并通过时隙窗口显示这组问题的本质，我在这里假设时间是整数。你知道吗

df = pd.DataFrame({'time':[8, 48, 250, 52, 1001, 1003, 1005, 1008, 2001, 2003, 2056], 
                   'gap': [1, 0,  1,   0,  0,    1,    0,    0,    1,    1,    1]})
slots = np.array([0, 1000, 1500])
df['slot'] = df.apply(func = lambda x: slots[np.argmax(slots[x['time']>slots])], axis=1)
df.groupby('slot')[['gap']].sum()

输出

       gap
slot    
     -
0       2
1000    1
1500    3

Answer 3

想法是将列time通过10Min转换成带有^{}的datetimes，然后转换成字符串HH:MM:SS：

d = {'slot1': '00:00:00', 'slot2': '00:10:00', 'slot3': '00:20:00'}
d1 = {v:k for k, v in d.items()}

df['time'] = pd.to_datetime(df['time']).dt.floor('10Min').dt.strftime('%H:%M:%S')
print (df)
   region        date      time  gap
0       1  2016-01-01  00:00:00    1
1       1  2016-01-01  00:00:00    0
2       1  2016-01-01  00:00:00    1
3       1  2016-01-01  00:00:00    0
4       1  2016-01-01  00:10:00    0
5       1  2016-01-01  00:10:00    1
6       1  2016-01-01  00:10:00    0
7       1  2016-01-01  00:10:00    0

按字典聚合sum和最后的^{}值，并使用值替换键：

regres = df.groupby(['region','date','time'], as_index=False)['gap'].sum()
regres['time'] = regres['time'] + '/' + regres['time'].map(d1)
print (regres)
   region        date            time  gap
0       1  2016-01-01  00:00:00/slot1    2
1       1  2016-01-01  00:10:00/slot2    1

如果要显示下一个10Min插槽：

d = {'slot1': '00:00:00', 'slot2': '00:10:00', 'slot3': '00:20:00'}
d1 = {v:k for k, v in d.items()}

times = pd.to_datetime(df['time']).dt.floor('10Min')
df['time'] = times.dt.strftime('%H:%M:%S')
df['time1'] = times.add(pd.Timedelta('10Min')).dt.strftime('%H:%M:%S')
print (df)
   region        date      time  gap     time1
0       1  2016-01-01  00:00:00    1  00:10:00
1       1  2016-01-01  00:00:00    0  00:10:00
2       1  2016-01-01  00:00:00    1  00:10:00
3       1  2016-01-01  00:00:00    0  00:10:00
4       1  2016-01-01  00:10:00    0  00:20:00
5       1  2016-01-01  00:10:00    1  00:20:00
6       1  2016-01-01  00:10:00    0  00:20:00
7       1  2016-01-01  00:10:00    0  00:20:00

regres = df.groupby(['region','date','time','time1'], as_index=False)['gap'].sum()
regres['time'] = regres.pop('time1') + '/' + regres['time'].map(d1)
print (regres)
   region        date            time  gap
0       1  2016-01-01  00:10:00/slot1    2
1       1  2016-01-01  00:20:00/slot2    1

编辑：

对floor和convert to string的改进是通过^{}或searchsorted使用bining：

df['time'] = pd.to_timedelta(df['time'])

bins = pd.timedelta_range('00:00:00', '24:00:00', freq='10Min')
labels = np.array(['{}'.format(str(x)[-8:]) for x in bins])
labels = labels[:-1]

df['time1'] = pd.cut(df['time'], bins=bins, labels=labels)
df['time11'] = labels[np.searchsorted(bins, df['time'].values) - 1]