vistas tips tablas rendimiento query para optimizar optimizacion lento grandes datos consultas cantidades python python-3.x pandas performance vectorization

tips - Python-Para bucle millones de filas



tips para mysql (4)

Tengo un marco de datos c con muchas columnas diferentes. Además, arr es un marco de datos que corresponde a un subconjunto de c : arr = c[c[''A_D''] == ''A''] .

La idea principal de mi código es iterar sobre todas las filas en el marco de datos c y buscar todos los casos posibles (en el marco de datos de arr ) donde deben ocurrir algunas condiciones específicas:

  • Solo es necesario iterar sobre las filas donde c[''A_D''] == D y c[''Already_linked''] == 0
  • La hour en el marco de datos arr debe ser menor que la hour_aux en el marco de datos c
  • La columna Already_linked del Already_linked de datos arr debe ser cero: arr.Already_linked == 0
  • La Terminal y el Operator deben ser iguales en el marco de datos c y arr

En este momento, las condiciones se almacenan utilizando tanto la indexación booleana como el grupo by get_group:

  • Agrupe el marco de datos arr para elegir el mismo operador y terminal: g = groups.get_group((row.Operator, row.Terminal ))
  • Elija solo las llegadas donde la hora es más pequeña que la hora en el marco de datos c y donde Ya está enlazado == 0: vb = g[(g.Already_linked==0) & (g.hour<row.hour_aux)]

Para cada una de las filas en el marco de datos c que verifica todas las condiciones, se crea un marco de datos vb . Naturalmente, este marco de datos tiene diferentes longitudes en cada iteración. Después de crear el marco de datos vb , mi objetivo es elegir el índice del marco de datos vb que minimiza el tiempo entre vb.START yc [ x ]. El FightID que corresponde a este índice se almacena en el marco de datos c en la columna a . Además, dado que la llegada estaba vinculada a una partida, la columna Already_linked en el marco de datos arr se cambia de 0 a 1.

Es importante tener en cuenta que la columna Already_linked del Already_linked de datos arr puede cambiar en cada iteración (y arr.Already_linked == 0 es una de las condiciones para crear el marco de datos vb ). Por lo tanto, no es posible paralelizar este código.

Ya he usado c.itertuples() para c.itertuples() eficiencia, sin embargo, dado que c tiene millones de filas, este código aún requiere demasiado tiempo.

Otra opción también sería usar pd.apply para cada fila. No obstante, esto no es realmente sencillo, ya que en cada ciclo hay valores que cambian tanto en c como en arr (también creo que incluso con pd.apply sería extremadamente lento).

¿Hay alguna forma posible de convertir esto para un bucle en una solución vectorizada (o disminuir el tiempo de ejecución en 10X (si es posible aún más))?

Marco de datos inicial:

START END A_D Operator FlightID Terminal TROUND_ID tot 0 2017-03-26 16:55:00 2017-10-28 16:55:00 A QR QR001 4 QR002 70 1 2017-03-26 09:30:00 2017-06-11 09:30:00 D DL DL001 3 " " 84 2 2017-03-27 09:30:00 2017-10-28 09:30:00 D DL DL001 3 " " 78 3 2017-10-08 15:15:00 2017-10-22 15:15:00 D VS VS001 3 " " 45 4 2017-03-26 06:50:00 2017-06-11 06:50:00 A DL DL401 3 " " 9 5 2017-03-27 06:50:00 2017-10-28 06:50:00 A DL DL401 3 " " 19 6 2017-03-29 06:50:00 2017-04-19 06:50:00 A DL DL401 3 " " 3 7 2017-05-03 06:50:00 2017-10-25 06:50:00 A DL DL401 3 " " 32 8 2017-06-25 06:50:00 2017-10-22 06:50:00 A DL DL401 3 " " 95 9 2017-03-26 07:45:00 2017-10-28 07:45:00 A DL DL402 3 " " 58

Salida deseada (algunas de las columnas se excluyeron en el marco de datos a continuación. Solo las columnas a y Already_linked son relevantes):

START END A_D Operator a Already_linked 0 2017-03-26 16:55:00 2017-10-28 16:55:00 A QR 0 1 1 2017-03-26 09:30:00 2017-06-11 09:30:00 D DL DL402 1 2 2017-03-27 09:30:00 2017-10-28 09:30:00 D DL DL401 1 3 2017-10-08 15:15:00 2017-10-22 15:15:00 D VS No_link_found 0 4 2017-03-26 06:50:00 2017-06-11 06:50:00 A DL 0 0 5 2017-03-27 06:50:00 2017-10-28 06:50:00 A DL 0 1 6 2017-03-29 06:50:00 2017-04-19 06:50:00 A DL 0 0 7 2017-05-03 06:50:00 2017-10-25 06:50:00 A DL 0 0 8 2017-06-25 06:50:00 2017-10-22 06:50:00 A DL 0 0 9 2017-03-26 07:45:00 2017-10-28 07:45:00 A DL 0 1

