05 Explore Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "aa1c14e7-5dd2-49f0-b513-48c37a3a0d04",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Summarize data using Azure Databricks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "44f32403-bb0a-4bfd-8886-8fcdf614e6b2",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Select the scored data generated by the Azure Data Factory pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "2e15d0ff-245e-4f07-a0be-4d860e55466c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "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>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "arguments": {},
       "data": "",
       "errorSummary": "",
       "errorTraceType": null,
       "metadata": {},
       "type": "ipynbError"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "select * from scoredflights"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "8c1359f4-905b-44d5-81e1-9e22336d8cbd",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Run the previous cell. You should see a table displayed with the scored data. Scroll all the way to the side. There you will find the prediction column containing the flight delay prediction provided by your machine learning model."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "e470b3c2-48af-4c47-bddd-c3afcb6b8af6",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "In the following cell, you will create a table that summarizes the flight delays data. Instead of containing one row per flight, this new summary table will contain one row per origin airport at a given hour, along with a count of the quantity of anticipated delays. We also join the **airport_code_location_lookup_clean** table you created at the beginning of the lab, so we can extract the airport coordinates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "0bf46e08-3f87-4ef1-bd92-2d067090042f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "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>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "arguments": {},
       "data": "",
       "errorSummary": "",
       "errorTraceType": null,
       "metadata": {},
       "type": "ipynbError"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "SELECT  OriginAirportCode, Month, DayofMonth, CRSDepHour, Sum(prediction) NumDelays,\n",
    "    CONCAT(Latitude, ',', Longitude) OriginLatLong\n",
    "    FROM scoredflights s\n",
    "    INNER JOIN airport_code_location_lookup_clean a\n",
    "    ON s.OriginAirportCode = a.Airport\n",
    "    WHERE Month = 4\n",
    "    GROUP BY OriginAirportCode, OriginLatLong, Month, DayofMonth, CRSDepHour\n",
    "    Having Sum(prediction) > 1\n",
    "    ORDER BY NumDelays DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "69a257f3-089d-4a0e-90b0-3431b1f58d61",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "The final step is to save this summary calculation as a table, which we can later query using Power BI (in the next exercise)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "ab3e45fb-256d-4d3a-9b1c-ddd777fc5439",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "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>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "arguments": {},
       "data": "",
       "errorSummary": "",
       "errorTraceType": null,
       "metadata": {},
       "type": "ipynbError"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "summary = spark.sql(\"SELECT  OriginAirportCode, Month, DayofMonth, CRSDepHour, Sum(prediction) NumDelays,     CONCAT(Latitude, ',', Longitude) OriginLatLong FROM scoredflights s INNER JOIN airport_code_location_lookup_clean a ON s.OriginAirportCode = a.Airport WHERE Month = 4 GROUP BY OriginAirportCode, OriginLatLong, Month, DayofMonth, CRSDepHour  Having Sum(prediction) > 1 ORDER BY NumDelays DESC\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "f9274e8c-e22b-4417-82c9-9d4fa6f61546",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "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>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "arguments": {},
       "data": "",
       "errorSummary": "",
       "errorTraceType": null,
       "metadata": {},
       "type": "ipynbError"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "summary.write.mode(\"overwrite\").saveAsTable(\"flight_delays_summary\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "a6ba4a2d-cb40-4a4e-b06d-4aadfe9984f1",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Execute the following to verify the table has data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "b0836350-efea-4f25-a358-74389c1360a6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "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>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "arguments": {},
       "data": "",
       "errorSummary": "",
       "errorTraceType": null,
       "metadata": {},
       "type": "ipynbError"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "select * from flight_delays_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "2ec6da77-fdcf-44fa-9989-b95b12730097",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## Next steps\n",
    "\n",
    "You are done executing notebooks in this lab. Please continue to the next exercise, **Exercise 7: Visualizing in Power BI Desktop** in the hands-on lab document."
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "01 Explore Data",
   "notebookOrigID": 1036921577453807,
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}