如何计算数据框中“开始”和“结束”列包含特定日期的行数？

Question

我有一个看起来像

        Category   Start       End
0          a     2014-12-01 2015-06-01
1          a     2015-10-02 2015-10-16
2          b     2015-10-01 2016-04-01
3          b     2015-10-01 2015-12-01
4          c     2015-06-01 2015-08-01

对于日期范围d中的每个日期，我想查找开始<；=日期<；=结束，然后我想数一数有多少不同的类别

最有效的方法是什么

import pandas as pd
import datetime

d = pd.date_range(start='2015-01-01', end='2015-12-31', freq='D')
s = {'Start':[datetime.date(2014,12,1), datetime.date(2015,10,2), datetime.date(2015,10,1), datetime.date(2015,10,1), datetime.date(2015,6,1)]}
e = {'End':[datetime.date(2015,6,1), datetime.date(2015,10,16), datetime.date(2016,4,1), datetime.date(2015,12,1), datetime.date(2015,8,1)]}
c = {'Category': ['a', 'a', 'b', 'b', 'c']}
c.update(s)
c.update(e)
df = pd.DataFrame(c)
df_count = pd.DataFrame(index=d, col['count']

for date in d:
   count_occourances = len(set(df.loc[(df['Start'] <= date) & (df['End'] >= date), 'Category']))
   # Some saving to keep track on count for this particular date e.g.
   df_count.loc[date, 'count'] = count_occourances

然后，预期的输出将 df_计数：

        Category   Count      
2015-01-01          1    
2015-01-02          1    
2015-01-03          1    
2015-01-04          1     
2015-01-05          1     
    .
    .
    .
2015-05-31          1
2015-06-01          2
2015-06-02          1
2015-06-03          1
    .
    .
    .
2015-07-31          1
2015-08-01          1
2015-08-02          0
    .
    .
    .
2015-09-30          0
2015-10-01          2
2015-10-02          3
2015-10-03          3
    .
    .
    .
2015-10-15          3
2015-10-16          3
2015-10-17          2
    .
    .
    .
2015-12-01          2
2015-12-02          1
    .
    .
    .
2015-12-31          1

Answer 1

如果我理解正确，您可以通过以下方式实现：

将日期转换为日期时间数据类型：

df = df.assign(Start = df.Start.transform(pd.to_datetime), 
               End = df.End.transform(pd.to_datetime))

创建intervalindex：

intervals = pd.IntervalIndex.from_arrays(df.Start, df.End, closed='both')

创建字典，将唯一计数与各个日期配对：

  counter = {}
  category = df.Category.array
  for dates in d:
      booleans = intervals.contains(dates)
      count = category[booleans].unique().size
      counter[dates] = count

然后可以创建一个系列：

series = pd.Series(counter)
series.index.name = 'Category'
series.name = 'counter'

series.head()

Category
2015-01-01    1
2015-01-02    1
2015-01-03    1
2015-01-04    1
2015-01-05    1
Name: counter, dtype: int64

既然这些都是间隔，假设我的问题是对的，你应该得到显著的加速