Объединить pandas dataframes, где одно значение находится между двумя другими

Мне нужно объединить два фрейма pandas в идентификаторе и условие, когда дата в одном фрейме данных находится между двумя датами в другом фрейме данных.

Dataframe A имеет дату ( "fdate" ) и идентификатор ( "cusip" ):

Мне нужно объединить это с этим фреймом данных B:

on A.cusip==B.ncusip и A.fdate находится между B.namedt и B.nameenddt.

В SQL это было бы тривиально, но единственный способ увидеть, как это сделать в pandas, - это сначала безоговорочно объединить идентификатор и затем фильтровать условие даты:

df = pd.merge(A, B, how='inner', left_on='cusip', right_on='ncusip')
df = df[(df['fdate']>=df['namedt']) & (df['fdate']<=df['nameenddt'])]

Действительно ли это лучший способ сделать это? Похоже, что было бы намного лучше, если бы можно было фильтровать внутри слияния, чтобы избежать слияния потенциально очень большого фрейма после слияния, но до того, как фильтр завершился.

Ответ 1

Как вы говорите, это довольно просто в SQL, так почему бы не сделать это в SQL?

import pandas as pd
import sqlite3

#We'll use firelynx tables:
presidents = pd.DataFrame({"name": ["Bush", "Obama", "Trump"],
                           "president_id":[43, 44, 45]})
terms = pd.DataFrame({'start_date': pd.date_range('2001-01-20', periods=5, freq='48M'),
                      'end_date': pd.date_range('2005-01-21', periods=5, freq='48M'),
                      'president_id': [43, 43, 44, 44, 45]})
war_declarations = pd.DataFrame({"date": [datetime(2001, 9, 14), datetime(2003, 3, 3)],
                                 "name": ["War in Afghanistan", "Iraq War"]})
#Make the db in memory
conn = sqlite3.connect(':memory:')
#write the tables
terms.to_sql('terms', conn, index=False)
presidents.to_sql('presidents', conn, index=False)
war_declarations.to_sql('wars', conn, index=False)

qry = '''
    select  
        start_date PresTermStart,
        end_date PresTermEnd,
        wars.date WarStart,
        presidents.name Pres
    from
        terms join wars on
        date between start_date and end_date join presidents on
        terms.president_id = presidents.president_id
    '''
df = pd.read_sql_query(qry, conn)

ДФ:

         PresTermStart          PresTermEnd             WarStart  Pres
0  2001-01-31 00:00:00  2005-01-31 00:00:00  2001-09-14 00:00:00  Bush
1  2001-01-31 00:00:00  2005-01-31 00:00:00  2003-03-03 00:00:00  Bush

Ответ 2

В настоящий момент нет пандамического способа сделать это.

Этот ответ раньше касался проблемы с полиморфизмом, который считался очень плохой идеей.

Затем в другом ответе появилась функция numpy.piecewise, но с небольшим пояснением, поэтому я подумал, что уточню, как эта функция может быть б.

Путь в цифрах с кусочным (память тяжелая)

Функция np.piecewise может использоваться для генерации поведения пользовательского соединения. Существует много накладных расходов, и это не очень эффективно, но он выполняет эту работу.

Условия изготовления для соединения

import pandas as pd
from datetime import datetime


presidents = pd.DataFrame({"name": ["Bush", "Obama", "Trump"],
                           "president_id":[43, 44, 45]})
terms = pd.DataFrame({'start_date': pd.date_range('2001-01-20', periods=5, freq='48M'),
                      'end_date': pd.date_range('2005-01-21', periods=5, freq='48M'),
                      'president_id': [43, 43, 44, 44, 45]})
war_declarations = pd.DataFrame({"date": [datetime(2001, 9, 14), datetime(2003, 3, 3)],
                                 "name": ["War in Afghanistan", "Iraq War"]})

start_end_date_tuples = zip(terms.start_date.values, terms.end_date.values)
conditions = [(war_declarations.date.values >= start_date) &
              (war_declarations.date.values <= end_date) for start_date, end_date in start_end_date_tuples]

> conditions
[array([ True,  True], dtype=bool),
 array([False, False], dtype=bool),
 array([False, False], dtype=bool),
 array([False, False], dtype=bool),
 array([False, False], dtype=bool)]

Это список массивов, в которых каждый массив сообщает нам, соответствует ли временной интервал для каждой из двух указанных нами военных деклараций. Условия могут взорваться с более крупными наборами данных, так как это будет длина левого df и правильного умножения df.

Кусочно "волшебный"

Теперь кусочно возьмем president_id из терминов и поместим его в фреймворк war_declarations для каждой из соответствующих войн.

war_declarations['president_id'] = np.piecewise(np.zeros(len(war_declarations)),
                                                conditions,
                                                terms.president_id.values)
    date        name                president_id
0   2001-09-14  War in Afghanistan          43.0
1   2003-03-03  Iraq War                    43.0

Теперь, чтобы закончить этот пример, нам просто нужно регулярно сливаться с именем президентов.

