2
\$\begingroup\$

I'm a bit new in python and I'm trying to achieve what I can't with VBA, get things faster.

I have large datasets like this in the source(just a sample): Sample data

This is done also by python and it's a CSV file.

What my code has to do with this is:

  1. Get the column "Planned" And "Department Planned".
  2. Convert the schedules in "Planned" column to 30 min intervals between starting and ending. Some schedules can be weird like 09:37-15:42 (so they need to be handled, normalizing the intervals from 9:30 to 15:30)
  3. With that list, calculate the minutes worked in another column refering to the original schedule, so in the example 09:37 will have an interval starting from 9:30 but there will be only 23 mins working there.
  4. explode the columns from points 2 and 3 to rows repeating the column Department Planned and Location NAme.
  5. Group by: Location Name, Date Time (from point 3), Department Planned. Adding the minutes worked.

This is the code:

#calcula_Presentes_Contratados
import pandas as pd
from itertools import groupby
from datetime import datetime, timedelta
def configuraDataFrame(df, horario, modo):
    #drop unwanted columns and filter empty rows
    df = df[df.columns.intersection([
                                        'CentroPlanificacion', 
                                        'Fecha',
                                        horario,
                                        modo
                                        ])]
    df = df[df[horario].notna()]
    if df.empty:
        return df       
    return df[df[horario].str.contains(":")]
def fechaYHora(horario, fecha, posicion):
    #calculate time and date for each start and ending time if the ending time < starting time, add one day to the ending.
    hora = datetime.strptime(horario.split('-')[posicion], '%H:%M')
    fecha = datetime.strptime(fecha, '%Y-%m-%d')
    if posicion == 1: 
        horaI = datetime.strptime(horario.split('-')[0], '%H:%M')
        horaI = datetime(fecha.year, fecha.month, fecha.day, horaI.hour, 0)
    if hora.minute >= 30:
        hora = datetime(fecha.year, fecha.month, fecha.day, hora.hour, 30)
    else:
        hora = datetime(fecha.year, fecha.month, fecha.day, hora.hour, 0)
    if posicion == 1 and hora < horaI: hora += timedelta(days=1)
    return hora
def extraeRangos(horario, fecha, aplicaSeparador):
    try:
        #get all match schedules if there are any
        partido = horario.split('/')
        listahorarios = []        
        #loop through al the schedules and add the range with the split separator "Separa aquí"
        for horarios in partido:
            rangoactual = pd.date_range(start=fechaYHora(horarios, fecha, 0), end=fechaYHora(horarios, fecha, 1), freq="30min").strftime('%Y-%m-%d, %H:%M').to_list()        
            listahorarios += rangoactual
            if aplicaSeparador: listahorarios.append('Separa aquí')  
        return listahorarios
    except:
        return
def calculaMinutosInicio(horaInicio, tramoInicial):
    #function to calculate starting minutes
    horaInicio = datetime.strptime(horaInicio,'%H:%M')
    tramoFin = datetime.strptime(tramoInicial.split(", ")[1], '%H:%M') + timedelta(minutes=30)
    if tramoFin < horaInicio: horaFin += timedelta(days=1)
    return tramoFin - horaInicio
def calculaMinutosFin(horaFin, tramoFin):
    #function to calculate ending minutes
    horaFin = datetime.strptime(horaFin,'%H:%M')
    if horaFin.minute == 0 or horaFin.minute == 30: return timedelta(seconds=0)
    tramoFin = datetime.strptime(tramoFin.split(", ")[1], '%H:%M')
    return horaFin - tramoFin
def generaListasPorPartido(lista_tramos):
    lista_final = []
    listaTemp = []
    #loop through the list looking for "Separa aquí"
    for tramo in lista_tramos:
        #if found, all the items stored before in the list will go in a single list
        if tramo == 'Separa aquí':
            lista_final.append(listaTemp)
            listaTemp = []
        #store all items in between "Separa aquí"
        else:
            listaTemp.append(tramo)
    #return a list of lists for each matching schedule
    return lista_final
def generaListaTramos(lista_tramos, horario):
    listatramos = []
    #calculate starting minutes from starting time to starting interval
    minutosInicio = int(calculaMinutosInicio(horario.split("-")[0], lista_tramos[0]).seconds / 60)
    #calculate ending minutes from ending time to ending interval
    minutosFin = int(calculaMinutosFin(horario.split("-")[1], lista_tramos[-1]).seconds / 60)
    for tramo in lista_tramos:
        #the first index will have the starting minutes
        if lista_tramos.index(tramo) == 0:
            listatramos.append(minutosInicio)
        #the last index will have the ending minutes
        elif tramo == lista_tramos[-1]:
            listatramos.append(minutosFin)
        #every interval in between will always have 30 minutes
        else:
            listatramos.append(30)
    return listatramos
