如何在迭代过程中将行移到新的df中

Question

我有一个超过1亿行的df。我需要识别与时间段相关的任何重叠行，并将这些重叠行放入新的df（df2）中，然后从初始df中删除它们。我的代码工作正常，并提供了@PeterLeimbigler的有用建议的最后两个df。然而，这是获得答案的一种资源密集型方法，因为每次迭代都需要再次遍历整个df。177K行大约需要11分20秒（这因迭代的长度不同而有所不同）。你知道吗

df =  pd.DataFrame({'Key': ['10003', '10003', '10003', '10003', '10003','10003','10034', '10034'], 
               'Num1': [12,13,13,12,13,13,16,13],
               'Num2': [121,122,122,124,125,126,127,128],
              'admit': [20120506, 20120508, 20120510,20121010,20121010,20121110,20120516,20120520],
          'discharge': [20120515, 20120508, 20120512,20121016,20121023,20121111,20120520,20120522]})
df['admit'] = pd.to_datetime(df['admit'], format='%Y%m%d')
df['discharge'] = pd.to_datetime(df['discharge'], format='%Y%m%d')

初始测向：

    Key     Num1    Num2    admit       discharge
0   10003   12      121     2012-05-06  2012-05-15
1   10003   13      122     2012-05-08  2012-05-08
2   10003   13      122     2012-05-10  2012-05-12
3   10003   12      124     2012-10-10  2012-10-16
4   10003   13      125     2012-10-10  2012-10-23
5   10003   13      126     2012-11-10  2012-11-11
6   10034   16      127     2012-05-16  2012-05-20
7   10034   13      128     2012-05-20  2012-05-22

此df表示最终将移动的列：

    Key     Num1    Num2    admit       discharge   flag
0   10003   12      121     2012-05-06  2012-05-15  move
1   10003   13      122     2012-05-08  2012-05-08  move
2   10003   13      122     2012-05-10  2012-05-12  move 
3   10003   12      124     2012-10-10  2012-10-16  move
4   10003   13      125     2012-10-10  2012-10-23  move
5   10003   13      126     2012-11-10  2012-11-11  
6   10034   16      127     2012-05-16  2012-05-20  
7   10034   13      128     2012-05-20  2012-05-22  

脚本：

#create new df
df2 = pd.DataFrame()

# Step 1: first find all 'Initial_overlap' rows,
df.loc[df.groupby(['Key']).apply(lambda x : (x['admit']<=x['admit'].shift(-1))&(x['discharge'] > x['admit'].shift(-1))).values,'flag']='Initial_Overlap'

#Step 2: then find first set of overlaps, then move those overlaps to df2, then drop from df1, iterate to the max number of rows with a group (since it could be the case that all rows are overlaps)
for item in range(df.groupby('Key')['Key'].count().max()):
    df.loc[df.groupby(['Key']).apply(lambda x : (x['admit']>=x['admit'].shift(1))&(x['admit'] < x['discharge'].shift(1))).values,'flag']='Overlap'
    moved_rows = df.loc[df['flag']=='Overlap',:]
    df2 = df2.append(moved_rows)
    df.drop(moved_rows.index, inplace=True)

#Step 3: after all iterations to find overlaps, then the INITIAL overlaps are moved to df2 and dropped from df1
moved_initial = df.loc[df['flag']=='Initial_Overlap',:]
df2 = df2.append(moved_initial)
df.drop(moved_initial.index, inplace=True)

循环在删除第一个已识别的重叠之后再次分析所有行，但我的一部分认为有一种资源密集度较低的方法来识别这些行。有没有一种方法可以在迭代过程中移动/删除一行，这样代码就不必多次过滤数据帧（就像我原来的代码那样）？你知道吗

最后两个预期结果：

df1
    Key     Num1    Num2    admit       discharge   flag
5   10003   13      126     2012-11-10  2012-11-11  0
6   10034   16      127     2012-05-16  2012-05-20  0
7   10034   13      128     2012-05-20  2012-05-22  0

