使用 timedelta 将 df1 中的每一行的 pandas DataFrame

Question

我有两个熊猫数据框。我想将所有行保留在df2等于Typein TypeANDdf1之间Date（Date- df11 天或 + 1 天）。我怎样才能做到这一点？df1   IBSN  Type          Date0     1     X    2014-08-171     1     Y    2019-09-22df2   IBSN  Type          Date0     2     X    2014-08-161     2     D    2019-09-222     9     X    2014-08-183     3     H    2019-09-224     3     Y    2019-09-235     5     G    2019-09-22资源   IBSN  Type          Date0     2     X    2014-08-16 <-- keep because Type = df1[0]['Type'] AND Date = df1[0]['Date'] - 11     9     X    2014-08-18 <-- keep because Type = df1[0]['Type'] AND Date = df1[0]['Date'] + 12     3     Y    2019-09-23 <-- keep because Type = df1[1]['Type'] AND Date = df1[1]['Date'] + 1

Answer 1

这应该这样做：

import pandas as pd

from datetime import timedelta

# create dummy data

df1 = pd.DataFrame([[1, 'X', '2014-08-17'], [1, 'Y', '2019-09-22']], columns=['IBSN', 'Type', 'Date'])

df1['Date'] = pd.to_datetime(df1['Date'])  # might not be necessary if your Date column already contain datetime objects

df2 = pd.DataFrame([[2, 'X', '2014-08-16'], [2, 'D', '2019-09-22'], [9, 'X', '2014-08-18'], [3, 'H', '2019-09-22'], [3, 'Y', '2014-09-23'], [5, 'G', '2019-09-22']], columns=['IBSN', 'Type', 'Date'])

df2['Date'] = pd.to_datetime(df2['Date'])  # might not be necessary if your Date column already contain datetime objects

# add date boundaries to the first dataframe

df1['Date_from'] = df1['Date'].apply(lambda x: x - timedelta(days=1))

df1['Date_to'] = df1['Date'].apply(lambda x: x + timedelta(days=1))

# merge the date boundaries to df2 on 'Type'. Filter rows where date is between

# data_from and date_to (inclusive). Drop 'date_from' and 'date_to' columns

df2 = df2.merge(df1.loc[:, ['Type', 'Date_from', 'Date_to']], on='Type', how='left')

df2[(df2['Date'] >= df2['Date_from']) & (df2['Date'] <= df2['Date_to'])].\

    drop(['Date_from', 'Date_to'], axis=1)

请注意，根据您的逻辑，df2（3 Y 2014-09-23）中的第 4 行不应保留，因为其日期（2014）不在 df1（2019 年）的给定日期之间。

Answer 2

假设Date两个数据框中的列已经在 dtype 中datetime。我会构造IntervalIndex分配给df1. to的Map列Type。最后检查相等性以创建要切片的掩码df1df2

iix = pd.IntervalIndex.from_arrays(df1.Date + pd.Timedelta(days=-1), 

                                   df1.Date + pd.Timedelta(days=1), closed='both')

df1 = df1.set_index(iix)

s = df2['Date'].map(df1.Type)

df_final = df2[df2.Type == s]

Out[1131]:

   IBSN Type       Date

0     2    X 2014-08-16

2     9    X 2014-08-18

4     3    Y 2019-09-23