<jupyter_start><jupyter_text>Summarize data using Azure Databricks Select the scored data generated by the Azure Data Factory pipeline<jupyter_code>%sql
select * from scoredflights<jupyter_output><empty_output><jupyter_text>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. 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.<jupyter_code>%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<jupyter_output><empty_output><jupyter_text>The final step is to save this summary calculation as a table, which we can later query using Power BI (in the next exercise).<jupyter_code>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")
summary.write.mode("overwrite").saveAsTable("flight_delays_summary")<jupyter_output><empty_output><jupyter_text>Execute the following to verify the table has data<jupyter_code>%sql
select * from flight_delays_summary<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0587406015037594, 'fraction_numerical': 0.0018796992481203006, 'mean_word_length': 6.096666666666667, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '365097', 'license': 'MIT', 'n_tokens_mistral': 615, 'n_tokens_neox': 596, 'n_words': 234, 'path': 'Hands-on lab/lab-files/BigDataVis/Exercise 6/01 Explore Data.ipynb', 'repo_name': 'josuneha/AzureBigData-analytics', 'size': '2128'}