Tokyo-olympic-transformation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "f6bb0c00-1cbf-40d9-aa96-41b55e471926",
     "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": "9bb9c49f-ff5b-4c19-835b-9b7e68c374f0",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Out[2]: True"
     ]
    }
   ],
   "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\": \"2c42ba23-9dff-4fac-bdf6-a1c31a75060e\",\n",
    "\"fs.azure.account.oauth2.client.secret\": 'AYa8Q~AZZb1T8O6ud5IS0Oi-llWtEA6TlJUyTcvK',\n",
    "\"fs.azure.account.oauth2.client.endpoint\": \"https://login.microsoftonline.com/cf3a4497-6286-460b-b510-4f12079d3573/oauth2/token\"}\n",
    "\n",
    "dbutils.fs.mount(\n",
    "source = \"abfss://tokyo-olympic-data@tokyoolympicdata0.dfs.core.windows.net\", # contrainer@storageacc\n",
    "mount_point = \"/mnt/tokyoolymic\",\n",
    "extra_configs = configs)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3908abc4-3dab-44bf-9efe-538cd11f5ff2",
     "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/tokyoolymic/raw-data/</td><td>raw-data/</td><td>0</td><td>1707601530000</td></tr><tr><td>dbfs:/mnt/tokyoolymic/transformed-data/</td><td>transformed-data/</td><td>0</td><td>1707601540000</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/tokyoolymic/raw-data/",
         "raw-data/",
         0,
         1707601530000
        ],
        [
         "dbfs:/mnt/tokyoolymic/transformed-data/",
         "transformed-data/",
         0,
         1707601540000
        ]
       ],
       "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/tokyoolymic\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "90598645-c7b9-4c6f-b562-0bae87a0ccfb",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "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=2538105201169966#setting/sparkui/0211-192319-v2wpcxcq/driver-2102699644077770552\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.3.2</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",
       "        "
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "arguments": {},
       "data": "\n            <div>\n                <p><b>SparkSession - hive</b></p>\n                \n        <div>\n            <p><b>SparkContext</b></p>\n\n            <p><a href=\"/?o=2538105201169966#setting/sparkui/0211-192319-v2wpcxcq/driver-2102699644077770552\">Spark UI</a></p>\n\n            <dl>\n              <dt>Version</dt>\n                <dd><code>v3.3.2</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        ",
       "datasetInfos": [],
       "metadata": {},
       "removedWidgets": [],
       "textData": null,
       "type": "htmlSandbox"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0b5449ec-8993-4025-b996-54983b7b540b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "athletes = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolymic/raw-data/athletes.csv\")\n",
    "coaches = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolymic/raw-data/coaches.csv\")\n",
    "entriesgender = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolymic/raw-data/entriesgender.csv\")\n",
    "medals = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolymic/raw-data/medals.csv\")\n",
    "teams = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\",\"true\").load(\"/mnt/tokyoolymic/raw-data/teams.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "55503aa2-d03b-48e9-b3e3-47e581da2044",
     "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": "5c66f812-9619-43e0-b46a-b5526df90972",
     "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": "f7be1e72-2b17-4f14-bc1f-3d638bc4e8b8",
     "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": "1a7fd573-b183-4549-98ec-441e12f45659",
     "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": "8bdc0ed9-eb94-4055-9239-5a8a75f162bd",
     "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": "200507f6-3abb-4281-a42f-04dab7e87b24",
     "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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e789fc8e-3840-429e-b527-4272a5c8cceb",
     "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": "b13427d0-2d82-4dbd-93aa-02bfb50ef51d",
     "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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "575e04d0-320f-4c9b-912c-b461c90397e4",
     "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": "63939344-de7c-4c91-8af5-24a4bacbd1d9",
     "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": "3ecc3b2d-b30b-49ec-b69d-9ac62e9b7c57",
     "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": "fcd4a0ac-a5c2-48ff-900c-e3ade4d891ad",
     "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": "d73a3818-767c-4e47-b229-42a5baecbde9",
     "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|              Canada|   7|\n|              Brazil|   7|\n|         New Zealand|   7|\n|                Cuba|   7|\n|             Hungary|   6|\n|   Republic of Korea|   6|\n|              Poland|   4|\n|      Czech Republic|   4|\n|               Kenya|   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": "b38ef57b-7cb8-4794-8071-1d08f7f81a22",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+----+------+------+-----+\n|        Team_Country|Gold|Silver|Bronze|Total|\n+--------------------+----+------+------+-----+\n|United States of ...|  39|    41|    33|  113|\n|People's Republic...|  38|    32|    18|   88|\n|                 ROC|  20|    28|    23|   71|\n|       Great Britain|  22|    21|    22|   65|\n|               Japan|  27|    14|    17|   58|\n|           Australia|  17|     7|    22|   46|\n|               Italy|  10|    10|    20|   40|\n|             Germany|  10|    11|    16|   37|\n|         Netherlands|  10|    12|    14|   36|\n|              France|  10|    12|    11|   33|\n|              Canada|   7|     6|    11|   24|\n|              Brazil|   7|     6|     8|   21|\n|         New Zealand|   7|     6|     7|   20|\n|             Hungary|   6|     7|     7|   20|\n|   Republic of Korea|   6|     4|    10|   20|\n|             Ukraine|   1|     6|    12|   19|\n|               Spain|   3|     8|     6|   17|\n|                Cuba|   7|     3|     5|   15|\n|              Poland|   4|     5|     5|   14|\n|         Switzerland|   3|     4|     6|   13|\n+--------------------+----+------+------+-----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import functions as F\n",
    "\n",
    "# Assuming you have a DataFrame named 'medals'\n",
    "# Add a new column for total medals\n",
    "medals = medals.withColumn(\"Total\", F.col(\"Gold\") + F.col(\"Silver\") + F.col(\"Bronze\"))\n",
    "\n",
    "# Order by total medals in descending order\n",
    "top_countries_total_medals = medals.orderBy(\"Total\", ascending=False).select(\"Team_Country\", \"Gold\", \"Silver\", \"Bronze\", \"Total\").show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5f9cc970-1289-432c-8bbd-a27f468e6bea",
     "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": "020e6493-7d10-4b5d-935c-59194cd749f3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "athletes.repartition(1).write.mode(\"overwrite\").option(\"header\",'true').csv(\"/mnt/tokyoolymic/transformed-data/athletes\")\n",
    "coaches.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/tokyoolymic/transformed-data/coaches\")\n",
    "entriesgender.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/tokyoolymic/transformed-data/entriesgender\")\n",
    "medals.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/tokyoolymic/transformed-data/medals\")\n",
    "teams.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/tokyoolymic/transformed-data/teams\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "eff4eb54-86ce-4303-9d6d-275b83580b95",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 2936703441001739,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "Tokyo-olympic-transformation",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}