按周分组的时间分布

Question

我想为timeseries数据找到按周分组的时间分布。例如，时间序列是：

2019-04-01 02:00:00    0.6
2019-04-02 10:45:00    2.0
2019-04-03 02:00:00    3.0
2019-04-10 00:00:00    0.6
2019-04-11 10:45:00    2.0
2019-04-13 10:45:00    6.0
2019-04-17 11:45:00    2.5
2019-04-18 11:45:00    3.0
2019-04-19 11:45:00    6.0
dtype: float64

我想知道在第14周(2019-04-01周)，02:00:00有两个记录，10:45:00有一个记录，其他时候没有记录。在第15周，00:00:00有一个记录，10:45:00有两个记录，其他时候没有记录。

目前，这是我的解决方案，用于在15分钟的增量时间内找到分布：

import panda as pd
import numpy as np
import datetime as dt

def dist(series, bins):
    h = np.histogram(series, bins)
    return dict(zip(h[1][:-1], h[0]))

# creating bins, i.e. 15min increments throughout the day
times = pd.Series(index = pd.date_range(start='2019-01-01', end='2019-01-02', freq='15min'))
times = set(times.index.time)
times = list(times)
times.sort()
dummy = (dt.datetime.combine(dt.date.today(), max(times))+dt.timedelta(seconds = 10)).time()
times = times + [dummy]

# finding distribution each week
df = pd.DataFrame({'week': list(timeseries.index.week), 'time': list(timeseries.index.time)})
df = df.groupby(by=['week'])['time'].apply(lambda x: dist(x, times))
df.index.names = ['week', 'time']
df.name = 'counts'
df = df.reset_index().pivot(index='time', columns='week', values='counts')

有没有更好的方法来做到这一点？

Answer 1

像这样简单的东西呢？

# I'm starting with a Series here
s.head(2)

# time
# 2019-04-01 02:00:00    0.6
# 2019-04-02 10:45:00    2.0
# Name:  value, dtype: float64

# Resampling the series to the expected bin, say 15 min 
# filling with NaN undefined values
s = s.resample('15min').asfreq()
s.head(3)

# time
# 2019-04-01 02:00:00    0.6
# 2019-04-01 02:15:00    NaN
# 2019-04-01 02:30:00    NaN
# Freq: 15T, Name:  value, dtype: float64


# Performing the summary to get how many times are defined by week / time 
# sampled by 15 min (NaN are not counted)
result = s.groupby([s.index.week, s.index.time]).count()
result.head()

# time          
# 14    00:00:00    0
#       00:15:00    0
#       00:30:00    0
#       00:45:00    0
#       01:00:00    0
# Name:  value, dtype: int64

# Getting only the hours with values
result[result != 0]

# time          
# 14    02:00:00    2
#       10:45:00    1
# 15    00:00:00    1
#       10:45:00    2
# 16    11:45:00    3
# Name:  value, dtype: int64

我想它能给你你想要的答案。

想知道在第14周(2019-04-01周)，02:00:00有两个记录，10:45:00有一个记录，其他时候没有记录。在第15周，00:00:00有一个记录，10:45:00有两个记录，其他时候没有记录。

备注

这就是如何生成示例DataFrame。

import pandas as pd
import io

zz = """
time, value
2019-04-01 02:00:00, 0.6
2019-04-02 10:45:00, 2.0
2019-04-03 02:00:00, 3.0
2019-04-10 00:00:00, 0.6
2019-04-11 10:45:00, 2.0
2019-04-13 10:45:00, 6.0
2019-04-17 11:45:00, 2.5
2019-04-18 11:45:00, 3.0
2019-04-19 11:45:00, 6.0"""

df = pd.read_table(io.StringIO(zz), sep=',')
df = df.set_index(pd.DatetimeIndex(df['time']))
df = df.drop('time', axis=1)
s = df.iloc[:,0]