PySpark4 grupowanie.ipynb

{"cells":[{"cell_type":"markdown","source":["## PySpark\n","### grupowanie"],"metadata":{"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"bd047781-7ef0-4d14-b1bd-c97e7c358502"},{"cell_type":"code","source":["# utworzenie dataframe\n","df = spark.sql(\"SELECT ProductID, Name, ProductNumber, MakeFlag, Color, Size, ListPrice FROM AdventureWorks.Production_Product\")"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"34f0be2e-6b1c-465a-b077-bb18b86c3004","statement_id":7,"statement_ids":[7],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:57:51.4199355Z","session_start_time":null,"execution_start_time":"2024-05-14T09:57:51.9004607Z","execution_finish_time":"2024-05-14T09:57:52.9130225Z","parent_msg_id":"874d05e0-bd9b-4d11-b74e-f4ed313ee152"},"text/plain":"StatementMeta(, 34f0be2e-6b1c-465a-b077-bb18b86c3004, 7, Finished, Available)"},"metadata":{}}],"execution_count":5,"metadata":{"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"90aa25e9-c101-435c-bfcb-675c1d1e326f"},{"cell_type":"code","source":["# zliczanie wierszy\n","df.count()"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"34f0be2e-6b1c-465a-b077-bb18b86c3004","statement_id":16,"statement_ids":[16],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T10:08:25.0955032Z","session_start_time":null,"execution_start_time":"2024-05-14T10:08:25.5912673Z","execution_finish_time":"2024-05-14T10:08:26.4544592Z","parent_msg_id":"cc48d6f8-348d-430d-adbe-ff4425500963"},"text/plain":"StatementMeta(, 34f0be2e-6b1c-465a-b077-bb18b86c3004, 16, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":47,"data":{"text/plain":"504"},"metadata":{}}],"execution_count":14,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"9942893c-9eb0-42bb-bafd-a52382293e4a"},{"cell_type":"code","source":["df.groupBy(\"Color\").count().show()"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"34f0be2e-6b1c-465a-b077-bb18b86c3004","statement_id":4,"statement_ids":[4],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:57:06.4974309Z","session_start_time":null,"execution_start_time":"2024-05-14T09:57:06.9881837Z","execution_finish_time":"2024-05-14T09:57:12.0020831Z","parent_msg_id":"21db9eab-214f-48b4-877c-72902a988317"},"text/plain":"StatementMeta(, 34f0be2e-6b1c-465a-b077-bb18b86c3004, 4, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+------------+-----+\n|       Color|count|\n+------------+-----+\n|        null|  248|\n|       Multi|    8|\n|      Silver|   43|\n|        Blue|   26|\n|       White|    4|\n|       Black|   93|\n|      Yellow|   36|\n|         Red|   38|\n|        Grey|    1|\n|Silver/Black|    7|\n+------------+-----+\n\n"]}],"execution_count":2,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"edc1c9a6-cd87-43c1-b973-f3c1b2499bd9"},{"cell_type":"code","source":["df.groupBy(\"Color\").sum(\"ListPrice\").show()\n","df.groupBy(\"Color\").min(\"ListPrice\").show()\n","df.groupBy(\"Color\").max(\"ListPrice\").show()"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"34f0be2e-6b1c-465a-b077-bb18b86c3004","statement_id":5,"statement_ids":[5],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:57:18.4180515Z","session_start_time":null,"execution_start_time":"2024-05-14T09:57:18.905094Z","execution_finish_time":"2024-05-14T09:57:22.7405488Z","parent_msg_id":"a04a2498-5818-40f5-b923-df0c2cfb8fde"},"text/plain":"StatementMeta(, 34f0be2e-6b1c-465a-b077-bb18b86c3004, 5, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+------------+--------------+\n|       Color|sum(ListPrice)|\n+------------+--------------+\n|        null|     4182.3200|\n|       Multi|      478.9200|\n|      Silver|    36563.1300|\n|        Blue|    24015.6600|\n|       White|       36.9800|\n|       Black|    67436.2600|\n|      Yellow|    34527.2900|\n|         Red|    53274.1000|\n|        Grey|      125.0000|\n|Silver/Black|      448.1300|\n+------------+--------------+\n\n+------------+--------------+\n|       Color|min(ListPrice)|\n+------------+--------------+\n|        null|        0.0000|\n|       Multi|        8.9900|\n|      Silver|        0.0000|\n|        Blue|       34.9900|\n|       White|        8.9900|\n|       Black|        0.0000|\n|      Yellow|       53.9900|\n|         Red|       34.9900|\n|        Grey|      125.0000|\n|Silver/Black|       40.4900|\n+------------+--------------+\n\n+------------+--------------+\n|       Color|max(ListPrice)|\n+------------+--------------+\n|        null|      229.4900|\n|       Multi|       89.9900|\n|      Silver|     3399.9900|\n|        Blue|     2384.0700|\n|       White|        9.5000|\n|       Black|     3374.9900|\n|      Yellow|     2384.0700|\n|         Red|     3578.2700|\n|        Grey|      125.0000|\n|Silver/Black|       80.9900|\n+------------+--------------+\n\n"]}],"execution_count":3,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"58af4090-ebf7-408a-808c-73b8fb100b86"},{"cell_type":"code","source":["df.groupBy(\"Color\", \"Size\").sum(\"ListPrice\").show()"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"34f0be2e-6b1c-465a-b077-bb18b86c3004","statement_id":8,"statement_ids":[8],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:57:58.0435484Z","session_start_time":null,"execution_start_time":"2024-05-14T09:57:58.5236094Z","execution_finish_time":"2024-05-14T09:57:59.4356008Z","parent_msg_id":"b589dd85-952c-45b0-9d6d-61715373065e"},"text/plain":"StatementMeta(, 34f0be2e-6b1c-465a-b077-bb18b86c3004, 8, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+------+----+--------------+\n| Color|Size|sum(ListPrice)|\n+------+----+--------------+\n|   Red|  58|     7047.8800|\n|   Red|  44|    10626.1500|\n|  Blue|  44|     1075.7700|\n|Yellow|  48|     3416.3100|\n|Yellow|  54|     4463.7500|\n|Silver|  46|     4818.0700|\n|   Red|  62|     6129.9800|\n| Black|  46|     3644.5900|\n| Multi|  XL|       49.9900|\n| Black|  38|     8448.3300|\n|   Red|  52|    10626.1500|\n|  null|null|     4182.3200|\n|Yellow|  42|     3416.3100|\n| Multi|null|        8.9900|\n| Black|  44|    12478.1700|\n|Silver|  40|     1962.6200|\n|  Blue|  58|     1075.7700|\n|Yellow|  46|     3387.9800|\n|Yellow|   L|       53.9900|\n| Multi|   L|      139.9800|\n+------+----+--------------+\nonly showing top 20 rows\n\n"]}],"execution_count":6,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"24862cf5-17f2-4679-a3f1-3b8ea09f48cb"}],"metadata":{"language_info":{"name":"python"},"kernel_info":{"name":"synapse_pyspark"},"microsoft":{"language":"python","language_group":"synapse_pyspark","ms_spell_check":{"ms_spell_check_language":"en"}},"widgets":{},"kernelspec":{"name":"synapse_pyspark","language":"Python","display_name":"Synapse PySpark"},"nteract":{"version":"nteract-front-end@1.0.0"},"spark_compute":{"compute_id":"/trident/default"},"dependencies":{"lakehouse":{"default_lakehouse":"de6f32c6-ff57-4e70-baac-a89780d36482","default_lakehouse_name":"AdventureWorks","default_lakehouse_workspace_id":"1e8d4c96-f071-4b69-86dc-247ebd5e83d1"}}},"nbformat":4,"nbformat_minor":5}