Código:

groups = arr.groupby([''Operator'', ''Terminal'']) for row in c[(c.A_D == "D") & (c.Already_linked == 0)].itertuples(): try: g = groups.get_group((row.Operator, row.Terminal)) vb = g[(g.Already_linked==0) & (g.hour<row.hour_aux)] aux = (vb.START - row.x).abs().idxmin() c.loc[row.Index, ''a''] = vb.loc[aux].FlightID arr.loc[aux, ''Already_linked''] = 1 continue except: continue c[''Already_linked''] = np.where((c.a != 0) & (c.a != ''No_link_found'') & (c.A_D == ''D''), 1, c[''Already_linked'']) c.Already_linked.loc[arr.Already_linked.index] = arr.Already_linked c[''a''] = np.where((c.Already_linked == 0) & (c.A_D == ''D''),''No_link_found'',c[''a''])

Código para el marco de datos inicial c :

import numpy as np import pandas as pd import io s = '''''' A_D Operator FlightID Terminal TROUND_ID tot A QR QR001 4 QR002 70 D DL DL001 3 " " 84 D DL DL001 3 " " 78 D VS VS001 3 " " 45 A DL DL401 3 " " 9 A DL DL401 3 " " 19 A DL DL401 3 " " 3 A DL DL401 3 " " 32 A DL DL401 3 " " 95 A DL DL402 3 " " 58 '''''' data_aux = pd.read_table(io.StringIO(s), delim_whitespace=True) data_aux.Terminal = data_aux.Terminal.astype(str) data_aux.tot= data_aux.tot.astype(str) d = {''START'': [''2017-03-26 16:55:00'', ''2017-03-26 09:30:00'',''2017-03-27 09:30:00'',''2017-10-08 15:15:00'', ''2017-03-26 06:50:00'',''2017-03-27 06:50:00'',''2017-03-29 06:50:00'',''2017-05-03 06:50:00'', ''2017-06-25 06:50:00'',''2017-03-26 07:45:00''], ''END'': [''2017-10-28 16:55:00'' ,''2017-06-11 09:30:00'' , ''2017-10-28 09:30:00'' ,''2017-10-22 15:15:00'',''2017-06-11 06:50:00'' ,''2017-10-28 06:50:00'', ''2017-04-19 06:50:00'' ,''2017-10-25 06:50:00'',''2017-10-22 06:50:00'' ,''2017-10-28 07:45:00'']} aux_df = pd.DataFrame(data=d) aux_df.START = pd.to_datetime(aux_df.START) aux_df.END = pd.to_datetime(aux_df.END) c = pd.concat([aux_df, data_aux], axis = 1) c[''A_D''] = c[''A_D''].astype(str) c[''Operator''] = c[''Operator''].astype(str) c[''Terminal''] = c[''Terminal''].astype(str) c[''hour''] = pd.to_datetime(c[''START''], format=''%H:%M'').dt.time c[''hour_aux''] = pd.to_datetime(c[''START''] - pd.Timedelta(15, unit=''m''), format=''%H:%M'').dt.time c[''start_day''] = c[''START''].astype(str).str[0:10] c[''end_day''] = c[''END''].astype(str).str[0:10] c[''x''] = c.START - pd.to_timedelta(c.tot.astype(int), unit=''m'') c["a"] = 0 c["Already_linked"] = np.where(c.TROUND_ID != " ", 1 ,0) arr = c[c[''A_D''] == ''A'']

Si bien esta no es una solución virtual, debería acelerar las cosas rápidamente si su conjunto de datos de muestra imita su verdadero conjunto de datos. Actualmente, está perdiendo el tiempo en cada fila, pero solo le importa hacer un ciclo en las filas donde [''A_D''] == ''D'' y [''Already_linked''] ==0 . En su lugar, elimine los if''s y haga un bucle sobre el marco de datos truncado, que es solo el 30% del marco de datos inicial

for row in c[(c.A_D == ''D'') & (c.Already_linked == 0)].itertuples(): vb = arr[(arr.Already_linked == 0) & (arr.hour < row.hour_aux)].copy().query(row.query_string) try: aux = (vb.START - row.x).abs().idxmin() print(row.x) c.loc[row.Index, ''a''] = vb.loc[aux,''FlightID''] arr.loc[aux, ''Already_linked''] = 1 continue except: continue

Su pregunta fue si hay una forma de vectorizar el bucle for, pero creo que esa pregunta oculta lo que realmente quiere, lo cual es una manera fácil de acelerar su código . Para preguntas de desempeño, un buen punto de partida es siempre perfilar. Sin embargo, tengo una fuerte sospecha de que la operación dominante en su código es .query(row.query_string) . Ejecutar eso para cada fila es costoso si arr es grande.

