Pandas按具有缺失间期的间期合并

roejwanj  于 2023-01-19  发布在  其他
关注(0)|答案(1)|浏览(95)

这个问题可以总结在这张图片中

  • 我有一个包含ID列和日期的表(df1
  • 另一个具有日期间隔的表(df2
  • 我需要一个最终表格来指示每个日期是否在其中一个间隔内。

print(df1)

    ID       Date
0    1 2022-02-01
1    1 2022-02-02
2    1 2022-02-03
3    1 2022-02-04
4    1 2022-02-05
5    1 2022-02-06
6    1 2022-02-07
7    2 2022-02-01
8    2 2022-02-02
9    2 2022-02-03
10   2 2022-02-04
11   2 2022-02-05
12   2 2022-02-06
13   2 2022-02-07
14   2 2022-02-08

这里是:

print(df2)

   ID      Start        End
0   1 2022-02-02 2022-02-04
1   2 2022-02-04 2022-02-06

我尝试了here发布的一个解决方案,

idx = pd.IntervalIndex.from_arrays(df_2['Start'], df_2['End'], closed='both')
df_2.index=idx
df_1['event']=df_2.loc[df_1.Date,'event'].values

但是我得到了一个错误

KeyError: "[Timestamp('2022-02-01 00:00:00'), Timestamp('2022-02-07 00:00:00'), Timestamp('2022-02-08 00:00:00')] not in index"

由于并非所有日期都在索引中,因此此解决方案不起作用。
所以我想知道如何解决这个问题。
EDIT:例如,df2中的每个ID可以有多个间隔

data_df2 = StringIO("""
ID;Start;End
1;2022-02-02;2022-02-04
1;2022-02-06;2022-02-07
2;2022-02-04;2022-02-06
"""
)
p5fdfcr1

p5fdfcr11#

这是一个使用merge的左连接作业。
首先,将df2中的相关开始日期和结束日期添加到df1中的每个日期。其次,构造指示符变量,如果日期在间隔内,则为该变量赋值1,否则赋值0。

from io import StringIO
import pandas as pd
import numpy as np

# setup df1
data = StringIO("""
ID;Date
1;2022-02-01
1;2022-02-02
1;2022-02-03
1;2022-02-04
1;2022-02-05
1;2022-02-06
1;2022-02-07
2;2022-02-01
2;2022-02-02
2;2022-02-03
2;2022-02-04
2;2022-02-05
2;2022-02-06
2;2022-02-07
2;2022-02-08
"""
)
df1 = pd.read_csv(data, sep=";")
df1['Date'] = pd.to_datetime(df1['Date'])

# setup df2
data = StringIO("""
ID;Start;End
1;2022-02-02;2022-02-04
2;2022-02-04;2022-02-06
"""
)
df2 = pd.read_csv(data, sep=";")
df2['Start'] = pd.to_datetime(df2['Start'])
df2['End'] = pd.to_datetime(df2['End'])

# merge interval from df2 to dates in df1
df3 = df1.merge(
    df2,
    on='ID',
    how='left'
)

# create indicator variable
df3['indicator'] = np.where(
    (df3['Date']>=df3['Start'])&(df3['Date']<=df3['End']),
    1,
    0
)

要匹配所需的结果,现在可以删除日期超出间隔的行的开始日期和结束日期

df3.loc[df3['indicator']==0,['Start','End']] = pd.NaT

结果是:

ID       Date      Start        End  indicator
0    1 2022-02-01        NaT        NaT          0
1    1 2022-02-02 2022-02-02 2022-02-04          1
2    1 2022-02-03 2022-02-02 2022-02-04          1
3    1 2022-02-04 2022-02-02 2022-02-04          1
4    1 2022-02-05        NaT        NaT          0
5    1 2022-02-06        NaT        NaT          0
6    1 2022-02-07        NaT        NaT          0
7    2 2022-02-01        NaT        NaT          0
8    2 2022-02-02        NaT        NaT          0
9    2 2022-02-03        NaT        NaT          0
10   2 2022-02-04 2022-02-04 2022-02-06          1
11   2 2022-02-05 2022-02-04 2022-02-06          1
12   2 2022-02-06 2022-02-04 2022-02-06          1
13   2 2022-02-07        NaT        NaT          0
14   2 2022-02-08        NaT        NaT          0
    • 对已编辑问题的答复**

因为您需要能够将每个日期与所有间隔进行比较(以ID为条件),所以我认为这是可以采用的方法,但是,您可以选择减少生成的 Dataframe 。
您没有指定它应该是什么样子的。我可以想象以下内容适合您的情况:(1)如果至少有一个间隔包含该日期,则保留所有间隔。(2)如果没有这样的间隔,则仅保留一行,其中间隔的日期缺失,指示器的值为0。
如果是这样,那么在将上述代码应用于(新的)原始数据之后,执行以下命令

df3.groupby(['ID','Date'])[['Start','End','indicator']].apply(lambda x: x[x['indicator']==1] if x['indicator'].sum()>0 else pd.DataFrame([[pd.NaT,pd.NaT,0]], columns=x.columns))

生成的 Dataframe :

Start        End  indicator
ID Date                                          
1  2022-02-01 0         NaT        NaT          0
   2022-02-02 2  2022-02-02 2022-02-04          1
   2022-02-03 4  2022-02-02 2022-02-04          1
   2022-02-04 6  2022-02-02 2022-02-04          1
   2022-02-05 0         NaT        NaT          0
   2022-02-06 11 2022-02-06 2022-02-07          1
   2022-02-07 13 2022-02-06 2022-02-07          1
2  2022-02-01 0         NaT        NaT          0
   2022-02-02 0         NaT        NaT          0
   2022-02-03 0         NaT        NaT          0
   2022-02-04 17 2022-02-04 2022-02-06          1
   2022-02-05 18 2022-02-04 2022-02-06          1
   2022-02-06 19 2022-02-04 2022-02-06          1
   2022-02-07 0         NaT        NaT          0
   2022-02-08 0         NaT        NaT          0

如果有内存限制,可以在df1的块中顺序执行代码,然后将结果附加到初始化的 Dataframe 或列表中。

相关问题