我正在尝试计算在给定日期每小时登录到系统的用户数。我的日期类似于:
df=
Name Date
name_1 2012-07-12 22:20:00
name_1 2012-07-16 22:19:00
name_1 2013-12-16 17:50:00
...
name_2 2010-01-11 19:54:00
name_2 2010-02-06 12:10:00
...
name_2 2012-07-18 22:12:00
...
name_5423 2013-11-23 10:21:00由于我对用户名不感兴趣,因此删除了该列。我使用以下命令创建了一个分组的数据结构和一个新的数据帧df2
grp = df.groupby(by=[df.Date.map(lambda x: (x.year, x.month, x.day, x.hour))])
df2 = pd.DataFrame({'Count' : grp.size()}).reset_index()grp将datetime类型转换为(year, month, day, hour)的元组。
我可以使用for循环将其转换回datetime类型
for i in range(len(df2)):
proper_date = datetime.datetime(*df2['Date'][i])
df2.set_value(i, 'Date', proper_date)我想知道的是,是否有更好/更有效的方法来做这件事?
发布于 2016-09-22 03:52:40
print (df.Date.values.astype('datetime64[h]'))
['2012-07-12T22+0200' '2012-07-16T22+0200' '2013-12-16T17+0100'
'2010-01-11T19+0100' '2010-02-06T12+0100' '2012-07-18T22+0200'
'2013-11-23T10+0100']
print (df.Name.groupby([df.Date.values.astype('datetime64[h]')]).size())
2010-01-11 19:00:00 1
2010-02-06 12:00:00 1
2012-07-12 22:00:00 1
2012-07-16 22:00:00 1
2012-07-18 22:00:00 1
2013-11-23 10:00:00 1
2013-12-16 17:00:00 1
dtype: int64另一种解决方案:
print (df.Date.values.astype('<M8[h]'))
['2012-07-12T22+0200' '2012-07-16T22+0200' '2013-12-16T17+0100'
'2010-01-11T19+0100' '2010-02-06T12+0100' '2012-07-18T22+0200'
'2013-11-23T10+0100']
print (df.Name.groupby([df.Date.values.astype('<M8[h]')]).size())
2010-01-11 19:00:00 1
2010-02-06 12:00:00 1
2012-07-12 22:00:00 1
2012-07-16 22:00:00 1
2012-07-18 22:00:00 1
2013-11-23 10:00:00 1
2013-12-16 17:00:00 1
dtype: int64发布于 2016-09-22 04:06:15
另一个答案是使用重采样。我认为效率不是很高,但很有趣。
# Test data
d = {'Date': ['2012-07-12 22:20:00', '2012-07-12 22:19:00', '2013-12-16 17:50:00', '2010-01-11 19:54:00', '2010-02-06 12:10:00', '2012-07-18 22:12:00'],
'Name': ['name_1', 'name_1', 'name_1', 'name_2', 'name_2', 'name_5']}
df = pd.DataFrame(d)
df['Date'] = pd.to_datetime(df['Date'])
result = df.set_index('Date')
# Resampling data for each hour
result = result.resample('H').count()
# Filtering to keep only hours with at least one row
result[result['Name'] > 0]
Name
Date
2010-01-11 19:00:00 1
2010-02-06 12:00:00 1
2012-07-12 22:00:00 2
2012-07-18 22:00:00 1
2013-12-16 17:00:00 1https://stackoverflow.com/questions/39625328
复制相似问题