df2
    Key     Num1    Num2    admit       discharge    flag
0   10003   12      121     2012-05-06  2012-05-15   Initial_overlap
1   10003   13      122     2012-05-08  2012-05-08   Overlap
2   10003   13      122     2012-05-10  2012-05-12   Overlap
3   10003   12      124     2012-10-10  2012-10-16   Initial_overlap
4   10003   13      125     2012-10-10  2012-10-23   Overlap

编辑： 更新的DF

df =  pd.DataFrame({'Key': ['10003', '10003', '10003', '10003', '10003','10003','10003', '10003','2003'], 
               'Num1': [12,13,13,12,13,13,16,13,4],
               'Num2': [121,122,122,124,125,126,127,128,128],
              'admit': [20150119, 20150124, 20150206,20150211,20150220,20150304,20150407,20150422,20150407],
          'discharge': [20150123, 20150202, 20150211,20150220,20150304,20150422,20120410,20120523,20150410]})
df['admit'] = pd.to_datetime(df['admit'], format='%Y%m%d')
df['discharge'] = pd.to_datetime(df['discharge'], format='%Y%m%d')

预期结果：

    Key    Num1 Num2    admit   discharge   flag
0   10003   12  121 2015-01-19  2015-01-23  NaN
1   10003   13  122 2015-01-24  2015-02-02  NaN
2   10003   13  122 2015-02-06  2015-02-11  NaN
3   10003   12  124 2015-02-11  2015-02-20  NaN
4   10003   13  125 2015-02-20  2015-03-04  NaN
5   10003   13  126 2015-03-04  2015-04-22  Move
6   10003   16  127 2015-04-07  2012-04-10  Move
7   10003   13  128 2015-04-22  2012-05-23  NaN
8   2003    4   128 2015-04-07  2015-04-10  NaN

Answer 1

让我们看看这是否有效：

您的设置：

df =  pd.DataFrame({'Key': ['10003', '10003', '10003', '10003', '10003','10003','10034', '10034'], 
               'Num1': [12,13,13,12,13,13,16,13],
               'Num2': [121,122,122,124,125,126,127,128],
              'admit': [20120506, 20120508, 20120510,20121010,20121010,20121110,20120516,20120520],
          'discharge': [20120515, 20120508, 20120512,20121016,20121023,20121111,20120520,20120522]})
df['admit'] = pd.to_datetime(df['admit'], format='%Y%m%d')
df['discharge'] = pd.to_datetime(df['discharge'], format='%Y%m%d')

df.loc[df.groupby(['Key']).apply(lambda x : (x['admit']<=x['admit'].shift(-1))&(x['discharge'] > x['admit'].shift(-1))).values,'flag']='Initial_Overlap'

让我们填写“重叠”，检查“承认”是否在前一个“初始重叠”的日期之间。使用cumsum将“初始重叠”记录分组到下一个“初始重叠”：

df['flag'] = df.flag.fillna('Overlap')\
               .where(df.groupby(df.flag.notnull().cumsum(), group_keys=False)
                        .apply(lambda x: x.admit.between(x.admit.iloc[0],
                                                         x.discharge.iloc[0])))
df1 = df[df.flag.isnull()]
df2 = df[df.flag.notnull()]

输出：

print(df1)
     Key  Num1  Num2      admit  discharge flag
5  10003    13   126 2012-11-10 2012-11-11  NaN
6  10034    16   127 2012-05-16 2012-05-20  NaN
7  10034    13   128 2012-05-20 2012-05-22  NaN

print(df2)
     Key  Num1  Num2      admit  discharge             flag
0  10003    12   121 2012-05-06 2012-05-15  Initial_Overlap
1  10003    13   122 2012-05-08 2012-05-08          Overlap
2  10003    13   122 2012-05-10 2012-05-12          Overlap
3  10003    12   124 2012-10-10 2012-10-16  Initial_Overlap
4  10003    13   125 2012-10-10 2012-10-23          Overlap

编辑：将标志更改为标志