Para consultas arbitrarias, ese tiempo de ejecución no se puede mejorar en absoluto sin eliminar las dependencias entre iteraciones y paralelizar el paso costoso. Aunque podrías ser un poco más afortunado. La cadena de consulta siempre verifica dos columnas diferentes para ver si son iguales a algo que le importa. Sin embargo, para cada fila que requiere pasar por toda su porción de arr . Dado que la división cambia cada vez, eso podría causar problemas, pero aquí hay algunas ideas:

  • Ya que estás cortando arr cada vez de todas formas, mantén una vista de las filas arr.Already_Linked==0 para que estés iterando sobre un objeto más pequeño.
  • Mejor aún, antes de hacer un bucle, primero debe agrupar arr por Terminal y Operator . Luego, en lugar de ejecutar todo el arr , primero seleccione el grupo que desea y luego haga su rebanado y filtrado. Esto requeriría repensar un poco la implementación exacta de query_string , pero la ventaja es que si tiene muchos terminales y operadores, normalmente trabajará con un objeto mucho más pequeño que arr . Además, ni siquiera tendría que consultar ese objeto, ya que el grupo lo hizo implícitamente.
  • Dependiendo de cómo aux.hour se relacione típicamente con row.hour_aux , puede tener mejoras al clasificar aux al principio con respecto a la hour . Si solo utiliza el operador de desigualdad, probablemente no verá ninguna ganancia, pero puede emparejarlo con una búsqueda logarítmica del punto de corte y luego cortarlo hasta ese punto de corte.
  • Y así. De nuevo, sospecho que cualquier forma de reestructurar la consulta que está haciendo en todos los arr para cada fila ofrecerá sustancialmente más ganancias que solo cambiar los marcos o vectorizar bits y piezas.

Ampliando algunos de esos puntos un poco y adaptando un poco el código de @ DJK, observa lo que sucede cuando tenemos los siguientes cambios.

groups = arr.groupby([''Operator'', ''Terminal'']) for row in c[(c.A_D == ''D'') & (c.Already_linked == 0)].itertuples(): g = groups.get_group((row.Operator, row.Terminal)) vb = g[(g.Already_linked==0) & (g.hour<row.hour_aux)] try: aux = (vb.START - row.x).abs().idxmin() print(row.x) c.loc[row.Index, ''a''] = vb.loc[aux,''FlightID''] g.loc[aux, ''Already_linked''] = 1 continue except: continue

Parte de la razón por la que su consulta es tan lenta es porque está buscando todos los arr cada vez. En contraste, .groupby() ejecuta aproximadamente al mismo tiempo que una consulta, pero luego para cada iteración subsiguiente puede usar .get_group() para encontrar de manera eficiente el pequeño subconjunto de los datos que le interesan.

Una regla de oro útil (extremadamente cruda) cuando se hace una evaluación comparativa es que mil millones de cosas toman un segundo. Si está viendo tiempos mucho más largos que eso para algo medido en millones de cosas, como sus millones de filas, eso significa que para cada una de esas filas está haciendo toneladas de cosas para obtener miles de millones de operaciones. Eso deja un montón de potencial para que los mejores algoritmos reduzcan el número de operaciones, mientras que la vectorización en realidad solo produce mejoras de factores constantes (y para muchas operaciones de cadena / consulta ni siquiera grandes mejoras en eso).

Su problema parece uno de los problemas más comunes en la operación de la base de datos. No entiendo completamente lo que quiere obtener porque no ha formulado la tarea. Ahora a la posible solución - evitar bucles en absoluto .

Tiene una table muy larga con columnas de time, FlightID, Operator, Terminal, A_D . Otras columnas y fechas no importan si te entiendo correctamente. También start_time y end_time son iguales en cada fila. Por cierto, puede obtener la columna de time con el código table.loc[:, ''time''] = table.loc[:, ''START''].dt.time .

  1. table = table.drop_duplicates(subset=[''time'', ''FlightID'', ''Operator'', ''Terminal'']) . Y tu table volverá significativamente más corta.

  2. Dividir table en table_arr y table_dep según el valor A_D : table_arr = table.loc[table.loc[:, ''A_D''] == ''A'', [''FlightID'', ''Operator'', ''Terminal'', ''time'']] , table_dep = table.loc[table.loc[:, ''A_D''] == ''D'', [''FlightID'', ''Operator'', ''Terminal'', ''time'']]

  3. Parece que todo lo que intentaste obtener con los bucles puedes obtener con una sola línea: table_result = table_arr.merge(table_dep, how=''right'', on=[''Operator'', ''Terminal''], suffixes=(''_arr'', ''_dep'')) . Es básicamente la misma operación que JOIN en SQL.

