python - 如何在迭代pandas期间将行移动到新的df中

Question

我有一个 100+ 百万行的 df。我需要识别与时间段相关的任何重叠行，并将这些重叠行放入新的 df (df2) 中，并将它们从初始 df 中删除。我的代码可以正常工作，并根据 @PeterLeimbigler 的有用建议正确提供了最后两个 dfs。然而，这是一种获取答案的资源密集型方法，因为每次迭代都需要再次遍历整个 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')

初始 df:

    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'

让我们填充“overlap”，检查“admit”是否位于上一个“Initial_Overlap”的日期之间。使用cumsum将“Intial_Overlap”记录分组到下一个“Initial Overlap”:

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

编辑:将标志更改为标志