def calculaConexion(lista_tramos, horario):
    #if the list is not empty
    if len(lista_tramos) > 0:
        listatramos = []      
        #gnerate a list for each match schedule
        lista_tramos = generaListasPorPartido(lista_tramos)
        horario: horario = horario.split("/")
        #add the working minutes to each interval
        for i in range(len(lista_tramos)):
            listatramos.extend(generaListaTramos(lista_tramos[i], horario[i]))      
        return listatramos
def eliminaSeparadores(lista):    
    return list(filter(('Separa aquí').__ne__, lista))
def generaRangoHoras(df, campo, nuevo_campo):
    #generate range of times column
    df[nuevo_campo] = df.apply(lambda row: extraeRangos(row[campo], row['Fecha'], True), axis=1)
    #generate working minutes column
    df['Conexión_' + nuevo_campo] = df.apply(lambda row: calculaConexion(row[nuevo_campo], row[campo]), axis=1)
    #get rid of the manual separator for match schedules
    df[nuevo_campo] = df.apply(lambda row: eliminaSeparadores(row[nuevo_campo]), axis=1)
    return df
def explotaHoras(df, listaSinExplotar):
    df = df.set_index(listaSinExplotar).apply(pd.Series.explode).fillna('').reset_index()
    return df
def main(rutaficheros):
    #read soruce file
    rutaExcel = rutaficheros + 'BaseACC.csv'
    dfOrigen = pd.read_csv(rutaExcel, converters={i: str for i in range(100)}) 
    #create lists to loop   
    listaCamposHorario = ['Programado', 'Programado 2']
    listaCamposModo = ['Modo Planificado', 'Modo Planificado 2']
    #for each item in list, create the time range, working minutes and export the file
    for horario, modo in zip(listaCamposHorario, listaCamposModo):
        df = configuraDataFrame(dfOrigen, horario, modo)
        if not df.empty:
            df = generaRangoHoras(df, horario, 'Franjas_Programado')
            df = explotaHoras(df, ['CentroPlanificacion', 'Fecha', horario, modo])
            #drop unwanted columns for the final result
            df = df.drop([horario, 'Fecha'], axis=1)
            #drop unwanted rows for the final result
            df = df[df['Conexión_Franjas_Programado'] > 0]
            #group all the working minutes by location, planned/planned2 and department/department2
            df = df.groupby(['CentroPlanificacion', modo, 'Franjas_Programado']).agg({'Conexión_Franjas_Programado' : 'sum'}).reset_index()
            df.to_csv(rutaficheros + horario + '.csv', index=False)
    return

And this should be the output:

Output files

The output file:

Output file

This takes around 35 seconds when I have a 100k rows source file and I think it could be better but I'm still very new in python to know more efficient ways to code.

Sample data

Dirección,Territorio,DNI,Agente,CentroPlanificacion,Campaña Origen,Fecha,Presentes,Contratados,ModoPlanificacion,Habilidades Agente,Programado,Modo Planificado,Programado 2,Modo Planificado 2
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-01,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-02,09:00-15:00,09:00-15:00,Department1,Department1,09:00-13:00,Department1,13:00-15:00,Department2
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-03,09:00-15:00,09:00-15:00,Department1,Department1,09:00-10:00/11:00-14:00/15:00-18:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-04,09:00-15:00,09:00-15:00,Department1,Department1,22:00-06:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-05,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-06,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-07,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-08,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-09,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-10,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-11,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-12,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-13,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-14,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-15,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-16,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-17,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-18,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-19,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-20,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-21,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-22,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-23,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-24,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-25,VAC,09:00-15:00,Department1,Department1,VAC,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-26,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-27,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-28,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-29,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-30,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-31,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
Improve this question
\$\endgroup\$
6
  • \$\begingroup\$ Does calculaMinutosInicio work? It refers to a horaFin that is not defined in scope. \$\endgroup\$
    – Reinderien
    May 17, 2022 at 17:46
  • \$\begingroup\$ @Reinderien I've uploaded to my github the same sample I'm using thought changing everything (not important) so there is no problem. Also the horaFin is wrong, shoudl be tramoFin \$\endgroup\$
    – Damian
    May 17, 2022 at 19:01
  • 1
    \$\begingroup\$ @Reinderien now I've pasted the raw data from a CSV file. Should work pasting this to a CSV back. \$\endgroup\$
    – Damian
    May 18, 2022 at 6:04
  • 1
    \$\begingroup\$ The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles. \$\endgroup\$
    – Toby Speight
    May 19, 2022 at 10:02
  • 1
    \$\begingroup\$ @TobySpeight I think now it's ok. \$\endgroup\$
    – Damian
    May 20, 2022 at 12:53

