olympicdata-transformation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "12e24aa9-a556-448c-b7e7-692b57e97945",
     "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\": \"a7fa39fe-d2bd-4868-b313-9a95aeb120d5\",\n",
    "\"fs.azure.account.oauth2.client.secret\": 'HRg8Q~cpU4PMlXlnIFNzQYgkPcCezdKP78deadso',\n",
    "\"fs.azure.account.oauth2.client.endpoint\": \"https://login.microsoftonline.com/c558fd0d-955b-4616-94ef-0d3357193f15/oauth2/token\"}\n",
    "\n",
    "dbutils.fs.mount(\n",
    "source = \"abfss://olympic-data@olympicdatae.dfs.core.windows.net\", # contrainer@storageacc\n",
    "mount_point = \"/mnt/olympic\",\n",
    "extra_configs = configs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "ea69f7b5-5ee1-4fd3-8042-23d09c5a2e84",
     "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": "1d167001-d7f8-4331-89cf-9bb6b01f724e",
     "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/olympic/raw-data/</td><td>raw-data/</td><td>0</td><td>1722613031000</td></tr><tr><td>dbfs:/mnt/olympic/transformed-data/</td><td>transformed-data/</td><td>0</td><td>1722613046000</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/olympic/raw-data/",
         "raw-data/",
         0,
         1722613031000
        ],
        [
         "dbfs:/mnt/olympic/transformed-data/",
         "transformed-data/",
         0,
         1722613046000
        ]
       ],
       "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/olympic\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "75e406fc-7a84-429c-be55-1f3cd3c3bf33",
     "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=219921301501441#setting/sparkui/0803-084506-utmgubw2/driver-4959428730788448753\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.5.0</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 0x7f1b1c082c50>"
      ]
     },
     "execution_count": 2,
     "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": "5467a702-cf69-4d86-8527-0ee222860f5c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "athletes = spark.read.format(\"csv\").option(\"header\", \"true\").option(\"inferSchema\", \"true\").load(\"/mnt/olympic/raw-data/athletes.csv\") \n",
    "coaches = spark.read.format(\"csv\").option(\"header\", \"true\").option(\"inferSchema\", \"true\").load(\"/mnt/olympic/raw-data/coaches.csv\") \n",
    "entriesgender = spark.read.format(\"csv\").option(\"header\", \"true\").option(\"inferSchema\", \"true\").load(\"/mnt/olympic/raw-data/entriesgender.csv\") \n",
    "medals = spark.read.format(\"csv\").option(\"header\", \"true\").option(\"inferSchema\", \"true\").load(\"/mnt/olympic/raw-data/medals.csv\") \n",
    "teams = spark.read.format(\"csv\").option(\"header\", \"true\").option(\"inferSchema\", \"true\").load(\"/mnt/olympic/raw-data/teams.csv\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "d7a95ff1-d0cc-4867-8d06-e402bec67de1",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+-------------------+\n|                Name|                 NOC|         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": "58152cab-1297-434c-a0fb-e5829d5bf0ff",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- Name: string (nullable = true)\n |-- NOC: 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": "e4ae5a85-3540-48a7-a370-feb777f940fc",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+--------------------+----+------+------+-----+-------------+\n|Rank|            Team/NOC|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": "779fa89d-ef3c-4984-a068-243ff5d061e1",
     "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()))\n",
    "     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b3edde71-275a-49e8-a6ec-075be2be1a63",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- Rank: integer (nullable = true)\n |-- Team/NOC: 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": "37be2bc7-13b2-46df-84a8-10be4469ccfa",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+----+\n|            Team/NOC|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": [
    "top_gold_medal_countries = medals.orderBy(\"Gold\", ascending=False).select(\"Team/NOC\", \"Gold\").show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "f6538e86-9331-4cb9-91e0-9c0e2541bf20",
     "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": [
    "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": "43b3c646-e0a1-46bf-be16-908f0977d7ac",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "athletes.repartition(1).write.mode(\"overwrite\").option(\"header\",'true').csv(\"/mnt/tokyoolymic/transformed-data/athletes\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "489c79a4-7aac-473d-99fb-014912a88e55",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "athletes.repartition(1).write.mode(\"overwrite\").option(\"header\",'true').csv(\"/mnt/olympic/transformed-data/athletes\")\n",
    "coaches.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/olympic/transformed-data/coaches\")\n",
    "entriesgender.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/olympic/transformed-data/entriesgender\")\n",
    "medals.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/olympic/transformed-data/medals\")\n",
    "teams.repartition(1).write.mode(\"overwrite\").option(\"header\",\"true\").csv(\"/mnt/olympic/transformed-data/teams\")"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": null,
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 755463797354718,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "olympicdata-transformation",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}