在带有datetime条件的列中查找更改

Question

我有一个很大的数据框（1000000多行），里面有关于员工的信息

它包含了有关员工身份证、记录日期和离职情况的信息。如果营业额不等于1，则员工当前正在工作

以下是示例：

test_df =\
pd.DataFrame({'empl_id': [1,2,3,1,2,3,1,2,1,2,1,2,3], 
              'rec_date':pd.to_datetime(['20080131','20080131','20080131', 
                                         '20080229', '20080229', '20080229', 
                                         '20080331', '20080331', 
                                         '20080430', '20080430',
                                         '20080531', '20080531', '20080531'], 
                                        format='%Y%m%d'), 
              'turnover':[0,0,0,0,0,1,0,0,0,0,1,0,0]})




+----+-----------+---------------------+------------+
|    |   empl_id | rec_date            |   turnover |
+====+===========+=====================+============+
|  0 |         1 | 2008-01-31 00:00:00 |          0 |
+----+-----------+---------------------+------------+
|  1 |         2 | 2008-01-31 00:00:00 |          0 |
+----+-----------+---------------------+------------+
|  2 |         3 | 2008-01-31 00:00:00 |          0 |
+----+-----------+---------------------+------------+
|  3 |         1 | 2008-02-29 00:00:00 |          0 |
+----+-----------+---------------------+------------+
|  4 |         2 | 2008-02-29 00:00:00 |          0 |
+----+-----------+---------------------+------------+
|  5 |         3 | 2008-02-29 00:00:00 |          1 |
+----+-----------+---------------------+------------+
|  6 |         1 | 2008-03-31 00:00:00 |          0 |
+----+-----------+---------------------+------------+
|  7 |         2 | 2008-03-31 00:00:00 |          0 |
+----+-----------+---------------------+------------+
|  8 |         1 | 2008-04-30 00:00:00 |          0 |
+----+-----------+---------------------+------------+
|  9 |         2 | 2008-04-30 00:00:00 |          0 |
+----+-----------+---------------------+------------+
| 10 |         1 | 2008-05-31 00:00:00 |          1 |
+----+-----------+---------------------+------------+
| 11 |         2 | 2008-05-31 00:00:00 |          0 |
+----+-----------+---------------------+------------+
| 12 |         3 | 2008-05-31 00:00:00 |          0 |
+----+-----------+---------------------+------------+

我需要显示员工是否在记录中规定的2个月后离开公司

我找到了解决办法，但处理得太慢了。对于这样大小的数据帧，它将需要超过54小时

这是我的剧本：

    from datetime import datetime, date, timedelta
    import calendar
    import pandas as pd
import numpy as np

    # look only in employees with turnover
    res = test_df.groupby('empl_id')['turnover'].sum()
    keys_with_turn = res[res>0].index

    # function for add months
    def add_months(sourcedate,months):
        month = sourcedate.month - 1 + months
        year = sourcedate.year + month // 12
        month = month % 12 + 1
        day = min(sourcedate.day, calendar.monthrange(year,month)[1])
        return date(year,month,day)

    # add 2 months and convert to timestamp
    test_df['rec_date_plus_2'] = test_df['rec_date'].apply(lambda x: add_months(x, 2))
    test_df['rec_date_plus_2'] = pd.to_datetime(test_df['rec_date_plus_2'])



    test_df['turn_nxt_2'] = np.nan

    for i in range(len(keys_with_turn)): # loop over employees ids
        for index, row in test_df[test_df['empl_id']==keys_with_turn[i]].iterrows(): # loop over all recs with employee
            a = row['rec_date']
            b = row['rec_date_plus_2']

            turn_coef = test_df[(test_df['empl_id']==keys_with_turn[i]) & 
                                ((test_df['rec_date']>=a) & (test_df['rec_date']<=b))]['turnover'].sum()

            test_df.loc[(test_df['rec_date']==a) & 
                        (test_df['empl_id']==keys_with_turn[i]), 'turn_nxt_2'] = 0 if turn_coef == 0 else 1     

    test_df['turn_nxt_2'].fillna(0, inplace=True)

我想要的结果是：

+----+-----------+---------------------+------------+--------------+
|    |   empl_id | rec_date            |   turnover |   turn_nxt_2 |
+====+===========+=====================+============+==============+
|  0 |         1 | 2008-01-31 00:00:00 |          0 |            0 |
+----+-----------+---------------------+------------+--------------+
|  1 |         2 | 2008-01-31 00:00:00 |          0 |            0 |
+----+-----------+---------------------+------------+--------------+
|  2 |         3 | 2008-01-31 00:00:00 |          0 |            1 |
+----+-----------+---------------------+------------+--------------+
|  3 |         1 | 2008-02-29 00:00:00 |          0 |            0 |
+----+-----------+---------------------+------------+--------------+
|  4 |         2 | 2008-02-29 00:00:00 |          0 |            0 |
+----+-----------+---------------------+------------+--------------+
|  5 |         3 | 2008-02-29 00:00:00 |          1 |            1 |
+----+-----------+---------------------+------------+--------------+
|  6 |         1 | 2008-03-31 00:00:00 |          0 |            1 |
+----+-----------+---------------------+------------+--------------+
|  7 |         2 | 2008-03-31 00:00:00 |          0 |            0 |
+----+-----------+---------------------+------------+--------------+
|  8 |         1 | 2008-04-30 00:00:00 |          0 |            1 |
+----+-----------+---------------------+------------+--------------+
|  9 |         2 | 2008-04-30 00:00:00 |          0 |            0 |
+----+-----------+---------------------+------------+--------------+
| 10 |         1 | 2008-05-31 00:00:00 |          1 |            1 |
+----+-----------+---------------------+------------+--------------+
| 11 |         2 | 2008-05-31 00:00:00 |          0 |            0 |
+----+-----------+---------------------+------------+--------------+
| 12 |         3 | 2008-05-31 00:00:00 |          0 |            0 |
+----+-----------+---------------------+------------+--------------+

如何更快更有效地完成

Answer 1

一个更简单的方法是创建一个复制的数据帧并在适当的键上进行合并

我做了一个简单的代码来演示，虽然可以改进，但它是：

从原始数据集开始，我们导入一个新库并转换日期类型，以便稍后对其执行操作：

import pandas as pd
from dateutil.relativedelta import relativedelta

DF_1 = pd.DataFrame({'empl_id': [1,2,3,1,2,3,1,2,1,2,1,2], 
              'rec_date':pd.to_datetime(['20080131','20080131','20080131', 
                                         '20080229', '20080229', '20080229', 
                                         '20080331', '20080331', 
                                         '20080430', '20080430',
                                         '20080531', '20080531'], 
                                        format='%Y%m%d'), 
              'turnover':[0,0,0,0,0,1,0,0,0,0,1,0]})

print (type(DF_1.rec_date[0]))
DF_1.rec_date = DF_1.rec_date.map(lambda X: X.date())
print (type(DF_1.rec_date[0]))

现在，我们创建一个复制的数据帧，其中一个合并列引用每个条目所需的合并日期

DF_2 = DF_1.copy()
DF_2['merge_value'] = DF_2.rec_date - relativedelta(months=2)

我们还在原始数据帧上创建一个merge列，这样pd.merge中的引用就更容易了

DF_1['merge_value'] = DF_1.rec_date.values

现在我们要做的就是合并

DF_1.merge(DF_2, on=['empl_id','merge_value'])

另一条建议是，首先在一个较小的示例上尝试，如果您认为主键不是，merge有时会出现问题(在这种情况下，如果相同的['emp\u id'，'merge\u value']组合有多个条目）

希望有帮助