Module 1 Basics of PySpark and the DataFrame API.ipynb

{"cells":[{"cell_type":"markdown","source":["Today, we're going to dive into the basics of PySpark and the DataFrame API. Our goal is to set up and get familiar with PySpark API, focusing on the DataFrame API and advanced data operations such as filtering, joining, aggregating, and grouping. We'll also discuss what use cases PySpark is a good fit for."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"4bdbdd71-e76e-4037-b79a-a0fe097645f0","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["# Starting a SparkSession\n\n# from pyspark.sql import SparkSession\n\n# spark = SparkSession.builder \\\n#     .appName(\"PySpark Advanced Basics\") \\\n#     .getOrCreate()"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"5cba482e-048b-455a-8947-18bbe14e6505","inputWidgets":{},"title":""}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["# Demonstrate entrypoint via autocomplete\n# spark."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"52a76c82-1c9c-4c9e-8237-8706fb3dc836","inputWidgets":{},"title":""}},"outputs":[],"execution_count":0},{"cell_type":"markdown","source":["We have set SparkSession available as the spark variable. It's our entrypoint into using Spark.\n\nNow, let's load a dataset from the Databricks file system as a DataFrame. \n\nThe \"Wine Quality\" dataset consists of 2 datasets, related to red and white vinho verde wine samples, from the north of Portugal. This dataset was originally used to model wine quality based on physicochemical tests.\n\nInput variables (based on physicochemical tests):\n1. fixed acidity\n2. volatile acidity\n3. citric acid\n4. residual sugar\n5. chlorides\n6. free sulfur dioxide\n7. total sulfur dioxide\n8. density\n9. pH\n10. sulphates\n11. alcohol  \n\nOutput variable (based on sensory data):  \n12. quality (score between 0 and 10)\n\n*Source:  \nPaulo Cortez, University of Minho, Guimarães, Portugal, http://www3.dsi.uminho.pt/pcortez  \nA. Cerdeira, F. Almeida, T. Matos and J. Reis, Viticulture Commission of the Vinho Verde Region(CVRVV), Porto, Portugal\n@2009*"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"ccc86308-6052-47bd-b4f9-9b480e214af4","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["dbutils.fs.ls('/databricks-datasets')"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"515fff71-4707-4d5b-a0fb-4ca933d7cdfb","inputWidgets":{},"title":""}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["%fs\n\nls databricks-datasets/wine-quality"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"933f40ee-36f1-467f-b30c-26276139b548","inputWidgets":{},"title":""}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"overflow":false,"datasetInfos":[],"data":[["dbfs:/databricks-datasets/wine-quality/README.md","README.md",1066,1594262736000],["dbfs:/databricks-datasets/wine-quality/winequality-red.csv","winequality-red.csv",84199,1594262736000],["dbfs:/databricks-datasets/wine-quality/winequality-white.csv","winequality-white.csv",264426,1594262736000]],"plotOptions":{"displayType":"table","customPlotOptions":{},"pivotColumns":null,"pivotAggregation":null,"xColumns":null,"yColumns":null},"columnCustomDisplayInfos":{},"aggType":"","isJsonSchema":true,"removedWidgets":[],"aggSchema":[],"schema":[{"name":"path","type":"\"string\"","metadata":"{}"},{"name":"name","type":"\"string\"","metadata":"{}"},{"name":"size","type":"\"long\"","metadata":"{}"},{"name":"modificationTime","type":"\"long\"","metadata":"{}"}],"aggError":"","aggData":[],"addedWidgets":{},"metadata":{"isDbfsCommandResult":false},"dbfsResultPath":null,"type":"table","aggOverflow":false,"aggSeriesLimitReached":false,"arguments":{}}},"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:/databricks-datasets/wine-quality/README.md</td><td>README.md</td><td>1066</td><td>1594262736000</td></tr><tr><td>dbfs:/databricks-datasets/wine-quality/winequality-red.csv</td><td>winequality-red.csv</td><td>84199</td><td>1594262736000</td></tr><tr><td>dbfs:/databricks-datasets/wine-quality/winequality-white.csv</td><td>winequality-white.csv</td><td>264426</td><td>1594262736000</td></tr></tbody></table></div>"]}}],"execution_count":0},{"cell_type":"code","source":["data_path = \"dbfs:/databricks-datasets/wine-quality/winequality-red.csv\"\ndf = spark.read.csv(data_path, header=True, inferSchema=True, sep=\";\")"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"c46c8966-65e3-444d-b832-02cff298a063","inputWidgets":{},"title":""}},"outputs":[],"execution_count":0},{"cell_type":"markdown","source":["The above code reads a CSV file from the specified path and creates a DataFrame called df with the schema inferred from the data."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"cd4ef0e3-5a33-4720-8dc5-5c0175460494","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["df.show(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"f203790c-4f8b-43eb-aa0a-039563ade978","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\n|fixed acidity|volatile acidity|citric acid|residual sugar|chlorides|free sulfur dioxide|total sulfur dioxide|density|  pH|sulphates|alcohol|quality|\n+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\n|          7.4|             0.7|        0.0|           1.9|    0.076|               11.0|                34.0| 0.9978|3.51|     0.56|    9.4|      5|\n|          7.8|            0.88|        0.0|           2.6|    0.098|               25.0|                67.0| 0.9968| 3.2|     0.68|    9.8|      5|\n|          7.8|            0.76|       0.04|           2.3|    0.092|               15.0|                54.0|  0.997|3.26|     0.65|    9.8|      5|\n|         11.2|            0.28|       0.56|           1.9|    0.075|               17.0|                60.0|  0.998|3.16|     0.58|    9.8|      6|\n|          7.4|             0.7|        0.0|           1.9|    0.076|               11.0|                34.0| 0.9978|3.51|     0.56|    9.4|      5|\n+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\nonly showing top 5 rows\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["#### What are DataFrames and why are they important in PySpark?\n\nA DataFrame is a distributed collection of data organized into named columns. It is conceptually equivalent to a table in a relational database or a data frame in Python's pandas library, but with optimizations for distributed processing and the ability to scale to big data.\n\nDataFrames provide a high-level API for distributed data processing in PySpark. They allow us to perform complex data manipulations and analysis using concise and expressive syntax. Additionally, DataFrames in PySpark benefit from the Spark engine's optimizations, resulting in better performance compared to the lower-level RDD (Resilient Distributed Datasets) API."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"e7bec6fa-9b77-4e19-abd4-eb471af4d90c","inputWidgets":{},"title":""}}},{"cell_type":"markdown","source":["**Nulls**"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"890811aa-3a94-4a77-b950-e6fe765e7fa8","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["# import some standard functions\nfrom pyspark.sql.functions import col, sum \n\n# df.select(col('quality')).show(2)\ndf.select(sum( col('quality').isNull().cast('int') ).alias('quality')).collect()[0]['quality']"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"f9648559-8921-4547-be8b-08262b73a3d2","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["Out[11]: 0"]}],"execution_count":0},{"cell_type":"code","source":["null_counts = df.select([sum( col(c).isNull().cast('int') ).alias(c) for c in df.columns]).collect()\nnull_counts = {c: null_counts[0][c] for c in df.columns}\nprint(null_counts)"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"eabf4054-2f1e-4742-84af-7d2f322b3f3a","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["{'fixed acidity': 0, 'volatile acidity': 0, 'citric acid': 0, 'residual sugar': 0, 'chlorides': 0, 'free sulfur dioxide': 0, 'total sulfur dioxide': 0, 'density': 0, 'pH': 0, 'sulphates': 0, 'alcohol': 0, 'quality': 0}\n"]}],"execution_count":0},{"cell_type":"markdown","source":["1. `col(c).isNull().cast('int')`: For each column `c`, the `isNull()` function returns a boolean value indicating whether the value is null or not. We then cast this boolean value to an integer, where `True` becomes `1` and `False` becomes `0`. This creates a DataFrame where the null values are represented as `1` and non-null values as `0`.\n\n2. `[sum(col(c).isNull().cast('int')).alias(c) for c in df.columns]`: We use a list comprehension to apply the previous step for all columns in the DataFrame `df`. The `sum()` function is used to aggregate the 1s and 0s, calculating the total number of null values for each column. The `alias(c)` function is used to keep the original column name for the resulting DataFrame.\n\n3. `df.select(...)`: We use the `select()` function to create a new DataFrame with the aggregated null counts for each column.\n\n4. `null_counts = ...collect()`: The `collect()` function is used to retrieve the result of the null count calculation as a list of Row objects. In this case, there will only be one Row object because we've aggregated the data.\n\n5. `{c: null_counts[0][c] for c in df.columns}`: We use a dictionary comprehension to convert the Row object into a dictionary, where the keys are the column names and the values are the null counts for each column.\n\n6. `print(null_counts)`: Finally, we print the dictionary containing the null counts for each column."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"ba223190-dddc-4539-91a5-6920bd15ae80","inputWidgets":{},"title":""}}},{"cell_type":"markdown","source":["**Sorting**\n\nSort the records based on the pH and residual sugar amount."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"21ea2ab6-b79a-4b71-8586-9a43809ce684","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["from pyspark.sql.functions import desc\n\nsorted_df = df.orderBy(desc(\"pH\"), desc(\"residual sugar\"))\nsorted_df.show()"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"31ee9324-b3bb-43e2-bb43-c88f16d14720","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\n|fixed acidity|volatile acidity|citric acid|residual sugar|chlorides|free sulfur dioxide|total sulfur dioxide|density|  pH|sulphates|alcohol|quality|\n+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\n|          5.4|            0.74|        0.0|           1.2|    0.041|               16.0|                46.0|0.99258|4.01|     0.59|   12.5|      6|\n|          5.0|            0.74|        0.0|           1.2|    0.041|               16.0|                46.0|0.99258|4.01|     0.59|   12.5|      6|\n|          4.6|            0.52|       0.15|           2.1|    0.054|                8.0|                65.0| 0.9934| 3.9|     0.56|   13.1|      4|\n|          5.1|            0.47|       0.02|           1.3|    0.034|               18.0|                44.0| 0.9921| 3.9|     0.62|   12.8|      6|\n|          4.7|             0.6|       0.17|           2.3|    0.058|               17.0|               106.0| 0.9932|3.85|      0.6|   12.9|      6|\n|          5.2|           0.645|        0.0|          2.15|     0.08|               15.0|                28.0|0.99444|3.78|     0.61|   12.5|      6|\n|          5.4|            0.42|       0.27|           2.0|    0.092|               23.0|                55.0|0.99471|3.78|     0.64|   12.3|      7|\n|          5.0|            1.02|       0.04|           1.4|    0.045|               41.0|                85.0| 0.9938|3.75|     0.48|   10.5|      4|\n|          5.0|            1.04|       0.24|           1.6|     0.05|               32.0|                96.0| 0.9934|3.74|     0.62|   11.5|      5|\n|          5.0|            0.42|       0.24|           2.0|     0.06|               19.0|                50.0| 0.9917|3.72|     0.74|   14.0|      8|\n|          5.6|            0.54|       0.04|           1.7|    0.049|                5.0|                13.0| 0.9942|3.72|     0.58|   11.4|      5|\n|          5.6|            0.54|       0.04|           1.7|    0.049|                5.0|                13.0| 0.9942|3.72|     0.58|   11.4|      5|\n|          5.2|            0.49|       0.26|           2.3|     0.09|               23.0|                74.0| 0.9953|3.71|     0.62|   12.2|      6|\n|          5.6|            0.66|        0.0|           2.2|    0.087|                3.0|                11.0|0.99378|3.71|     0.63|   12.8|      7|\n|          5.6|            0.66|        0.0|           2.2|    0.087|                3.0|                11.0|0.99378|3.71|     0.63|   12.8|      7|\n|          4.9|            0.42|        0.0|           2.1|    0.048|               16.0|                42.0|0.99154|3.71|     0.74|   14.0|      7|\n|          5.0|            0.38|       0.01|           1.6|    0.048|               26.0|                60.0|0.99084| 3.7|     0.75|   14.0|      6|\n|          6.9|            0.54|       0.04|           3.0|    0.077|                7.0|                27.0| 0.9987|3.69|     0.91|    9.4|      6|\n|          6.9|            0.54|       0.04|           3.0|    0.077|                7.0|                27.0| 0.9987|3.69|     0.91|    9.4|      6|\n|          6.6|            0.61|       0.01|           1.9|     0.08|                8.0|                25.0|0.99746|3.69|     0.73|   10.5|      5|\n+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\nonly showing top 20 rows\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["**Filtering**\n\nFilter the records to keep only those within the ideal pH range"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"102b54a3-73b1-458d-b53d-e30dafd9e7b1","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["filtered_df = df.filter((df[\"pH\"] >= 3.4) & (df[\"pH\"] <= 3.6))\nfiltered_df.show()"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"fdd92c0f-7a0f-4861-ba8f-6dd2e24ac3cf","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\n|fixed acidity|volatile acidity|citric acid|residual sugar|chlorides|free sulfur dioxide|total sulfur dioxide|density|  pH|sulphates|alcohol|quality|\n+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\n|          7.4|             0.7|        0.0|           1.9|    0.076|               11.0|                34.0| 0.9978|3.51|     0.56|    9.4|      5|\n|          7.4|             0.7|        0.0|           1.9|    0.076|               11.0|                34.0| 0.9978|3.51|     0.56|    9.4|      5|\n|          7.4|            0.66|        0.0|           1.8|    0.075|               13.0|                40.0| 0.9978|3.51|     0.56|    9.4|      5|\n|          5.6|           0.615|        0.0|           1.6|    0.089|               16.0|                59.0| 0.9943|3.58|     0.52|    9.9|      5|\n|          7.6|            0.39|       0.31|           2.3|    0.082|               23.0|                71.0| 0.9982|3.52|     0.65|    9.7|      5|\n|          6.9|             0.4|       0.14|           2.4|    0.085|               21.0|                40.0| 0.9968|3.43|     0.63|    9.7|      6|\n|          7.1|            0.71|        0.0|           1.9|     0.08|               14.0|                35.0| 0.9972|3.47|     0.55|    9.4|      5|\n|          6.9|           0.685|        0.0|           2.5|    0.105|               22.0|                37.0| 0.9966|3.46|     0.57|   10.6|      6|\n|          6.9|           0.605|       0.12|          10.7|    0.073|               40.0|                83.0| 0.9993|3.45|     0.52|    9.4|      6|\n|          7.8|           0.645|        0.0|           5.5|    0.086|                5.0|                18.0| 0.9986| 3.4|     0.55|    9.6|      6|\n|          7.8|             0.6|       0.14|           2.4|    0.086|                3.0|                15.0| 0.9975|3.42|      0.6|   10.8|      6|\n|          5.7|            1.13|       0.09|           1.5|    0.172|                7.0|                19.0|  0.994| 3.5|     0.48|    9.8|      4|\n|          6.8|            0.67|       0.02|           1.8|     0.05|                5.0|                11.0| 0.9962|3.48|     0.52|    9.5|      5|\n|          6.6|            0.52|       0.04|           2.2|    0.069|                8.0|                15.0| 0.9956| 3.4|     0.63|    9.4|      6|\n|          7.8|            0.59|       0.18|           2.3|    0.076|               17.0|                54.0| 0.9975|3.43|     0.59|   10.0|      5|\n|          7.3|            0.39|       0.31|           2.4|    0.074|                9.0|                46.0| 0.9962|3.41|     0.54|    9.4|      6|\n|          8.8|             0.4|        0.4|           2.2|    0.079|               19.0|                52.0|  0.998|3.44|     0.64|    9.2|      5|\n|          7.2|           0.725|       0.05|          4.65|    0.086|                4.0|                11.0| 0.9962|3.41|     0.39|   10.9|      5|\n|          7.2|           0.725|       0.05|          4.65|    0.086|                4.0|                11.0| 0.9962|3.41|     0.39|   10.9|      5|\n|          7.5|            0.52|       0.11|           1.5|    0.079|               11.0|                39.0| 0.9968|3.42|     0.58|    9.6|      5|\n+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+\nonly showing top 20 rows\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["**Window functions**\n\nNext, let's look at window functions. They allow us to perform calculations across sets of rows that are related to the current row.  \nIn this example, we use a window function:"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"f66ec005-3646-4152-8c77-8fbee3850947","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["from pyspark.sql.window import Window\nfrom pyspark.sql.functions import row_number, desc\n\nwindow_spec = Window.partitionBy(\"quality\").orderBy(desc(\"alcohol\"))\nranked_df = df.withColumn(\"rank\", row_number().over(window_spec))\nranked_df.show()\n"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"53846df1-d45e-435e-a1ac-dff99bb29773","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+----+\n|fixed acidity|volatile acidity|citric acid|residual sugar|chlorides|free sulfur dioxide|total sulfur dioxide|density|  pH|sulphates|alcohol|quality|rank|\n+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+----+\n|          8.3|            1.02|       0.02|           3.4|    0.084|                6.0|                11.0|0.99892|3.48|     0.49|   11.0|      3|   1|\n|          7.6|            1.58|        0.0|           2.1|    0.137|                5.0|                 9.0|0.99476| 3.5|      0.4|   10.9|      3|   2|\n|          7.4|           1.185|        0.0|          4.25|    0.097|                5.0|                14.0| 0.9966|3.63|     0.54|   10.7|      3|   3|\n|          7.1|           0.875|       0.05|           5.7|    0.082|                3.0|                14.0|0.99808| 3.4|     0.52|   10.2|      3|   4|\n|          6.7|            0.76|       0.02|           1.8|    0.078|                6.0|                12.0|  0.996|3.55|     0.63|   9.95|      3|   5|\n|         10.4|            0.44|       0.42|           1.5|    0.145|               34.0|                48.0|0.99832|3.38|     0.86|    9.9|      3|   6|\n|          6.8|           0.815|        0.0|           1.2|    0.267|               16.0|                29.0|0.99471|3.32|     0.51|    9.8|      3|   7|\n|          7.3|            0.98|       0.05|           2.1|    0.061|               20.0|                49.0|0.99705|3.31|     0.55|    9.7|      3|   8|\n|         11.6|            0.58|       0.66|           2.2|    0.074|               10.0|                47.0| 1.0008|3.25|     0.57|    9.0|      3|   9|\n|         10.4|            0.61|       0.49|           2.1|      0.2|                5.0|                16.0| 0.9994|3.16|     0.63|    8.4|      3|  10|\n|          4.6|            0.52|       0.15|           2.1|    0.054|                8.0|                65.0| 0.9934| 3.9|     0.56|   13.1|      4|   1|\n|          8.5|             0.4|        0.4|           6.3|     0.05|                3.0|                10.0|0.99566|3.28|     0.56|   12.0|      4|   2|\n|          6.5|            0.67|        0.0|           4.3|    0.057|               11.0|                20.0|0.99488|3.45|     0.56|   11.8|      4|   3|\n|          6.5|            0.58|        0.0|           2.2|    0.096|                3.0|                13.0|0.99557|3.62|     0.62|   11.5|      4|   4|\n|          6.0|            0.33|       0.32|          12.9|    0.054|                6.0|               113.0|0.99572| 3.3|     0.56|   11.5|      4|   5|\n|          6.9|            1.09|       0.06|           2.1|    0.061|               12.0|                31.0| 0.9948|3.51|     0.43|   11.4|      4|   6|\n|          6.9|            0.39|       0.24|           2.1|    0.102|                4.0|                 7.0|0.99462|3.44|     0.58|   11.4|      4|   7|\n|         10.1|           0.935|       0.22|           3.4|    0.105|               11.0|                86.0|  1.001|3.43|     0.64|   11.3|      4|   8|\n|          7.5|           1.115|        0.1|           3.1|    0.086|                5.0|                12.0| 0.9958|3.54|      0.6|   11.2|      4|   9|\n|          6.5|            0.88|       0.03|           5.6|    0.079|               23.0|                47.0|0.99572|3.58|      0.5|   11.2|      4|  10|\n+-------------+----------------+-----------+--------------+---------+-------------------+--------------------+-------+----+---------+-------+-------+----+\nonly showing top 20 rows\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["The output DataFrame contains an additional \"rank\" column. The rank column assigns a unique rank to each row within the group of rows with the same \"quality\". The ranking is determined by the \"alcohol\" column, with higher values receiving a higher rank (1 being the highest rank in each group)."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"337335d2-5130-4c85-920c-ec26919b4e2a","inputWidgets":{},"title":""}}},{"cell_type":"markdown","source":["**Summary statistics**"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"d9afe619-21d5-42ec-83b7-7abb161acfa0","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["summary = df.describe()\n\nsummary.select(\"summary\", \"fixed acidity\", \"residual sugar\").show()"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"a4e95ca1-5fa4-4616-b3bc-b2c7ea153e88","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+-------+------------------+------------------+\n|summary|     fixed acidity|    residual sugar|\n+-------+------------------+------------------+\n|  count|              1599|              1599|\n|   mean| 8.319637273295838|2.5388055034396517|\n| stddev|1.7410963181276948|  1.40992805950728|\n|    min|               4.6|               0.9|\n|    max|              15.9|              15.5|\n+-------+------------------+------------------+\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["**Using SQL queries**\n\nLastly, we'll cover how to use SQL queries with DataFrames.  \nHere, we create a temporary view called \"people\" from our DataFrame df and then execute an SQL query to calculate the average age for each occupation group."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"db2651a4-876b-4603-997e-3dd3120188c2","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["# Register the DataFrame as a temporary view\ndf.createOrReplaceTempView(\"wine\")\n\n# Example: Counting the number of matches and non-matches\nresults = spark.sql(\"SELECT quality, COUNT(*) as count FROM wine GROUP BY quality\")\nresults.show()"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"0c173380-3a02-46a8-a995-ad2363af4a3e","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+-------+-----+\n|quality|count|\n+-------+-----+\n|      6|  638|\n|      3|   10|\n|      5|  681|\n|      4|   53|\n|      8|   18|\n|      7|  199|\n+-------+-----+\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["In this module, we've covered the basics of PySpark and the DataFrame API. We've learned how to set up a SparkSession, load data, perform data operations, and use SQL queries. Now that you're familiar with the DataFrame API, we'll move on to using PySpark for natural language processing tasks in the next module."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"c283388f-e0b2-4674-b106-ab96d990f9fb","inputWidgets":{},"title":""}}}],"metadata":{"application/vnd.databricks.v1+notebook":{"notebookName":"Module 1: Basics of PySpark and the DataFrame API","dashboards":[],"notebookMetadata":{"pythonIndentUnit":4,"mostRecentlyExecutedCommandWithImplicitDF":{"commandId":948763216766264,"dataframes":["_sqldf"]}},"language":"python","widgets":{}}},"nbformat":4,"nbformat_minor":0}