notebooks/PySpark_Tutorial.ipynb

{"cells":[{"cell_type":"markdown","source":["# Our First PySpark Notebook on Databricks\n\nIn this first notebook we will cover some of the basic functionalities of PySpark. In particular, we will discuss how to play with data using PySpark's <code>DataFrame</code> as building block.\nThe first part of the notebook will show how <code>DataFrame</code> objects can be created \"manually\", whilst in the second part we will see how to load _external_ data sources into a <code>DataFrame</code> object. Of course, the latter is way more useful as most of the times we will be needing to work with (possibly many) external data sources.\n\nWe will start our journey just checking that our PySpark environment hosted on Databricks platform is up and running properly!"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"dd8d747b-b933-4053-bf9c-59505b53bd26"}}},{"cell_type":"markdown","source":["## Import useful PySpark packages"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2e441c20-d582-48ef-9d44-709087cc9f1e"}}},{"cell_type":"code","source":["import pyspark\nfrom pyspark.sql import *\nfrom pyspark.sql.types import *\nfrom pyspark.sql.functions import *\nfrom pyspark import SparkContext, SparkConf"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"d66dab12-456d-4d47-9632-85b1a394ebd7"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["## Check everything is ok"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2b0ea003-9568-4e22-b5f3-b1acd8e19ad8"}}},{"cell_type":"code","source":["spark"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8ae9f55a-4a56-4321-aff1-461a257048de"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["sc._conf.getAll()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"7440dba9-e1d9-45b7-98cf-275bec498b96"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["# **Create PySpark's <code>DataFrame</code> \"manually\"**\n\nLet's first create <code>Employee</code> and <code>Department</code> <code>Row</code> instances."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"852782c0-3227-4936-99fb-3e977767e373"}}},{"cell_type":"code","source":["# Row is a generic \"record\" object with an ordered collection of fields that can be accessed by index or name\n# In this case, we use Row to create the schema of other Row objects\nEmployee = Row(\"firstName\", \"lastName\", \"email\", \"salary\")\n\n# Create some Row instances following the above schema\nemployee_1 = Employee('Basher', 'armbrust', 'bash@edureka.co', 100000)\nemployee_2 = Employee('Daniel', 'meng', 'daniel@stanford.edu', 120000 )\nemployee_3 = Employee('Muriel', None, 'muriel@waterloo.edu', 140000 )\nemployee_4 = Employee('Rachel', 'wendell', 'rach_3@edureka.co', 160000 )\nemployee_5 = Employee('Zach', 'galifianakis', 'zach_g@edureka.co', 160000 )\n\n# Print out a whole Row instance\nprint(employee_3)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"70a4a133-bf6d-49ca-a68f-a132ff1b2629"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Access and print out the first Row field of `employee_1` by index\nprint(\"Employee 1's first name is: {:s}\".format(employee_1[0]))\n\n# Access and print out the `salary` Row field of `employee_4` by name\nprint(\"Employee 4's salary is: {:d}\".format(employee_4.salary))"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3b4d8cf6-4537-47ac-9a0f-5c3741eecead"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Create another set of Row objects. \n# This time, schema is defined by means of 2 named attributes, i.e., `id` and `name`.\n# Each Row object specifies those attribute at creation time \n# (rather than having a `Department` Row object containing only the schema, as we did before with `Employee`)\ndepartment_1 = Row(id='123456', name='HR')\ndepartment_2 = Row(id='789012', name='OPS')\ndepartment_3 = Row(id='345678', name='FN')\ndepartment_4 = Row(id='901234', name='DEV')"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"540d0d9a-c61f-487e-a46c-4a4b6cd0e926"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["Now, we'll create a <code>DepartmentWithEmployees</code> <code>Row</code> instance from the Employee and Departments."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"6e86acdd-171a-43f0-b8d2-61eb774e27a8"}}},{"cell_type":"code","source":["departmentWithEmployees_1 = Row(department=department_1, employees=[employee_1, employee_2, employee_5])\ndepartmentWithEmployees_2 = Row(department=department_2, employees=[employee_3, employee_4])\ndepartmentWithEmployees_3 = Row(department=department_3, employees=[employee_1, employee_4, employee_3])\ndepartmentWithEmployees_4 = Row(department=department_4, employees=[employee_2, employee_3])"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"22ffdc45-6e1b-46a6-93e8-6555348c45e7"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["print(departmentWithEmployees_2)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"dbaa3b52-fad4-4280-a001-fe5b0d5fe405"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["Let's create our <code>DataFrame</code> from the list of rows above"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"b91f7680-d6b6-4d98-8c89-4533a78b0106"}}},{"cell_type":"code","source":["departmentsWithEmployees = [departmentWithEmployees_1, \n                            departmentWithEmployees_2, \n                            departmentWithEmployees_3,                           \n                            departmentWithEmployees_4]\n\n# Create the Spark's DataFrame object\ndf = spark.createDataFrame(departmentsWithEmployees)\n# Print out the DataFrame\ndf.show(n=2, truncate=False) # try, playing with the arguments (e.g., truncate=True/False)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"e27a165f-b1c4-4fb5-8783-d97acb29fed6"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["# **Create PySpark's <code>DataFrame</code> from an external input source**\n\nMost of the times, data we will be working with are not manually created as above, yet they are located at several remote repositories and herein stored in various formats like <code>csv</code>, <code>json</code>, <code>xml</code>, or [Parquet](https://spark.apache.org/docs/latest/sql-data-sources-parquet.html) files. Moreover, data can be loaded either from _local_ or _distributed_ file systems (e.g., HDFS or Amazon S3).\n\nRemember, though, that our development environment is provided by Databricks (Community Edition) which - differently from classical Jupyter Notebook that usually is executed on our local machine - runs on the Databricks' cloud infrastructure. As such, \"_local_\" file system **in this case** means local to Databricks' cloud! In other words, any references to data made within this notebook is relative to the Databrick's end side. That is why we need Databricks to know how to get our data before we can make use of it.\n\nDatabricks makes use of its own distributed file system, which is called [Databricks File System (DBFS)](https://docs.databricks.com/data/databricks-file-system.html). As such, we must load our data to DBFS **before** we can actually use it!"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"564d3933-893d-452f-af61-7359e8d475bc"}}},{"cell_type":"markdown","source":["## **1. Data Acquisition (from a remote source)**\n\nIf you have a dataset stored on your local machine and you want to make it available to DBFS you can easily take advantage of the **Data** menu item on the left-hand panel. Before you can access that menu, though, you must be sure the option _DBFS File Browser_ in the _Advanced Settings_ of the _Admin Console_ is **enabled** (by default, it is not). Once this is done, you can simply refer to this [link](https://docs.databricks.com/data/databricks-file-system.html#file-upload-interface) for any further instructions.\n\nHowever, most of the time, datasets are not stored on your local machine, yet they are located in remote repositories (i.e., external sources). As such, if you want to use the DBFS File Browser you may need to proceed as follows:\n1. Download the dataset from its original remote location to your own local file system;\n2. Upload the dataset from your own local file system to DBFS.\n \nTo avoid the 2-step procedure above - which may cause several latency due to multiple network transfers - you would rather prefer to download/store a remote dataset file to DBFS **directly**.\nLet's see how!"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"08c95495-41dd-471f-a595-69f3e9dd847a"}}},{"cell_type":"markdown","source":["#### DBFS vs. Local Driver Node's File System\n\nYou can work with files on DBFS or on the local driver node of the cluster. Either way, there are **4 main options** to access files:\n1. ```%fs```\n2. ```%sh```\n3. Databricks file system utilities (```dbutils.fs```)\n4. Single-node file system APIs (```os```)\n\nThe first two are **magic commands** integrated with Notebook.\n\n##### Accessing files on DBFS\nThe path to the default blog storage (**root**) is ```dbfs:/```. \nThe default location for ```%fs``` and ```dbutils.fs``` is **root**. Thus, to read from or write to root you can either use:\n\n```%fs command /path/```<br>\nor<br>\n```dbutils.fs command (\"/<path>/\")```\n\nBoth ```%sh``` and single-node file system APIs read from the local files ystem by default (i.e., the local file system of the driver node). To access root or mounted paths in root with ```%sh``` or file system APIs, you need to preface the path with ```/dbfs/```:\n\n```%sh <command> /dbfs/<path>/```<br>\nor<br>\n```\nimport os \nos.<command>(\"/dbfs/<path>/\")\n```\n\n##### Accessing files on the local driver node's file system\n```%fs``` and ```dbutils.fs``` read by default from **root** (```dbfs:/```). To read from the local file system, you must prepend ```file:/```\n\n```%fs <command> file:/<path>/```<br>\nor<br>\n```dbutils.fs.<command> (\"file:/<path>/\")```\n\nBoth ```%sh``` and single-node file system APIs read from the local files ystem by default, therefore:\n\n```%sh <command> /<path>/```<br>\nor<br>\n```\nimport os \nos.<command>(\"/<path>/\")\n```\n<hr>\n\n##### To Wrap Up\n<center>\n<table border=\"1\" class=\"docutils\">\n<colgroup>\n<col width=\"18%\" />\n<col width=\"22%\" />\n<col width=\"24%\" />\n<col width=\"37%\" />\n</colgroup>\n<thead valign=\"bottom\">\n<tr class=\"row-odd\"><th class=\"head\">Command</th>\n<th class=\"head\">Default location</th>\n<th class=\"head\">To read from root</th>\n<th class=\"head\">To read from local filesystem</th>\n</tr>\n</thead>\n<tbody valign=\"top\">\n<tr class=\"row-even\"><td> <code class=\"docutils literal notranslate\"><span class=\"pre\">%fs</span></code></td>\n<td> Root</td>\n<td>&#160;</td>\n<td> Add <code class=\"docutils literal notranslate\"><span class=\"pre\">file:/</span></code> to path</td>\n</tr>\n<tr class=\"row-odd\"><td> <code class=\"docutils literal notranslate\"><span class=\"pre\">%sh</span></code></td>\n<td> Local driver node</td>\n<td> Add <code class=\"docutils literal notranslate\"><span class=\"pre\">/dbfs</span></code> to path</td>\n<td>&#160;</td>\n</tr>\n<tr class=\"row-even\"><td>  <code class=\"docutils literal notranslate\"><span class=\"pre\">dbutils.fs</span></code></td>\n<td> Root</td>\n<td>&#160;</td>\n<td> Add <code class=\"docutils literal notranslate\"><span class=\"pre\">file:/</span></code> to path</td>\n</tr>\n<tr class=\"row-odd\"><td>  <code class=\"docutils literal notranslate\"><span class=\"pre\">os.&lt;command&gt;</span></code></td>\n<td> Local driver node</td>\n<td> Add <code class=\"docutils literal notranslate\"><span class=\"pre\">/dbfs</span></code> to path</td>\n<td>&#160;</td>\n</tr>\n</tbody>\n</table>\n</center>\n\n<center>![Accessing Files](https://docs.databricks.com/_images/dbfs-and-local-file-paths.png)</center>"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"d0fd5319-b4de-4a65-884e-bc6753fa2c7a"}}},{"cell_type":"markdown","source":["#### List all directories from DBFS root (i.e., ```dbfs:/```)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"85cb5562-12f5-4e3b-a635-8885e96cec77"}}},{"cell_type":"code","source":["%fs ls"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"c0e32917-f916-4735-af0d-01ba4dd104c0"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["dbutils.fs.ls(\"/\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"f1b6781e-04e7-4214-8d5e-ff83546492d6"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["%sh ls /dbfs/\n# NOTE: Starting from Databricks Runtime 7 this mount point is disabled (https://stackoverflow.com/a/63814218)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"a70602c6-88f6-40f0-83e3-38ff079c5e9b"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["import os\nos.listdir(\"/dbfs/\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"db8b19c5-22ce-4eae-9ed2-8e7654d340b7"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["#### List all directories from local driver node's root"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"4f2e0277-6fc2-430f-907f-c707d6b35ea5"}}},{"cell_type":"code","source":["%fs ls file:/"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"408be6bf-e68f-40e0-9a38-ee2883d4c65d"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["dbutils.fs.ls(\"file:/\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3b84b779-1bf7-4858-9f10-66ee8c7aa77f"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["%sh ls /"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8808c3d4-c04b-4c7a-b03d-10a84b915c80"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["import os\nos.listdir(\"/\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8156793e-597a-47e8-b6d0-f7c6ed08f876"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### 1.1 Create a dedicated <code>bdc-2020-21</code> folder on DBFS\nAs first step, let's create a dedicated folder on DBFS and double-check this leads to what we expect.\n\n**NOTE:** This is a one-time step; once you do it you will have access to the DBFS folder also in the future."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"6728ded8-acdf-46fe-bc4a-363b36341674"}}},{"cell_type":"code","source":["%fs mkdirs bdc-2020-21/datasets/"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"ecc88b29-906d-40ea-9dd6-681678799737"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["%fs ls"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"02b90290-7e4c-4cb1-8c32-cb512c633396"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["%fs ls /bdc-2020-21/"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"9aec908a-a1e5-4103-8f13-d3af4388c2d1"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### 1.2 Download the dataset to the local driver node's ```/tmp``` folder using ```wget```"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"03971967-5641-407f-93a4-530d5158381d"}}},{"cell_type":"code","source":["%sh wget -P /tmp https://github.com/gtolomei/big-data-computing/raw/master/datasets/fifa-players-2020.csv.bz2"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"798ed5b1-82c3-421b-b6c6-5f5dfa0b828c"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["%fs ls file:/tmp/"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"04e8ffe1-d9b3-4dc1-acd7-13cd538e6b50"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### 1.3 Move the file from local driver node's file system to DBFS"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"beb7a957-0cb6-4556-9d1b-7affba1b9cf4"}}},{"cell_type":"code","source":["dbutils.fs.mv(\"file:/tmp/fifa-players-2020.csv.bz2\", \"dbfs:/bdc-2020-21/datasets/fifa-players-2020.csv.bz2\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"c9a21d28-88cf-4711-8c92-669cdba3a517"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["%fs ls file:/tmp/"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"fad12f12-f552-44d0-a8b2-696ef58bf310"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["%fs ls /bdc-2020-21/datasets/"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"e3e65810-7848-4a85-8bba-63dee33edfc9"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### 1.4 Load dataset from DBFS to Spark ```DataFrame```"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"522d4500-a4c6-4cfc-88e9-193ba6f5a982"}}},{"cell_type":"code","source":["fifa_df = spark.read.load(\"dbfs:/bdc-2020-21/datasets/fifa-players-2020.csv.bz2\", \n                           format=\"csv\", \n                           sep=\",\", \n                           inferSchema=\"true\", \n                           header=\"true\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"c4d7c71e-b565-4af0-8b1c-4abff6c2d6e2"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **1.5 Display the first <code>n=5</code> rows of the loaded dataset**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"209ecfe0-be14-4bf4-9044-b6b6471093af"}}},{"cell_type":"code","source":["fifa_df.show(n=5, truncate=False)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"202498e9-7698-4603-b332-d67472dff5ce"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **1.6 Print out the schema of the DataFrame**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"91543509-6ade-40d9-8ff4-31fd495b2da7"}}},{"cell_type":"code","source":["fifa_df.printSchema()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"7055c72b-f665-4f96-8d1e-f135b7361595"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **1.7 Check the shape of the loaded dataset, i.e., number of rows and columns**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"75dd767a-c4b5-4343-804a-1a64614e3b55"}}},{"cell_type":"code","source":["print(\"The shape of the dataset is {:d} rows by {:d} columns\".format(fifa_df.count(), len(fifa_df.columns)))"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"0bde549b-558d-4671-b195-5e745919cf08"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **1.8 Check the data types of the loaded dataset**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"80601f89-d19d-4f9d-9dd0-c14ed2afcc65"}}},{"cell_type":"code","source":["print(fifa_df.dtypes)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2efffebd-1917-4b5a-adb6-2531d6138b82"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **1.9 Describing a particular column**\n\nIf we want to have a look at the summary of any particular column of a DataFrame, we use the <code>describe</code> method. This method gives us the statistical summary of the given column if not specified, it provides the statistical summary of the DataFrame."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"ffd78cb2-3655-44fe-8b4d-8c13a56b740d"}}},{"cell_type":"code","source":["# Statistical summary of the `age` column\nfifa_df.describe(\"age\").show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"d50afbd1-f25e-4bec-9883-54743d24cba9"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Statistical summary of the `height_cm` column\nfifa_df.describe(\"height_cm\").show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8090f056-1b3a-4b7a-b5b9-ad55e1d21289"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["## **2. Data Manipulation**\n\nOnce data has been successfully loaded into a Spark DataFrame, we can start working with it. Most of the operations involve: _selecting_, _filtering_, _sorting_, _grouping_, and compute _aggregate functions_ (e.g., _count_)."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3696dc86-722b-448a-8b3c-fa4da0c997c9"}}},{"cell_type":"markdown","source":["### **2.1 Selecting Multiple Columns**\n\nIf we want to select particular columns from the DataFrame, we use the <code>select</code> method."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"cba6ab34-168a-48a4-be22-1d5b6bafbf93"}}},{"cell_type":"code","source":["fifa_df.select([\"long_name\", \"club\", \"nationality\"]).show(n=10, truncate=False)\n# Alternatively (no list notation):\n# fifa_df.select(\"long_name\", \"club\", \"nationality\").show(n=10, truncate=False)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8346f8cb-de85-4c2c-8882-b23e1adff26c"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["If we want to select (multiple) **distinct** columns, we will use the <code>distinct</code> method."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"9475ec13-2919-42fa-b2ff-42ffb2df9003"}}},{"cell_type":"code","source":["fifa_df.select([\"nationality\"]).distinct().show(n=10, truncate=False)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"aa5a775d-bfab-46a5-9c6b-1bbb695b459e"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["fifa_df.select([\"club\", \"nationality\"]).distinct().show(n=10, truncate=False)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"9dd596c1-9d33-484a-9c35-2a6f5bbcd9a0"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **2.2 Find Duplicates (if any)**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"d26473c8-d16d-4686-a750-8c9715c8ad45"}}},{"cell_type":"code","source":["print(\"The total number of duplicated ages are {:d} out of {:d}\".\n      format(fifa_df.count() - fifa_df.dropDuplicates([\"age\"]).count(), fifa_df.count()))"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"61debff0-273c-41b4-9487-bb0e1b0c5661"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **2.3 Filtering Data**\nIn order to filter data, according to the condition specified, we use the <code>filter</code> command. Here we are filtering our DataFrame based on the condition that <code>team_jersey_number</code> must be equal to <code>10</code> and then we are calculating how many records/rows are there in the filtered output."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"9978bbe4-dd54-4adf-abd8-deca4a5b88fd"}}},{"cell_type":"code","source":["fifa_df.filter(fifa_df.team_jersey_number==10).show(truncate=True)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"6907758c-0a19-465f-98fd-86dedc971670"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["print(\"The total number of players having jersey #10 are: {:d}\".\n      format(fifa_df.filter(fifa_df.team_jersey_number==10).count()))"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"0adcd8d1-911f-4e59-9dcb-1c96c4a8e357"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **2.4 Filtering Data (using multiple parameters)**\nWe can filter our data based on multiple logical conditions connected together by <code>AND</code> or <code>OR</code> operators."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3d518641-29e3-4d96-a74d-d96e9a92507e"}}},{"cell_type":"code","source":["# Filter Italian goalkeepers only\nfifa_df.filter((fifa_df.team_position==\"GK\") & (fifa_df.nationality==\"Italy\")).show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"072f8ec3-cf56-4882-b17a-868876c62f39"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **2.5 Sorting Data**\n\nWe can sort data using the <code>sort</code> method _or_ the <code>orderBy</code> method (which is just an alias of the former)."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"9b7bb33a-754f-49eb-9ef2-f110e5bc8984"}}},{"cell_type":"code","source":["fifa_df.sort([\"height_cm\", \"weight_kg\"], ascending=[False, False]).show()\n# Alternatively:\n# fifa_df.orderBy([\"height_cm\", \"weight_kg\"], ascending=[False, False]).show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"14b8d6ca-b6c3-4172-82a8-732216bf2a28"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **2.6 Filtering + Sorting Data**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"0bd45818-e131-4271-bed2-c9d6d0aaa884"}}},{"cell_type":"code","source":["# Filter Italian goalkeepers only, and sort them by height in ascending order\nfifa_df.filter((fifa_df.team_position==\"GK\") & (fifa_df.nationality==\"Italy\")).sort(\"height_cm\").show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"4c8c886d-b99a-4bc2-a705-7459a128e68f"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **2.7 Grouping Data**\n\nIn order to group data in a <code>DataFrame</code> on the basis of the values of one or more column, the <code>groupBy</code> operator is used."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"b6eac515-8c2e-463b-ae98-1f6f1b09a052"}}},{"cell_type":"code","source":["# Let's group data by `age`\nfifa_df.groupby([\"age\"]).count().show(10)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"ffe76453-f5db-4b29-be9e-df9fe57a97bc"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Let's group data by `age` and sort it according to the resulting count in non-ascending order\nfifa_df.groupby([\"age\"]).count().sort(\"count\", ascending=False).show(10)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2ce86843-df31-4933-9492-88ef222935a8"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Let's group data by `nationality` and `team_position`\nfifa_df.groupby([\"nationality\", \"team_position\"]).count().sort(\"count\", ascending=False).show(10)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8f44f1c1-bfce-4855-be36-ee79d6c9a891"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Let's group data by `nationality` and `team_position` (except \"SUB\" and \"RES\")\nfifa_df.filter((fifa_df.team_position != \"SUB\") & (fifa_df.team_position != \"RES\")).groupby([\"nationality\", \"team_position\"]).count().sort(\"count\", ascending=False).show(10)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"25bbc047-926e-4688-b9e0-55d9e8aa6e2f"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **2.8 Performing SQL Queries**\n\nWe can also pass SQL queries directly to any DataFrame, for that we need to create a table from the DataFrame using the <code>registerTempTable</code> method and then use  <code>sqlContext.sql()</code> to pass the SQL queries."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"d9c7b2cf-846f-4413-8d70-a4196a42abbc"}}},{"cell_type":"code","source":["fifa_df.registerTempTable('fifa_table')\nspark.sql('select * from fifa_table').show(10)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"940a439e-8d7f-4a95-ad95-d11d6426d7e3"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["spark.sql('select distinct(club) from fifa_table').show(10)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"21d104d4-46f4-45a7-8068-e6e69ee614c9"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["## **3. Data Visualization**\n\nA crucial step of the typical _exploratory analysis_ phase is to visually inspect data in order to get a sense of possible insights (e.g., _outliers_, _relationships_, _patterns_).\n\nPySpark **does not** have any plotting functionality (yet). If we want to plot something, we **must** bring the data out of the Spark (_distributed_) Context and into our _local_ Python session, where we can deal with it using any of Python's many plotting libraries, such as <code>matplotlib</code> and <code>seaborn</code>. Please, refer to _Lecture 10_ available at this [link](https://github.com/gtolomei/python-for-datascience#Class-Schedule) for a deeper understanding of these libraries.\n\n**NOTE:** Databricks provides itself support for visualization through the ```display``` function, although ```matplotlib``` and ```seaborn``` may guarantee more flexibility (see [here](https://docs.databricks.com/notebooks/visualizations/index.html) for more information)."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"c9b519f6-42ae-4f89-bb88-d95482905c3a"}}},{"cell_type":"code","source":["# The following import allows to use Python's plotting APIs: matplotlib and seaborn\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n# The following directive is to allow the inline visualization of generated plots\n#%matplotlib inline "],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2bb73498-4c5e-4ef3-b93c-1f50d967d671"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **3.1 Move Data out from <code>SparkContext</code> into <code>Pandas</code>**\n\nThis can be achieved by calling the method <code>toPandas</code> on our original Spark DataFrame."],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"c24553e8-c5be-40c8-b7f8-50de8fe1e8e9"}}},{"cell_type":"code","source":["# Convert our `fifa_df` Spark DataFrame into the corresponding Pandas DataFrame\np_fifa_df = fifa_df.toPandas()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2a5bf918-7421-4e3b-9918-319e891cf280"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **3.2 Plot <code>age</code> Distribution using <code>Pandas</code>' built-in plotting functions**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"102c3391-4dea-4195-ac2e-398e82c18d46"}}},{"cell_type":"code","source":["# Create a 1x1 figure\nfig, ax = plt.subplots(1,1, figsize=(8,6))\n\n_ = p_fifa_df.age.plot.hist(ax=ax, bins=10, color=\"lightblue\", edgecolor='black', linewidth=1.2)\n_ = p_fifa_df.age.plot.density(ax=ax, secondary_y=True, color=\"red\")\n_ = ax.set_xlabel(\"Age\")\n_ = ax.set_ylabel(\"Frequency\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"c84ac7da-b858-4f19-9697-36c97125d3f0"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **3.3 Plot <code>age</code> Distribution using <code>matplotlib</code>'s plotting functions**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"8116b0dd-5c42-4267-a0a1-66bda0e0191b"}}},{"cell_type":"code","source":["# Create a 1x1 figure\nfig, ax = plt.subplots(1,1, figsize=(8,6))\n\n_ = ax.hist(p_fifa_df.age, bins=10, color=\"lightblue\", edgecolor='black', linewidth=1.2)\n_ = ax\n_ = ax.set_xlabel(\"Age\")\n_ = ax.set_ylabel(\"Frequency\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"75e9547c-6a1e-4472-961a-9013b2a76def"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **3.4 Plot <code>age</code> Distribution using <code>seaborn</code>'s plotting functions**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"f9be46c7-2208-4027-b768-e1cb453a9ef4"}}},{"cell_type":"code","source":["# Create a 1x1 figure\nfig, ax = plt.subplots(1,1, figsize=(8,6))\n\n_ = sns.distplot(p_fifa_df.age, \n                 bins=10, \n                 kde=True,\n                 ax=ax, \n                 color=\"lightblue\", \n                 hist_kws={\"edgecolor\":\"k\", \"linewidth\":1.2},\n                 kde_kws={\"color\": \"r\", \"lw\": 1.5}\n                 )\n_ = ax.set_xlabel(\"Age\")\n_ = ax.set_ylabel(\"Density(%)\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"cfc1853d-d3f1-4095-8130-904edb441041"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **3.5 Boxplot of a single variable**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"e502cd2e-6735-45eb-9f88-0be5f5d50461"}}},{"cell_type":"code","source":["# Create a 1x1 figure\nfig, ax = plt.subplots(1,1, figsize=(6,4))\n\n_ = sns.boxplot(x=p_fifa_df.height_cm, color=\"g\")\n# Alternatively:\n#_ = sns.boxplot(x=\"height_cm\", data=p_fifa_df, color=\"g\")\n_ = ax.set_xlabel(\"Height (cm)\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3d6500ac-9027-4c4e-919c-74b86e726a44"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **3.6 Boxplot combining 2 variables**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"af4e448c-e56f-4c82-840f-8beb69d92fdc"}}},{"cell_type":"code","source":["# Create a 1x1 figure\nfig, ax = plt.subplots(1,1, figsize=(8,6))\n\n_ = sns.boxplot(x=\"team_position\", y=\"height_cm\", data=p_fifa_df)\n_ = ax.set_xlabel(\"Team Position\")\n_ = ax.set_ylabel(\"Height (cm)\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"9aebbc53-0a81-4d28-a3a1-9c4d8b2b00de"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Let's restrict the same plot above to the top-k position, escept for \"SUB\" and \"RES\"\nk = 10\n# Filter out `SUB` and `RES` players\nfiltered_df = p_fifa_df[(p_fifa_df.team_position != \"SUB\") & (p_fifa_df.team_position != \"RES\")]\n# Find out what are the top-k roles\ntop_k_roles = filtered_df[\"team_position\"].value_counts()[:k].index.values\nprint(\"The top-{:d} roles are: {:s}\".format(k, \", \".join(top_k_roles)))\n# Select only those top-k players\nfiltered_df = filtered_df[(filtered_df.team_position.isin(top_k_roles))]"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"914f5a50-3498-4656-ade7-3bf5c2647153"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"code","source":["# Create a 1x1 figure\nfig, ax = plt.subplots(1,1, figsize=(8,6))\n\n_ = sns.boxplot(x=\"team_position\", y=\"height_cm\", data=filtered_df)\n_ = ax.set_xlabel(\"Team Position\")\n_ = ax.set_ylabel(\"Height (cm)\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"e2d30ec3-58df-4471-b18d-e231186e1a43"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0},{"cell_type":"markdown","source":["### **3.7 Pairplot: Showing relationship between 2 or more variables**"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"08edb219-95a0-40be-8510-386a9305b686"}}},{"cell_type":"code","source":["_ = sns.pairplot(p_fifa_df[[\"height_cm\", \"weight_kg\"]],\n                 kind=\"reg\",\n                 diag_kind=\"hist\", # hist or kde\n                 diag_kws={\"color\":\"r\", \"alpha\":0.7, \"edgecolor\":\"k\"},     \n                 height=3\n                 )"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"bfe739f3-b593-4687-a25e-23885539df60"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"type":"ipynbError","data":"","errorSummary":"","arguments":{}}},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>"]}}],"execution_count":0}],"metadata":{"application/vnd.databricks.v1+notebook":{"notebookName":"PySpark_Tutorial","dashboards":[],"language":"python","widgets":{},"notebookOrigID":2727532414594245}},"nbformat":4,"nbformat_minor":0}