Resultados.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - hive</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://jupyter-spark:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v2.4.1</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>pyspark-shell</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7fa7dc02bda0>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.csv(\"/bases/results.csv\",header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----------------+-------------+--------------+--------------+--------------+---------+-------------------+-----------+\n",
      "|      date|   home_teamName|away_teamName|home_scoreHome|away_scoreAway|tournamentName| cityCity|     countryCountry|neutralTRUE|\n",
      "+----------+----------------+-------------+--------------+--------------+--------------+---------+-------------------+-----------+\n",
      "|1872-11-30|        Scotland|      England|             0|             0|      Friendly|  Glasgow|           Scotland|      FALSE|\n",
      "|1873-03-08|         England|     Scotland|             4|             2|      Friendly|   London|            England|      FALSE|\n",
      "|1874-03-07|        Scotland|      England|             2|             1|      Friendly|  Glasgow|           Scotland|      FALSE|\n",
      "|1875-03-06|         England|     Scotland|             2|             2|      Friendly|   London|            England|      FALSE|\n",
      "|1876-03-04|        Scotland|      England|             3|             0|      Friendly|  Glasgow|           Scotland|      FALSE|\n",
      "|1876-03-25|        Scotland|        Wales|             4|             0|      Friendly|  Glasgow|           Scotland|      FALSE|\n",
      "|1877-03-03|         England|     Scotland|             1|             3|      Friendly|   London|            England|      FALSE|\n",
      "|1877-03-05|           Wales|     Scotland|             0|             2|      Friendly|  Wrexham|              Wales|      FALSE|\n",
      "|1878-03-02|        Scotland|      England|             7|             2|      Friendly|  Glasgow|           Scotland|      FALSE|\n",
      "|1878-03-23|        Scotland|        Wales|             9|             0|      Friendly|  Glasgow|           Scotland|      FALSE|\n",
      "|1879-01-18|         England|        Wales|             2|             1|      Friendly|   London|            England|      FALSE|\n",
      "|1879-04-05|         England|     Scotland|             5|             4|      Friendly|   London|            England|      FALSE|\n",
      "|1879-04-07|           Wales|     Scotland|             0|             3|      Friendly|  Wrexham|              Wales|      FALSE|\n",
      "|1880-03-13|        Scotland|      England|             5|             4|      Friendly|  Glasgow|           Scotland|      FALSE|\n",
      "|1880-03-15|           Wales|      England|             2|             3|      Friendly|  Wrexham|              Wales|      FALSE|\n",
      "|1880-03-27|        Scotland|        Wales|             5|             1|      Friendly|  Glasgow|           Scotland|      FALSE|\n",
      "|1881-02-26|         England|        Wales|             0|             1|      Friendly|Blackburn|            England|      FALSE|\n",
      "|1881-03-12|         England|     Scotland|             1|             6|      Friendly|   London|            England|      FALSE|\n",
      "|1881-03-14|           Wales|     Scotland|             1|             5|      Friendly|  Wrexham|              Wales|      FALSE|\n",
      "|1882-02-18|Northern Ireland|      England|             0|            13|      Friendly|  Belfast|Republic of Ireland|      FALSE|\n",
      "+----------+----------------+-------------+--------------+--------------+--------------+---------+-------------------+-----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "resultados = df.drop('neutralTRUE') \\\n",
    "    .withColumnRenamed('date','data') \\\n",
    "    .withColumnRenamed('home_teamName','mandante') \\\n",
    "    .withColumnRenamed('away_teamName','visitante') \\\n",
    "    .withColumnRenamed('home_scoreHome','gols_mandante') \\\n",
    "    .withColumnRenamed('away_scoreAway','gols_visitante') \\\n",
    "    .withColumnRenamed('tournamentName','torneio') \\\n",
    "    .withColumnRenamed('cityCity','cidade') \\\n",
    "    .withColumnRenamed('countryCountry','pais') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----------------+---------+-------------+--------------+--------+---------+-------------------+\n",
      "|      data|        mandante|visitante|gols_mandante|gols_visitante| torneio|   cidade|               pais|\n",
      "+----------+----------------+---------+-------------+--------------+--------+---------+-------------------+\n",
      "|1872-11-30|        Scotland|  England|            0|             0|Friendly|  Glasgow|           Scotland|\n",
      "|1873-03-08|         England| Scotland|            4|             2|Friendly|   London|            England|\n",
      "|1874-03-07|        Scotland|  England|            2|             1|Friendly|  Glasgow|           Scotland|\n",
      "|1875-03-06|         England| Scotland|            2|             2|Friendly|   London|            England|\n",
      "|1876-03-04|        Scotland|  England|            3|             0|Friendly|  Glasgow|           Scotland|\n",
      "|1876-03-25|        Scotland|    Wales|            4|             0|Friendly|  Glasgow|           Scotland|\n",
      "|1877-03-03|         England| Scotland|            1|             3|Friendly|   London|            England|\n",
      "|1877-03-05|           Wales| Scotland|            0|             2|Friendly|  Wrexham|              Wales|\n",
      "|1878-03-02|        Scotland|  England|            7|             2|Friendly|  Glasgow|           Scotland|\n",
      "|1878-03-23|        Scotland|    Wales|            9|             0|Friendly|  Glasgow|           Scotland|\n",
      "|1879-01-18|         England|    Wales|            2|             1|Friendly|   London|            England|\n",
      "|1879-04-05|         England| Scotland|            5|             4|Friendly|   London|            England|\n",
      "|1879-04-07|           Wales| Scotland|            0|             3|Friendly|  Wrexham|              Wales|\n",
      "|1880-03-13|        Scotland|  England|            5|             4|Friendly|  Glasgow|           Scotland|\n",
      "|1880-03-15|           Wales|  England|            2|             3|Friendly|  Wrexham|              Wales|\n",
      "|1880-03-27|        Scotland|    Wales|            5|             1|Friendly|  Glasgow|           Scotland|\n",
      "|1881-02-26|         England|    Wales|            0|             1|Friendly|Blackburn|            England|\n",
      "|1881-03-12|         England| Scotland|            1|             6|Friendly|   London|            England|\n",
      "|1881-03-14|           Wales| Scotland|            1|             5|Friendly|  Wrexham|              Wales|\n",
      "|1882-02-18|Northern Ireland|  England|            0|            13|Friendly|  Belfast|Republic of Ireland|\n",
      "+----------+----------------+---------+-------------+--------------+--------+---------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "resultados.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[data: string, mandante: string, visitante: string, gols_mandante: string, gols_visitante: string, torneio: string, cidade: string, pais: string]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resultados.persist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "40839"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resultados.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----------+-------------+-------------+------------------+------------------+---------------+-------------------+-----------+\n",
      "|summary|      data|     mandante|    visitante|     gols_mandante|    gols_visitante|        torneio|             cidade|       pais|\n",
      "+-------+----------+-------------+-------------+------------------+------------------+---------------+-------------------+-----------+\n",
      "|  count|     40839|        40839|        40839|             40839|             40839|          40839|              40839|      40839|\n",
      "|   mean|      null|         null|         null|1.7457087587844953| 1.188104507945836|           null|               null|       null|\n",
      "| stddev|      null|         null|         null| 1.749144918253105|1.4051198000976632|           null|               null|       null|\n",
      "|    min|1872-11-30|     Abkhazia|     Abkhazia|                 0|                 0|ABCS Tournament|6th of October City|Afghanistan|\n",
      "|    max|2019-07-20|Åland Islands|Åland Islands|                 9|                 9|World Unity Cup|             ‘Atele|       Éire|\n",
      "+-------+----------+-------------+-------------+------------------+------------------+---------------+-------------------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "resultados.describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---------------+----------------+-------------+--------------+--------------------+---------+-------------+\n",
      "|      data|       mandante|       visitante|gols_mandante|gols_visitante|             torneio|   cidade|         pais|\n",
      "+----------+---------------+----------------+-------------+--------------+--------------------+---------+-------------+\n",
      "|2019-07-19|        Senegal|         Algeria|            0|             1|African Cup of Na...|    Cairo|        Egypt|\n",
      "|2019-07-19|     Tajikistan|     North Korea|            0|             1|Intercontinental Cup|Ahmedabad|        India|\n",
      "|2019-07-18|          Samoa|         Vanuatu|            0|            11|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-17|        Tunisia|         Nigeria|            0|             1|African Cup of Na...|    Cairo|        Egypt|\n",
      "|2019-07-15|          Tonga|         Vanuatu|            0|            14|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-15|         Tahiti|   New Caledonia|            0|             3|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-13|          India|     North Korea|            2|             5|Intercontinental Cup|Ahmedabad|        India|\n",
      "|2019-07-12|Solomon Islands|          Tahiti|            0|             3|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-11|     Madagascar|         Tunisia|            0|             3|African Cup of Na...|    Cairo|        Egypt|\n",
      "|2019-07-10|Solomon Islands|   New Caledonia|            0|             2|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-10| American Samoa|            Fiji|            0|             9|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-10|         Tuvalu|          Tahiti|            0|             7|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-08|          Samoa|Papua New Guinea|            0|             6|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-08|           Mali|     Ivory Coast|            0|             1|African Cup of Na...|     Suez|        Egypt|\n",
      "|2019-07-08| American Samoa|   New Caledonia|            0|             5|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-08|         Tuvalu| Solomon Islands|            0|            13|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-08|         Tahiti|            Fiji|            1|             2|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-07|  United States|          Mexico|            0|             1|            Gold Cup|  Chicago|United States|\n",
      "|2019-07-07|          India|      Tajikistan|            2|             4|Intercontinental Cup|Ahmedabad|        India|\n",
      "|2019-07-06|          Egypt|    South Africa|            0|             1|African Cup of Na...|    Cairo|        Egypt|\n",
      "+----------+---------------+----------------+-------------+--------------+--------------------+---------+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "resultados.filter(resultados.gols_visitante > resultados.gols_mandante).orderBy('data', ascending=False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "resultados.registerTempTable('resultados')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---------------+----------------+-------------+--------------+--------------------+---------+-------------+\n",
      "|      data|       mandante|       visitante|gols_mandante|gols_visitante|             torneio|   cidade|         pais|\n",
      "+----------+---------------+----------------+-------------+--------------+--------------------+---------+-------------+\n",
      "|2019-07-19|        Senegal|         Algeria|            0|             1|African Cup of Na...|    Cairo|        Egypt|\n",
      "|2019-07-19|     Tajikistan|     North Korea|            0|             1|Intercontinental Cup|Ahmedabad|        India|\n",
      "|2019-07-18|          Samoa|         Vanuatu|            0|            11|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-17|        Tunisia|         Nigeria|            0|             1|African Cup of Na...|    Cairo|        Egypt|\n",
      "|2019-07-15|          Tonga|         Vanuatu|            0|            14|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-15|         Tahiti|   New Caledonia|            0|             3|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-13|          India|     North Korea|            2|             5|Intercontinental Cup|Ahmedabad|        India|\n",
      "|2019-07-12|Solomon Islands|          Tahiti|            0|             3|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-11|     Madagascar|         Tunisia|            0|             3|African Cup of Na...|    Cairo|        Egypt|\n",
      "|2019-07-10|Solomon Islands|   New Caledonia|            0|             2|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-10| American Samoa|            Fiji|            0|             9|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-10|         Tuvalu|          Tahiti|            0|             7|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-08|          Samoa|Papua New Guinea|            0|             6|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-08|           Mali|     Ivory Coast|            0|             1|African Cup of Na...|     Suez|        Egypt|\n",
      "|2019-07-08| American Samoa|   New Caledonia|            0|             5|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-08|         Tuvalu| Solomon Islands|            0|            13|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-08|         Tahiti|            Fiji|            1|             2|       Pacific Games|     Apia|        Samoa|\n",
      "|2019-07-07|  United States|          Mexico|            0|             1|            Gold Cup|  Chicago|United States|\n",
      "|2019-07-07|          India|      Tajikistan|            2|             4|Intercontinental Cup|Ahmedabad|        India|\n",
      "|2019-07-06|          Egypt|    South Africa|            0|             1|African Cup of Na...|    Cairo|        Egypt|\n",
      "+----------+---------------+----------------+-------------+--------------+--------------------+---------+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select * from resultados where gols_visitante > gols_mandante order by data desc').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------+----------------+-------------+--------------+--------------------+----------+--------+\n",
      "|      data|mandante|       visitante|gols_mandante|gols_visitante|             torneio|    cidade|    pais|\n",
      "+----------+--------+----------------+-------------+--------------+--------------------+----------+--------+\n",
      "|1873-03-08| England|        Scotland|            4|             2|            Friendly|    London| England|\n",
      "|1874-03-07|Scotland|         England|            2|             1|            Friendly|   Glasgow|Scotland|\n",
      "|1876-03-04|Scotland|         England|            3|             0|            Friendly|   Glasgow|Scotland|\n",
      "|1876-03-25|Scotland|           Wales|            4|             0|            Friendly|   Glasgow|Scotland|\n",
      "|1878-03-02|Scotland|         England|            7|             2|            Friendly|   Glasgow|Scotland|\n",
      "|1878-03-23|Scotland|           Wales|            9|             0|            Friendly|   Glasgow|Scotland|\n",
      "|1879-01-18| England|           Wales|            2|             1|            Friendly|    London| England|\n",
      "|1879-04-05| England|        Scotland|            5|             4|            Friendly|    London| England|\n",
      "|1880-03-13|Scotland|         England|            5|             4|            Friendly|   Glasgow|Scotland|\n",
      "|1880-03-27|Scotland|           Wales|            5|             1|            Friendly|   Glasgow|Scotland|\n",
      "|1882-02-25|   Wales|Northern Ireland|            7|             1|            Friendly|   Wrexham|   Wales|\n",
      "|1882-03-11|Scotland|         England|            5|             1|            Friendly|   Glasgow|Scotland|\n",
      "|1882-03-13|   Wales|         England|            5|             3|            Friendly|   Wrexham|   Wales|\n",
      "|1882-03-25|Scotland|           Wales|            5|             0|            Friendly|   Glasgow|Scotland|\n",
      "|1883-02-03| England|           Wales|            5|             0|            Friendly|    London| England|\n",
      "|1883-02-24| England|Northern Ireland|            7|             0|            Friendly| Liverpool| England|\n",
      "|1884-02-09|   Wales|Northern Ireland|            6|             0|British Championship|   Wrexham|   Wales|\n",
      "|1884-03-15|Scotland|         England|            1|             0|British Championship|   Glasgow|Scotland|\n",
      "|1884-03-29|Scotland|           Wales|            4|             1|British Championship|   Glasgow|Scotland|\n",
      "|1885-02-28| England|Northern Ireland|            4|             0|British Championship|Manchester| England|\n",
      "+----------+--------+----------------+-------------+--------------+--------------------+----------+--------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "resultados.filter(resultados.gols_mandante > resultados.gols_visitante).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------+----------------+-------------+--------------+--------------------+----------+--------+\n",
      "|      data|mandante|       visitante|gols_mandante|gols_visitante|             torneio|    cidade|    pais|\n",
      "+----------+--------+----------------+-------------+--------------+--------------------+----------+--------+\n",
      "|1873-03-08| England|        Scotland|            4|             2|            Friendly|    London| England|\n",
      "|1874-03-07|Scotland|         England|            2|             1|            Friendly|   Glasgow|Scotland|\n",
      "|1876-03-04|Scotland|         England|            3|             0|            Friendly|   Glasgow|Scotland|\n",
      "|1876-03-25|Scotland|           Wales|            4|             0|            Friendly|   Glasgow|Scotland|\n",
      "|1878-03-02|Scotland|         England|            7|             2|            Friendly|   Glasgow|Scotland|\n",
      "|1878-03-23|Scotland|           Wales|            9|             0|            Friendly|   Glasgow|Scotland|\n",
      "|1879-01-18| England|           Wales|            2|             1|            Friendly|    London| England|\n",
      "|1879-04-05| England|        Scotland|            5|             4|            Friendly|    London| England|\n",
      "|1880-03-13|Scotland|         England|            5|             4|            Friendly|   Glasgow|Scotland|\n",
      "|1880-03-27|Scotland|           Wales|            5|             1|            Friendly|   Glasgow|Scotland|\n",
      "|1882-02-25|   Wales|Northern Ireland|            7|             1|            Friendly|   Wrexham|   Wales|\n",
      "|1882-03-11|Scotland|         England|            5|             1|            Friendly|   Glasgow|Scotland|\n",
      "|1882-03-13|   Wales|         England|            5|             3|            Friendly|   Wrexham|   Wales|\n",
      "|1882-03-25|Scotland|           Wales|            5|             0|            Friendly|   Glasgow|Scotland|\n",
      "|1883-02-03| England|           Wales|            5|             0|            Friendly|    London| England|\n",
      "|1883-02-24| England|Northern Ireland|            7|             0|            Friendly| Liverpool| England|\n",
      "|1884-02-09|   Wales|Northern Ireland|            6|             0|British Championship|   Wrexham|   Wales|\n",
      "|1884-03-15|Scotland|         England|            1|             0|British Championship|   Glasgow|Scotland|\n",
      "|1884-03-29|Scotland|           Wales|            4|             1|British Championship|   Glasgow|Scotland|\n",
      "|1885-02-28| England|Northern Ireland|            4|             0|British Championship|Manchester| England|\n",
      "+----------+--------+----------------+-------------+--------------+--------------------+----------+--------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select * From resultados where gols_mandante > gols_visitante').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "partidasBrasil = resultados.filter((resultados.mandante == 'Brazil') | (resultados.visitante == 'Brazil'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "partidasBrasil = sql('select * from resultados where mandante = \"Brazil\" or visitante = \"Brazil\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+---------+---------+-------------+--------------+------------+--------------+---------+\n",
      "|      data| mandante|visitante|gols_mandante|gols_visitante|     torneio|        cidade|     pais|\n",
      "+----------+---------+---------+-------------+--------------+------------+--------------+---------+\n",
      "|1914-09-20|Argentina|   Brazil|            3|             0|    Friendly|  Buenos Aires|Argentina|\n",
      "|1914-09-27|Argentina|   Brazil|            0|             1|   Copa Roca|  Buenos Aires|Argentina|\n",
      "|1916-07-08|   Brazil|    Chile|            1|             1|Copa América|  Buenos Aires|Argentina|\n",
      "|1916-07-10|Argentina|   Brazil|            1|             1|Copa América|  Buenos Aires|Argentina|\n",
      "|1916-07-12|   Brazil|  Uruguay|            1|             2|Copa América|  Buenos Aires|Argentina|\n",
      "|1916-07-18|  Uruguay|   Brazil|            0|             1|    Friendly|    Montevideo|  Uruguay|\n",
      "|1917-10-03|Argentina|   Brazil|            4|             2|Copa América|    Montevideo|  Uruguay|\n",
      "|1917-10-07|  Uruguay|   Brazil|            4|             0|Copa América|    Montevideo|  Uruguay|\n",
      "|1917-10-12|   Brazil|    Chile|            5|             0|Copa América|    Montevideo|  Uruguay|\n",
      "|1917-10-16|  Uruguay|   Brazil|            3|             1|    Friendly|    Montevideo|  Uruguay|\n",
      "|1919-05-11|   Brazil|    Chile|            6|             0|Copa América|Rio de Janeiro|   Brazil|\n",
      "|1919-05-18|   Brazil|Argentina|            3|             1|Copa América|Rio de Janeiro|   Brazil|\n",
      "|1919-05-26|   Brazil|  Uruguay|            2|             2|Copa América|Rio de Janeiro|   Brazil|\n",
      "|1919-05-29|   Brazil|  Uruguay|            1|             0|Copa América|Rio de Janeiro|   Brazil|\n",
      "|1919-06-01|   Brazil|Argentina|            3|             3|    Friendly|Rio de Janeiro|   Brazil|\n",
      "|1920-09-11|    Chile|   Brazil|            0|             1|Copa América|  Viña del Mar|    Chile|\n",
      "|1920-09-18|   Brazil|  Uruguay|            0|             6|Copa América|  Viña del Mar|    Chile|\n",
      "|1920-09-25|Argentina|   Brazil|            2|             0|Copa América|  Viña del Mar|    Chile|\n",
      "|1921-10-02|Argentina|   Brazil|            1|             0|Copa América|  Buenos Aires|Argentina|\n",
      "|1921-10-12|   Brazil| Paraguay|            3|             0|Copa América|  Buenos Aires|Argentina|\n",
      "+----------+---------+---------+-------------+--------------+------------+--------------+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "partidasBrasil.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "partidasBrasil.registerTempTable('partidasBrasil')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "977"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "partidasBrasil.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+\n",
      "|count(1)|\n",
      "+--------+\n",
      "|     977|\n",
      "+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select count(*) from partidasBrasil').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Jogos empate - 196"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "196"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "partidasBrasil.where(partidasBrasil.gols_mandante == partidasBrasil.gols_visitante).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+\n",
      "|count(1)|\n",
      "+--------+\n",
      "|     196|\n",
      "+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select count(*) from partidasBrasil where gols_mandante = gols_visitante').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# partidas vistoria Brasil "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "625"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "partidasBrasil \\\n",
    "    .where( \\\n",
    "               ((partidasBrasil.mandante == 'Brazil') & (partidasBrasil.gols_mandante > partidasBrasil.gols_visitante)) \\\n",
    "               | \\\n",
    "               ((partidasBrasil.visitante == 'Brazil') & (partidasBrasil.gols_visitante > partidasBrasil.gols_mandante)) \\\n",
    "          ).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+\n",
      "|Total|\n",
      "+-----+\n",
      "|  625|\n",
      "+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select count(*) as Total from partidasBrasil \\\n",
    "    where \\\n",
    "        (mandante = \"Brazil\" and gols_mandante > gols_visitante) \\\n",
    "        or \\\n",
    "        (visitante = \"Brazil\" and gols_visitante > gols_mandante) \\\n",
    "    ').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# partidas derrota Brasil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "156"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "partidasBrasil \\\n",
    "    .where( \\\n",
    "               ((partidasBrasil.mandante == 'Brazil') & (partidasBrasil.gols_mandante < partidasBrasil.gols_visitante)) \\\n",
    "               | \\\n",
    "               ((partidasBrasil.visitante == 'Brazil') & (partidasBrasil.gols_visitante < partidasBrasil.gols_mandante)) \\\n",
    "          ).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+\n",
      "|Total|\n",
      "+-----+\n",
      "|  156|\n",
      "+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select count(*) as Total from partidasBrasil \\\n",
    "    where \\\n",
    "        (mandante = \"Brazil\" and gols_mandante < gols_visitante) \\\n",
    "        or \\\n",
    "        (visitante = \"Brazil\" and gols_visitante < gols_mandante) \\\n",
    "    ').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "977\n"
     ]
    }
   ],
   "source": [
    "print(156+625+196)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Brasil tem 977 partidas, sendo 156 derrotas, 625 vitorias e 196 empates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------------+\n",
      "|torneio                     |\n",
      "+----------------------------+\n",
      "|Atlantic Cup                |\n",
      "|Brazil Independence Cup     |\n",
      "|Confederations Cup          |\n",
      "|Copa América                |\n",
      "|Copa Bernardo O'Higgins     |\n",
      "|Copa Oswaldo Cruz           |\n",
      "|Copa Rio Branco             |\n",
      "|Copa Roca                   |\n",
      "|FIFA World Cup              |\n",
      "|FIFA World Cup qualification|\n",
      "|Friendly                    |\n",
      "|Gold Cup                    |\n",
      "|King's Cup                  |\n",
      "|Lunar New Year Cup          |\n",
      "|Mundialito                  |\n",
      "|Pan American Championship   |\n",
      "|Rous Cup                    |\n",
      "|Tournoi de France           |\n",
      "|USA Cup                     |\n",
      "+----------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "partidasBrasil \\\n",
    "    .select('torneio') \\\n",
    "    .distinct() \\\n",
    "    .orderBy('torneio') \\\n",
    "    .show(100,False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------------+\n",
      "|torneio                     |\n",
      "+----------------------------+\n",
      "|Atlantic Cup                |\n",
      "|Brazil Independence Cup     |\n",
      "|Confederations Cup          |\n",
      "|Copa América                |\n",
      "|Copa Bernardo O'Higgins     |\n",
      "|Copa Oswaldo Cruz           |\n",
      "|Copa Rio Branco             |\n",
      "|Copa Roca                   |\n",
      "|FIFA World Cup              |\n",
      "|FIFA World Cup qualification|\n",
      "|Friendly                    |\n",
      "|Gold Cup                    |\n",
      "|King's Cup                  |\n",
      "|Lunar New Year Cup          |\n",
      "|Mundialito                  |\n",
      "|Pan American Championship   |\n",
      "|Rous Cup                    |\n",
      "|Tournoi de France           |\n",
      "|USA Cup                     |\n",
      "+----------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select distinct torneio from partidasBrasil order by torneio ').show(100,False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------------+--------------+\n",
      "|torneio                     |count(torneio)|\n",
      "+----------------------------+--------------+\n",
      "|King's Cup                  |1             |\n",
      "|Lunar New Year Cup          |1             |\n",
      "|Rous Cup                    |2             |\n",
      "|Mundialito                  |3             |\n",
      "|Tournoi de France           |3             |\n",
      "|USA Cup                     |3             |\n",
      "|Brazil Independence Cup     |4             |\n",
      "|Atlantic Cup                |5             |\n",
      "|Copa Bernardo O'Higgins     |10            |\n",
      "|Gold Cup                    |14            |\n",
      "|Copa Oswaldo Cruz           |16            |\n",
      "|Pan American Championship   |16            |\n",
      "|Copa Rio Branco             |18            |\n",
      "|Copa Roca                   |23            |\n",
      "|Confederations Cup          |33            |\n",
      "|FIFA World Cup qualification|104           |\n",
      "|FIFA World Cup              |109           |\n",
      "|Copa América                |184           |\n",
      "|Friendly                    |428           |\n",
      "+----------------------------+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "partidasBrasil \\\n",
    "    .groupBy('torneio') \\\n",
    "    .agg({'torneio' : 'count'}) \\\n",
    "    .orderBy('count(torneio)') \\\n",
    "    .show(100,False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------------+--------+\n",
      "|torneio                     |count(1)|\n",
      "+----------------------------+--------+\n",
      "|King's Cup                  |1       |\n",
      "|Lunar New Year Cup          |1       |\n",
      "|Rous Cup                    |2       |\n",
      "|Mundialito                  |3       |\n",
      "|USA Cup                     |3       |\n",
      "|Tournoi de France           |3       |\n",
      "|Brazil Independence Cup     |4       |\n",
      "|Atlantic Cup                |5       |\n",
      "|Copa Bernardo O'Higgins     |10      |\n",
      "|Gold Cup                    |14      |\n",
      "|Copa Oswaldo Cruz           |16      |\n",
      "|Pan American Championship   |16      |\n",
      "|Copa Rio Branco             |18      |\n",
      "|Copa Roca                   |23      |\n",
      "|Confederations Cup          |33      |\n",
      "|FIFA World Cup qualification|104     |\n",
      "|FIFA World Cup              |109     |\n",
      "|Copa América                |184     |\n",
      "|Friendly                    |428     |\n",
      "+----------------------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql('select torneio, count(*) from partidasBrasil group by torneio order by 2').show(100,False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------------+--------------+\n",
      "|torneio                     |count(torneio)|\n",
      "+----------------------------+--------------+\n",
      "|Mundialito                  |1             |\n",
      "|Tournoi de France           |1             |\n",
      "|Rous Cup                    |1             |\n",
      "|Lunar New Year Cup          |1             |\n",
      "|USA Cup                     |1             |\n",
      "|King's Cup                  |1             |\n",
      "|Atlantic Cup                |3             |\n",
      "|Brazil Independence Cup     |3             |\n",
      "|Copa Bernardo O'Higgins     |6             |\n",
      "|Gold Cup                    |8             |\n",
      "|Copa Rio Branco             |8             |\n",
      "|Pan American Championship   |11            |\n",
      "|Copa Oswaldo Cruz           |11            |\n",
      "|Copa Roca                   |11            |\n",
      "|Confederations Cup          |23            |\n",
      "|FIFA World Cup qualification|65            |\n",
      "|FIFA World Cup              |73            |\n",
      "|Copa América                |103           |\n",
      "|Friendly                    |294           |\n",
      "+----------------------------+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "partidasBrasil \\\n",
    "    .filter( \\\n",
    "               ((partidasBrasil.mandante == 'Brazil') & (partidasBrasil.gols_mandante > partidasBrasil.gols_visitante)) \\\n",
    "               | \\\n",
    "               ((partidasBrasil.visitante == 'Brazil') & (partidasBrasil.gols_visitante > partidasBrasil.gols_mandante)) \\\n",
    "          ) \\\n",
    "    .groupBy('torneio') \\\n",
    "    .agg({'torneio' : 'count'}) \\\n",
    "    .orderBy('count(torneio)') \\\n",
    "    .show(100,False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_mandante = resultados \\\n",
    "    .filter(resultados.torneio == 'FIFA World Cup') \\\n",
    "    .filter(resultados.gols_mandante > resultados.gols_visitante) \\\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_visitante = resultados \\\n",
    "    .filter(resultados.torneio == 'FIFA World Cup') \\\n",
    "    .filter(resultados.gols_mandante < resultados.gols_visitante) \\"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+\n",
      "|      mandante|\n",
      "+--------------+\n",
      "|        France|\n",
      "|     Argentina|\n",
      "|         Chile|\n",
      "|       Uruguay|\n",
      "|     Argentina|\n",
      "|         Chile|\n",
      "|       Uruguay|\n",
      "|     Argentina|\n",
      "|     Argentina|\n",
      "|       Uruguay|\n",
      "|       Uruguay|\n",
      "|       Austria|\n",
      "|Czechoslovakia|\n",
      "|         Italy|\n",
      "|       Austria|\n",
      "|Czechoslovakia|\n",
      "|       Germany|\n",
      "|         Italy|\n",
      "|Czechoslovakia|\n",
      "|         Italy|\n",
      "+--------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_mandante.select('mandante').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_visitante \\\n",
    "    .select('visitante') \\\n",
    "    .union(df_mandante.select('mandante')) \\\n",
    "    .withColumnRenamed('visitante','selecao') \\\n",
    "    .groupBy('selecao') \\\n",
    "    .agg({'selecao' : 'count'}) \\\n",
    "    .orderBy('count(selecao)') \\\n",
    "    .repartition(1).write.csv('/bases/analise_copa.csv',header=True)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------+--------+\n",
      "|selecao               |count(1)|\n",
      "+----------------------+--------+\n",
      "|Wales                 |1       |\n",
      "|North Korea           |1       |\n",
      "|Slovenia              |1       |\n",
      "|Jamaica               |1       |\n",
      "|Czech Republic        |1       |\n",
      "|Slovakia              |1       |\n",
      "|Bosnia and Herzegovina|1       |\n",
      "|Cuba                  |1       |\n",
      "|Tunisia               |2       |\n",
      "|Republic of Ireland   |2       |\n",
      "|Norway                |2       |\n",
      "|German DR             |2       |\n",
      "|Greece                |2       |\n",
      "|Australia             |2       |\n",
      "|South Africa          |2       |\n",
      "|Iran                  |2       |\n",
      "|Ukraine               |2       |\n",
      "|Morocco               |2       |\n",
      "|Senegal               |3       |\n",
      "|Ivory Coast           |3       |\n",
      "|Saudi Arabia          |3       |\n",
      "|Bulgaria              |3       |\n",
      "|Algeria               |3       |\n",
      "|Northern Ireland      |3       |\n",
      "|Ecuador               |4       |\n",
      "|Ghana                 |4       |\n",
      "|Cameroon              |4       |\n",
      "|Serbia                |4       |\n",
      "|Scotland              |4       |\n",
      "|Peru                  |5       |\n",
      "|Japan                 |5       |\n",
      "|Costa Rica            |5       |\n",
      "|Turkey                |5       |\n",
      "|Nigeria               |6       |\n",
      "|South Korea           |6       |\n",
      "|Paraguay              |7       |\n",
      "|United States         |8       |\n",
      "|Romania               |8       |\n",
      "|Colombia              |9       |\n",
      "|Denmark               |9       |\n",
      "|Czechoslovakia        |11      |\n",
      "|Chile                 |11      |\n",
      "|Croatia               |11      |\n",
      "|Austria               |12      |\n",
      "|Switzerland           |12      |\n",
      "|Portugal              |14      |\n",
      "|Yugoslavia            |14      |\n",
      "|Hungary               |15      |\n",
      "|Mexico                |16      |\n",
      "|Poland                |16      |\n",
      "|Russia                |19      |\n",
      "|Sweden                |19      |\n",
      "|Belgium               |20      |\n",
      "|Uruguay               |24      |\n",
      "|Netherlands           |27      |\n",
      "|England               |29      |\n",
      "|Spain                 |30      |\n",
      "|France                |34      |\n",
      "|Argentina             |43      |\n",
      "|Italy                 |45      |\n",
      "|Germany               |67      |\n",
      "|Brazil                |73      |\n",
      "+----------------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql( \\\n",
    "    'select visitante  as selecao,count(*) From \\\n",
    "    (select visitante from resultados where torneio = \"FIFA World Cup\" and gols_mandante < gols_visitante \\\n",
    "    union all \\\n",
    "    select mandante from resultados where torneio = \"FIFA World Cup\" and gols_mandante > gols_visitante) \\\n",
    "    group by selecao \\\n",
    "    order by 2'\\\n",
    "   ).show(100,False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------+--------------+-------------+--------------+--------------+--------------+-----------+\n",
      "|      data|mandante|     visitante|gols_mandante|gols_visitante|       torneio|        cidade|       pais|\n",
      "+----------+--------+--------------+-------------+--------------+--------------+--------------+-----------+\n",
      "|1930-07-14|  Brazil|    Yugoslavia|            1|             2|FIFA World Cup|    Montevideo|    Uruguay|\n",
      "|1930-07-20| Bolivia|        Brazil|            0|             4|FIFA World Cup|    Montevideo|    Uruguay|\n",
      "|1934-05-27|  Brazil|         Spain|            1|             3|FIFA World Cup|         Genoa|      Italy|\n",
      "|1938-06-05|  Brazil|        Poland|            6|             5|FIFA World Cup|    Strasbourg|     France|\n",
      "|1938-06-12|  Brazil|Czechoslovakia|            1|             1|FIFA World Cup|      Bordeaux|     France|\n",
      "|1938-06-14|  Brazil|Czechoslovakia|            2|             1|FIFA World Cup|      Bordeaux|     France|\n",
      "|1938-06-16|  Brazil|         Italy|            1|             2|FIFA World Cup|     Marseille|     France|\n",
      "|1938-06-19|  Brazil|        Sweden|            4|             2|FIFA World Cup|      Bordeaux|     France|\n",
      "|1950-06-24|  Brazil|        Mexico|            4|             0|FIFA World Cup|Rio de Janeiro|     Brazil|\n",
      "|1950-06-28|  Brazil|   Switzerland|            2|             2|FIFA World Cup|     São Paulo|     Brazil|\n",
      "|1950-07-01|  Brazil|    Yugoslavia|            2|             0|FIFA World Cup|Rio de Janeiro|     Brazil|\n",
      "|1950-07-09|  Brazil|        Sweden|            7|             1|FIFA World Cup|Rio de Janeiro|     Brazil|\n",
      "|1950-07-13|  Brazil|         Spain|            6|             1|FIFA World Cup|Rio de Janeiro|     Brazil|\n",
      "|1950-07-16|  Brazil|       Uruguay|            1|             2|FIFA World Cup|Rio de Janeiro|     Brazil|\n",
      "|1954-06-16|  Brazil|        Mexico|            5|             0|FIFA World Cup|        Geneva|Switzerland|\n",
      "|1954-06-19|  Brazil|    Yugoslavia|            1|             1|FIFA World Cup|      Lausanne|Switzerland|\n",
      "|1954-06-27|  Brazil|       Hungary|            2|             4|FIFA World Cup|         Berne|Switzerland|\n",
      "|1958-06-08| Austria|        Brazil|            0|             3|FIFA World Cup|     Uddevalla|     Sweden|\n",
      "|1958-06-11|  Brazil|       England|            0|             0|FIFA World Cup|    Gothenburg|     Sweden|\n",
      "|1958-06-15|  Brazil|        Russia|            2|             0|FIFA World Cup|    Gothenburg|     Sweden|\n",
      "+----------+--------+--------------+-------------+--------------+--------------+--------------+-----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "partidasBrasil.filter(partidasBrasil.torneio == 'FIFA World Cup').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----------+--------+---------+------------------+------------------+------------+---------+--------+\n",
      "|summary|      data|mandante|visitante|     gols_mandante|    gols_visitante|     torneio|   cidade|    pais|\n",
      "+-------+----------+--------+---------+------------------+------------------+------------+---------+--------+\n",
      "|  count|       977|     977|      977|               977|               977|         977|      977|     977|\n",
      "|   mean|      null|    null|     null|1.8607983623336746|1.2425793244626406|        null|     null|    null|\n",
      "| stddev|      null|    null|     null|1.7347309806074491| 1.371365800922911|        null|     null|    null|\n",
      "|    min|1914-09-20| Algeria|  Algeria|                 0|                 0|Atlantic Cup|Abu Dhabi| Algeria|\n",
      "|    max|2019-07-07|Zimbabwe|   Zambia|                 9|                 8|     USA Cup|al-Rayyan|Zimbabwe|\n",
      "+-------+----------+--------+---------+------------------+------------------+------------+---------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "partidasBrasil.describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----------------+---------+-------------+--------------+--------------+-------------+-------------+\n",
      "|      data|        mandante|visitante|gols_mandante|gols_visitante|       torneio|       cidade|         pais|\n",
      "+----------+----------------+---------+-------------+--------------+--------------+-------------+-------------+\n",
      "|2002-06-13|      Costa Rica|   Brazil|            2|             5|FIFA World Cup|        Suwon|  South Korea|\n",
      "|1958-06-29|          Sweden|   Brazil|            2|             5|FIFA World Cup|        Solna|       Sweden|\n",
      "|1938-06-05|          Brazil|   Poland|            6|             5|FIFA World Cup|   Strasbourg|       France|\n",
      "|1930-07-20|         Bolivia|   Brazil|            0|             4|FIFA World Cup|   Montevideo|      Uruguay|\n",
      "|2006-06-22|           Japan|   Brazil|            1|             4|FIFA World Cup|     Dortmund|      Germany|\n",
      "|1962-06-13|           Chile|   Brazil|            2|             4|FIFA World Cup|     Santiago|        Chile|\n",
      "|1958-06-08|         Austria|   Brazil|            0|             3|FIFA World Cup|    Uddevalla|       Sweden|\n",
      "|1974-06-22|        DR Congo|   Brazil|            0|             3|FIFA World Cup|Gelsenkirchen|      Germany|\n",
      "|1994-07-09|     Netherlands|   Brazil|            2|             3|FIFA World Cup|       Dallas|United States|\n",
      "|1982-07-02|       Argentina|   Brazil|            1|             3|FIFA World Cup|    Barcelona|        Spain|\n",
      "|1986-06-12|Northern Ireland|   Brazil|            0|             3|FIFA World Cup|  Guadalajara|       Mexico|\n",
      "|1970-06-10|          Brazil|  Romania|            3|             2|FIFA World Cup|  Guadalajara|       Mexico|\n",
      "|1958-06-24|          Brazil|   France|            5|             2|FIFA World Cup|        Solna|       Sweden|\n",
      "|1974-06-30|       Argentina|   Brazil|            1|             2|FIFA World Cup|      Hanover|      Germany|\n",
      "|1970-06-14|          Brazil|     Peru|            4|             2|FIFA World Cup|  Guadalajara|       Mexico|\n",
      "|1938-06-19|          Brazil|   Sweden|            4|             2|FIFA World Cup|     Bordeaux|       France|\n",
      "|2002-06-21|         England|   Brazil|            1|             2|FIFA World Cup|      Fukuroi|        Japan|\n",
      "|2018-06-27|          Serbia|   Brazil|            0|             2|FIFA World Cup|       Moscow|       Russia|\n",
      "|1998-07-03|          Brazil|  Denmark|            3|             2|FIFA World Cup|       Nantes|       France|\n",
      "|2002-06-30|         Germany|   Brazil|            0|             2|FIFA World Cup|     Yokohama|        Japan|\n",
      "+----------+----------------+---------+-------------+--------------+--------------+-------------+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "partidasBrasil \\\n",
    "    .filter(partidasBrasil.torneio == 'FIFA World Cup') \\\n",
    "    .filter( \\\n",
    "                ((partidasBrasil.mandante == 'Brazil') & (partidasBrasil.gols_mandante > partidasBrasil.gols_visitante)) \\\n",
    "                | \\\n",
    "                ((partidasBrasil.visitante == 'Brazil') & (partidasBrasil.gols_mandante < partidasBrasil.gols_visitante)) \\\n",
    "           ) \\\n",
    "    .orderBy('gols_visitante', ascending=False) \\\n",
    ".show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-----------+-----------+-------------+--------------+--------------+-----------------+------------+\n",
      "|      data|   mandante|  visitante|gols_mandante|gols_visitante|       torneio|           cidade|        pais|\n",
      "+----------+-----------+-----------+-------------+--------------+--------------+-----------------+------------+\n",
      "|2014-07-08|     Brazil|    Germany|            1|             7|FIFA World Cup|   Belo Horizonte|      Brazil|\n",
      "|1954-06-27|     Brazil|    Hungary|            2|             4|FIFA World Cup|            Berne| Switzerland|\n",
      "|1966-07-19|     Brazil|   Portugal|            1|             3|FIFA World Cup|        Liverpool|     England|\n",
      "|1934-05-27|     Brazil|      Spain|            1|             3|FIFA World Cup|            Genoa|       Italy|\n",
      "|2014-07-12|     Brazil|Netherlands|            0|             3|FIFA World Cup|         Brasília|      Brazil|\n",
      "|1966-07-15|     Brazil|    Hungary|            1|             3|FIFA World Cup|        Liverpool|     England|\n",
      "|1982-07-05|      Italy|     Brazil|            3|             2|FIFA World Cup|        Barcelona|       Spain|\n",
      "|2018-07-06|     Brazil|    Belgium|            1|             2|FIFA World Cup|            Kazan|      Russia|\n",
      "|1938-06-16|     Brazil|      Italy|            1|             2|FIFA World Cup|        Marseille|      France|\n",
      "|1998-06-23|     Brazil|     Norway|            1|             2|FIFA World Cup|        Marseille|      France|\n",
      "|1930-07-14|     Brazil| Yugoslavia|            1|             2|FIFA World Cup|       Montevideo|     Uruguay|\n",
      "|1950-07-16|     Brazil|    Uruguay|            1|             2|FIFA World Cup|   Rio de Janeiro|      Brazil|\n",
      "|1990-06-24|     Brazil|  Argentina|            0|             1|FIFA World Cup|            Turin|       Italy|\n",
      "|1974-07-06|     Brazil|     Poland|            0|             1|FIFA World Cup|           Munich|     Germany|\n",
      "|2006-07-01|     Brazil|     France|            0|             1|FIFA World Cup|Frankfurt am Main|     Germany|\n",
      "|2010-07-02|Netherlands|     Brazil|            2|             1|FIFA World Cup|   Port Elizabeth|South Africa|\n",
      "|1974-07-03|Netherlands|     Brazil|            2|             0|FIFA World Cup|         Dortmund|     Germany|\n",
      "|1998-07-12|     France|     Brazil|            3|             0|FIFA World Cup|      Saint-Denis|      France|\n",
      "+----------+-----------+-----------+-------------+--------------+--------------+-----------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "partidasBrasil \\\n",
    "    .filter(partidasBrasil.torneio == 'FIFA World Cup') \\\n",
    "    .filter( \\\n",
    "                ((partidasBrasil.mandante == 'Brazil') & (partidasBrasil.gols_mandante < partidasBrasil.gols_visitante)) \\\n",
    "                | \\\n",
    "                ((partidasBrasil.visitante == 'Brazil') & (partidasBrasil.gols_mandante > partidasBrasil.gols_visitante)) \\\n",
    "           ) \\\n",
    "    .orderBy('gols_visitante', ascending=False) \\\n",
    ".show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "derrotas_mandante = partidasBrasil \\\n",
    "    .filter(partidasBrasil.torneio == 'FIFA World Cup') \\\n",
    "    .filter(partidasBrasil.mandante == 'Brazil') \\\n",
    "    .filter(partidasBrasil.gols_mandante < partidasBrasil.gols_visitante) \\\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "derrotas_visitante = partidasBrasil \\\n",
    "    .filter(partidasBrasil.torneio == 'FIFA World Cup') \\\n",
    "    .filter(partidasBrasil.visitante == 'Brazil') \\\n",
    "    .filter(partidasBrasil.gols_mandante > partidasBrasil.gols_visitante) \\\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "derrotas_mandante = derrotas_mandante \\\n",
    "    .withColumnRenamed('mandante','brasil') \\\n",
    "    .withColumnRenamed('visitante','adversario') \\\n",
    "    .withColumnRenamed('gols_mandante','gol_brasil') \\\n",
    "    .withColumnRenamed('gols_visitante','gol_adversario') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+-----------+----------+--------------+--------------+-----------------+-----------+\n",
      "|      data|brasil| adversario|gol_brasil|gol_adversario|       torneio|           cidade|       pais|\n",
      "+----------+------+-----------+----------+--------------+--------------+-----------------+-----------+\n",
      "|1930-07-14|Brazil| Yugoslavia|         1|             2|FIFA World Cup|       Montevideo|    Uruguay|\n",
      "|1934-05-27|Brazil|      Spain|         1|             3|FIFA World Cup|            Genoa|      Italy|\n",
      "|1938-06-16|Brazil|      Italy|         1|             2|FIFA World Cup|        Marseille|     France|\n",
      "|1950-07-16|Brazil|    Uruguay|         1|             2|FIFA World Cup|   Rio de Janeiro|     Brazil|\n",
      "|1954-06-27|Brazil|    Hungary|         2|             4|FIFA World Cup|            Berne|Switzerland|\n",
      "|1966-07-15|Brazil|    Hungary|         1|             3|FIFA World Cup|        Liverpool|    England|\n",
      "|1966-07-19|Brazil|   Portugal|         1|             3|FIFA World Cup|        Liverpool|    England|\n",
      "|1974-07-06|Brazil|     Poland|         0|             1|FIFA World Cup|           Munich|    Germany|\n",
      "|1990-06-24|Brazil|  Argentina|         0|             1|FIFA World Cup|            Turin|      Italy|\n",
      "|1998-06-23|Brazil|     Norway|         1|             2|FIFA World Cup|        Marseille|     France|\n",
      "|2006-07-01|Brazil|     France|         0|             1|FIFA World Cup|Frankfurt am Main|    Germany|\n",
      "|2014-07-08|Brazil|    Germany|         1|             7|FIFA World Cup|   Belo Horizonte|     Brazil|\n",
      "|2014-07-12|Brazil|Netherlands|         0|             3|FIFA World Cup|         Brasília|     Brazil|\n",
      "|2018-07-06|Brazil|    Belgium|         1|             2|FIFA World Cup|            Kazan|     Russia|\n",
      "+----------+------+-----------+----------+--------------+--------------+-----------------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "derrotas_mandante.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "derrotas_visitante = derrotas_visitante \\\n",
    "    .withColumnRenamed('mandante','adversario') \\\n",
    "    .withColumnRenamed('visitante','brasil') \\\n",
    "    .withColumnRenamed('gols_mandante','gol_adversario') \\\n",
    "    .withColumnRenamed('gols_visitante','gol_brasil') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-----------+------+--------------+----------+--------------+-----------------+------------+\n",
      "|      data| adversario|brasil|gol_adversario|gol_brasil|       torneio|           cidade|        pais|\n",
      "+----------+-----------+------+--------------+----------+--------------+-----------------+------------+\n",
      "|1974-07-03|Netherlands|Brazil|             2|         0|FIFA World Cup|         Dortmund|     Germany|\n",
      "|1982-07-05|      Italy|Brazil|             3|         2|FIFA World Cup|        Barcelona|       Spain|\n",
      "|1998-07-12|     France|Brazil|             3|         0|FIFA World Cup|      Saint-Denis|      France|\n",
      "|2010-07-02|Netherlands|Brazil|             2|         1|FIFA World Cup|   Port Elizabeth|South Africa|\n",
      "|1930-07-14| Yugoslavia|Brazil|             2|         1|FIFA World Cup|       Montevideo|     Uruguay|\n",
      "|1934-05-27|      Spain|Brazil|             3|         1|FIFA World Cup|            Genoa|       Italy|\n",
      "|1938-06-16|      Italy|Brazil|             2|         1|FIFA World Cup|        Marseille|      France|\n",
      "|1950-07-16|    Uruguay|Brazil|             2|         1|FIFA World Cup|   Rio de Janeiro|      Brazil|\n",
      "|1954-06-27|    Hungary|Brazil|             4|         2|FIFA World Cup|            Berne| Switzerland|\n",
      "|1966-07-15|    Hungary|Brazil|             3|         1|FIFA World Cup|        Liverpool|     England|\n",
      "|1966-07-19|   Portugal|Brazil|             3|         1|FIFA World Cup|        Liverpool|     England|\n",
      "|1974-07-06|     Poland|Brazil|             1|         0|FIFA World Cup|           Munich|     Germany|\n",
      "|1990-06-24|  Argentina|Brazil|             1|         0|FIFA World Cup|            Turin|       Italy|\n",
      "|1998-06-23|     Norway|Brazil|             2|         1|FIFA World Cup|        Marseille|      France|\n",
      "|2006-07-01|     France|Brazil|             1|         0|FIFA World Cup|Frankfurt am Main|     Germany|\n",
      "|2014-07-08|    Germany|Brazil|             7|         1|FIFA World Cup|   Belo Horizonte|      Brazil|\n",
      "|2014-07-12|Netherlands|Brazil|             3|         0|FIFA World Cup|         Brasília|      Brazil|\n",
      "|2018-07-06|    Belgium|Brazil|             2|         1|FIFA World Cup|            Kazan|      Russia|\n",
      "+----------+-----------+------+--------------+----------+--------------+-----------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "derrotas_visitante \\\n",
    "    .select('data','adversario','brasil','gol_adversario','gol_brasil','torneio','cidade','pais') \\\n",
    "    .union(derrotas_mandante.select('data','adversario','brasil','gol_adversario','gol_brasil','torneio','cidade','pais')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-----------+------+--------------+----------+--------------+--------------+------------+\n",
      "|      data| adversario|brasil|gol_adversario|gol_brasil|       torneio|        cidade|        pais|\n",
      "+----------+-----------+------+--------------+----------+--------------+--------------+------------+\n",
      "|1974-07-03|Netherlands|Brazil|             2|         0|FIFA World Cup|      Dortmund|     Germany|\n",
      "|1982-07-05|      Italy|Brazil|             3|         2|FIFA World Cup|     Barcelona|       Spain|\n",
      "|1998-07-12|     France|Brazil|             3|         0|FIFA World Cup|   Saint-Denis|      France|\n",
      "|2010-07-02|Netherlands|Brazil|             2|         1|FIFA World Cup|Port Elizabeth|South Africa|\n",
      "+----------+-----------+------+--------------+----------+--------------+--------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "derrotas_visitante.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+-----------+----------+--------------+--------------+-----------------+-----------+\n",
      "|      data|brasil| adversario|gol_brasil|gol_adversario|       torneio|           cidade|       pais|\n",
      "+----------+------+-----------+----------+--------------+--------------+-----------------+-----------+\n",
      "|1930-07-14|Brazil| Yugoslavia|         1|             2|FIFA World Cup|       Montevideo|    Uruguay|\n",
      "|1934-05-27|Brazil|      Spain|         1|             3|FIFA World Cup|            Genoa|      Italy|\n",
      "|1938-06-16|Brazil|      Italy|         1|             2|FIFA World Cup|        Marseille|     France|\n",
      "|1950-07-16|Brazil|    Uruguay|         1|             2|FIFA World Cup|   Rio de Janeiro|     Brazil|\n",
      "|1954-06-27|Brazil|    Hungary|         2|             4|FIFA World Cup|            Berne|Switzerland|\n",
      "|1966-07-15|Brazil|    Hungary|         1|             3|FIFA World Cup|        Liverpool|    England|\n",
      "|1966-07-19|Brazil|   Portugal|         1|             3|FIFA World Cup|        Liverpool|    England|\n",
      "|1974-07-06|Brazil|     Poland|         0|             1|FIFA World Cup|           Munich|    Germany|\n",
      "|1990-06-24|Brazil|  Argentina|         0|             1|FIFA World Cup|            Turin|      Italy|\n",
      "|1998-06-23|Brazil|     Norway|         1|             2|FIFA World Cup|        Marseille|     France|\n",
      "|2006-07-01|Brazil|     France|         0|             1|FIFA World Cup|Frankfurt am Main|    Germany|\n",
      "|2014-07-08|Brazil|    Germany|         1|             7|FIFA World Cup|   Belo Horizonte|     Brazil|\n",
      "|2014-07-12|Brazil|Netherlands|         0|             3|FIFA World Cup|         Brasília|     Brazil|\n",
      "|2018-07-06|Brazil|    Belgium|         1|             2|FIFA World Cup|            Kazan|     Russia|\n",
      "+----------+------+-----------+----------+--------------+--------------+-----------------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "derrotas_mandante.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PySpark",
   "language": "python",
   "name": "pyspark"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}