Manejo de datos en Excel

Siguiendo con los post relacionados con el manejo de datos en excel, vamos a repasar una serie de funciones avanzadas en Excel que nos van a permitir ajustar a nuestras necesidades, datos copiados desde páginas web.

Las páginas que ofrecen resultados de partidos no suelen seguir un criterio único para mostrar estos datos. Las opciones más comunes son tres: presentar el resultado separado por un guión (1-1), por dos puntos (1:1), o en diferentes columnas (1 1).

El primer paso que debemos dar es copiar los datos y hacer un Pegado Especial > Texto en Excel. Cuando hacemos esto con resultados separados por guiones (1-1), Excel los interpreta como una fecha, siempre que no se presente ninguna incoherencia (días de la supuesta fecha, primer número normalmente, sea menor que 1 o mayor que 31, o cuando los meses sean menor que 1 o mayor que 12). Si Excel advierte una incoherencia en la supuesta fecha, copiará el resultado como una cadena de texto. Para salir de dudas y conocer con que formato ha pegado Excel los datos lo mas apropiado es utilizar la funcion =ESNUMERO(celda). Esta función nos devolvera verdadero o falso segun la celda contenga un valor o no. Hay que tener en cuenta que Excel trata las fechas y las horas como un numero, que representa el numero de días transcurridos desde el 1 de enero de 1990. Así, si el dato que hemos copiado lo ha pegado como una fecha, la funcion nos devolvera verdadero.

Otra manera de detectarlo es viendo la alineación de la celda. Una alineación a la izquierda se usa para cadena de texto y la alineación a la derecha para los números.

Así pues, tenemos 2 opciones, el dato ha sido pegado como fecha o como texto. Si se ha pegado como fecha debemos de utilizar las funciones =DIA(Dato) y MES(Dato). Con la funcion dia obtendremos los goles o puntos anotados por el equipo1 y con la funcion mes los del equipo 2.

Si los datos de partida estan en formato 1:1. Al pegarlos los interpretará como horas, con lo que para separar el marcador utilizaremos las funciones =HORA(Dato) (para obtener los goles del equipo de casa) y =MINUTO(Dato), para el segundo. con este formato segimos teniendo restricciones con respecto a los numeros a pegar y si Excel detecta una incoherencia, hará lo mismo que para el caso de las fechas, pegará los datos como texto.

Para estos casos en que Excel convierte los datos en texto podemos hacer lo siguiente. si los datos siempre tienen el mismo numero de digitos, por ejemplo resultados de futbol el 99% de las vecs los goles seran un solo digito, goles de balonmano 2 digitos (en partido completo). se pueden usar las funciones =DERECHA(Texto; Numero de Caracteres) e =IZQUIERDA(Texto; Numero de Caracteres) combinadas co las funciones =ESPACIOS(Texto) y =VALOR(Texto). las funciones izquierda y derecha son similares y lo que hacen es devolver una cantidad especifica de caracteres empezando por la izquierda o por la derecha del texto selecionado. Estas funciones siempre nos devuelven un texto, que convertiremos en numero con la funcion valor.

La función espacios es muy importante, ya que elimina del texto todos los espacios excepto los que hay entre palabras. nos servira para eliminar espacios sobrantes al comienzo y al final del texto.

El resultado final de todo esto seria algo asi (los datos son de BetExplorer.com)


En la siguiente entada veremos como podemos arreglar nuestros datos cuando tenemos entre los valores pegados textos y numeros, y como se pueden obtener automaticamente los nombres de los equipos.

¿Ha cambiado el Madrid su estilo de juego?

Desde el día 9 de Diciembre pasado, Juande Ramos se hizo con las riendas del Real Madrid sustituyendo a Bernd Schuster al frente del equipo. Es claro que cada entrenador tiene unas características particulares y le gusta un tipo de juego determinado. Cuando dirigen a un equipo, cambian jugadores, el esquema de juego, la disposición del equipo en el campo, o el tipo de juego. Todo con el fin de imprimir su sello particular en el equipo.

Esto podría ser una buena base para el comienzo de una tertulia después de una buena comida con los amigos, pero, ¿se podrá demostrar estadísticamente?. ¿Será posible demostrar que el Madrid de Juande tiene un estilo de juego diferente al de Schuster utilizando la estadística?. Pues a ello vamos en esta entrada.

Para hacerlo vamos a utilizar dos variables, los goles que ha marcado el Real Madrid y los que ha recibido en los partidos dirigidos por uno y otro técnico.

GOLES A FAVOR:


En el gráfico podemos ver los goles que ha marcado el Real Madrid en los dos períodos. A la izquierda tenemos los goles marcados con Juande como entrenador y a la izquierda los marcados con Schuster. Se puede apreciar que las líneas medias (lineas en verde) son bastante similares, con Schuster habían marcado una media de 2.36 goles por partido y con Juande ese valor baja hasta los 1.57. Hay una diferencia de casi 0.8 goles por partido a favor de Schuster, pero esta diferencia no es lo suficientemente grande como para que sea significativa en un test 2-Sample T test. Dejaremos la explicación de este test para futuras entradas.

