Pregunta Fusionar marcos de datos de pandas donde un valor es entre otros dos [duplicar]


Esta pregunta ya tiene una respuesta aquí:

Necesito fusionar dos data frames de pandas en un identificador y una condición donde una fecha en un dataframe está entre dos fechas en el otro dataframe.

Dataframe A tiene una fecha ("fdate") y una identificación ("cusip"):

enter image description here

Necesito fusionar esto con este datagrama B:

enter image description here

en A.cusip==B.ncusip y A.fdate está entre B.namedt y B.nameenddt.

En SQL esto sería trivial, pero la única forma en que puedo ver cómo hacerlo en pandas es fusionar primero incondicionalmente en el identificador y luego filtrar en la condición de fecha:

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

¿Es esta la mejor manera de hacer esto? Parece que sería mucho mejor si se pudiera filtrar dentro de la combinación para evitar tener un dataframe potencialmente muy grande después de la fusión, pero antes de que el filtro se haya completado.


32
2018-06-03 18:33


origen


Respuestas:


Como dices, esto es bastante fácil en SQL, ¿por qué no hacerlo en SQL?

import pandas as pd
import sqlite3

#We'll use firelynx's 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)

df:

         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

17
2018-03-14 20:49



Deberías poder hacer esto ahora usando el paquete 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())

Creo que la respuesta de @ChuHo es buena. Creo que pandasql está haciendo lo mismo por ti. No he comparado los dos, pero es más fácil de leer.


9
2017-08-04 15:32



No hay forma pandemia de hacer esto en este momento.

Esta respuesta solía consistir en abordar el problema del polimorfismo, que resultó ser una muy mala idea.

Entonces el numpy.piecewise la función apareció en otra respuesta, pero con poca explicación, así que pensé en aclarar cómo se puede usar esta función.

Numpy manera con partes (memoria pesada)

los np.piecewise función se puede utilizar para generar el comportamiento de una unión personalizada. Hay una gran cantidad de gastos indirectos y no es muy eficiente, pero cumple su función.

Produciendo condiciones para unirse

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)]

Esta es una lista de matrices donde cada matriz nos dice si el término intervalo de tiempo coincide para cada una de las dos declaraciones de guerra que tenemos. Las condiciones pueden explotar con conjuntos de datos más grandes como será la longitud de la df izquierda y la df derecha multiplicada.

La "magia" por partes

Ahora por partes tomará el president_id de los términos y colocarlo en el war_declarations dataframe para cada una de las guerras correspondientes.

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

Ahora, para terminar este ejemplo, solo necesitamos fusionarnos regularmente en el nombre de los presidentes.

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

Polimorfismo (no funciona)

Quería compartir mis esfuerzos de investigación, por lo que incluso si esto no resuelve el problema, Yo espero que sea permitido vivir aquí como una respuesta útil al menos. Como es difícil detectar el error, alguien más puede intentarlo y pensar que tiene una solución de trabajo, mientras que de hecho, no es así.

La única otra manera que pude descubrir es crear dos nuevas clases, una PointInTime y una Timespan

Ambos deberían tener __eq__ métodos en los que devuelven verdadero si se compara un PointInTime con un Timespan que lo contiene.

Después de eso, puede llenar su DataFrame con estos objetos y unirse a las columnas en las que viven.

Algo como esto:

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 __ne__(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 __ne__(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)

Nota importante: no hago una subclase de fecha y hora porque los pandas considerarán que el tipo de la columna de los objetos de fecha y hora es un tipo de fecha y hora, y dado que el intervalo de tiempo no lo es, pandas se niega a fusionarse.

Si instanciamos dos objetos de estas clases, ahora se pueden comparar:

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

También podemos completar dos DataFrames con estos objetos:

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))]})

Y luego el tipo de fusión funciona:

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

Pero solo un poco.

PointInTime(2015,3,3) también debería haber sido incluido en esta unión en Timespan(datetime(2015,2,1), datetime(2015,4,1))

Pero no lo es.

Me imagino que los pandas se comparan PointInTime(2015,3,3) a PointInTime(2015,2,2) y hace la suposición de que, dado que no son iguales, PointInTime(2015,3,3) no puede ser igual a Timespan(datetime(2015,2,1), datetime(2015,4,1)), ya que este intervalo de tiempo fue igual a PointInTime(2015,2,2)

Más o menos así:

Rose == Flower
Lilly != Rose

Por lo tanto:

Lilly != Flower

Editar:

Traté de hacer que PointInTime sea igual el uno al otro, esto cambió el comportamiento de la unión para incluir el 2015-3-3, pero el 2015-2-2 solo se incluyó para el Timespan 2015-2-1 -> 2015-2 -5, entonces esto fortalece mi hipótesis anterior.

Si alguien tiene alguna otra idea, por favor comenten y puedo intentarlo.


6
2018-06-03 21:16



Una solución de pandas sería genial si se implementa de forma similar a foverlaps () del paquete data.table en R. Hasta ahora he encontrado numpy's piecewise () para ser eficiente. He proporcionado el código basado en una discusión anterior Fusionando dataframes basados ​​en el rango de fechas

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)

3
2018-02-13 07:20