python - leer - seleccionar columnas en pandas
Fusionar marcos de datos de pandas donde un valor está entre otros dos (4)
Esta pregunta ya tiene una respuesta aquí:
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.
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.
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
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.
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)