De acuerdo con mi comprensión de su problema y con la pequeña cantidad de datos que nos ha proporcionado, obtiene la salida deseada (la correspondencia entre FlightID_dep y FlightID_arr para todos los valores de FlightID_dep ) sin ningún bucle mucho más rápido. table_result es:

FlightID_arr Operator Terminal time_arr FlightID_dep time_dep 0 DL401 DL 3 06:50:00 DL001 09:30:00 1 DL402 DL 3 07:45:00 DL001 09:30:00 2 NaN VS 3 NaN VS001 15:15:00

Por supuesto, en el caso general (con datos reales) necesitará un paso más: filtre table_result en la condición time_arr < time_dep o cualquier otra condición que tenga. Desafortunadamente, los datos que ha proporcionado no son suficientes para resolver completamente su problema.

El código completo es:

import io import pandas as pd data = '''''' START,END,A_D,Operator,FlightID,Terminal,TROUND_ID,tot 2017-03-26 16:55:00,2017-10-28 16:55:00,A,QR,QR001,4,QR002,70 2017-03-26 09:30:00,2017-06-11 09:30:00,D,DL,DL001,3,,84 2017-03-27 09:30:00,2017-10-28 09:30:00,D,DL,DL001,3,,78 2017-10-08 15:15:00,2017-10-22 15:15:00,D,VS,VS001,3,,45 2017-03-26 06:50:00,2017-06-11 06:50:00,A,DL,DL401,3,,9 2017-03-27 06:50:00,2017-10-28 06:50:00,A,DL,DL401,3,,19 2017-03-29 06:50:00,2017-04-19 06:50:00,A,DL,DL401,3,,3 2017-05-03 06:50:00,2017-10-25 06:50:00,A,DL,DL401,3,,32 2017-06-25 06:50:00,2017-10-22 06:50:00,A,DL,DL401,3,,95 2017-03-26 07:45:00,2017-10-28 07:45:00,A,DL,DL402,3,,58 '''''' table = pd.read_csv(io.StringIO(data), parse_dates=[0, 1]) table.loc[:, ''time''] = table.loc[:, ''START''].dt.time table = table.drop_duplicates(subset=[''time'', ''FlightID'', ''Operator'', ''Terminal'']) table_arr = table.loc[table.loc[:, ''A_D''] == ''A'', [''FlightID'', ''Operator'', ''Terminal'', ''time'']] table_dep = table.loc[table.loc[:, ''A_D''] == ''D'', [''FlightID'', ''Operator'', ''Terminal'', ''time'']] table_result = table_arr.merge( table_dep, how=''right'', on=[''Operator'', ''Terminal''], suffixes=(''_arr'', ''_dep'')) print(table_result)

Esta solución usa pd.DataFrame.isin que usa numpy.in1d

Al parecer, ''isin'' no es necesariamente más rápido para conjuntos de datos pequeños (como este ejemplo), pero es significativamente más rápido para conjuntos de datos grandes. Tendrá que ejecutarlo contra sus datos para determinar el rendimiento.

flight_record_linkage.ipynb

Expandió el conjunto de datos usando c = pd.concat([c] * 10000, ignore_index=True)

  • Aumente la longitud del conjunto de datos en 3 órdenes de magnitud (10000 filas en total).
    • Método original: Tiempo de pared: 8.98s
    • Nuevo método: tiempo de pared: 16.4s
  • Aumente la longitud del conjunto de datos en 4 órdenes de magnitud (100000 filas en total).
    • Método original: Tiempo de pared: 8min 17s
    • Nuevo método: tiempo de pared: 1min 14s
  • Aumente la longitud del conjunto de datos en 5 órdenes de magnitud (1000000 filas en total).
    • Nuevo método: tiempo de pared: 11min 33s

Nuevo método: usar isin y aplicar

def apply_do_g(it_row): """ This is your function, but using isin and apply """ keep = {''Operator'': [it_row.Operator], ''Terminal'': [it_row.Terminal]} # dict for isin combined mask holder1 = arr[list(keep)].isin(keep).all(axis=1) # create boolean mask holder2 = arr.Already_linked.isin([0]) # create boolean mask holder3 = arr.hour < it_row.hour_aux # create boolean mask holder = holder1 & holder2 & holder3 # combine the masks holder = arr.loc[holder] if not holder.empty: aux = np.absolute(holder.START - it_row.x).idxmin() c.loc[it_row.name, ''a''] = holder.loc[aux].FlightID # use with apply ''it_row.name'' arr.loc[aux, ''Already_linked''] = 1 def new_way_2(): keep = {''A_D'': [''D''], ''Already_linked'': [0]} df_test = c[c[list(keep)].isin(keep).all(axis=1)].copy() # returns the resultant df df_test.apply(lambda row: apply_do_g(row), axis=1) # g is multiple DataFrames" #call the function new_way_2()