2 Answers 2

Reset to default
2
\$\begingroup\$

Consider revamping your current setup to work mostly in Pandas methods that operates on whole DataFrames and Series objects as opposed to mostly built-in Python methods on scalar values that loop and append to lists.

Beginners should know Pandas/Numpy programming is markedly different than general purpose Python programming. Below is working solution for one shift column on sample data. Of course, adjust and expand as needed to meet actual, larger data.

Input

For demonstration, I changed the shift start of very first record to 9:37 and shift end of very last record to 13:37.

from io import StringIO
import pandas as pd

txt = '''Dirección,Territorio,DNI,Agente,CentroPlanificacion,Campaña Origen,Fecha,Presentes,Contratados,ModoPlanificacion,Habilidades Agente,Programado,Modo Planificado,Programado 2,Modo Planificado 2
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-01,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-02,09:00-15:00,09:00-15:00,Department1,Department1,09:37-13:00,Department1,13:00-15:00,Department2
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-03,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-04,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-05,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-06,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-07,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-08,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-09,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-10,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-11,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-12,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-13,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-14,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-15,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-16,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-17,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-18,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-19,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-20,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-21,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-22,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-23,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-24,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-25,VAC,09:00-15:00,Department1,Department1,VAC,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-26,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-27,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-28,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-29,LIBRE,LIBRE,Department1,Department1,LIBRE,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-30,09:00-15:00,09:00-15:00,Department1,Department1,09:00-15:00,Department1,,
BussinessName,TerritoryNAme,WorkerID1,WorkerName1,Location1,Department1,2022-05-31,09:00-15:00,09:00-15:00,Department1,Department1,09:00-13:37,Department1,,'''

with StringIO(txt) as f:
    raw_df = pd.read_csv(f)

Cleaning

See Series.replace, Series.split, Series.str.cat, pandas.to_datetime, and pandas.notnull.

# SUBSET COLUMNS
raw_df = raw_df[
    ["Territorio", "Campaña Origen", "Fecha", "Programado", "Modo Planificado"]
]

# SPLIT SHIFT RANGE
raw_df[["Shift_Start", "Shift_End"]] = (
    raw_df["Programado"].replace({"LIBRE": None, "VAC": None})
       .str.split("-", 1, expand=True)
)

# CONCATENATE DAY AND TIME, THEN CONVERT TO DATETIME
raw_df["Shift_Start"], raw_df["Shift_End"] = (
    pd.to_datetime(raw_df["Fecha"].str.cat(raw_df["Shift_Start"], sep= " ")),
    pd.to_datetime(raw_df["Fecha"].str.cat(raw_df["Shift_End"], sep=" "))
)

# REMOVE FREE DAYS
raw_df = raw_df[
    pd.notnull(raw_df["Shift_Start"]) & pd.notnull(raw_df["Shift_End"])
]

Expanding Rows

See pandas.date_range, pandas.Timestamp.floor, pandas.Timedelta, DataFrame.loc, DataFrame.iterrows, and pandas.concat.

def expand_row_by_dates(row):
    # BUILD RANGE FROM START TO END IN 30 MIN INTERVALS
    shift_range = pd.date_range(
        row["Shift_Start"].floor('30min'),
        row["Shift_End"].floor('30min'), 
        freq='30min'
    )

    # BUILD NEW LONGER DATA FRAME, BINDING ROW VALUES AND DATE RANGE
    df = pd.DataFrame({
        "Territorio": row["Territorio"], 
        "Campaña Origen": row["Campaña Origen"], 
        "Fecha": row["Fecha"],
        "Shift_Date": shift_range,
        "Minutes_Worked": 30
    })
    
    # RE-CALCULATE FIRST 30 MIN INTERVAL TO ACTUAL
    diff = (
        (row["Shift_Start"] - df["Shift_Date"].min()) / 
        pd.Timedelta(minutes=1)
    )
    df.loc[df.index[0], "Minutes_Worked"] = (30-diff) if diff != 0 else 30)    
        
    # RE-CALCULATE LAST 30 MIN INTERVAL TO ACTUAL
    diff = (
        (row["Shift_End"] - df["Shift_Date"].max()) / 
        pd.Timedelta(minutes=1)
    )
    df.loc[df.index[-1], "Minutes_Worked"] = (diff if diff != 0 else 30)
    
    return df

# ITERATE THROUGH ROWS AND COMPILE EXPANDED DATA FRAMES
shifts_df = pd.concat(
    [expand_row_by_dates(row) for i, row in raw_df.iterrows()],
    ignore_index=True
)

Output

shifts_df

        Territorio Campaña Origen       Fecha          Shift_Date  Minutes_Worked