GOLES EN CONTRA:


Si analizamos los goles en contra las diferencias se agrandan. Con Schuster el Madrid recibía una media de 1.71 goles por partido, mientras que con Juande el equipo defiende bastante mejor y encaja una media de 0.43. Bastante más de 1 gol menos de media por partido. Esta diferencia, utilizando el mismo test, SI ES SIGNIFICATIVA con un nivel de significación del 95%. Lo que quiere decir que es bastante improbable que esta diferencia entre las medias sea debida al azar, una racha de buena suerte, la disposición de partidos en el calendario, etc.

Con esto se puede concluir que Juande ha cambiado el estilo de juego que tenía el Madrid de Schuster y ahora son un equipo que defiende mejor y al que es más difícil hacerle un gol.

Así que la siguiente vez que alguien os plantee la típica pregunta de ¿para qué sirve la estadistica? podeís decir sin ningún temor a equivocaros, que sirve para comprobar que el Madrid de Juande defiende mejor que el de Schuster. Y quedareis como unos campeones.

Relacion entre cuota y probabilidad

Como ya hemos comentado en otras entradas existe una relación inversa entre la cuota y la probabilidad. Esto quiere decir que cuanto mayor es la cuota menor es la probabilidad teórica de que aparezca ese resultado.

En un juego justo, donde ninguno de los dos jugadores ganaría usando una estrategia de picks aleatorios a largo plazo, esta relación viene determinada por la siguiente fórmula:

Probabilidad = 1 / Cuota

Sin embargo ante las casas de apuestas tradicionales no estamos jugando a un juego justo. Las casas ofrecen unas cuotas relacionadas con la probabilidad de que aparezca ese resultado pero no podemos calcularla directamente con esta ecuación. Veamos por qué y como se puede calcular la probabilidad estimada de cada evento partiendo de las cuotas ofrecidas.

Tomaremos como ejemplo las cuotas que teníamos para el resultado exacto de sets, en el partido de Nadal contra Verdasco:

Nadal 3-0 @1.7
Nadal 3-1 @2.9
Nadal 3-2 @7
Verdasco 3-0 @25
Verdasco 3-1 @14.1
Verdasco 3-2 @13.5

Aquí tenemos todos los resultados posibles del partido. Si apostasemos la cantidad exacta para ganar 1 ud. a todos los resultado, al final del partido acertaríamos siempre uno de ellos y nuestro retorno sería de 1 ud. Estaríamos ante un suceso cierto o seguro. Pero ¿cuanto nos costaría esta apuesta?. Vamos a calcularlo.

Para el primer resultado si apostamos 1/1.7 = 0.588 uds nos devolverían 1 ud si Nadal gana 3-0, ya que apostaríamos 1/1.7 uds. a una cuota 1.7 con lo que el retorno sería de 1/1.7 x 1.7 = 1

Haciendo la misma operación con todos los resultados posibles tendríamos:


Para cada una de las opciones hemos apostado las unidades necesarias para que nos devuelvan 1 ud si acertamos (Apuesta Ganancia Unitaria). Al final, como ya hemos dicho, acertaremos una de ellas seguro con lo que la casa nos pagará 1 ud, pero hacer todas estas apuestas nos habrá costado un total de 1.26 uds. Es decir perderemos 0.26 uds por cada 1.26 uds. apostadas (un 20.6%). Mal negocio.

Visto desde el punto de vista de la casa, la cosa cambia. Pase lo que pase en el partido la casa va a pagar 1 ud y va a ingresar 1.26. Es decir va a pagar 1/1.26 = 79.31% de lo que ingresa, a esto se le llama fracción de pago. Si paga el 79.31% de lo que ingresa, ofertando estas cuotas, eso quiere decir que va a tener unos beneficios de 100 - 79.31 = 20.69%. ¡Justamente lo que nosotros perdemos!

Evidentemente la ecuación Probabilidad = 1 / Cuota no puede ser correcta en este tipo de juegos. Ya que 1 / Cuota es lo que nosotros hemos llamado Apuesta Ganancia Unitaria y la suma total de todas nuestras apuestas es 1.26 cuando debería ser 1, puesto que estamos ante la suma de las probabilidades de un suceso cierto. Entonces, ¿Cuales son las probabilidades correctas?. El cálculo sigue siendo bastante sencillo, añadiremos el término de la fracción de pago a la ecuación y con ello tendremos las probabilidades estimadas correctas. Así la ecuación quedaría:

Probabilidad = Fracción de pago / Cuota

Todo estos cálculos los he colocado en un nuevo libro de GoogleDocs que podeis encontrar en este enlace, y lo utilizaremos en la siguiente entrada para analizar los resultados esperados de apuestas equivalentes. Pero eso será otro día.