QuinielaML - Preparación de datos y gestión de modelo predictivo |

Artículo

Luis Angel Pére... · 5 sep, 2023 Lectura de 12 min

Open Exchange

#Bases de datos #Importación y Exportación de Datos #IntegratedML #InterSystems IRIS #Open Exchange

Continuamos con la serie de artículos relativos a la aplicación de QuinielaML. En este artículo vamos a tratar cómo preparamos los datos en crudo que hemos capturado mediante la funcionalidad de Embedded Python.

¡Bienvenidos a todos!

Happy Artist GIF by VIRTUTE - Find & Share on GIPHY

Introducción

Si recordáis el artículo anterior hemos capturado mediante Embedded Python los datos de los resultados de fútbol de Primera y Segunda División de los últimos 23 años desde una página web externa. Pues bien, ahora que tenemos los datos en crudo deberemos transformarlos y prepararlos para ayudar tanto al mantenimiento de la aplicación como a nuestro modelo de predicción para trabajar con ellos.

QUINIELA_Object.RawMatch

Veamos que forma tienen los datos que hemos capturado en nuestra base de datos de IRIS:

Como podéis ver en la siguiente captura, no difieren mucho de la información presente en la web de BDFutbol:

Tablas maestras:

De cara a facilitar un posterior mantenimiento de los datos comunes así como mejorar el desempeño del modelo de predicción hemos definido 2 tablas maestras en las que almacenaremos los equipos y los árbitros. Estas tablas únicamente constarán de la columna con el identificador del registro y el nombre. Echemos un vistazo a ambas:

QUINIELA_Object.Referee:

Correspondiente a la tabla maestra de árbitros.

QUINIELA_Object.Team

Correspondiente a la tabla maestra de árbitros.

Preparación de los datos

Perfecto, con nuestras tablas maestras y nuestros datos en crudo ya podemos comenzar el proceso de preparación de los datos para entrenar nuestro modelo. Aquí tenemos el Business Operation que se va a encargar de la preparación:

