python - leer - seleccionar columnas en pandas

Fusionar marcos de datos de pandas donde un valor está entre otros dos

Question

Esta pregunta ya tiene una respuesta aquí:

• ¿Cómo unir dos marcos de datos para los que los valores de columna están dentro de un cierto rango? 5 respuestas

Necesito fusionar dos marcos de datos de pandas en un identificador y una condición en la que una fecha en un marco de datos se encuentra entre dos fechas en el otro marco de datos.

El marco de datos A tiene una fecha ("fdate") y una ID ("cusip"):

Necesito fusionar esto con este marco de datos B:

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

En SQL esto sería trivial, pero la única forma en que puedo ver cómo hacer esto en pandas es fusionar primero incondicionalmente 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 realmente la mejor manera de hacer esto? Parece que sería mucho mejor si uno pudiera filtrar dentro de la fusión para evitar tener un marco de datos potencialmente muy grande después de la fusión pero antes de que el filtro se haya completado.

Answer 1

No hay una forma pandamica de hacer esto en este momento.

Esta respuesta solía ser sobre abordar el problema con el polimorfismo, que resultó ser una muy mala idea .

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

Numpy way with piecewise (Memoria pesada)

La función numpy.piecewise se puede usar para generar el comportamiento de una unión personalizada. Hay muchos gastos generales involucrados y no es una persecución muy eficiente, pero hace el trabajo.

Condiciones de producción 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 período de tiempo coincidió para cada una de las dos declaraciones de guerra que tenemos. Las condiciones pueden explotar con conjuntos de datos más grandes, ya que será la longitud del df izquierdo y el df derecho multiplicados.

La "magia" a trozos

Ahora, por partes, tomará el president_id de los términos y lo colocará en el war_declarations war_declarations 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 , espero que se permita 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 que funciona, mientras que, de hecho, no la tiene.

La única otra forma que podría descubrir es crear dos nuevas clases, una PointInTime y una Timespan

Ambos deben tener métodos __eq__ donde devuelven verdadero si un PointInTime se compara 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 subclasifico datetime porque los pandas considerarán que el dtype de la columna de objetos datetime es un dtype datetime, y dado que el intervalo de tiempo no lo es, los pandas se niegan silenciosamente a fusionarse en ellos.

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 llenar 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 de obras:

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 haberse incluido en esta unión en Timespan(datetime(2015,2,1), datetime(2015,4,1))

Pero no lo es.

Me imagino que los pandas comparan PointInTime(2015,3,3) con PointInTime(2015,2,2) y PointInTime(2015,2,2) 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 era igual a PointInTime(2015,2,2)

Algo así como esto:

Rose == Flower
Lilly != Rose

Por lo tanto:

Lilly != Flower

Editar:

Traté de hacer que todos los PointInTime fueran iguales entre sí, 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 otras ideas, por favor comente y puedo intentarlo.

Answer 2

Como dices, esto es bastante fácil en SQL, entonces ¿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

Answer 3

Debería 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.

Answer 4

Una solución de pandas sería excelente si se implementara de manera similar a foverlaps () del paquete data.table en R. Hasta ahora, he encontrado que Numpy''s piecewise () es eficiente. He proporcionado el código basado en una discusión anterior Fusionar marcos de datos 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)