0    TerritoryNAme    Department1  2022-05-02 2022-05-02 09:30:00              23
1    TerritoryNAme    Department1  2022-05-02 2022-05-02 10:00:00              30
2    TerritoryNAme    Department1  2022-05-02 2022-05-02 10:30:00              30
3    TerritoryNAme    Department1  2022-05-02 2022-05-02 11:00:00              30
4    TerritoryNAme    Department1  2022-05-02 2022-05-02 11:30:00              30
..             ...            ...         ...                 ...             ...
260  TerritoryNAme    Department1  2022-05-31 2022-05-31 11:30:00              30
261  TerritoryNAme    Department1  2022-05-31 2022-05-31 12:00:00              30
262  TerritoryNAme    Department1  2022-05-31 2022-05-31 12:30:00              30
263  TerritoryNAme    Department1  2022-05-31 2022-05-31 13:00:00              30
264  TerritoryNAme    Department1  2022-05-31 2022-05-31 13:30:00               7
Improve this answer
\$\endgroup\$
9
  • 1
    \$\begingroup\$ Thank you Parfait, will give this a try and report back! \$\endgroup\$
    – Damian
    May 19, 2022 at 6:10
  • 1
    \$\begingroup\$ Wonderful approach! Just had to tweak a little thing things. where you recalculate the last interval. If someone has 9:00-15:00 for example, at 15:00 their working minutes are 0 so should be like this df.loc[df.index[-1], "Minutes_Worked"] = (diff if diff != 0 else 0) \$\endgroup\$
    – Damian
    May 20, 2022 at 11:05
  • \$\begingroup\$ Funny thing though, this code takes twice as much... Maybe isn't that efficient with large datasets? The sample is only 30 rows, but the sample data is around 100k Parfait. \$\endgroup\$
    – Damian
    May 20, 2022 at 12:52
  • \$\begingroup\$ Hmmmm...Only explicit loop is iterrows. Thinking now it is expanding shift duration in 30 min intervals for each of 100k days. See if pands.concat is bottleneck by just running the list comprehension with iterrows. Maybe date_range is a hidden loop. Might be a numpy alternative. \$\endgroup\$
    – Parfait
    May 20, 2022 at 13:19
  • 1
    \$\begingroup\$ Finally understood why, your code runs fast, mine does too but I had a flaw preventing pandas from reading the whole dataframe when yours was doing it fine. \$\endgroup\$
    – Damian
    May 21, 2022 at 9:04
2
\$\begingroup\$

Things that are good to stay in Spanish: your starting input file, your final output file. Everything else - your variable names, function names, comments, parameter names, and the column names of your intermediate data-processing dataframes - should all be in English. You're halfway there already. For better or worse, English is the de-facto language of programming, and it's important that you be consistent for the purposes of international collaboration.

By PEP8 your comments # should have a space between the hash and the text. There are lots of other PEP8 issues that will be picked up if you (a) write this within a reasonable IDE like PyCharm, and (b) follow its recommendations. An important one: two blank lines between every function.

Also by PEP8, use lower_snake_case for function and variable names; i.e. fechaYHora would be date_and_time (better yet, get_date_and_time which is an action phrase).

You have some variable names - listatramos and lista_tramos - that only differ by underscore within the same scope. This is a deeply bad idea. Instead use perhaps old_ranges and new_ranges (if that's what these actually represent).

You need to decompose every single df.apply that you have into vectorised operations. I don't think a single one of them needs to exist. Each of these needs to be replaced with a series of operations that operates on the whole dataframe at once. This is probably the source of most of your performance trouble.

Related: list-wise operations such as those in generaListasPorPartido also need to go away and be replaced with vectorised operations.

In extraeRangos your try/except needs to go away. If there are errors, either your code is wrong and you need to fix it, or there are edge cases in the data that you need to take care of explicitly.

Don't do this:

converters={i: str for i in range(100)}

Instead, load the dataframe with proper datetimes where they exist; basically:

parse_dates=['Fecha']

I encourage you to work on all of the above, and then once you're satisfied and ready for another round of review, come back to this site and post a new question with the new code.

Improve this answer
\$\endgroup\$
2
  • \$\begingroup\$ That's the thing here Reinderien. I don't know how to change the df.apply into vectorised operations... I was looking here for some directions or possible replacement for the highest time consuming functions. About the code, will get everything you taught me here and replace everything. But if you could consider telling me how to change some logic that I have in UDF to vectorised operations... Would be so helpfull! \$\endgroup\$
    – Damian
    May 18, 2022 at 14:34
  • 1
    \$\begingroup\$ You need to do some self-guided research, particularly around the Pandas support for vectorised string and datetime operations. \$\endgroup\$
    – Reinderien
    May 18, 2022 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.