Class QUINIELA.BO.PrepareBO Extends Ens.BusinessOperation
{

Parameter INVOCATION = "Queue";
Method PrepareData(pRequest As QUINIELA.Message.PrepareRequest, pResponse As QUINIELA.Message.PrepareResponse) As %Status
{
    Set sc = $$$OK
    set pResponse = ##class(QUINIELA.Message.PrepareResponse).%New()
    set pResponse.Operation = pRequest.Operation
    
    set sqlTruncateTrain = "TRUNCATE TABLE QUINIELA_Object.MatchTrain"
    set statementTruncateTrain = ##class(%SQL.Statement).%New()
    set statusTruncateTrain = statementTruncateTrain.%Prepare(sqlTruncateTrain)
    if ($$$ISOK(statusTruncateTrain)) {
        set resultSetTruncateTrain = statementTruncateTrain.%Execute()
        if (resultSetTruncateTrain.%SQLCODE = 0) {
            set sqlMatchTrain = "INSERT INTO QUINIELA_Object.MatchTrain (Day, Division, Journey, LocalTeam, Referee, Result, VisitorTeam, IntDay) "_
                "SELECT "_
                "TO_DATE(RM.Day,'DD/MM/YYYY') AS DayTransformed, "_
                "RM.Division, "_
                "RM.Journey, "_
                "LT.ID as LocalTeam, "_
                "R.ID as Referee, "_
                "CASE WHEN CAST(RM.GoalsLocal As INTEGER) > CAST(RM.GoalsVisitor As INTEGER) THEN 1 WHEN CAST(RM.GoalsLocal As INTEGER) < CAST(RM.GoalsVisitor As INTEGER) THEN 2 ELSE 0 END as Result, "_
                "VT.ID as VisitorTeam, "_
                "CAST({fn CONCAT({fn CONCAT(SUBSTR(RM.Day,7,4),SUBSTR(RM.Day,4,2))},SUBSTR(RM.Day,1,2))} As INTEGER) as IntDay "_
                "FROM "_
                "QUINIELA_Object.RawMatch RM "_
                "LEFT JOIN QUINIELA_Object.Team LT ON UPPER(RM.LocalTeam) = UPPER(LT.Name) "_
                "LEFT JOIN QUINIELA_Object.Team VT ON UPPER(RM.VisitorTeam) = UPPER(VT.Name) "_
                "LEFT JOIN QUINIELA_Object.Referee R ON UPPER(RM.Referee) = UPPER(R.Name)"
            set statementMatchTrain = ##class(%SQL.Statement).%New()
            set statusMatchTrain = statementMatchTrain.%Prepare(sqlMatchTrain)
            if ($$$ISOK(statusMatchTrain)) {
                set resultSetMatchTrain = statementMatchTrain.%Execute()
                if (resultSetMatchTrain.%SQLCODE = 0) {
                    set sqlUpdateLocalStreak = "UPDATE QUINIELA_Object.MatchTrain SET QUINIELA_Object.MatchTrain.LocalStreak = "_
                        "(SELECT SUM(CASE WHEN IsVictory = 1 THEN 4-%VID ELSE 0 END) FROM "_
                        "(SELECT TOP 3 SubMatch.IntDay, "_
                        "CASE WHEN Result = 1 THEN 1 ELSE 0 END AS IsVictory "_
                        "FROM QUINIELA_Object.MatchTrain AS SubMatch "_
                        "WHERE "_
                        "UPPER(SubMatch.LocalTeam) = UPPER(QUINIELA_Object.MatchTrain.LocalTeam) "_
                        "AND SubMatch.IntDay < QUINIELA_Object.MatchTrain.IntDay "_
                        "ORDER BY SubMatch.IntDay DESC)) "
                    set statementUpdateLocalStreak = ##class(%SQL.Statement).%New()
                    set statusUpdateLocalStreak = statementUpdateLocalStreak.%Prepare(sqlUpdateLocalStreak)
                    if ($$$ISOK(statusUpdateLocalStreak)) {
                        set resultSetUpdateLocalStreak = statementUpdateLocalStreak.%Execute()
                        if (resultSetUpdateLocalStreak.%SQLCODE = 0) {
                            set sqlUpdateVisitorStreak = "UPDATE QUINIELA_Object.MatchTrain SET QUINIELA_Object.MatchTrain.VisitorStreak = "_
                                "(SELECT SUM(CASE WHEN IsVictory = 1 THEN 4-%VID ELSE 0 END) FROM "_
                                "(SELECT TOP 3 SubMatch.IntDay, "_
                                "CASE WHEN Result = 2 THEN 1 ELSE 0 END AS IsVictory "_
                                "FROM QUINIELA_Object.MatchTrain AS SubMatch "_
                                "WHERE "_
                                "UPPER(SubMatch.VisitorTeam) = UPPER(QUINIELA_Object.MatchTrain.VisitorTeam) "_
                                "AND SubMatch.IntDay < QUINIELA_Object.MatchTrain.IntDay "_
                                "ORDER BY SubMatch.IntDay DESC)) "
                            set statementUpdateVisitorStreak = ##class(%SQL.Statement).%New()
                            set statusUpdateVisitorStreak = statementUpdateVisitorStreak.%Prepare(sqlUpdateVisitorStreak)
                            if ($$$ISOK(statusUpdateVisitorStreak)) {
                                set resultSetUpdateVisitorStreak = statementUpdateVisitorStreak.%Execute()
                                set sc = statusUpdateVisitorStreak
                            }
                            else {
                                set sc = statusUpdateVisitorStreak
                            }
                        }
                    }
                    else {
                        set sc = statusUpdateLocalStreak
                    }
                }
            }
            else {
                set sc = statusMatchTrain
            }
        }
    }
    
    set pResponse.Status = "Finished"
    Return sc
}

XData MessageMap
{
<MapItems>
  <MapItem MessageType="QUINIELA.Message.PrepareRequest">
    <Method>PrepareData</Method>
  </MapItem>
</MapItems>
}

}
ObjectScript
ObjectScript

Veamos ahora en detalle cada una de las instrucciones de SQL que estamos lanzando:

Limpiamos la tabla de datos de entrenamiento:

TRUNCATE TABLE QUINIELA_Object.MatchTrainSQL
SQL

Lanzamos una inserción masiva en nuestra tabla de entrenamiento QUINIELA_Object.MatchTrain

INSERT INTO QUINIELA_Object.MatchTrain (Day, Division, Journey, LocalTeam, Referee, Result, VisitorTeam, IntDay) 
    SELECT
        TO_DATE(RM.Day,'DD/MM/YYYY') AS DayTransformed, 
        RM.Division, 
        RM.Journey, 
        LT.ID as LocalTeam, 
        R.ID as Referee, 
        CASE WHEN CAST(RM.GoalsLocal As INTEGER) > CAST(RM.GoalsVisitor As INTEGER) THEN 1 WHEN CAST(RM.GoalsLocal As INTEGER) < CAST(RM.GoalsVisitor As INTEGER) THEN 2 ELSE 0 END as Result, 
        VT.ID as VisitorTeam, 
        CAST({fn CONCAT({fn CONCAT(SUBSTR(RM.Day,7,4),SUBSTR(RM.Day,4,2))},SUBSTR(RM.Day,1,2))} As INTEGER) as IntDay 
    FROM
        QUINIELA_Object.RawMatch RM 
        LEFT JOIN QUINIELA_Object.Team LT ON UPPER(RM.LocalTeam) = UPPER(LT.Name) 
        LEFT JOIN QUINIELA_Object.Team VT ON UPPER(RM.VisitorTeam) = UPPER(VT.Name) 
        LEFT JOIN QUINIELA_Object.Referee R ON UPPER(RM.Referee) = UPPER(R.Name)SQL
