使用pandas的Bin日期时间范围

Question

我有一个dataframe，它包含一个操作id和一个事件开始和结束的日期时间戳。在

OperID               Start                 End
   141 2014-03-04 19:28:39 2014-03-04 19:33:38
 10502 2014-03-04 02:26:26 2014-03-08 20:09:21
 10502 2014-03-15 00:03:45 2014-03-15 10:03:44

我想采取这些数据，并能够轻松创建各种类型的箱子（月，日，小时） 显示在每个箱子中，操作处于受影响状态的时间。开始和结束日期通常跨越小时、日和月的边界。在

如果我是按天分类的话，我想要的输出是：

^{pr2}$

Answer 1

这是一个非常冗长的解决方案，循环很难摆脱：

In [3]:

from collections import OrderedDict
In [4]:

df['End_d']=pd.DatetimeIndex(df['End']).day
df['Start_d']=pd.DatetimeIndex(df['Start']).day
In [5]:

print df
   OperID               Start                 End  End_d  Start_d
0     141 2014-03-04 19:28:39 2014-03-04 19:33:38      4        4
1   10502 2014-03-04 02:26:26 2014-03-08 20:09:21      8        4
2   10502 2014-03-15 00:03:45 2014-03-15 10:03:44     15       15

[3 rows x 5 columns]
In [6]:

df.dtypes
Out[6]:
OperID              int64
Start      datetime64[ns]
End        datetime64[ns]
End_d               int32
Start_d             int32
dtype: object
In [7]:

df1=df[df.End_d==df.Start_d].loc[:,['OperID', 'Start','End']]  #the obs. of which the duration < 1day
df2=df[df.End_d!=df.Start_d]                                   #the obs. of which the duration > 1day
for i in df2.index:                                            #Expend it in to multiple rows.
    days=df2.loc[i,:].End_d-df2.loc[i,:].Start_d+1
    start_d_str=df2.loc[i,:].Start.strftime('%Y-%m-%d')
    temp_df=pd.DataFrame(OrderedDict({'OperID': df2.loc[i,:].OperID,
              'Start': pd.date_range('%s 00:00:00'%start_d_str, periods=days),
              'End':   pd.date_range('%s 23:59:59'%start_d_str, periods=days)}))
    temp_df.loc[0,'Start'] = df2.loc[i,'Start']
    temp_df.loc[days-1, 'End'] = df2.loc[i,'End']
    df1=df1.append(temp_df)
df1['Bin']=pd.DatetimeIndex(df1.Start.apply(lambda x: x.strftime('%Y-%m-%d')))   #Get the YMD only
df1['Seconds']=(df1['End']-df1['Start'])/np.timedelta64(1,'s')                   #Convert to seconds
df1.sort(columns=['OperID', 'Start'], ascending=[-1,-1], inplace=True)
print df1
                  End  OperID               Start        Bin  Seconds
0 2014-03-04 19:33:38     141 2014-03-04 19:28:39 2014-03-04      299
0 2014-03-04 23:59:59   10502 2014-03-04 02:26:26 2014-03-04    77613
1 2014-03-05 23:59:59   10502 2014-03-05 00:00:00 2014-03-05    86399
2 2014-03-06 23:59:59   10502 2014-03-06 00:00:00 2014-03-06    86399
3 2014-03-07 23:59:59   10502 2014-03-07 00:00:00 2014-03-07    86399
4 2014-03-08 20:09:21   10502 2014-03-08 00:00:00 2014-03-08    72561
2 2014-03-15 10:03:44   10502 2014-03-15 00:03:45 2014-03-15    35999

[7 rows x 5 columns]

如果你把1天算为86400秒，而不是86299秒，你不是把最后一秒数了两次吗？不管怎样都是小问题。在