Tokyo Olympic Transformation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "1c1dcbf2-c209-476c-bf61-7ba0c9f92cf8",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import col\n",
    "from pyspark.sql.types import IntegerType, DoubleType, BooleanType, DateType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0e40c64f-a0db-4478-9118-86be8153ff5a",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "configs = {\"fs.azure.account.auth.type\": \"OAuth\",\n",
    "\"fs.azure.account.oauth.provider.type\": \"org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider\",\n",
    "\"fs.azure.account.oauth2.client.id\": \"d76e13fb-0272-4059-a377-630cc6288571\",\n",
    "\"fs.azure.account.oauth2.client.secret\": 'jSi8Q~2k3rSMTuZGreuf-z8IN4s6aoEVKKbqda4a',\n",
    "\"fs.azure.account.oauth2.client.endpoint\": \"https://login.microsoftonline.com/1fa0016a-3035-44a6-9a5b-434facfebc27/oauth2/token\"}\n",
    "\n",
    "\n",
    "dbutils.fs.mount(\n",
    "source = \"abfss://tokyo-olympic-data@tokyoolympicdatasanith.dfs.core.windows.net\", # contrainer@storageacc\n",
    "mount_point = \"/mnt/tokyoolympic\",\n",
    "extra_configs = configs)\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "dec9106d-7cf7-4f51-99c8-41f986747821",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>path</th><th>name</th><th>size</th><th>modificationTime</th></tr></thead><tbody><tr><td>dbfs:/mnt/tokyoolympic/raw-data/</td><td>raw-data/</td><td>0</td><td>1709108455000</td></tr><tr><td>dbfs:/mnt/tokyoolympic/transformed-data/</td><td>transformed-data/</td><td>0</td><td>1709108472000</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "dbfs:/mnt/tokyoolympic/raw-data/",
         "raw-data/",
         0,
         1709108455000
        ],
        [
         "dbfs:/mnt/tokyoolympic/transformed-data/",
         "transformed-data/",
         0,
         1709108472000
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {
        "isDbfsCommandResult": false
       },
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "path",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "size",
         "type": "\"long\""
        },
        {
         "metadata": "{}",
         "name": "modificationTime",
         "type": "\"long\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%fs\n",
    "ls \"/mnt/tokyoolympic\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2cd51764-3f8f-4585-9cba-f3740a658f4d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "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=\"/?o=1859209167919459#setting/sparkui/0228-162618-o88v3sbi/driver-6628875607896506591\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.4.1</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*, 4]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>Databricks Shell</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7f9f6c3c2500>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "685bc59d-57e7-4558-903c-4459181ba15d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "athletes = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolympic/raw-data/athletes.csv\")\n",
    "coaches = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolympic/raw-data/coaches.csv\")\n",
    "entriesgender = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolympic/raw-data/entriesgender.csv\")\n",
    "medals = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolympic/raw-data/medals.csv\")\n",
    "teams = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolympic/raw-data/teams.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "710a7e96-9dc5-4a82-bdbc-81408f3d1c29",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+-------------------+\n|          PersonName|             Country|         Discipline|\n+--------------------+--------------------+-------------------+\n|     AALERUD Katrine|              Norway|       Cycling Road|\n|         ABAD Nestor|               Spain|Artistic Gymnastics|\n|   ABAGNALE Giovanni|               Italy|             Rowing|\n|      ABALDE Alberto|               Spain|         Basketball|\n|       ABALDE Tamara|               Spain|         Basketball|\n|           ABALO Luc|              France|           Handball|\n|        ABAROA Cesar|               Chile|             Rowing|\n|       ABASS Abobakr|               Sudan|           Swimming|\n|    ABBASALI Hamideh|Islamic Republic ...|             Karate|\n|       ABBASOV Islam|          Azerbaijan|          Wrestling|\n|        ABBINGH Lois|         Netherlands|           Handball|\n|         ABBOT Emily|           Australia|Rhythmic Gymnastics|\n|       ABBOTT Monica|United States of ...|  Baseball/Softball|\n|ABDALLA Abubaker ...|               Qatar|          Athletics|\n|      ABDALLA Maryam|               Egypt|  Artistic Swimming|\n|      ABDALLAH Shahd|               Egypt|  Artistic Swimming|\n| ABDALRASOOL Mohamed|               Sudan|               Judo|\n|   ABDEL LATIF Radwa|               Egypt|           Shooting|\n|    ABDEL RAZEK Samy|               Egypt|           Shooting|\n|   ABDELAZIZ Abdalla|               Egypt|             Karate|\n+--------------------+--------------------+-------------------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "athletes.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "44b294b3-4264-4fc5-b794-4b5cfd6b4cb1",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- PersonName: string (nullable = true)\n |-- Country: string (nullable = true)\n |-- Discipline: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "athletes.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3e920439-bb24-491c-9b16-9ee8a9bfb8d2",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+-----------------+--------+\n|                Name|             Country|       Discipline|   Event|\n+--------------------+--------------------+-----------------+--------+\n|     ABDELMAGID Wael|               Egypt|         Football|    NULL|\n|           ABE Junya|               Japan|       Volleyball|    NULL|\n|       ABE Katsuhiko|               Japan|       Basketball|    NULL|\n|        ADAMA Cherif|       C�te d'Ivoire|         Football|    NULL|\n|          AGEBA Yuya|               Japan|       Volleyball|    NULL|\n|AIKMAN Siegfried ...|               Japan|           Hockey|     Men|\n|       AL SAADI Kais|             Germany|           Hockey|     Men|\n|       ALAMEDA Lonni|              Canada|Baseball/Softball|Softball|\n|     ALEKNO Vladimir|Islamic Republic ...|       Volleyball|     Men|\n|     ALEKSEEV Alexey|                 ROC|         Handball|   Women|\n|ALLER CARBALLO Ma...|               Spain|       Basketball|    NULL|\n|       ALSHEHRI Saad|        Saudi Arabia|         Football|     Men|\n|           ALY Kamal|               Egypt|         Football|    NULL|\n| AMAYA GAITAN Fabian|         Puerto Rico|       Basketball|    NULL|\n|    AMO AGUADO Pablo|               Spain|         Football|    NULL|\n|   ANDONOVSKI Vlatko|United States of ...|         Football|   Women|\n|        ANNAN Alyson|         Netherlands|           Hockey|   Women|\n|  ARNAU CREUS Xavier|               Japan|           Hockey|   Women|\n|       ARNOLD Graham|           Australia|         Football|     Men|\n|         AXNER Tomas|              Sweden|         Handball|   Women|\n+--------------------+--------------------+-----------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "coaches.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "73fb5ad4-74dd-4cbc-b4d8-0b6a4ef93df6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- Name: string (nullable = true)\n |-- Country: string (nullable = true)\n |-- Discipline: string (nullable = true)\n |-- Event: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "coaches.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "40147d69-47b4-4c64-b2cc-f926bc5bac0a",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------+----+-----+\n|          Discipline|Female|Male|Total|\n+--------------------+------+----+-----+\n|      3x3 Basketball|    32|  32|   64|\n|             Archery|    64|  64|  128|\n| Artistic Gymnastics|    98|  98|  196|\n|   Artistic Swimming|   105|   0|  105|\n|           Athletics|   969|1072| 2041|\n|           Badminton|    86|  87|  173|\n|   Baseball/Softball|    90| 144|  234|\n|          Basketball|   144| 144|  288|\n|    Beach Volleyball|    48|  48|   96|\n|              Boxing|   102| 187|  289|\n|        Canoe Slalom|    41|  41|   82|\n|        Canoe Sprint|   123| 126|  249|\n|Cycling BMX Frees...|    10|   9|   19|\n|  Cycling BMX Racing|    24|  24|   48|\n|Cycling Mountain ...|    38|  38|   76|\n|        Cycling Road|    70| 131|  201|\n|       Cycling Track|    90|  99|  189|\n|              Diving|    72|  71|  143|\n|          Equestrian|    73| 125|  198|\n|             Fencing|   107| 108|  215|\n+--------------------+------+----+-----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "entriesgender.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "f9be39e0-4c15-46d5-9237-a3cdb76f3cd3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- Discipline: string (nullable = true)\n |-- Female: integer (nullable = true)\n |-- Male: integer (nullable = true)\n |-- Total: integer (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "entriesgender.printSchema()\n",
    "     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "29b73472-ce14-4056-9646-94fd0c7c83a2",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "entriesgender = entriesgender.withColumn(\"Female\",col(\"Female\").cast(IntegerType()))\\\n",
    "    .withColumn(\"Male\",col(\"Male\").cast(IntegerType()))\\\n",
    "    .withColumn(\"Total\",col(\"Total\").cast(IntegerType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "1d2d35a4-ca4c-4164-8e79-bd3a84eab4de",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+--------------------+----+------+------+-----+-------------+\n|Rank|        Team_Country|Gold|Silver|Bronze|Total|Rank by Total|\n+----+--------------------+----+------+------+-----+-------------+\n|   1|United States of ...|  39|    41|    33|  113|            1|\n|   2|People's Republic...|  38|    32|    18|   88|            2|\n|   3|               Japan|  27|    14|    17|   58|            5|\n|   4|       Great Britain|  22|    21|    22|   65|            4|\n|   5|                 ROC|  20|    28|    23|   71|            3|\n|   6|           Australia|  17|     7|    22|   46|            6|\n|   7|         Netherlands|  10|    12|    14|   36|            9|\n|   8|              France|  10|    12|    11|   33|           10|\n|   9|             Germany|  10|    11|    16|   37|            8|\n|  10|               Italy|  10|    10|    20|   40|            7|\n|  11|              Canada|   7|     6|    11|   24|           11|\n|  12|              Brazil|   7|     6|     8|   21|           12|\n|  13|         New Zealand|   7|     6|     7|   20|           13|\n|  14|                Cuba|   7|     3|     5|   15|           18|\n|  15|             Hungary|   6|     7|     7|   20|           13|\n|  16|   Republic of Korea|   6|     4|    10|   20|           13|\n|  17|              Poland|   4|     5|     5|   14|           19|\n|  18|      Czech Republic|   4|     4|     3|   11|           23|\n|  19|               Kenya|   4|     4|     2|   10|           25|\n|  20|              Norway|   4|     2|     2|    8|           29|\n+----+--------------------+----+------+------+-----+-------------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "medals.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "907a4dea-1d9b-4cb2-9e19-af7b6d92cf8c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- Rank: integer (nullable = true)\n |-- Team_Country: string (nullable = true)\n |-- Gold: integer (nullable = true)\n |-- Silver: integer (nullable = true)\n |-- Bronze: integer (nullable = true)\n |-- Total: integer (nullable = true)\n |-- Rank by Total: integer (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "medals.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "17bdb29e-6284-4656-baf1-0d945179f8d6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------------+--------------------+------------+\n|     TeamName|    Discipline|             Country|       Event|\n+-------------+--------------+--------------------+------------+\n|      Belgium|3x3 Basketball|             Belgium|         Men|\n|        China|3x3 Basketball|People's Republic...|         Men|\n|        China|3x3 Basketball|People's Republic...|       Women|\n|       France|3x3 Basketball|              France|       Women|\n|        Italy|3x3 Basketball|               Italy|       Women|\n|        Japan|3x3 Basketball|               Japan|         Men|\n|        Japan|3x3 Basketball|               Japan|       Women|\n|       Latvia|3x3 Basketball|              Latvia|         Men|\n|     Mongolia|3x3 Basketball|            Mongolia|       Women|\n|  Netherlands|3x3 Basketball|         Netherlands|         Men|\n|       Poland|3x3 Basketball|              Poland|         Men|\n|          ROC|3x3 Basketball|                 ROC|         Men|\n|          ROC|3x3 Basketball|                 ROC|       Women|\n|      Romania|3x3 Basketball|             Romania|       Women|\n|       Serbia|3x3 Basketball|              Serbia|         Men|\n|United States|3x3 Basketball|United States of ...|       Women|\n|    Australia|       Archery|           Australia|  Men's Team|\n|    Australia|       Archery|           Australia|  Mixed Team|\n|   Bangladesh|       Archery|          Bangladesh|  Mixed Team|\n|      Belarus|       Archery|             Belarus|Women's Team|\n+-------------+--------------+--------------------+------------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "teams.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "8b234399-7dc9-4833-af53-1cd6b3298aff",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- TeamName: string (nullable = true)\n |-- Discipline: string (nullable = true)\n |-- Country: string (nullable = true)\n |-- Event: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "teams.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "7949f63f-4282-4ab5-8f56-b1ce41df24c5",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+----+\n|        Team_Country|Gold|\n+--------------------+----+\n|United States of ...|  39|\n|People's Republic...|  38|\n|               Japan|  27|\n|       Great Britain|  22|\n|                 ROC|  20|\n|           Australia|  17|\n|         Netherlands|  10|\n|              France|  10|\n|             Germany|  10|\n|               Italy|  10|\n|                Cuba|   7|\n|         New Zealand|   7|\n|              Brazil|   7|\n|              Canada|   7|\n|             Hungary|   6|\n|   Republic of Korea|   6|\n|               Kenya|   4|\n|              Poland|   4|\n|      Czech Republic|   4|\n|              Norway|   4|\n+--------------------+----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "# Find the top countries with the highest number of gold medals\n",
    "top_gold_medal_countries = medals.orderBy(\"Gold\", ascending=False).select(\"Team_Country\",\"Gold\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "bab48a5e-87ac-4176-ba91-6171c3c88cab",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+------+----+-----+-------------------+-------------------+\n|          Discipline|Female|Male|Total|         Avg_Female|           Avg_Male|\n+--------------------+------+----+-----+-------------------+-------------------+\n|      3x3 Basketball|    32|  32|   64|                0.5|                0.5|\n|             Archery|    64|  64|  128|                0.5|                0.5|\n| Artistic Gymnastics|    98|  98|  196|                0.5|                0.5|\n|   Artistic Swimming|   105|   0|  105|                1.0|                0.0|\n|           Athletics|   969|1072| 2041| 0.4747672709456149| 0.5252327290543851|\n|           Badminton|    86|  87|  173|0.49710982658959535| 0.5028901734104047|\n|   Baseball/Softball|    90| 144|  234|0.38461538461538464| 0.6153846153846154|\n|          Basketball|   144| 144|  288|                0.5|                0.5|\n|    Beach Volleyball|    48|  48|   96|                0.5|                0.5|\n|              Boxing|   102| 187|  289|0.35294117647058826| 0.6470588235294118|\n|        Canoe Slalom|    41|  41|   82|                0.5|                0.5|\n|        Canoe Sprint|   123| 126|  249| 0.4939759036144578| 0.5060240963855421|\n|Cycling BMX Frees...|    10|   9|   19| 0.5263157894736842|0.47368421052631576|\n|  Cycling BMX Racing|    24|  24|   48|                0.5|                0.5|\n|Cycling Mountain ...|    38|  38|   76|                0.5|                0.5|\n|        Cycling Road|    70| 131|  201| 0.3482587064676617| 0.6517412935323383|\n|       Cycling Track|    90|  99|  189|0.47619047619047616| 0.5238095238095238|\n|              Diving|    72|  71|  143| 0.5034965034965035| 0.4965034965034965|\n|          Equestrian|    73| 125|  198| 0.3686868686868687| 0.6313131313131313|\n|             Fencing|   107| 108|  215|0.49767441860465117| 0.5023255813953489|\n+--------------------+------+----+-----+-------------------+-------------------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "# Calculate the average number of entries by gender for each discipline\n",
    "average_entries_by_gender = entriesgender.withColumn(\n",
    "    'Avg_Female', entriesgender['Female'] / entriesgender['Total']\n",
    ").withColumn(\n",
    "    'Avg_Male', entriesgender['Male'] / entriesgender['Total']\n",
    ")\n",
    "average_entries_by_gender.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "ba8b38da-0a99-4bdd-9beb-8e1e28a501eb",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "athletes.repartition(1).write.mode(\"overwrite\").option(\"header\",'true').csv(\"/mnt/tokyoolympic/transformed-data/athletes\")\n",
    "coaches.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/tokyoolympic/transformed-data/coaches\")\n",
    "entriesgender.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/tokyoolympic/transformed-data/entriesgender\")\n",
    "medals.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/tokyoolympic/transformed-data/medals\")\n",
    "teams.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/tokyoolympic/transformed-data/teams\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "beeb4848-289f-4e35-9144-390565022ba8",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 685142573927876,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "Tokyo Olympic Transformation",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}