SQL

Para ello sustituimos el literal con el nombre del árbitro y de los equipos por su referencia en las tablas maestras. También obtenemos el resultado a partir de los goles del partido, 0 para el empate, 1 para la victoria local y 2 para la visitante. Esta columna Result será la que defina nuestro modelo de predicción como un modelo de clasificación, es decir, cada partido pertenecerá a una de nuestras 3 clases (1, X o 2).

Calculamos la racha para cada equipo dependiendo si juegan como local o visitante. Hemos añadido estas columnas para mejorar, en la medida posible, el desempeño del modelo predictivo. Hemos supuesto que un equipo que lleva varias victorias consecutivas en casa o fuera es más sencillo que continúe con las victorias al estar "en racha". El cálculo se hace de la siguiente manera, se obtienen los 3 últimos partidos (en casa para el equipo que juega como local o fuera para el que lo hace de visitante), si ha ganado el último partido se le asigna 3 puntos, si ha ganado en penúltimo se le asigna 2 y si ha ganado el antepenúltimo se le asigna 1, finalmente se suman los puntos obtenidos dando un valor numérico a la racha. Racha local:

UPDATE QUINIELA_Object.MatchTrain SET QUINIELA_Object.MatchTrain.LocalStreak = 
    (SELECT SUM(CASE WHEN IsVictory = 1 THEN 4-%VID ELSE 0 END) FROM
        (SELECT TOP 3 SubMatch.IntDay, 
            CASE WHEN Result = 1 THEN 1 ELSE 0 END AS IsVictory 
        FROM QUINIELA_Object.MatchTrain AS SubMatch 
        WHERE
            UPPER(SubMatch.LocalTeam) = UPPER(QUINIELA_Object.MatchTrain.LocalTeam) 
            AND SubMatch.IntDay < QUINIELA_Object.MatchTrain.IntDay 
        ORDER BY SubMatch.IntDay DESC
        )
    )SQL
SQL

Racha visitante:

UPDATE QUINIELA_Object.MatchTrain SET QUINIELA_Object.MatchTrain.VisitorStreak = 
    (SELECT SUM(CASE WHEN IsVictory = 1 THEN 4-%VID ELSE 0 END) 
    FROM
        (SELECT TOP 3 SubMatch.IntDay, 
            CASE WHEN Result = 2 THEN 1 ELSE 0 END AS IsVictory 
        FROM QUINIELA_Object.MatchTrain AS SubMatch 
        WHERE
            UPPER(SubMatch.VisitorTeam) = UPPER(QUINIELA_Object.MatchTrain.VisitorTeam) 
            AND SubMatch.IntDay < QUINIELA_Object.MatchTrain.IntDay 
            ORDER BY SubMatch.IntDay DESC
        )
    )SQL
SQL

Veamos cual es el resultado de este conjunto de inserciones y actualizaciones consultando la tabla QUINIELA_Object.MatchTrain:

Como podéis ver hemos transformado los campos de texto a valores numéricos...¿o no? veamos la definición de la clase:

Class QUINIELA.Object.MatchTrain Extends (%Persistent, %JSON.Adaptor) [ DdlAllowed ]
{

/// Day of the match
Property Day As %Date;
/// Local Team
Property LocalTeam As %String;
/// Visitor Team
Property VisitorTeam As %String;
/// Local Streak
Property LocalStreak As %Integer;
/// Visitor Streak
Property VisitorStreak As %Integer;
/// Referee
Property Referee As %String;
/// Result
Property Result As %String;
/// Division
Property Division As %String;
/// Journey
Property Journey As %String;
/// Integer day
Property IntDay As %Integer;
}ObjectScript
ObjectScript

Como veis las referencias a las tablas maestras continúan siendo de tipo %String. ¿Cuál es el motivo de esto? Pues bien, podéis encontrar la explicación en esta página de la documentación, pero en resumen es debido a que, aunque realmente sean numéricos, no corresponden a valores cuantificables, si no a identificadores.

Perfecto, ya lo tenemos todo para crear y entrenar nuestro modelo predictivo.

Creación y entrenamiento del modelo de predicción

Gracias a la funcionalidad de IntegratedML este paso es extremadamente sencillo para nosotros, ya que sólo necesitaremos ejecutar 2 sencillas comandos en nuestra base de datos. Echemos un vistazo al Business Operation que hemos creado para ello:

