python - 如何计算数据框中的 "actual"行？

Question

我的数据集中有以下列:

id |用户 ID |开始日期 |结束日期 |检查日期

我想从第一列获取check_date，找到check_date在start_date和end_date<之间的所有行/强>。然后计算每个 user_id 的行数。第二行执行相同的操作，依此类推。

这里的要点是，我将拥有每个用户和每个 check_date 的所有实际行数。

这是我的解决方案(在我的数据集中，我的检查日期比用户少):

df['actual_rows'] = 0
for c_d in df.check_date.unique():
  temp_df = df[(start_date <= c_d) & (end_date > c_d)]
  gr = temp_df.groupby('user_id')['id'].count()
  df.loc[df.check_date == c_d, 'actual_rows'] =\
                     df[df.check_date == c_d].user_id.map(gr.to_dict()) 
  del temp_df 
  del gr

我认为这有点棘手而且效率不高。也许有人有更好的解决方案？

编辑

示例行:

id, user_id, start_date, end_date, check_date, actual
1, 1, 2018-11-05, 2018-12-06, 2018-11-22, 2
2, 1, 2018-11-10, 2018-11-25, 2018-11-24, 2
3, 1, 2018-12-05, 2018-12-31, 2018-12-20, 1
4, 1, 2018-12-25, 2019-01-30, 2018-12-30, 2

Answer 1

import pandas as pd
from io import StringIO

# example data (with result column and spaces removed, a few rows added)   
df = pd.read_csv(StringIO("""id,user_id,start_date,end_date,check_date
1,1,2018-11-05,2018-12-06,2018-11-22
2,1,2018-11-10,2018-11-25,2018-11-24
3,1,2018-12-05,2018-12-31,2018-12-20
4,1,2018-12-25,2019-01-30,2018-12-30
5,2,2018-11-05,2018-12-06,2018-11-22
6,2,2018-11-10,2018-11-25,2018-11-24
"""))

def count_for_check_date(df, user_id, check_date):
    """
    :return: count of rows in df for given user_id and check_date
    """
    return df.query('user_id == @user_id and start_date <= @check_date and @check_date <= end_date').shape[0]

# apply the counting function to each pair of [user_id, checkdate] - assign result to column actual
df['actual'] = df[['user_id','check_date']].apply(lambda r: count_for_check_date(df, r[0], r[1]), axis=1)

print(df)

结果:

   id  user_id  start_date    end_date  check_date  actual
0   1        1  2018-11-05  2018-12-06  2018-11-22       2
1   2        1  2018-11-10  2018-11-25  2018-11-24       2
2   3        1  2018-12-05  2018-12-31  2018-12-20       1
3   4        1  2018-12-25  2019-01-30  2018-12-30       2
4   5        2  2018-11-05  2018-12-06  2018-11-22       2
5   6        2  2018-11-10  2018-11-25  2018-11-24       2