Pandas中按组计算唯一值的滚动1年计数

Question

我有以下数据框：

Period      group     ID    
20130101     A        10
20130101     A        20
20130301     A        20
20140101     A        20
20140301     A        30
20140401     A        40

20130101     B        11
20130201     B        21
20130401     B        31
20140401     B        41
20140501     B        51

我需要按照组别统计去年有多少不同的ID。所以，我的期望输出应该像这样：

Period      group     num_ids_last_year
20130101     A            2 # ID 10 and 20 in the last year
20130301     A            2 
20140101     A            2 
20140301     A            2 # ID 30 enters, ID 10 leaves
20140401     A            3 # ID 40 enters

20130101     B            1
20130201     B            2
20130401     B            3
20140401     B            2 # ID 11 and 21 leave 
20140501     B            2 # ID 31 leaves, ID 51 enters

Period是日期时间格式。我尝试了很多类似于以下的方法：

df.groupby(['group','Period'])['ID'].nunique() # Get number of IDs by group in a given period.
df.groupby(['group'])['ID'].nunique() # Get total number of IDs by group.

df.set_index('Period').groupby('group')['ID'].rolling(window=1, freq='Y').nunique()

但是最后一个甚至不可能。有没有什么简单直接的方法来做到这一点？我考虑可能需要结合 cumcount() 和 pd.DateOffset, 或者可能需要使用 ge(df.Period - dt.timedelta(365)，但是我找不到答案。

谢谢。

编辑：添加了在给定的 Period 中可以找到多个 ID 的事实。

- Juan C

---

滚动窗口需要一个固定的窗口大小。由于闰年的存在，既不是'M'也不是'Y'是固定频率。在这个答案中，我将解释如何“规范化”您的时间序列，以允许一个月的滚动窗口：https://stackoverflow.com/questions/59569963/pandas-monthly-rolling-window/59677389#59677389 - Markus Rother

为什么 20130101 B 1 被设置为1？难道不应该是2吗？如果不是，那么为什么这个逻辑不适用于2013年A组 - df.groupby([df['Period'].dt.year,'group'])['ID'].nunique()？ - Umar.H

因为在“20130101”时，B组只有一个“ID”：11。在这种情况下，我正在计算从20120101到20130101有多少个ID。我正在计算的是，在今天的一年窗口期内，每个组有多少个不同的ID。 - Juan C

Answer 1

从您的数据结构来看，我猜测您有很多重复，因此首先要删除它们。 drop_duplicates 通常很快。

我假设 df['Period'] 列的数据类型为 datetime64[ns]

df = df.drop_duplicates()
results = dict()
for start in df['Period'].drop_duplicates():
    end = start.date() - relativedelta(years=1)
    screen = (df.Period <= start) & (df.Period >= end)  # screen for 1 year of data
    singles = df.loc[screen, ['group', 'ID']].drop_duplicates()  # screen for same year ID by groups
    x = singles.groupby('group').count()
    results[start] = x
results = pd.concat(results, 0)


results
                  ID
           group    
2013-01-01 A       2
           B       1
2013-02-01 A       2
           B       2
2013-03-01 A       2
           B       2
2013-04-01 A       2
           B       3
2014-01-01 A       2
           B       3
2014-03-01 A       2
           B       1
2014-04-01 A       3
           B       2
2014-05-01 A       3
           B       2

这样会更快吗？

附：如果df['Period']不是日期时间类型：

df['Period'] = pd.to_datetime(df['Period'],format='%Y%m%d', errors='ignore')

- Steven G

---

很遗憾，没有Period-ID重复，所以这可能需要很长时间。无论如何，我会尝试一下，以防它比我预期的更快。 - Juan C

@JuanC，你说有77个周期可以在循环中处理，我正在尝试尽快排除ID-组合重复的情况，祝好运！ - Steven G

啊哈，是的！这将是77个groupby操作，大约需要40分钟左右，所以还不错。您知道在dask上运行是否会更快吗？ - Juan C

@JuanC 抱歉，不确定使用dask是否是更好的方法。 - Steven G

没问题，我有时间的时候会尝试两种方法。无论如何，谢谢！ - Juan C

Answer 2

这里是使用groupby和rolling解决问题的方法。注意：您所需的输出从YYYY0101到下一年YYYY0101，因此您需要使用366D而不是365D进行滚动。

df['Period'] = pd.to_datetime(df.Period, format='%Y%m%d')
df = df.set_index('Period')

df_final = (df.groupby('group')['ID'].rolling(window='366D')
                                     .apply(lambda x: np.unique(x).size, raw=True)
                                     .reset_index(name='ID_count')
                                     .drop_duplicates(['group','Period'], keep='last'))

Out[218]:
   group     Period  ID_count
1      A 2013-01-01       2.0
2      A 2013-03-01       2.0
3      A 2014-01-01       2.0
4      A 2014-03-01       2.0
5      A 2014-04-01       3.0
6      B 2013-01-01       1.0
7      B 2013-02-01       2.0
8      B 2013-04-01       3.0
9      B 2014-04-01       2.0
10     B 2014-05-01       2.0

注意：在处理1800万行数据时，我认为这个解决方案不可能在10分钟内完成，希望能在30分钟左右完成。

- Andy L.

Answer 3

from dateutil.relativedelta import relativedelta
df.sort_values(by=['Period'], inplace=True) # if not already sorted

# create new output df
df1 = (df.groupby(['Period','group'])['ID']
       .apply(lambda x: list(x))
       .reset_index())
df1['num_ids_last_year'] = df1.apply(lambda x: len(set(df1.loc[(df1['Period'] >= x['Period']-relativedelta(years=1)) & (df1['Period'] <= x['Period']) & (df1['group'] == x['group'])].ID.apply(pd.Series).stack())), axis=1)
df1.sort_values(by=['group'], inplace=True)
df1.drop('ID', axis=1, inplace=True)
df1 = df1.reset_index(drop=True)

- jlewkovich

---

很抱歉之前没有回答。这个速度对我的需求来说太慢了，运行 df1 这一行已经大约20分钟了，但它还没有完成，这对我正在做的事情来说是不可能的，真是遗憾！ - Juan C

一个小时后它仍在运行，所以很遗憾肯定是不行的。 - Juan C