首先,这是一种方法value_counts the periods在每个日期列中(使用to_period
时间戳方法):
In [11]: p = pd.PeriodIndex(freq='m', start='2000-1', periods=18)
In [12]: starts = df['LIST_DATE'].apply(lambda t: t.to_period(freq='m')).value_counts()
In [13]: ends = df['END_DATE'].apply(lambda t: t.to_period(freq='m')).value_counts()
通过PeriodIndex重新索引它们,填写NaN(以便您可以减去)并从累积结束处获取累积开始,以给出当前活动的:
In [14]: starts.reindex(p).fillna(0).cumsum() - ends.reindex(p).fillna(0).cumsum()
Out[14]:
2000-01 0
2000-02 0
2000-03 0
2000-04 2
2000-05 2
2000-06 2
2000-07 2
2000-08 2
2000-09 1
2000-10 1
2000-11 1
2000-12 1
2001-01 1
2001-02 1
2001-03 1
2001-04 1
2001-05 1
2001-06 0
Freq: M, dtype: float64
另一种最后步骤是创建一个 DataFrame(它最初跟踪更改,因此开始为正,结束为负):
In [21]: current = pd.DataFrame({'starts': starts, 'ends': -ends}, p)
In [22]: current
Out[22]:
ends starts
2000-01 NaN NaN
2000-02 NaN NaN
2000-03 NaN NaN
2000-04 NaN 2
2000-05 -1 1
2000-06 NaN NaN
2000-07 NaN NaN
2000-08 NaN NaN
2000-09 -1 NaN
2000-10 NaN NaN
2000-11 NaN NaN
2000-12 NaN NaN
2001-01 NaN NaN
2001-02 NaN NaN
2001-03 NaN NaN
2001-04 NaN NaN
2001-05 NaN NaN
2001-06 -1 NaN
In [23]: current.fillna(0)
Out[23]:
ends starts
2000-01 0 0
2000-02 0 0
2000-03 0 0
2000-04 0 2
2000-05 -1 1
2000-06 0 0
2000-07 0 0
2000-08 0 0
2000-09 -1 0
2000-10 0 0
2000-11 0 0
2000-12 0 0
2001-01 0 0
2001-02 0 0
2001-03 0 0
2001-04 0 0
2001-05 0 0
2001-06 -1 0
cumsum 跟踪到该点的开始和结束的运行总计:
In [24]: current.fillna(0).cumsum()
Out[24]:
ends starts
2000-01 0 0
2000-02 0 0
2000-03 0 0
2000-04 0 2
2000-05 -1 3
2000-06 -1 3
2000-07 -1 3
2000-08 -1 3
2000-09 -2 3
2000-10 -2 3
2000-11 -2 3
2000-12 -2 3
2001-01 -2 3
2001-02 -2 3
2001-03 -2 3
2001-04 -2 3
2001-05 -2 3
2001-06 -3 3
将这些列相加,得到当前活动的列,结果与上面相同:
In [25]: current.fillna(0).cumsum().sum(1)