Unadjusted Emission - Data Analysis with SparkSQL and SparkR.ipynb

{"cells":[{"cell_type":"markdown","source":["# **Data Analysis**\n","**With Spark SQL & R Programming Language and Power BI report**"],"metadata":{"nteract":{"transient":{"deleting":false}}},"id":"a1c88386-d237-4882-9802-c8eea0406ef4"},{"cell_type":"markdown","source":["This Project aim to Explore and Develop a BI Solution for *2022 Report* Using Microsoft Fabric - Azure Data Engineering."],"metadata":{"nteract":{"transient":{"deleting":false}}},"id":"5fb2e5db-2e8b-4860-94be-8e2a5612f7d5"},{"cell_type":"markdown","source":["##### Reading Data in Lake house with SQL Query"],"metadata":{"nteract":{"transient":{"deleting":false}}},"id":"969b6e9c-627e-49df-b2c8-fee3212cf0ca"},{"cell_type":"code","source":["SELECT COUNT(*) AS TotalRecords  \n","FROM Hackathon_LH.dbo_UnadjustedEmissions;\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"ae82ef6c-98e1-4d94-be44-0bbf7e6924c1","statement_id":2,"state":"finished","livy_statement_state":"available","queued_time":"2024-03-03T22:37:26.5521283Z","session_start_time":"2024-03-03T22:37:26.7819585Z","execution_start_time":"2024-03-03T22:37:32.5535858Z","execution_finish_time":"2024-03-03T22:37:49.0376386Z","parent_msg_id":"87ff14bd-ad53-4760-ab88-60fde36b0243"},"text/plain":"StatementMeta(, ae82ef6c-98e1-4d94-be44-0bbf7e6924c1, 2, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":1,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"TotalRecords","type":"long","nullable":false,"metadata":{}}]},"data":[["0"]]},"text/plain":"<Spark SQL result set with 1 rows and 1 fields>"},"metadata":{}}],"execution_count":1,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"58079172-53d8-445b-b459-bb405da47005"},{"cell_type":"markdown","source":["##### Let get familiar with the dataset and limiting query results to 5row for faster responses."],"metadata":{"nteract":{"transient":{"deleting":false}}},"id":"e0b995d5-9651-4a7f-a3d6-a18281a5ed5d"},{"cell_type":"code","source":["SELECT  * \n","FROM Hackathon_LH.dbo_UnadjustedEmissions \n","Where UnadjustedNOxEmissions > 0\n","LIMIT 5\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"fe781bfa-1b97-4495-9ed2-5f73db71e60e","statement_id":24,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-24T15:05:01.9127784Z","session_start_time":null,"execution_start_time":"2024-02-24T15:05:02.2379362Z","execution_finish_time":"2024-02-24T15:05:04.5065839Z","parent_msg_id":"60c590fe-232b-4856-b8a9-3b6053d28821"},"text/plain":"StatementMeta(, fe781bfa-1b97-4495-9ed2-5f73db71e60e, 24, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":22,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"YEAR","type":"long","nullable":true,"metadata":{}},{"name":"FacilityCode","type":"long","nullable":true,"metadata":{}},{"name":"PlantName","type":"string","nullable":true,"metadata":{}},{"name":"StateABB","type":"string","nullable":true,"metadata":{}},{"name":"UnitOperation","type":"string","nullable":true,"metadata":{}},{"name":"FuelType","type":"string","nullable":true,"metadata":{}},{"name":"UnadjustedHeat","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedOzoneFrmHeat","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedNOxEmissions","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedOzoneNOxEmissions","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedSO2","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedCO2","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedHg","type":"double","nullable":true,"metadata":{}},{"name":"GeneratorID","type":"string","nullable":true,"metadata":{}},{"name":"GenFuelType","type":"string","nullable":true,"metadata":{}},{"name":"GenMoverType","type":"string","nullable":true,"metadata":{}},{"name":"GenMWH","type":"double","nullable":true,"metadata":{}},{"name":"GenOzoneMWH","type":"double","nullable":true,"metadata":{}},{"name":"GenYearOnline","type":"long","nullable":true,"metadata":{}},{"name":"GenRetirementYear","type":"long","nullable":true,"metadata":{}},{"name":"UtilityName","type":"string","nullable":true,"metadata":{}},{"name":"EmissionOutput","type":"double","nullable":true,"metadata":{}},{"name":"EmissionIutput","type":"double","nullable":true,"metadata":{}}]},"data":[["2022","1001","Cayuga","IN","OP","BIT",27138745.304,12281336.164,2271.059,292.922,1119.473,2784435.768,17.42,"1","BIT","ST",1768140,679645,"1970",null,"Duke Energy Indiana, LLC",1.922,0.515],["2022","1001","Cayuga","IN","OP","BIT",27138745.304,12281336.164,2271.059,292.922,1119.473,2784435.768,17.42,"1","BIT","ST",1768140,679645,"1970",null,"Duke Energy Indiana, LLC",1.922,0.515],["2022","1001","Cayuga","IN","OP","BIT",27138745.304,12281336.164,2271.059,292.922,1119.473,2784435.768,17.42,"1","BIT","ST",1768140,679645,"1970",null,"Duke Energy Indiana, LLC",1.922,0.515],["2022","1001","Cayuga","IN","OP","BIT",27138745.304,12281336.164,2271.059,292.922,1119.473,2784435.768,17.42,"1","BIT","ST",1768140,679645,"1970",null,"Duke Energy Indiana, LLC",1.922,0.515],["2022","1001","Cayuga","IN","OP","BIT",27138745.304,12281336.164,2271.059,292.922,1119.473,2784435.768,17.42,"1","BIT","ST",1768140,679645,"1970",null,"Duke Energy Indiana, LLC",1.922,0.515]]},"text/plain":"<Spark SQL result set with 5 rows and 23 fields>"},"metadata":{}}],"execution_count":22,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"30ecd248-7150-4276-91e3-333161a1300a"},{"cell_type":"code","source":["SELECT  * FROM Hackathon_LH.dbo_UnadjustedEmissions LIMIT 5"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"c561cafb-184d-4bae-8a49-40e6b08b4895","statement_id":12,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T14:20:48.1941202Z","session_start_time":null,"execution_start_time":"2024-02-21T14:20:51.017355Z","execution_finish_time":"2024-02-21T14:20:52.3976299Z","parent_msg_id":"c0d2cc10-c6cd-49fa-adc8-20264a02c769"},"text/plain":"StatementMeta(, c561cafb-184d-4bae-8a49-40e6b08b4895, 12, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":11,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"YEAR","type":"long","nullable":true,"metadata":{}},{"name":"FacilityCode","type":"long","nullable":true,"metadata":{}},{"name":"PlantName","type":"string","nullable":true,"metadata":{}},{"name":"StateABB","type":"string","nullable":true,"metadata":{}},{"name":"UnitOperation","type":"string","nullable":true,"metadata":{}},{"name":"FuelType","type":"string","nullable":true,"metadata":{}},{"name":"UnadjustedHeat","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedOzoneFrmHeat","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedNOxEmissions","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedOzoneNOxEmissions","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedSO2","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedCO2","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedHg","type":"double","nullable":true,"metadata":{}},{"name":"GeneratorID","type":"string","nullable":true,"metadata":{}},{"name":"GenFuelType","type":"string","nullable":true,"metadata":{}},{"name":"GenMoverType","type":"string","nullable":true,"metadata":{}},{"name":"GenMWH","type":"double","nullable":true,"metadata":{}},{"name":"GenOzoneMWH","type":"double","nullable":true,"metadata":{}},{"name":"GenYearOnline","type":"long","nullable":true,"metadata":{}},{"name":"GenRetirementYear","type":"long","nullable":true,"metadata":{}},{"name":"UtilityName","type":"string","nullable":true,"metadata":{}},{"name":"EmissionOutput","type":"double","nullable":true,"metadata":{}},{"name":"EmissionIutput","type":"double","nullable":true,"metadata":{}}]},"data":[["2022","6469","Antelope Valley","ND","OP","LIG",32574164.134,14162695.75,2111.87,943.606,5673.448,3546345.476,97.83,"1","LIG","ST",3085383,1351681,"1984",null,"Basin Electric Power Coop",1.471,1.494],["2022","6469","Antelope Valley","ND","OP","LIG",32574164.134,14162695.75,2111.87,943.606,5673.448,3546345.476,97.83,"1","LIG","ST",3085383,1351681,"1984",null,"Basin Electric Power Coop",1.471,1.494],["2022","6469","Antelope Valley","ND","OP","LIG",33448936.111,14544987.969,2224.815,999.222,5948.708,3641583.477,101.5,"1","LIG","ST",3085383,1351681,"1984",null,"Basin Electric Power Coop",1.471,1.494],["2022","6469","Antelope Valley","ND","OP","LIG",33448936.111,14544987.969,2224.815,999.222,5948.708,3641583.477,101.5,"1","LIG","ST",3085383,1351681,"1984",null,"Basin Electric Power Coop",1.471,1.494],["2022","6469","Antelope Valley","ND","OP","LIG",32574164.134,14162695.75,2111.87,943.606,5673.448,3546345.476,97.83,"1","LIG","ST",3085383,1351681,"1984",null,"Basin Electric Power Coop",1.471,1.494]]},"text/plain":"<Spark SQL result set with 5 rows and 23 fields>"},"metadata":{}}],"execution_count":11,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"814308bd-4cdc-468c-b765-b8bc69a9d5b9"},{"cell_type":"markdown","source":["## **List of power plants with lower emission output than the average input**\n","##### This means that the power plant's are efficiently in converting fuel into electricity while minimizing emissions."],"metadata":{"nteract":{"transient":{"deleting":false}}},"id":"ebb3056b-52bc-4014-ae1f-937e864bf4a9"},{"cell_type":"code","source":["SELECT DISTINCT FacilityCode, PlantName, EmissionOutput,EmissionIutput, UtilityName,StateABB\n","FROM Hackathon_LH.dbo_UnadjustedEmissions\n","WHERE EmissionOutput < (SELECT AVG(EmissionIutput) FROM Hackathon_LH.dbo_UnadjustedEmissions)\n","ORDER BY EmissionOutput DESC\n","LIMIT 5\n"," "],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"c561cafb-184d-4bae-8a49-40e6b08b4895","statement_id":19,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T14:30:03.8948661Z","session_start_time":null,"execution_start_time":"2024-02-21T14:30:04.2839365Z","execution_finish_time":"2024-02-21T14:30:06.6003408Z","parent_msg_id":"e93ba75f-11c3-45dd-8857-7ff78b6fcd9c"},"text/plain":"StatementMeta(, c561cafb-184d-4bae-8a49-40e6b08b4895, 19, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":18,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"FacilityCode","type":"long","nullable":true,"metadata":{}},{"name":"PlantName","type":"string","nullable":true,"metadata":{}},{"name":"EmissionOutput","type":"double","nullable":true,"metadata":{}},{"name":"EmissionIutput","type":"double","nullable":true,"metadata":{}},{"name":"UtilityName","type":"string","nullable":true,"metadata":{}},{"name":"StateABB","type":"string","nullable":true,"metadata":{}}]},"data":[["64592","3 MW LLC",25.564,25.569,"3 MW LLC","CO"],["66438","Perkasie PA BTM",25.53,25.724,"American Mun Power-Ohio, Inc","PA"],["66392","Jax LNG",25.443,25.444,"Jax LNG","FL"],["960","North Ninth Street",25.418,26.777,"Rochelle Municipal Utilities","IL"],["59809","Mountain Home Energy Center",25.386,25.528,"Energy Systems Group LLC","TN"]]},"text/plain":"<Spark SQL result set with 5 rows and 6 fields>"},"metadata":{}}],"execution_count":18,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"0ea37ecc-5942-48a8-85ba-fa23e8a8aa38"},{"cell_type":"markdown","source":["## **List of Power plants with higher emission output than average input rate lb/MWh.**\n","##### When a NOx Emission output rate is greater than the input rate, It means the power plant process more emit pollutants than the energy it produces."],"metadata":{"nteract":{"transient":{"deleting":false}}},"id":"7d896b82-de08-4171-bac7-ee381d2da1d4"},{"cell_type":"code","source":["--This query returns the top 5 power plants that emit more NOx pollution than their average electricity input.\n","SELECT DISTINCT FacilityCode, StateABB,   EmissionOutput, PlantName, UtilityName\n","FROM Hackathon_LH.dbo_UnadjustedEmissions\n","WHERE EmissionOutput > (SELECT AVG(EmissionIutput) FROM Hackathon_LH.dbo_UnadjustedEmissions)\n","    AND YEAR = 2022\n"," ORDER BY EmissionOutput DESC\n","LIMIT 5;\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"c561cafb-184d-4bae-8a49-40e6b08b4895","statement_id":38,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T14:36:07.7687981Z","session_start_time":null,"execution_start_time":"2024-02-21T14:36:08.1530826Z","execution_finish_time":"2024-02-21T14:36:11.57531Z","parent_msg_id":"f9650e54-9412-4cf1-9c27-2eaefa339773"},"text/plain":"StatementMeta(, c561cafb-184d-4bae-8a49-40e6b08b4895, 38, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":37,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"FacilityCode","type":"long","nullable":true,"metadata":{}},{"name":"StateABB","type":"string","nullable":true,"metadata":{}},{"name":"EmissionOutput","type":"double","nullable":true,"metadata":{}},{"name":"PlantName","type":"string","nullable":true,"metadata":{}},{"name":"UtilityName","type":"string","nullable":true,"metadata":{}}]},"data":[["957","IL",12018.767,"Princeton (IL)","City of Princeton - (IL)"],["1168","IA",9332.475,"New Hampton","City of New Hampton - (IA)"],["2528","NY",7066,"Harris Lake","New York State Elec & Gas Corp"],["7719","MS",2145,"Meridian","Tennessee Valley Authority"],["56422","OH",969.388,"ST-1/1A Engine No 1","City of Columbus - (OH)"]]},"text/plain":"<Spark SQL result set with 5 rows and 5 fields>"},"metadata":{}}],"execution_count":37,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"ec18de87-9b89-440f-8ddf-5ae774748a89"},{"cell_type":"code","source":["--This query returns the top 5 power plants that emit more NOx pollution than their average electricity input.\n","--**This query is similar to the query above. ( I am adding an aggregation function to the select statement with Group By )**\n","SELECT FacilityCode, StateABB, MAX(EmissionOutput) AS MaxEmissionOutput, PlantName, UtilityName\n","FROM Hackathon_LH.dbo_UnadjustedEmissions\n","WHERE EmissionOutput > (SELECT AVG(EmissionIutput) FROM Hackathon_LH.dbo_UnadjustedEmissions)\n","    AND YEAR = 2022\n","GROUP BY FacilityCode, StateABB, PlantName, UtilityName\n","ORDER BY MaxEmissionOutput DESC\n","LIMIT 5;\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"c561cafb-184d-4bae-8a49-40e6b08b4895","statement_id":43,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T14:36:52.5704524Z","session_start_time":null,"execution_start_time":"2024-02-21T14:36:52.9836377Z","execution_finish_time":"2024-02-21T14:36:56.4289871Z","parent_msg_id":"ba64e06d-64db-40c2-a749-ac548dd6019d"},"text/plain":"StatementMeta(, c561cafb-184d-4bae-8a49-40e6b08b4895, 43, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":42,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"FacilityCode","type":"long","nullable":true,"metadata":{}},{"name":"StateABB","type":"string","nullable":true,"metadata":{}},{"name":"MaxEmissionOutput","type":"double","nullable":true,"metadata":{}},{"name":"PlantName","type":"string","nullable":true,"metadata":{}},{"name":"UtilityName","type":"string","nullable":true,"metadata":{}}]},"data":[["957","IL",12018.767,"Princeton (IL)","City of Princeton - (IL)"],["1168","IA",9332.475,"New Hampton","City of New Hampton - (IA)"],["2528","NY",7066,"Harris Lake","New York State Elec & Gas Corp"],["7719","MS",2145,"Meridian","Tennessee Valley Authority"],["56422","OH",969.388,"ST-1/1A Engine No 1","City of Columbus - (OH)"]]},"text/plain":"<Spark SQL result set with 5 rows and 5 fields>"},"metadata":{}}],"execution_count":42,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"8a514a58-80d4-4e53-95ee-f7efca2ad26d"},{"cell_type":"code","source":["--This query returns the top power plants that emit more NOx pollution than their average electricity input in 2022  in Tennessee, Arkansas, and Mississippi.\n","WITH MaxEmissionPerState AS (\n","    SELECT \n","        FacilityCode, \n","        StateABB, \n","        EmissionOutput,\n","        MAX(EmissionOutput) OVER (PARTITION BY StateABB) AS MaxEmissionInState,\n","        PlantName, \n","        UtilityName,\n","        ROW_NUMBER() OVER (PARTITION BY StateABB ORDER BY EmissionOutput DESC) AS RowNum\n","    FROM \n","        Hackathon_LH.dbo_UnadjustedEmissions\n","    WHERE \n","        EmissionOutput > (SELECT AVG(EmissionIutput) FROM Hackathon_LH.dbo_UnadjustedEmissions)\n","             AND YEAR = 2022\n","   AND StateABB IN ('TN', 'AR', 'MS')\n",")\n","SELECT \n","    FacilityCode, \n","    StateABB, \n","    EmissionOutput AS MaxEmissionOutput, \n","    PlantName, \n","    UtilityName\n","FROM \n","    MaxEmissionPerState\n","WHERE \n","    RowNum = 1\n","ORDER BY \n","    MaxEmissionInState DESC, StateABB, EmissionOutput DESC;\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"c561cafb-184d-4bae-8a49-40e6b08b4895","statement_id":44,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T14:43:01.9638114Z","session_start_time":null,"execution_start_time":"2024-02-21T14:43:02.3800227Z","execution_finish_time":"2024-02-21T14:43:07.185179Z","parent_msg_id":"28fdeb30-3fd0-4467-8395-c3675396cd43"},"text/plain":"StatementMeta(, c561cafb-184d-4bae-8a49-40e6b08b4895, 44, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":43,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"FacilityCode","type":"long","nullable":true,"metadata":{}},{"name":"StateABB","type":"string","nullable":true,"metadata":{}},{"name":"MaxEmissionOutput","type":"double","nullable":true,"metadata":{}},{"name":"PlantName","type":"string","nullable":true,"metadata":{}},{"name":"UtilityName","type":"string","nullable":true,"metadata":{}}]},"data":[["7719","MS",2145,"Meridian","Tennessee Valley Authority"],["55928","TN",31.837,"McMinnville","McMinnville Electric System"],["8015","AR",27.691,"Paragould Reciprocating","Paragould Light & Water Comm"]]},"text/plain":"<Spark SQL result set with 3 rows and 5 fields>"},"metadata":{}}],"execution_count":43,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"e5ae2e40-588f-4120-8547-bf3c291ed204"},{"cell_type":"code","source":["-- This query returns a list of power plants with different type emissions and number Generator's. \n","SELECT PlantName,\n","    MAX(UnadjustedSO2) AS UnadjustedSO2_Max,\n","    MAX(UnadjustedCO2) AS UnadjustedCO2_Max,\n","    MAX(UnadjustedHeat) AS UnadjustedHeat_Max,\n","    MAX(UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_Max,\n","    COUNT(DISTINCT GeneratorID) AS NumberOfGenerator,\n","     COUNT(DISTINCT GenFuelType) AS GeneratorFuelType,\n","    GenRetirementYear\n","FROM Hackathon_LH.dbo_UnadjustedEmissions\n","WHERE EmissionOutput > (SELECT AVG(EmissionIutput) FROM Hackathon_LH.dbo_UnadjustedEmissions)\n","-- Adding NULL for a conditional statement to include the list all of generators with no expiration date ## When LIMIT is more that 5rows\n","AND (GenRetirementYear > 2024 OR GenRetirementYear is null) \n","GROUP BY PlantName, GenRetirementYear\n","ORDER BY GenRetirementYear DESC\n","LIMIT 5"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"c561cafb-184d-4bae-8a49-40e6b08b4895","statement_id":61,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T14:52:59.9135558Z","session_start_time":null,"execution_start_time":"2024-02-21T14:53:00.2612402Z","execution_finish_time":"2024-02-21T14:53:02.9653004Z","parent_msg_id":"8c3ca507-e41a-4cdf-ade4-179517475517"},"text/plain":"StatementMeta(, c561cafb-184d-4bae-8a49-40e6b08b4895, 61, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":60,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"PlantName","type":"string","nullable":true,"metadata":{}},{"name":"UnadjustedSO2_Max","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedCO2_Max","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedHeat_Max","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedNOxEmissions_Max","type":"double","nullable":true,"metadata":{}},{"name":"NumberOfGenerator","type":"long","nullable":false,"metadata":{}},{"name":"GeneratorFuelType","type":"long","nullable":false,"metadata":{}},{"name":"GenRetirementYear","type":"long","nullable":true,"metadata":{}}]},"data":[["Cherry Street",0.3938,137.31,2349.41,4.343,"2","1","2030"],["Works 4",0.0726,40.865,500.408,0.799,"1","1","2027"],["Oberlin (KS)",0.0174,9.823,120.29,0.195,"5","1","2026"],["Papillion Creek Wastewater",0.0724,1510.279,23784,49.116,"3","1","2025"],["Weleetka",0.0107,4878.161,83466.516,49.241,"2","1","2025"]]},"text/plain":"<Spark SQL result set with 5 rows and 8 fields>"},"metadata":{}}],"execution_count":60,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"afec8251-67ff-4fbb-9a80-ddc1bb69020c"},{"cell_type":"markdown","source":["## List of power plants that their emission output is not equal to the input as require by law.\n","##### Read more online at https://www.epa.gov/renewable-fuel-standard-program/overview-renewable-fuel-standard"],"metadata":{"nteract":{"transient":{"deleting":false}}},"id":"083d9366-e3c5-4a2c-af78-38c6f81ad405"},{"cell_type":"code","source":["--This query returns the list of power plants that generate more NOx emissions above 100 lb/MWh.\n","-- And theur emissions output is above 50% compare to their electricity convertion rate is less the 50%.\n","-- Along with the 50%, 60%, 80%, and 100% percentile's that can be used as a KPI tracking mechanism for engaging in activies that will help reduce the pollution rate.\n","\n","WITH AllData AS (\n","    SELECT\n","    PlantName,\n","    StateABB,\n","      MAX(UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_Max,\n","    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_50th,\n","    PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_60th,\n","    PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_80th,\n","    PERCENTILE_CONT(1.0) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_100th\n","FROM\n","    Hackathon_LH.dbo_UnadjustedEmissions\n","WHERE \n","    EmissionOutput > (SELECT AVG(EmissionOutput) FROM Hackathon_LH.dbo_UnadjustedEmissions)\n"," GROUP BY\n","    PlantName , StateABB\n","ORDER BY\n","    UnadjustedNOxEmissions_50th DESC)\n","\n","   --- SELECT * FROM AllData WHERE UnadjustedNOxEmissions_50th != UnadjustedNOxEmissions_60th\n","     SELECT PlantName, UnadjustedNOxEmissions_50th, UnadjustedNOxEmissions_60th, UnadjustedNOxEmissions_80th, UnadjustedNOxEmissions_100th, StateABB\n","     FROM AllData\n","     WHERE UnadjustedNOxEmissions_100th > UnadjustedNOxEmissions_60th\n","     AND UnadjustedNOxEmissions_50th > 100\n","       "],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"6f2622ce-b6e3-440d-937c-aee85fe73517","statement_id":37,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T18:23:41.5962435Z","session_start_time":null,"execution_start_time":"2024-02-21T18:23:41.9688637Z","execution_finish_time":"2024-02-21T18:23:45.3692349Z","parent_msg_id":"02a592a2-f055-4a4e-a555-6c3c32ef0a68"},"text/plain":"StatementMeta(, 6f2622ce-b6e3-440d-937c-aee85fe73517, 37, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":36,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"PlantName","type":"string","nullable":true,"metadata":{}},{"name":"UnadjustedNOxEmissions_50th","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedNOxEmissions_60th","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedNOxEmissions_80th","type":"double","nullable":true,"metadata":{}},{"name":"UnadjustedNOxEmissions_100th","type":"double","nullable":true,"metadata":{}},{"name":"StateABB","type":"string","nullable":true,"metadata":{}}]},"data":[["New Hampton",1052.885,1377.7749999999987,1865.11,1865.11,"IA"],["Princeton (IL)",283.837,320.226,509.451,640.452,"IL"],["Dutch Harbor",124.63,124.63,147.29,147.29,"AK"],["Unisea G 2",108.316,108.316,116.981,116.981,"AK"]]},"text/plain":"<Spark SQL result set with 4 rows and 6 fields>"},"metadata":{}}],"execution_count":36,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"collapsed":false},"id":"824ea2a1-3d32-4f81-968e-675f46f90ca8"},{"cell_type":"markdown","source":["# Switching Section to R Programming Language "],"metadata":{"nteract":{"transient":{"deleting":false}}},"id":"c08617a7-ea07-477a-9bcf-b971eb8ea231"},{"cell_type":"code","source":["from pyspark.sql import SparkSession\n","\n","# Create a Spark session\n","spark = SparkSession.builder.appName(\"MyApp\").getOrCreate()\n","\n","# Read data from the specified path into a DataFrame (as Delta Parquet)\n","result_df = spark.read.format(\"delta\").load(\"abfss://c03564d3-6b73-4fbb-a507-4a1f6446db7c@onelake.dfs.fabric.microsoft.com/c18dcf4f-975e-42ff-8f98-b7bac72a6e92/Tables/dbo_UnadjustedEmissions\")\n","\n","# Show the first few rows of the DataFrame\n","result_df.show(10)\n","\n"],"outputs":[],"execution_count":null,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"b1ba4fc3-a741-4af7-a068-06db4281c1e2"},{"cell_type":"code","source":["# Load the SparkR package\n","library(SparkR)\n","spark <- sparkR.session(appName = \"MySparkApp\")\n","\n","\n","\n","# Read data from the specified path into a SparkDataFrame (as Delta Parquet)\n","df <- read.df(\"abfss://c03564d3-6b73-4fbb-a507-4a1f6446db7c@onelake.dfs.fabric.microsoft.com/c18dcf4f-975e-42ff-8f98-b7bac72a6e92/Tables/dbo_UnadjustedEmissions\", source = \"delta\")\n","head(df)  "],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"fe781bfa-1b97-4495-9ed2-5f73db71e60e","statement_id":140,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-24T16:05:19.4682728Z","session_start_time":null,"execution_start_time":"2024-02-24T16:05:19.7881925Z","execution_finish_time":"2024-02-24T16:05:22.0941578Z","parent_msg_id":"e1686fa8-0611-46da-bd30-1130b6fdcf7f"},"text/plain":"StatementMeta(, fe781bfa-1b97-4495-9ed2-5f73db71e60e, 140, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":[" [1] YEAR                        FacilityCode               \n [3] PlantName                   StateABB                   \n [5] UnitOperation               FuelType                   \n [7] UnadjustedHeat              UnadjustedOzoneFrmHeat     \n [9] UnadjustedNOxEmissions      UnadjustedOzoneNOxEmissions\n[11] UnadjustedSO2               UnadjustedCO2              \n[13] UnadjustedHg                GeneratorID                \n[15] GenFuelType                 GenMoverType               \n[17] GenMWH                      GenOzoneMWH                \n[19] GenYearOnline               GenRetirementYear          \n[21] UtilityName                 EmissionOutput             \n[23] EmissionIutput             \n<0 rows> (or 0-length row.names)\nWarning message:\nIn sparkR.session(appName = \"MySparkApp\") :\n  Version mismatch between Spark JVM and SparkR package. JVM version was 3.4.1.5.3-117503204, while R package version was 3.4.1"]}],"execution_count":138,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"0a9a18cb-f845-4ae4-976f-120e406a91c0"},{"cell_type":"code","source":["head(df, n = 5)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"fe781bfa-1b97-4495-9ed2-5f73db71e60e","statement_id":141,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-24T16:05:27.1053273Z","session_start_time":null,"execution_start_time":"2024-02-24T16:05:27.4409971Z","execution_finish_time":"2024-02-24T16:05:29.7706742Z","parent_msg_id":"52060c45-5368-489e-94c5-068a060763a2"},"text/plain":"StatementMeta(, fe781bfa-1b97-4495-9ed2-5f73db71e60e, 141, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["  YEAR FacilityCode PlantName StateABB UnitOperation FuelType UnadjustedHeat\n1 2022         3407  Kingston       TN            OP      SUB        3475443\n2 2022         3407  Kingston       TN            OP      SUB        3475443\n3 2022         3407  Kingston       TN            OP      SUB        3475443\n4 2022         3407  Kingston       TN            OP      SUB        3475443\n5 2022         3407  Kingston       TN            OP      SUB        3475443\n  UnadjustedOzoneFrmHeat UnadjustedNOxEmissions UnadjustedOzoneNOxEmissions\n1                1595013                142.473                      59.465\n2                1595013                142.473                      59.465\n3                1595013                142.473                      59.465\n4                1595013                142.473                      59.465\n5                1595013                142.473                      59.465\n  UnadjustedSO2 UnadjustedCO2 UnadjustedHg GeneratorID GenFuelType GenMoverType\n1       196.611      360542.7         1.94           2         SUB           ST\n2       196.611      360542.7         1.94           2         SUB           ST\n3       196.611      360542.7         1.94           2         SUB           ST\n4       196.611      360542.7         1.94           2         SUB           ST\n5       196.611      360542.7         1.94           2         SUB           ST\n  GenMWH GenOzoneMWH GenYearOnline GenRetirementYear                UtilityName\n1 247924      127809          1954              2027 Tennessee Valley Authority\n2 247924      127809          1954              2027 Tennessee Valley Authority\n3 247924      127809          1954              2027 Tennessee Valley Authority\n4 247924      127809          1954              2027 Tennessee Valley Authority\n5 247924      127809          1954              2027 Tennessee Valley Authority\n  EmissionOutput EmissionIutput\n1          1.049          0.968\n2          1.049          0.968\n3          1.049          0.968\n4          1.049          0.968\n5          1.049          0.968"]}],"execution_count":139,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"a77162f5-2f08-4be4-9d9a-d80afb37a566"},{"cell_type":"code","source":["# Assuming 'df' is your SparkDataFrame\n","selected_df <- df[, c(\"GeneratorID\", \"PlantName\", \"UnadjustedNOxEmissions\")]\n","showDF(selected_df)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"fe781bfa-1b97-4495-9ed2-5f73db71e60e","statement_id":168,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-24T16:35:34.782104Z","session_start_time":null,"execution_start_time":"2024-02-24T16:35:35.1179005Z","execution_finish_time":"2024-02-24T16:35:37.4598438Z","parent_msg_id":"3df10b3f-ea51-42e6-aa4a-db731231b516"},"text/plain":"StatementMeta(, fe781bfa-1b97-4495-9ed2-5f73db71e60e, 168, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+-----------+---------+----------------------+\n|GeneratorID|PlantName|UnadjustedNOxEmissions|\n+-----------+---------+----------------------+\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2271.059|\n|          2|   Cayuga|              2001.128|\n|          2|   Cayuga|              2001.128|\n|          2|   Cayuga|              2001.128|\n|          2|   Cayuga|              2001.128|\n+-----------+---------+----------------------+\nonly showing top 20 rows"]}],"execution_count":166,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"f965c825-243e-441b-9b0f-76f1d6b882c9"},{"cell_type":"code","source":["selected_df <- select(df, \"GeneratorID\", \"PlantName\", \"UnadjustedNOxEmissions\", \"UnadjustedOzoneNOxEmissions\")\n","filtered_df <- filter(selected_df, selected_df$UnadjustedNOxEmissions > 0)\n","summary_df <- summarize(filtered_df, avg_NOx = avg(filtered_df$UnadjustedNOxEmissions))\n","\n","showDF(summary_df)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"fe781bfa-1b97-4495-9ed2-5f73db71e60e","statement_id":33,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-24T15:07:03.47182Z","session_start_time":null,"execution_start_time":"2024-02-24T15:07:03.7972787Z","execution_finish_time":"2024-02-24T15:07:05.2781555Z","parent_msg_id":"54da927a-18e9-4fe2-94e1-888f0784280e"},"text/plain":"StatementMeta(, fe781bfa-1b97-4495-9ed2-5f73db71e60e, 33, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+-----------------+\n|          avg_NOx|\n+-----------------+\n|67.06073237124117|\n+-----------------+"]}],"execution_count":31,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"943e160b-3125-449f-b514-5e22ff343cda"},{"cell_type":"code","source":["selected_df <- select(df, \"GeneratorID\", \"PlantName\", \"UnadjustedNOxEmissions\", \"UnadjustedOzoneNOxEmissions\")\n","filtered_df <- filter(selected_df, selected_df$UnadjustedNOxEmissions > 0)\n","summary_df <- summarize(filtered_df, Numof_Gen = count(filtered_df$GeneratorID))\n","\n","showDF(summary_df)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"fe781bfa-1b97-4495-9ed2-5f73db71e60e","statement_id":39,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-24T15:19:42.1396766Z","session_start_time":null,"execution_start_time":"2024-02-24T15:19:42.4624016Z","execution_finish_time":"2024-02-24T15:19:43.8691525Z","parent_msg_id":"80c8699d-f476-4b84-899e-ce1482e9afd5"},"text/plain":"StatementMeta(, fe781bfa-1b97-4495-9ed2-5f73db71e60e, 39, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+\n|Numof_Gen|\n+---------+\n|  1384752|\n+---------+"]}],"execution_count":37,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"4e16e804-d0e0-43ba-86c6-b0ee58427cf3"},{"cell_type":"code","source":["# Read data with R from the specified path into a DataFrame (as Delta Parquet)\n","df <- read.df(\"abfss://c03564d3-6b73-4fbb-a507-4a1f6446db7c@onelake.dfs.fabric.microsoft.com/c18dcf4f-975e-42ff-8f98-b7bac72a6e92/Tables/dbo_UnadjustedEmissions\", source = \"delta\")\n","\n","# Register the DataFrame as a temporary view\n","createOrReplaceTempView(df, \"AllDataResult\")\n","\n","# Execute your SQL query\n","result_df <- sql(\"WITH AllData AS (\n","    SELECT\n","        PlantName,\n","        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_50th,\n","        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_60th,\n","        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_80th,\n","        PERCENTILE_CONT(1.0) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_100th\n","    FROM\n","        AllDataResult\n","    WHERE\n","        EmissionOutput > (SELECT AVG(EmissionOutput) FROM AllDataResult)\n","    GROUP BY\n","        PlantName\n","    ORDER BY\n","        UnadjustedNOxEmissions_50th DESC\n",")\n","SELECT * FROM AllData WHERE UnadjustedNOxEmissions_100th > UnadjustedNOxEmissions_80th\")\n","\n","# Show the result\n","result_df_local <- collect(result_df)\n","showDF(result_df)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"37f1191e-0895-4296-bdc0-6896b5bbbb7a","statement_id":9,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-24T11:07:27.6777882Z","session_start_time":null,"execution_start_time":"2024-02-24T11:07:27.9896597Z","execution_finish_time":"2024-02-24T11:07:37.9297137Z","parent_msg_id":"abd6fc11-335a-466a-b87f-3d5eae3a2760"},"text/plain":"StatementMeta(, 37f1191e-0895-4296-bdc0-6896b5bbbb7a, 9, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+--------------------+---------------------------+---------------------------+---------------------------+----------------------------+\n|           PlantName|UnadjustedNOxEmissions_50th|UnadjustedNOxEmissions_60th|UnadjustedNOxEmissions_80th|UnadjustedNOxEmissions_100th|\n+--------------------+---------------------------+---------------------------+---------------------------+----------------------------+\n|      Princeton (IL)|                    283.837|                    320.226|                    509.451|                     640.452|\n|          Heber City|                      41.52|                      41.52|                     50.262|                      54.632|\n|         Caterpillar|         34.726499999999994|                     68.201|                     85.251|                     102.301|\n|Northeast Generat...|                     32.087|                      35.19|                     40.781|                      41.526|\n|                 Tok|                    31.8735|                     35.061|                     35.061|                      41.436|\n|              Valdez|                       28.2|          30.31519999999988|          35.95560000000056|                      45.826|\n|                Orca|                     27.715|         28.176999999999975|          31.41040000000058|                      41.572|\n|Galena Electric U...|                     17.144|                     17.144|                     17.144|                       21.43|\n|Kingman Municipal...|          9.809999999999999|                      10.01|                     14.014|                      25.225|\n|              Easton|                      8.123|          9.047999999999982|          10.99780000000017|                      13.997|\n|              Dafter|                      8.068|                      8.068|          8.874600000000184|                      12.101|\n|          Kanoelehua|                      6.865|                      6.865|         11.018200000000945|                      27.631|\n|               Devon|                       6.71|                      9.544|                     10.373|                      11.524|\n|              Haines|                      3.792|                      3.914|                      4.648|                        6.85|\n|                Kake|                      3.784|                      3.784|          4.692000000000206|                       8.324|\n|   VMEA 1 Credit Gen|                      3.116|                      3.116|                      3.116|                       3.895|\n|            Carthage|                      3.104|                      3.449|                      4.138|                       4.828|\n|          Story City|                      2.449|                      2.449|                      3.795|                       4.285|\n|         Clay Center|                      2.272|                      2.272|                       3.31|                       4.413|\n|       Cherry Street|                      1.742|         2.0207999999999844|         2.8198000000000865|                       4.343|\n+--------------------+---------------------------+---------------------------+---------------------------+----------------------------+\nonly showing top 20 rows"]}],"execution_count":7,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"9a175936-b163-458c-9500-5cca74bb3d97"},{"cell_type":"code","source":[" \n","# Load the necessary library for plotting\n","library(ggplot2)\n","\n","\n","\n","# Create a bar chart\n","ggplot(result_df_local , aes(x = PlantName, y = UnadjustedNOxEmissions_50th)) +\n","  geom_bar(stat = \"identity\", fill = \"skyblue\") +\n","  labs(title = \"50th Percentile NOx Emissions by Plant\",\n","       x = \"Plant Name\",\n","       y = \"NOx Emissions\")"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"37f1191e-0895-4296-bdc0-6896b5bbbb7a","statement_id":20,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-24T11:15:28.5244407Z","session_start_time":null,"execution_start_time":"2024-02-24T11:15:28.8363173Z","execution_finish_time":"2024-02-24T11:15:29.056291Z","parent_msg_id":"ed6469f0-436a-4846-88ba-aa473e9dde47"},"text/plain":"StatementMeta(, 37f1191e-0895-4296-bdc0-6896b5bbbb7a, 20, Finished, Available)"},"metadata":{}}],"execution_count":18,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"119de714-c10a-4770-9a14-ea015e7abdc0"},{"cell_type":"code","source":["# Load the necessary libraries\n","library(ggplot2)\n","library(SparkR)\n","\n","\n","# Read data with R from the specified path into a DataFrame (as Delta Parquet)\n","df <- read.df(\"abfss://c03564d3-6b73-4fbb-a507-4a1f6446db7c@onelake.dfs.fabric.microsoft.com/c18dcf4f-975e-42ff-8f98-b7bac72a6e92/Tables/dbo_UnadjustedEmissions\", source = \"delta\")\n","\n","# Register the DataFrame as a temporary view\n","createOrReplaceTempView(df, \"AllDataResult\")\n","\n","# Execute your SQL query\n","result_df <- sql(\"WITH AllData AS (\n","    SELECT\n","        PlantName,\n","        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_50th,\n","        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_60th,\n","        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_80th,\n","        PERCENTILE_CONT(1.0) WITHIN GROUP (ORDER BY UnadjustedNOxEmissions) AS UnadjustedNOxEmissions_100th\n","    FROM\n","        AllDataResult\n","    WHERE\n","        EmissionOutput > (SELECT AVG(EmissionOutput) FROM AllDataResult)\n","    GROUP BY\n","        PlantName\n","    ORDER BY\n","        UnadjustedNOxEmissions_50th DESC\n",")\n","SELECT * FROM AllData WHERE UnadjustedNOxEmissions_100th > UnadjustedNOxEmissions_80th\")\n"," \n","result_df_local <- collect(result_df)\n","\n","# Create a scatter plot\n","ggplot(result_df_local, aes(x = UnadjustedNOxEmissions_50th, y = UnadjustedNOxEmissions_60th)) +\n","  geom_point(color = \"blue\", size = 3) +\n","  labs(title = \"Scatter Plot: 50th vs. 60th Percentile NOx Emissions\",\n","       x = \"50th Percentile NOx Emissions\",\n","       y = \"60th Percentile NOx Emissions\")\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"75b8f037-267d-4c48-842e-4727da7f5321","statement_id":39,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T08:35:48.5485039Z","session_start_time":null,"execution_start_time":"2024-02-21T08:35:48.8601225Z","execution_finish_time":"2024-02-21T08:35:52.2693215Z","parent_msg_id":"6fde3ebc-3cca-44b7-8f5a-cf851695bec7"},"text/plain":"StatementMeta(, 75b8f037-267d-4c48-842e-4727da7f5321, 39, Finished, Available)"},"metadata":{}}],"execution_count":38,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"49372806-61d0-439d-9e5c-4752fb31c909"},{"cell_type":"code","source":["\n","# Create a bar chart\n","p <- ggplot(result_df_local, aes(x = PlantName, y = UnadjustedNOxEmissions_50th)) +\n","  geom_bar(stat = \"identity\", fill = \"skyblue\") +\n","  labs(title = \"50th Percentile NOx Emissions by Plant\",\n","       x = \"Plant Name\",\n","       y = \"NOx Emissions\")\n","\n","# Print the plot\n","print(p)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"75b8f037-267d-4c48-842e-4727da7f5321","statement_id":42,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T08:36:10.8277545Z","session_start_time":null,"execution_start_time":"2024-02-21T08:36:11.1503308Z","execution_finish_time":"2024-02-21T08:36:11.9206529Z","parent_msg_id":"1a2cf601-b83c-4baa-943f-2f66bc033944"},"text/plain":"StatementMeta(, 75b8f037-267d-4c48-842e-4727da7f5321, 42, Finished, Available)"},"metadata":{}}],"execution_count":41,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"877b86a9-949b-4944-97d2-29303d3ecec7"},{"cell_type":"code","source":["# Load the necessary libraries\n","library(ggplot2)\n","library(SparkR)\n"," \n","result_df_local <- collect(result_df)\n","\n","barplot(result_df_local$UnadjustedNOxEmissions_50th, names.arg = result_df_local$PlantName, col = \"skyblue\",\n","        main = \"Bar Chart: 50th Percentile NOx Emissions by Plant\",\n","        xlab = \"Plant Name\", ylab = \"NOx Emissions\")"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"75b8f037-267d-4c48-842e-4727da7f5321","statement_id":74,"state":"finished","livy_statement_state":"available","queued_time":"2024-02-21T08:45:22.7121941Z","session_start_time":null,"execution_start_time":"2024-02-21T08:45:23.0214381Z","execution_finish_time":"2024-02-21T08:45:23.243263Z","parent_msg_id":"19f99519-a8e9-4db6-8e28-a2248989207c"},"text/plain":"StatementMeta(, 75b8f037-267d-4c48-842e-4727da7f5321, 74, Finished, Available)"},"metadata":{}}],"execution_count":73,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}}},"id":"cdde6c2b-ed50-4fc3-91db-e9debd4fa407"}],"metadata":{"language_info":{"name":"sql"},"kernel_info":{"name":"synapse_pyspark"},"microsoft":{"language":"r"},"widgets":{},"kernelspec":{"name":"synapse_pyspark","language":"Python","display_name":"Synapse PySpark"},"nteract":{"version":"nteract-front-end@1.0.0"},"synapse_widget":{"version":"0.1","state":{}},"spark_compute":{"compute_id":"/trident/default"},"trident":{"lakehouse":{"default_lakehouse":"c18dcf4f-975e-42ff-8f98-b7bac72a6e92","default_lakehouse_name":"Hackathon_LH","default_lakehouse_workspace_id":"c03564d3-6b73-4fbb-a507-4a1f6446db7c"}}},"nbformat":4,"nbformat_minor":5}