Class QUINIELA.BO.TrainBO Extends Ens.BusinessOperation
{

Parameter INVOCATION = "Queue";
/// Description
Method CreateAndTrainModel(pRequest As QUINIELA.Message.TrainRequest, pResponse As QUINIELA.Message.TrainResponse) As %Status
{
        Set tSC = $$$OK
        set pResponse = ##class(QUINIELA.Message.TrainResponse).%New()
        set pResponse.Operation = pRequest.Operation
        set pResponse.Status = "In Process"
        set sql = "SELECT MODEL_NAME FROM INFORMATION_SCHEMA.ML_MODELS WHERE MODEL_NAME = 'QuinielaModel'"
        set statement = ##class(%SQL.Statement).%New()
        set status = statement.%Prepare(sql)
        $$$TRACE(status)
        if ($$$ISOK(status)) {
            set resultSet = statement.%Execute()
            $$$TRACE(resultSet.%SQLCODE)
            if (resultSet.%SQLCODE = 0) {
                while (resultSet.%Next() '= 0) {
                    if (resultSet.%GetData(1) '= "") {
                        set sqlDrop = "DROP MODEL QuinielaModel"
                        set statementDrop = ##class(%SQL.Statement).%New()
                        set statusDrop = statementDrop.%Prepare(sqlDrop)
                        if ($$$ISOK(statusDrop)) {
                            set resultSetDrop = statementDrop.%Execute()
                            if (resultSetDrop.%SQLCODE = 0) {
                                set tSC = statusDrop                                                                
                            }
                        }
                    }
                }
            }            
        }
        $$$TRACE("Creating model")
        set sqlCreate = "CREATE MODEL QuinielaModel PREDICTING (Result) FROM QUINIELA_Object.MatchTrain"
        set statementCreate = ##class(%SQL.Statement).%New()
        set statusCreate = statementCreate.%Prepare(sqlCreate)
        if ($$$ISOK(statusCreate)) {
            $$$TRACE("Model created")
            set resultSetCreate = statementCreate.%Execute()
            if (resultSetCreate.%SQLCODE = 0) {
                set tSC = statusCreate                                
            }
        }
        else
        {
            set tSC = statusDrop
        }

        $$$TRACE("Training model")
        set sqlTrain = "TRAIN MODEL QuinielaModel"
        set statementTrain = ##class(%SQL.Statement).%New()
        set statusTrain = statementTrain.%Prepare(sqlTrain)
        if ($$$ISOK(statusTrain)) {
            set resultSetTrain = statementTrain.%Execute()
            if (resultSetTrain.%SQLCODE = 0) {
                // VALIDATION OF THE MODEL WITH THE PRE-LOADED MATCHES
                set sqlValidate = "VALIDATE MODEL QuinielaModel FROM QUINIELA_Object.MatchTrain"
                set statementValidate = ##class(%SQL.Statement).%New()
                set statusValidate = statementValidate.%Prepare(sqlValidate)
                set resultSetValidate = statementValidate.%Execute()
                set tSC = statusValidate                                    
            }
        }
        else {
            set tSC = statusTrain
        }
        
        set pResponse.Status = "Finished"
        Return tSC
}

XData MessageMap
{
<MapItems>
  <MapItem MessageType="QUINIELA.Message.TrainRequest">
    <Method>CreateAndTrainModel</Method>
  </MapItem>
</MapItems>
}

}
ObjectScript
ObjectScript

Analicemos que está haciendo nuestro BO:

Creación del modelo predictivo y definición del valor a predecir indicando la columna de nuestra tabla de entrenamiento correspondiente.
```
CREATE MODEL QuinielaModel PREDICTING (Result) FROM QUINIELA_Object.MatchTrainSQL
SQL
```
Entrenamiento de nuestro modelo recien creado.
```
TRAIN MODEL QuinielaModelSQL
SQL
```
Validación del modelo creado sobre la tabla de entrenamiento utilizada.
```
VALIDATE MODEL QuinielaModel FROM QUINIELA_Object.MatchTrainSQL
SQL
```

Con estos tres sencillísimos pasos ya tendríamos listo nuestro modelo para empezar a generar predicciones. Echemos un vistazo a la calidad de nuestro modelo, para ello ejecutaremos la siguiente consulta:

SELECT * FROM INFORMATION_SCHEMA.ML_VALIDATION_METRICSSQL
SQL

Con esta consulta obtendremos las siguientes métricas:

Bueno, ni tan mal, para la victoria local acierta un 52% de las ocasiones, para la victoria visitante un 41% y para los empates un 37%, ¡estamos por encima del 33% de la aleatoriedad!

Boxing Memes on X: "Golovkin's fellow country man reacts to his victory... #GolovkinGeale http://t.co/MDW6F5eJlz" / X

Ir a la aplicación en InterSystems Open Exchange