war_declarations.merge(presidents, on="president_id", suffixes=["_war", "_president"])

    date        name_war            president_id    name_president
0   2001-09-14  War in Afghanistan          43.0    Bush
1   2003-03-03  Iraq War                    43.0    Bush

Полиморфизм (не работает)

Я хотел поделиться своими исследовательскими усилиями, поэтому даже если этот не решает проблему, я надеюсь, что ей будет разрешено жить здесь как полезный ответ. наименее. Так как трудно обнаружить ошибку, кто-то другой может попробовать это и подумать, что у них есть рабочее решение, а на самом деле они этого не делают.

Единственный другой способ, который я мог бы выяснить, - создать два новых класса: один PointInTime и один Timespan

Оба должны иметь методы __eq__, где они возвращают true, если PointInTime сравнивается с Timespan, который содержит его.

После этого вы можете заполнить свой DataFrame этими объектами и присоединиться к столбцам, в которых они живут.

Что-то вроде этого:

class PointInTime(object):

    def __init__(self, year, month, day):
        self.dt = datetime(year, month, day)

    def __eq__(self, other):
        return other.start_date < self.dt < other.end_date

    def __neq__(self, other):
        return not self.__eq__(other)

    def __repr__(self):
        return "{}-{}-{}".format(self.dt.year, self.dt.month, self.dt.day)

class Timespan(object):
    def __init__(self, start_date, end_date):
        self.start_date = start_date
        self.end_date = end_date

    def __eq__(self, other):
        return self.start_date < other.dt < self.end_date

    def __neq__(self, other):
        return not self.__eq__(other)

    def __repr__(self):
        return "{}-{}-{} -> {}-{}-{}".format(self.start_date.year, self.start_date.month, self.start_date.day,
                                             self.end_date.year, self.end_date.month, self.end_date.day)

Важное примечание: я не подклассы datetime, потому что pandas будет рассматривать dtype столбца объектов datetime как datetime dtype, а так как временной промежуток отсутствует, pandas молча отказывается объединиться с ними.

Если мы создадим два объекта этих классов, их можно сравнить:

pit = PointInTime(2015,1,1)
ts = Timespan(datetime(2014,1,1), datetime(2015,2,2))
pit == ts
True

Мы также можем заполнить два DataFrames этими объектами:

df = pd.DataFrame({"pit":[PointInTime(2015,1,1), PointInTime(2015,2,2), PointInTime(2015,3,3)]})

df2 = pd.DataFrame({"ts":[Timespan(datetime(2015,2,1), datetime(2015,2,5)), Timespan(datetime(2015,2,1), datetime(2015,4,1))]})

И затем слияние произведений:

pd.merge(left=df, left_on='pit', right=df2, right_on='ts')

        pit                    ts
0  2015-2-2  2015-2-1 -> 2015-2-5
1  2015-2-2  2015-2-1 -> 2015-4-1

Но только вид.

PointInTime(2015,3,3) также должен был быть включен в это соединение на Timespan(datetime(2015,2,1), datetime(2015,4,1))

Но это не так.

Я полагаю, что pandas сравнивает PointInTime(2015,3,3) с PointInTime(2015,2,2) и делает предположение, что поскольку они не равны, PointInTime(2015,3,3) не может быть равно Timespan(datetime(2015,2,1), datetime(2015,4,1)), так как этот промежуток времени был равен PointInTime(2015,2,2)

Пример:

Rose == Flower
Lilly != Rose

Таким образом:

Lilly != Flower

Edit:

Я попытался сделать все PointInTime равными друг другу, это изменило поведение соединения, чтобы включить 2015-3-3, но 2015-2-2 был включен только в Timespan 2015-2-1 → 2015-2-5, так что это усиливает мою вышеприведенную гипотезу.

Если у кого-то есть другие идеи, прокомментируйте, и я могу попробовать.

Ответ 3

Решение A pandas было бы замечательным, если бы реализовано аналогично foverlaps() из пакета data.table в R. До сих пор я нашел numpy кусочком(), чтобы быть эффективным. Я предоставил код, основанный на более раннем обсуждении Объединение фреймов данных на основе диапазона дат

A['permno'] = np.piecewise(np.zeros(A.count()[0]),
                                 [ (A['cusip'].values == id) & (A['fdate'].values >= start) & (A['fdate'].values <= end) for id, start, end in zip(B['ncusip'].values, B['namedf'].values, B['nameenddt'].values)],
                                 B['permno'].values).astype(int)

Ответ 4

Вы должны иметь возможность сделать это сейчас, используя пакет pandasql

import pandasql as ps

sqlcode = '''
select A.cusip
from A
inner join B on A.cusip=B.ncusip
where A.fdate >= B.namedt and A.fdate <= B.nameenddt
group by A.cusip
'''

newdf = ps.sqldf(sqlcode,locals())

Я думаю, что ответ от @ChuHo довольно хорош. Я считаю, что пандаскль делает то же самое для вас. Я не сравнивал эти два, но читать их легче.