Data_Analysis_Practice.ipynb

{"cells":[{"cell_type":"code","source":["#read the data\ndf1 = spark.read.format(\"csv\").option(\"delimiter\", \"\\t\").option(\"header\",\"true\").option(\"inferschema\",\"true\").load(\"dbfs:/FileStore/shared_uploads/Satadru1998@gmail.com/Data_Analysis_Play.tsv\")"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"d227d60d-bcb3-42ad-93c9-39d14f0e43e1"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["#Print first 5 records\ndf1.show(5) "],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"a9f0ff81-063d-44f1-8960-322f73e4fb34"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+--------+--------+--------------------+--------------------+----------+\n|order_id|quantity|           item_name|  choice_description|item_price|\n+--------+--------+--------------------+--------------------+----------+\n|       1|       1|Chips and Fresh T...|                NULL|    $2.39 |\n|       1|       1|                Izze|        [Clementine]|    $3.39 |\n|       1|       1|    Nantucket Nectar|             [Apple]|    $3.39 |\n|       1|       1|Chips and Tomatil...|                NULL|    $2.39 |\n|       2|       2|        Chicken Bowl|[Tomatillo-Red Ch...|   $16.98 |\n+--------+--------+--------------------+--------------------+----------+\nonly showing top 5 rows\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+--------+--------+--------------------+--------------------+----------+\norder_id|quantity|           item_name|  choice_description|item_price|\n+--------+--------+--------------------+--------------------+----------+\n       1|       1|Chips and Fresh T...|                NULL|    $2.39 |\n       1|       1|                Izze|        [Clementine]|    $3.39 |\n       1|       1|    Nantucket Nectar|             [Apple]|    $3.39 |\n       1|       1|Chips and Tomatil...|                NULL|    $2.39 |\n       2|       2|        Chicken Bowl|[Tomatillo-Red Ch...|   $16.98 |\n+--------+--------+--------------------+--------------------+----------+\nonly showing top 5 rows\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#Print total records\ndf1.count(),len(df1.columns)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2f9ca311-2d03-464e-ac59-404c2f5893fb"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Out[29]: (4622, 5)</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">Out[29]: (4622, 5)</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#Print the name of all the columns\ndf1.columns"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"54fbea43-eb11-475a-bff0-e7ceb73bcf67"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Out[30]: [&#39;order_id&#39;, &#39;quantity&#39;, &#39;item_name&#39;, &#39;choice_description&#39;, &#39;item_price&#39;]</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">Out[30]: [&#39;order_id&#39;, &#39;quantity&#39;, &#39;item_name&#39;, &#39;choice_description&#39;, &#39;item_price&#39;]</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#Print type of variables\ndf1.printSchema()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"a15c74bb-62b3-4042-8df5-b3c440d703f0"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">root\n |-- order_id: integer (nullable = true)\n |-- quantity: integer (nullable = true)\n |-- item_name: string (nullable = true)\n |-- choice_description: string (nullable = true)\n |-- item_price: string (nullable = true)\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">root\n-- order_id: integer (nullable = true)\n-- quantity: integer (nullable = true)\n-- item_name: string (nullable = true)\n-- choice_description: string (nullable = true)\n-- item_price: string (nullable = true)\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#Print the last 7 records"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"11e5a328-dcb6-415c-bc1d-ce231116e77d"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["from pyspark.sql.functions import *"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"eb7fec95-923f-4877-bf54-68cce9915b2c"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["df1=df1.withColumn(\"Row_id\",monotonically_increasing_id());\ndf1.show(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"bc9a0ed9-48b2-4b3e-94fa-2dd02c73f575"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+--------+--------+--------------------+--------------------+----------+------+\n|order_id|quantity|           item_name|  choice_description|item_price|Row_id|\n+--------+--------+--------------------+--------------------+----------+------+\n|       1|       1|Chips and Fresh T...|                NULL|    $2.39 |     0|\n|       1|       1|                Izze|        [Clementine]|    $3.39 |     1|\n|       1|       1|    Nantucket Nectar|             [Apple]|    $3.39 |     2|\n|       1|       1|Chips and Tomatil...|                NULL|    $2.39 |     3|\n|       2|       2|        Chicken Bowl|[Tomatillo-Red Ch...|   $16.98 |     4|\n+--------+--------+--------------------+--------------------+----------+------+\nonly showing top 5 rows\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+--------+--------+--------------------+--------------------+----------+------+\norder_id|quantity|           item_name|  choice_description|item_price|Row_id|\n+--------+--------+--------------------+--------------------+----------+------+\n       1|       1|Chips and Fresh T...|                NULL|    $2.39 |     0|\n       1|       1|                Izze|        [Clementine]|    $3.39 |     1|\n       1|       1|    Nantucket Nectar|             [Apple]|    $3.39 |     2|\n       1|       1|Chips and Tomatil...|                NULL|    $2.39 |     3|\n       2|       2|        Chicken Bowl|[Tomatillo-Red Ch...|   $16.98 |     4|\n+--------+--------+--------------------+--------------------+----------+------+\nonly showing top 5 rows\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["tail_part=df1.filter(df1['Row_id']>=df1.count()-7);\ntail_part.show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"de570f0a-7ebb-4695-a6a2-1e79c8757da0"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+--------+--------+-------------------+--------------------+----------+------+\n|order_id|quantity|          item_name|  choice_description|item_price|Row_id|\n+--------+--------+-------------------+--------------------+----------+------+\n|    1832|       1| Chicken Soft Tacos|[Fresh Tomato Sal...|    $8.75 |  4615|\n|    1832|       1|Chips and Guacamole|                NULL|    $4.45 |  4616|\n|    1833|       1|      Steak Burrito|[Fresh Tomato Sal...|   $11.75 |  4617|\n|    1833|       1|      Steak Burrito|[Fresh Tomato Sal...|   $11.75 |  4618|\n|    1834|       1| Chicken Salad Bowl|[Fresh Tomato Sal...|   $11.25 |  4619|\n|    1834|       1| Chicken Salad Bowl|[Fresh Tomato Sal...|    $8.75 |  4620|\n|    1834|       1| Chicken Salad Bowl|[Fresh Tomato Sal...|    $8.75 |  4621|\n+--------+--------+-------------------+--------------------+----------+------+\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+--------+--------+-------------------+--------------------+----------+------+\norder_id|quantity|          item_name|  choice_description|item_price|Row_id|\n+--------+--------+-------------------+--------------------+----------+------+\n    1832|       1| Chicken Soft Tacos|[Fresh Tomato Sal...|    $8.75 |  4615|\n    1832|       1|Chips and Guacamole|                NULL|    $4.45 |  4616|\n    1833|       1|      Steak Burrito|[Fresh Tomato Sal...|   $11.75 |  4617|\n    1833|       1|      Steak Burrito|[Fresh Tomato Sal...|   $11.75 |  4618|\n    1834|       1| Chicken Salad Bowl|[Fresh Tomato Sal...|   $11.25 |  4619|\n    1834|       1| Chicken Salad Bowl|[Fresh Tomato Sal...|    $8.75 |  4620|\n    1834|       1| Chicken Salad Bowl|[Fresh Tomato Sal...|    $8.75 |  4621|\n+--------+--------+-------------------+--------------------+----------+------+\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#What is the most ordered item? & how many items were ordered?\ndf1.groupBy('item_name').agg(sum('quantity').alias(\"Total_number_of_orders\")).orderBy(\"Total_number_of_orders\",ascending=False).show(1)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"e6160bf7-abff-447e-9801-9859da4dead6"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+------------+----------------------+\n|   item_name|Total_number_of_orders|\n+------------+----------------------+\n|Chicken Bowl|                   761|\n+------------+----------------------+\nonly showing top 1 row\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+------------+----------------------+\n   item_name|Total_number_of_orders|\n+------------+----------------------+\nChicken Bowl|                   761|\n+------------+----------------------+\nonly showing top 1 row\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#What is the most ordered item in the choice_description column & how many items were ordered?\ndf2=df1.filter(df1['choice_description']!='NULL');\ndf2.groupBy('choice_description').agg(sum('quantity').alias(\"Total_number_of_orders\")).orderBy(\"Total_number_of_orders\",ascending=False).show(1)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"6b6a0953-c9bf-4dff-a4b0-ed3a86a0cae4"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+------------------+----------------------+\n|choice_description|Total_number_of_orders|\n+------------------+----------------------+\n|       [Diet Coke]|                   159|\n+------------------+----------------------+\nonly showing top 1 row\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+------------------+----------------------+\nchoice_description|Total_number_of_orders|\n+------------------+----------------------+\n       [Diet Coke]|                   159|\n+------------------+----------------------+\nonly showing top 1 row\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#Turn the item price into float\ndef convertera(x):\n  ms=float(x[1:]);\n  return ms;"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"a4c26c1a-6cf8-4c55-ba51-f7ebcb24aa49"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["from pyspark.sql.types import *"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3254d4b8-bc06-4e62-94d9-f5cbd09a3a7d"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["uff_for_convertera=udf(convertera,FloatType());"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2ee4470a-43de-420b-8896-c490c2ed7c43"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["df1=df1.withColumn('item_price',uff_for_convertera(df1['item_price']));\ndf1.show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"1843ec32-e2da-4cb5-9e5e-0fd27b73afbc"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+--------+--------+--------------------+--------------------+----------+------+\n|order_id|quantity|           item_name|  choice_description|item_price|Row_id|\n+--------+--------+--------------------+--------------------+----------+------+\n|       1|       1|Chips and Fresh T...|                NULL|      2.39|     0|\n|       1|       1|                Izze|        [Clementine]|      3.39|     1|\n|       1|       1|    Nantucket Nectar|             [Apple]|      3.39|     2|\n|       1|       1|Chips and Tomatil...|                NULL|      2.39|     3|\n|       2|       2|        Chicken Bowl|[Tomatillo-Red Ch...|     16.98|     4|\n|       3|       1|        Chicken Bowl|[Fresh Tomato Sal...|     10.98|     5|\n|       3|       1|       Side of Chips|                NULL|      1.69|     6|\n|       4|       1|       Steak Burrito|[Tomatillo Red Ch...|     11.75|     7|\n|       4|       1|    Steak Soft Tacos|[Tomatillo Green ...|      9.25|     8|\n|       5|       1|       Steak Burrito|[Fresh Tomato Sal...|      9.25|     9|\n|       5|       1| Chips and Guacamole|                NULL|      4.45|    10|\n|       6|       1|Chicken Crispy Tacos|[Roasted Chili Co...|      8.75|    11|\n|       6|       1|  Chicken Soft Tacos|[Roasted Chili Co...|      8.75|    12|\n|       7|       1|        Chicken Bowl|[Fresh Tomato Sal...|     11.25|    13|\n|       7|       1| Chips and Guacamole|                NULL|      4.45|    14|\n|       8|       1|Chips and Tomatil...|                NULL|      2.39|    15|\n|       8|       1|     Chicken Burrito|[Tomatillo-Green ...|      8.49|    16|\n|       9|       1|     Chicken Burrito|[Fresh Tomato Sal...|      8.49|    17|\n|       9|       2|         Canned Soda|            [Sprite]|      2.18|    18|\n|      10|       1|        Chicken Bowl|[Tomatillo Red Ch...|      8.75|    19|\n+--------+--------+--------------------+--------------------+----------+------+\nonly showing top 20 rows\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+--------+--------+--------------------+--------------------+----------+------+\norder_id|quantity|           item_name|  choice_description|item_price|Row_id|\n+--------+--------+--------------------+--------------------+----------+------+\n       1|       1|Chips and Fresh T...|                NULL|      2.39|     0|\n       1|       1|                Izze|        [Clementine]|      3.39|     1|\n       1|       1|    Nantucket Nectar|             [Apple]|      3.39|     2|\n       1|       1|Chips and Tomatil...|                NULL|      2.39|     3|\n       2|       2|        Chicken Bowl|[Tomatillo-Red Ch...|     16.98|     4|\n       3|       1|        Chicken Bowl|[Fresh Tomato Sal...|     10.98|     5|\n       3|       1|       Side of Chips|                NULL|      1.69|     6|\n       4|       1|       Steak Burrito|[Tomatillo Red Ch...|     11.75|     7|\n       4|       1|    Steak Soft Tacos|[Tomatillo Green ...|      9.25|     8|\n       5|       1|       Steak Burrito|[Fresh Tomato Sal...|      9.25|     9|\n       5|       1| Chips and Guacamole|                NULL|      4.45|    10|\n       6|       1|Chicken Crispy Tacos|[Roasted Chili Co...|      8.75|    11|\n       6|       1|  Chicken Soft Tacos|[Roasted Chili Co...|      8.75|    12|\n       7|       1|        Chicken Bowl|[Fresh Tomato Sal...|     11.25|    13|\n       7|       1| Chips and Guacamole|                NULL|      4.45|    14|\n       8|       1|Chips and Tomatil...|                NULL|      2.39|    15|\n       8|       1|     Chicken Burrito|[Tomatillo-Green ...|      8.49|    16|\n       9|       1|     Chicken Burrito|[Fresh Tomato Sal...|      8.49|    17|\n       9|       2|         Canned Soda|            [Sprite]|      2.18|    18|\n      10|       1|        Chicken Bowl|[Tomatillo Red Ch...|      8.75|    19|\n+--------+--------+--------------------+--------------------+----------+------+\nonly showing top 20 rows\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#How much was the revenue for the period in the dataset?"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"b28c64e6-f490-4e79-bdd8-3a60fca2c89e"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["df1.withColumn('Revenue',df1['quantity']*df1['item_price']).select(sum('Revenue')).show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"5bcf442d-fb88-4fa7-b8c8-e94accc24a4e"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+----------------+\n|    sum(Revenue)|\n+----------------+\n|39237.0197327137|\n+----------------+\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+----------------+\n    sum(Revenue)|\n+----------------+\n39237.0197327137|\n+----------------+\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#print a data frame with only two columns item_name and item_price"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"64d80cda-955d-42b6-8fa9-d4b44d8d8501"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\"></div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\"></div>"]}}],"execution_count":0},{"cell_type":"code","source":["df1.select(['item_name','item_price']).show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"ae26fc69-78be-4f23-8e69-695f2d2fbcfe"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+--------------------+----------+\n|           item_name|item_price|\n+--------------------+----------+\n|Chips and Fresh T...|      2.39|\n|                Izze|      3.39|\n|    Nantucket Nectar|      3.39|\n|Chips and Tomatil...|      2.39|\n|        Chicken Bowl|     16.98|\n|        Chicken Bowl|     10.98|\n|       Side of Chips|      1.69|\n|       Steak Burrito|     11.75|\n|    Steak Soft Tacos|      9.25|\n|       Steak Burrito|      9.25|\n| Chips and Guacamole|      4.45|\n|Chicken Crispy Tacos|      8.75|\n|  Chicken Soft Tacos|      8.75|\n|        Chicken Bowl|     11.25|\n| Chips and Guacamole|      4.45|\n|Chips and Tomatil...|      2.39|\n|     Chicken Burrito|      8.49|\n|     Chicken Burrito|      8.49|\n|         Canned Soda|      2.18|\n|        Chicken Bowl|      8.75|\n+--------------------+----------+\nonly showing top 20 rows\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+--------------------+----------+\n           item_name|item_price|\n+--------------------+----------+\nChips and Fresh T...|      2.39|\n                Izze|      3.39|\n    Nantucket Nectar|      3.39|\nChips and Tomatil...|      2.39|\n        Chicken Bowl|     16.98|\n        Chicken Bowl|     10.98|\n       Side of Chips|      1.69|\n       Steak Burrito|     11.75|\n    Steak Soft Tacos|      9.25|\n       Steak Burrito|      9.25|\n Chips and Guacamole|      4.45|\nChicken Crispy Tacos|      8.75|\n  Chicken Soft Tacos|      8.75|\n        Chicken Bowl|     11.25|\n Chips and Guacamole|      4.45|\nChips and Tomatil...|      2.39|\n     Chicken Burrito|      8.49|\n     Chicken Burrito|      8.49|\n         Canned Soda|      2.18|\n        Chicken Bowl|      8.75|\n+--------------------+----------+\nonly showing top 20 rows\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["# delete the duplicates in item_name and quantity\nfiltered_data=df1.drop_duplicates(['item_name','quantity']);\n# select only the products with quantity equals to 1\nprod_1=filtered_data.filter(filtered_data['quantity']==1);\n# select only the item_name and item_price columns\nproper_selection=prod_1.select('item_name','item_price')\n# sort the values from the most to less expensive\nproper_selection.orderBy('item_price',ascending=False).show(proper_selection.count())"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"03cf98bc-9539-4617-bd00-2eae37ebbc5a"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+--------------------+----------+\n|           item_name|item_price|\n+--------------------+----------+\n| Barbacoa Salad Bowl|     11.89|\n|    Steak Salad Bowl|     11.89|\n| Carnitas Salad Bowl|     11.89|\n|       Barbacoa Bowl|     11.75|\n|Barbacoa Crispy T...|     11.75|\n|       Steak Burrito|     11.75|\n|   Veggie Salad Bowl|     11.25|\n|         Veggie Bowl|     11.25|\n|      Veggie Burrito|     11.25|\n|   Veggie Soft Tacos|     11.25|\n|       Chicken Salad|     10.98|\n|        Chicken Bowl|     10.98|\n|Carnitas Crispy T...|      9.25|\n| Barbacoa Soft Tacos|      9.25|\n| Carnitas Soft Tacos|      9.25|\n|    Steak Soft Tacos|      9.25|\n|  Steak Crispy Tacos|      9.25|\n|      Carnitas Salad|      8.99|\n|       Carnitas Bowl|      8.99|\n|    Carnitas Burrito|      8.99|\n|         Steak Salad|      8.99|\n|          Steak Bowl|      8.99|\n|    Barbacoa Burrito|      8.99|\n|  Chicken Soft Tacos|      8.75|\n|  Chicken Salad Bowl|      8.75|\n|Chicken Crispy Tacos|      8.75|\n|        Veggie Salad|      8.49|\n| Veggie Crispy Tacos|      8.49|\n|     Chicken Burrito|      8.49|\n|        Crispy Tacos|       7.4|\n|               Salad|       7.4|\n|                Bowl|       7.4|\n|             Burrito|       7.4|\n|   6 Pack Soft Drink|      6.49|\n| Chips and Guacamole|      4.45|\n|    Nantucket Nectar|      3.39|\n|                Izze|      3.39|\n|Chips and Mild Fr...|       3.0|\n|Chips and Tomatil...|      2.95|\n|Chips and Tomatil...|      2.95|\n|Chips and Roasted...|      2.95|\n|Chips and Roasted...|      2.39|\n|Chips and Tomatil...|      2.39|\n|Chips and Fresh T...|      2.39|\n|Chips and Tomatil...|      2.39|\n|               Chips|      2.15|\n|       Side of Chips|      1.69|\n|   Canned Soft Drink|      1.25|\n|       Bottled Water|      1.09|\n|         Canned Soda|      1.09|\n+--------------------+----------+\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+--------------------+----------+\n           item_name|item_price|\n+--------------------+----------+\n Barbacoa Salad Bowl|     11.89|\n    Steak Salad Bowl|     11.89|\n Carnitas Salad Bowl|     11.89|\n       Barbacoa Bowl|     11.75|\nBarbacoa Crispy T...|     11.75|\n       Steak Burrito|     11.75|\n   Veggie Salad Bowl|     11.25|\n         Veggie Bowl|     11.25|\n      Veggie Burrito|     11.25|\n   Veggie Soft Tacos|     11.25|\n       Chicken Salad|     10.98|\n        Chicken Bowl|     10.98|\nCarnitas Crispy T...|      9.25|\n Barbacoa Soft Tacos|      9.25|\n Carnitas Soft Tacos|      9.25|\n    Steak Soft Tacos|      9.25|\n  Steak Crispy Tacos|      9.25|\n      Carnitas Salad|      8.99|\n       Carnitas Bowl|      8.99|\n    Carnitas Burrito|      8.99|\n         Steak Salad|      8.99|\n          Steak Bowl|      8.99|\n    Barbacoa Burrito|      8.99|\n  Chicken Soft Tacos|      8.75|\n  Chicken Salad Bowl|      8.75|\nChicken Crispy Tacos|      8.75|\n        Veggie Salad|      8.49|\n Veggie Crispy Tacos|      8.49|\n     Chicken Burrito|      8.49|\n        Crispy Tacos|       7.4|\n               Salad|       7.4|\n                Bowl|       7.4|\n             Burrito|       7.4|\n   6 Pack Soft Drink|      6.49|\n Chips and Guacamole|      4.45|\n    Nantucket Nectar|      3.39|\n                Izze|      3.39|\nChips and Mild Fr...|       3.0|\nChips and Tomatil...|      2.95|\nChips and Tomatil...|      2.95|\nChips and Roasted...|      2.95|\nChips and Roasted...|      2.39|\nChips and Tomatil...|      2.39|\nChips and Fresh T...|      2.39|\nChips and Tomatil...|      2.39|\n               Chips|      2.15|\n       Side of Chips|      1.69|\n   Canned Soft Drink|      1.25|\n       Bottled Water|      1.09|\n         Canned Soda|      1.09|\n+--------------------+----------+\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#What was the quantity of the most expensive item ordered?\ndf1.orderBy('item_price',ascending=False).select('*').show(1)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3178404e-197b-4f4d-a6b2-7e8051f45faf"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+--------+--------+--------------------+------------------+----------+------+\n|order_id|quantity|           item_name|choice_description|item_price|Row_id|\n+--------+--------+--------------------+------------------+----------+------+\n|    1443|      15|Chips and Fresh T...|              NULL|     44.25|  3598|\n+--------+--------+--------------------+------------------+----------+------+\nonly showing top 1 row\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+--------+--------+--------------------+------------------+----------+------+\norder_id|quantity|           item_name|choice_description|item_price|Row_id|\n+--------+--------+--------------------+------------------+----------+------+\n    1443|      15|Chips and Fresh T...|              NULL|     44.25|  3598|\n+--------+--------+--------------------+------------------+----------+------+\nonly showing top 1 row\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["#How many times were a Veggie Salad Bowl ordered?\ndf1.filter(df1['item_name']=='Veggie Salad Bowl').show()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"05026022-c138-4934-a7a7-04e813d7e3a6"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">+--------+--------+-----------------+--------------------+----------+------+\n|order_id|quantity|        item_name|  choice_description|item_price|Row_id|\n+--------+--------+-----------------+--------------------+----------+------+\n|      83|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     0|\n|     128|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     1|\n|     195|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     2|\n|     207|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     3|\n|     394|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|     4|\n|     536|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|     5|\n|     760|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     6|\n|     869|       1|Veggie Salad Bowl|[Tomatillo Red Ch...|     11.25|     7|\n|     896|       1|Veggie Salad Bowl|[Roasted Chili Co...|      8.75|     8|\n|     913|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|     9|\n|    1066|       1|Veggie Salad Bowl|[Roasted Chili Co...|      8.75|    10|\n|    1289|       1|Veggie Salad Bowl|[Tomatillo Red Ch...|     11.25|    11|\n|    1321|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|    12|\n|    1646|       1|Veggie Salad Bowl|[Tomatillo Red Ch...|     11.25|    13|\n|    1677|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|    14|\n|    1700|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|    15|\n|    1805|       1|Veggie Salad Bowl|[Tomatillo Green ...|      8.75|    16|\n|    1818|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|    17|\n+--------+--------+-----------------+--------------------+----------+------+\n\n</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">+--------+--------+-----------------+--------------------+----------+------+\norder_id|quantity|        item_name|  choice_description|item_price|Row_id|\n+--------+--------+-----------------+--------------------+----------+------+\n      83|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     0|\n     128|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     1|\n     195|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     2|\n     207|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     3|\n     394|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|     4|\n     536|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|     5|\n     760|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|     6|\n     869|       1|Veggie Salad Bowl|[Tomatillo Red Ch...|     11.25|     7|\n     896|       1|Veggie Salad Bowl|[Roasted Chili Co...|      8.75|     8|\n     913|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|     9|\n    1066|       1|Veggie Salad Bowl|[Roasted Chili Co...|      8.75|    10|\n    1289|       1|Veggie Salad Bowl|[Tomatillo Red Ch...|     11.25|    11|\n    1321|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|    12|\n    1646|       1|Veggie Salad Bowl|[Tomatillo Red Ch...|     11.25|    13|\n    1677|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|    14|\n    1700|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|     11.25|    15|\n    1805|       1|Veggie Salad Bowl|[Tomatillo Green ...|      8.75|    16|\n    1818|       1|Veggie Salad Bowl|[Fresh Tomato Sal...|      8.75|    17|\n+--------+--------+-----------------+--------------------+----------+------+\n\n</div>"]}}],"execution_count":0},{"cell_type":"code","source":["df1.filter(df1['item_name']=='Veggie Salad Bowl').count()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"2d5c9a50-40f0-40b2-bf83-c6270c0fdf13"}},"outputs":[{"output_type":"display_data","metadata":{"application/vnd.databricks.v1+output":{"datasetInfos":[],"data":"<div class=\"ansiout\">Out[72]: 18</div>","removedWidgets":[],"addedWidgets":{},"type":"html","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>\n<div class=\"ansiout\">Out[72]: 18</div>"]}}],"execution_count":0}],"metadata":{"application/vnd.databricks.v1+notebook":{"notebookName":"Data_Analysis_Practice","dashboards":[],"notebookMetadata":{"pythonIndentUnit":2},"language":"python","widgets":{},"notebookOrigID":3569253598047972}},"nbformat":4,"nbformat_minor":0}