chapter_03_4_IoT_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IoT Data\n",
    "\n",
    "Chapter 3 also contains on page 71 ff an end-to-end example with IoT data. Unfortunately, except the code snippets in the book, there is no comprehensive code available in the GitHub repo of the book. \n",
    "\n",
    "The data file `iot-devices.json` can be found in the repo in this directory:\n",
    "https://github.com/databricks/LearningSparkV2/tree/master/databricks-datasets/learning-spark-v2/iot-devices\n",
    "\n",
    "*Christoph Windheuser - April 13, 2022*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import required python spark libraries\n",
    "import findspark\n",
    "import pyspark\n",
    "\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql import Row\n",
    "\n",
    "# Connect Jupyter Notebook with the Spark application and create Spark Context\n",
    "findspark.init()\n",
    "sc = pyspark.SparkContext(appName=\"LearningSpark\")\n",
    "\n",
    "#create a SparkSession\n",
    "spark = (SparkSession\n",
    "       .builder\n",
    "       .appName(\"iot_Example_chapt_03\")\n",
    "       .getOrCreate())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking the data in the `data` directory:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"device_id\": 1, \"device_name\": \"meter-gauge-1xbYRYcj\", \"ip\": \"68.161.225.1\", \"cca2\": \"US\", \"cca3\": \"USA\", \"cn\": \"United States\", \"latitude\": 38.000000, \"longitude\": -97.000000, \"scale\": \"Celsius\", \"temp\": 34, \"humidity\": 51, \"battery_level\": 8, \"c02_level\": 868, \"lcd\": \"green\", \"timestamp\" :1458444054093 }\r\n",
      "{\"device_id\": 2, \"device_name\": \"sensor-pad-2n2Pea\", \"ip\": \"213.161.254.1\", \"cca2\": \"NO\", \"cca3\": \"NOR\", \"cn\": \"Norway\", \"latitude\": 62.470000, \"longitude\": 6.150000, \"scale\": \"Celsius\", \"temp\": 11, \"humidity\": 70, \"battery_level\": 7, \"c02_level\": 1473, \"lcd\": \"red\", \"timestamp\" :1458444054119 }\r\n",
      "{\"device_id\": 3, \"device_name\": \"device-mac-36TWSKiT\", \"ip\": \"88.36.5.1\", \"cca2\": \"IT\", \"cca3\": \"ITA\", \"cn\": \"Italy\", \"latitude\": 42.830000, \"longitude\": 12.830000, \"scale\": \"Celsius\", \"temp\": 19, \"humidity\": 44, \"battery_level\": 2, \"c02_level\": 1556, \"lcd\": \"red\", \"timestamp\" :1458444054120 }\r\n",
      "{\"device_id\": 4, \"device_name\": \"sensor-pad-4mzWkz\", \"ip\": \"66.39.173.154\", \"cca2\": \"US\", \"cca3\": \"USA\", \"cn\": \"United States\", \"latitude\": 44.060000, \"longitude\": -121.320000, \"scale\": \"Celsius\", \"temp\": 28, \"humidity\": 32, \"battery_level\": 6, \"c02_level\": 1080, \"lcd\": \"yellow\", \"timestamp\" :1458444054121 }\r\n",
      "{\"device_id\": 5, \"device_name\": \"therm-stick-5gimpUrBB\", \"ip\": \"203.82.41.9\", \"cca2\": \"PH\", \"cca3\": \"PHL\", \"cn\": \"Philippines\", \"latitude\": 14.580000, \"longitude\": 120.970000, \"scale\": \"Celsius\", \"temp\": 25, \"humidity\": 62, \"battery_level\": 4, \"c02_level\": 931, \"lcd\": \"green\", \"timestamp\" :1458444054122 }\r\n",
      "{\"device_id\": 6, \"device_name\": \"sensor-pad-6al7RTAobR\", \"ip\": \"204.116.105.67\", \"cca2\": \"US\", \"cca3\": \"USA\", \"cn\": \"United States\", \"latitude\": 35.930000, \"longitude\": -85.460000, \"scale\": \"Celsius\", \"temp\": 27, \"humidity\": 51, \"battery_level\": 3, \"c02_level\": 1210, \"lcd\": \"yellow\", \"timestamp\" :1458444054122 }\r\n",
      "{\"device_id\": 7, \"device_name\": \"meter-gauge-7GeDoanM\", \"ip\": \"220.173.179.1\", \"cca2\": \"CN\", \"cca3\": \"CHN\", \"cn\": \"China\", \"latitude\": 22.820000, \"longitude\": 108.320000, \"scale\": \"Celsius\", \"temp\": 18, \"humidity\": 26, \"battery_level\": 3, \"c02_level\": 1129, \"lcd\": \"yellow\", \"timestamp\" :1458444054123 }\r\n",
      "{\"device_id\": 8, \"device_name\": \"sensor-pad-8xUD6pzsQI\", \"ip\": \"210.173.177.1\", \"cca2\": \"JP\", \"cca3\": \"JPN\", \"cn\": \"Japan\", \"latitude\": 35.690000, \"longitude\": 139.690000, \"scale\": \"Celsius\", \"temp\": 27, \"humidity\": 35, \"battery_level\": 0, \"c02_level\": 1536, \"lcd\": \"red\", \"timestamp\" :1458444054123 }\r\n",
      "{\"device_id\": 9, \"device_name\": \"device-mac-9GcjZ2pw\", \"ip\": \"118.23.68.227\", \"cca2\": \"JP\", \"cca3\": \"JPN\", \"cn\": \"Japan\", \"latitude\": 35.690000, \"longitude\": 139.690000, \"scale\": \"Celsius\", \"temp\": 13, \"humidity\": 85, \"battery_level\": 3, \"c02_level\": 807, \"lcd\": \"green\", \"timestamp\" :1458444054124 }\r\n",
      "{\"device_id\": 10, \"device_name\": \"sensor-pad-10BsywSYUF\", \"ip\": \"208.109.163.218\", \"cca2\": \"US\", \"cca3\": \"USA\", \"cn\": \"United States\", \"latitude\": 33.610000, \"longitude\": -111.890000, \"scale\": \"Celsius\", \"temp\": 26, \"humidity\": 56, \"battery_level\": 7, \"c02_level\": 1470, \"lcd\": \"red\", \"timestamp\" :1458444054125 }\r\n"
     ]
    }
   ],
   "source": [
    "!head ./data/iot_devices.json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reading the json-file and infer the schema from the file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "jsonFile = \"data/iot_devices.json\"\n",
    "iot_data_df =  spark.read.json(jsonFile)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show the first 5 lines of the DataFrame and the number of lines:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+---------+----+----+-------------+---------+--------------------+--------+-------------+--------+------+---------+-------+----+-------------+\n",
      "|battery_level|c02_level|cca2|cca3|           cn|device_id|         device_name|humidity|           ip|latitude|   lcd|longitude|  scale|temp|    timestamp|\n",
      "+-------------+---------+----+----+-------------+---------+--------------------+--------+-------------+--------+------+---------+-------+----+-------------+\n",
      "|            8|      868|  US| USA|United States|        1|meter-gauge-1xbYRYcj|      51| 68.161.225.1|    38.0| green|    -97.0|Celsius|  34|1458444054093|\n",
      "|            7|     1473|  NO| NOR|       Norway|        2|   sensor-pad-2n2Pea|      70|213.161.254.1|   62.47|   red|     6.15|Celsius|  11|1458444054119|\n",
      "|            2|     1556|  IT| ITA|        Italy|        3| device-mac-36TWSKiT|      44|    88.36.5.1|   42.83|   red|    12.83|Celsius|  19|1458444054120|\n",
      "|            6|     1080|  US| USA|United States|        4|   sensor-pad-4mzWkz|      32|66.39.173.154|   44.06|yellow|  -121.32|Celsius|  28|1458444054121|\n",
      "|            4|      931|  PH| PHL|  Philippines|        5|therm-stick-5gimp...|      62|  203.82.41.9|   14.58| green|   120.97|Celsius|  25|1458444054122|\n",
      "+-------------+---------+----+----+-------------+---------+--------------------+--------+-------------+--------+------+---------+-------+----+-------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_data_df.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "198164"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iot_data_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show sensors with a temperature < 30 and a humidity > 70:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+---------+----+----+-------------+---------+--------------------+--------+--------------+--------+------+---------+-------+----+-------------+\n",
      "|battery_level|c02_level|cca2|cca3|           cn|device_id|         device_name|humidity|            ip|latitude|   lcd|longitude|  scale|temp|    timestamp|\n",
      "+-------------+---------+----+----+-------------+---------+--------------------+--------+--------------+--------+------+---------+-------+----+-------------+\n",
      "|            3|      807|  JP| JPN|        Japan|        9| device-mac-9GcjZ2pw|      85| 118.23.68.227|   35.69| green|   139.69|Celsius|  13|1458444054124|\n",
      "|            3|     1544|  IT| ITA|        Italy|       11|meter-gauge-11dlM...|      85| 88.213.191.34|   42.83|   red|    12.83|Celsius|  16|1458444054125|\n",
      "|            0|     1260|  US| USA|United States|       12|sensor-pad-12Y2kIm0o|      92|   68.28.91.22|    38.0|yellow|    -97.0|Celsius|  12|1458444054126|\n",
      "|            6|     1007|  IN| IND|        India|       13|meter-gauge-13Gro...|      92|59.144.114.250|    28.6|yellow|     77.2|Celsius|  13|1458444054127|\n",
      "|            1|     1346|  NO| NOR|       Norway|       14|sensor-pad-14QL93...|      90|193.156.90.200|   59.95|yellow|    10.75|Celsius|  16|1458444054127|\n",
      "+-------------+---------+----+----+-------------+---------+--------------------+--------+--------------+--------+------+---------+-------+----+-------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "61347"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iot_results_df = (iot_data_df\n",
    "                  .where((col(\"temp\") < 30) & (col(\"humidity\") > 70)))\n",
    "iot_results_df.show(5)\n",
    "iot_results_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show me the device name, device id and country of all devices with a temperature > 25:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+--------------------+---------+----+\n",
      "|temp|         device_name|device_id|cca3|\n",
      "+----+--------------------+---------+----+\n",
      "|  34|meter-gauge-1xbYRYcj|        1| USA|\n",
      "|  28|   sensor-pad-4mzWkz|        4| USA|\n",
      "|  27|sensor-pad-6al7RT...|        6| USA|\n",
      "|  27|sensor-pad-8xUD6p...|        8| JPN|\n",
      "|  26|sensor-pad-10Bsyw...|       10| USA|\n",
      "+----+--------------------+---------+----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "71451"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iot_results_df = (iot_data_df\n",
    "                  .select(\"temp\", \"device_name\", \"device_id\", \"cca3\")\n",
    "                  .where(col(\"temp\") > 25))\n",
    "iot_results_df.show(5)\n",
    "iot_results_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show me the max and min of: Temperature, Battery Level, C02 Level and Humidity:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+---------+\n",
      "|min(temp)|max(temp)|\n",
      "+---------+---------+\n",
      "|       10|       34|\n",
      "+---------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_data_df.select(min(\"temp\"), max(\"temp\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+------------------+\n",
      "|min(battery_level)|max(battery_level)|\n",
      "+------------------+------------------+\n",
      "|                 0|                 9|\n",
      "+------------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_data_df.select(min(\"battery_level\"), max(\"battery_level\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+--------------+\n",
      "|min(c02_level)|max(c02_level)|\n",
      "+--------------+--------------+\n",
      "|           800|          1599|\n",
      "+--------------+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_data_df.select(min(\"c02_level\"), max(\"c02_level\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+-------------+\n",
      "|min(humidity)|max(humidity)|\n",
      "+-------------+-------------+\n",
      "|           25|           99|\n",
      "+-------------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_data_df.select(min(\"humidity\"), max(\"humidity\")).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show me all sensors with a battery level below a critical threshold:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------------------+---------+----+\n",
      "|battery_level|         device_name|device_id|cca3|\n",
      "+-------------+--------------------+---------+----+\n",
      "|            0|sensor-pad-8xUD6p...|        8| JPN|\n",
      "|            0|sensor-pad-12Y2kIm0o|       12| USA|\n",
      "|            1|sensor-pad-14QL93...|       14| NOR|\n",
      "|            0|meter-gauge-17zb8...|       17| USA|\n",
      "|            1|sensor-pad-36VQv8...|       36| CYP|\n",
      "+-------------+--------------------+---------+----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "39727"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "low_battery_threshold = 2\n",
    "\n",
    "iot_results_df = (iot_data_df\n",
    "                  .select(\"battery_level\", \"device_name\", \"device_id\", \"cca3\")\n",
    "                  .where(col(\"battery_level\") < low_battery_threshold))\n",
    "iot_results_df.show(5)\n",
    "iot_results_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show me the top 20 countries with the highest C02 level:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+--------------+\n",
      "|cca3|max(c02_level)|\n",
      "+----+--------------+\n",
      "| AUS|          1599|\n",
      "| HUN|          1599|\n",
      "| POL|          1599|\n",
      "| THA|          1599|\n",
      "| BRA|          1599|\n",
      "| NOR|          1599|\n",
      "| BOL|          1599|\n",
      "| FIN|          1599|\n",
      "| UKR|          1599|\n",
      "| PER|          1599|\n",
      "| GBR|          1599|\n",
      "| NLD|          1599|\n",
      "| BMU|          1599|\n",
      "| TUR|          1599|\n",
      "| LVA|          1599|\n",
      "| USA|          1599|\n",
      "| ITA|          1599|\n",
      "| VNM|          1599|\n",
      "| ARE|          1599|\n",
      "| KOR|          1599|\n",
      "+----+--------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_max_df = (iot_data_df\n",
    "                  .select(\"c02_level\", \"cca3\")\n",
    "                  .groupBy(\"cca3\")\n",
    "                  .agg(max(\"c02_level\"))\n",
    "                  .sort(\"max(c02_level)\", ascending=False))\n",
    "\n",
    "iot_max_df.show(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------------+------------------+------------------+\n",
      "|cca3|         avg(temp)|    avg(c02_level)|     avg(humidity)|\n",
      "+----+------------------+------------------+------------------+\n",
      "| HTI|25.333333333333332|1291.3333333333333| 64.58333333333333|\n",
      "| POL|21.983965014577258|1193.7452623906706| 62.33163265306123|\n",
      "| LVA|21.899441340782122|1189.1340782122904| 63.11173184357542|\n",
      "| BRB|23.210526315789473|1257.5526315789473| 58.36842105263158|\n",
      "| BRA|21.958126550868485|1208.7382133995038| 61.96867245657568|\n",
      "| ARM| 21.58823529411765|1207.9117647058824| 63.23529411764706|\n",
      "| MOZ| 19.59090909090909|            1264.0| 58.77272727272727|\n",
      "| JOR|21.065217391304348|1222.3478260869565| 63.84782608695652|\n",
      "| CUB|25.866666666666667|1222.5333333333333| 49.53333333333333|\n",
      "| FRA|22.115739868049012|1200.7059377945334| 61.82054665409991|\n",
      "| ABW|             20.75|          1190.125|             64.75|\n",
      "| TCA|              17.0|             862.0|              38.0|\n",
      "| BRN|21.894736842105264|1200.2105263157894| 65.42105263157895|\n",
      "| BOL| 20.49074074074074|            1247.0|60.888888888888886|\n",
      "| URY|21.572649572649574|1214.2136752136753|62.376068376068375|\n",
      "| LBY|           22.0625|         1260.5625|           58.8125|\n",
      "| ITA| 21.94065180102916| 1194.461063464837| 61.90257289879931|\n",
      "| UKR|  21.9371546961326|1204.9343922651933| 63.34254143646409|\n",
      "| GHA|21.133333333333333|1235.6666666666667|60.766666666666666|\n",
      "| CMR|22.137931034482758|1187.5862068965516| 65.06896551724138|\n",
      "+----+------------------+------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_avg_df = (iot_data_df\n",
    "                  .select(\"temp\", \"c02_level\", \"humidity\", \"cca3\")\n",
    "                  .groupBy(\"cca3\")\n",
    "                  .agg(avg(\"temp\"), avg(\"c02_level\"), avg(\"humidity\")))\n",
    "\n",
    "iot_avg_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------------+\n",
      "|cca3|         avg(temp)|\n",
      "+----+------------------+\n",
      "| AIA|31.142857142857142|\n",
      "| GRL|              29.5|\n",
      "| GAB|              28.0|\n",
      "| VUT|              27.3|\n",
      "| LCA|              27.0|\n",
      "| TKM|26.666666666666668|\n",
      "| MWI|26.666666666666668|\n",
      "| IRQ|26.428571428571427|\n",
      "| LAO|26.285714285714285|\n",
      "| IOT|              26.0|\n",
      "| CUB|25.866666666666667|\n",
      "| HTI|25.333333333333332|\n",
      "| FJI| 25.09090909090909|\n",
      "| DMA| 24.73076923076923|\n",
      "| BEN|24.666666666666668|\n",
      "| SYR|              24.6|\n",
      "| BWA|              24.5|\n",
      "| TLS|24.333333333333332|\n",
      "| MNP|24.333333333333332|\n",
      "| BHS| 24.27777777777778|\n",
      "+----+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_avg_df = (iot_data_df\n",
    "                  .select(\"temp\", \"cca3\")\n",
    "                  .groupBy(\"cca3\")\n",
    "                  .agg(avg(\"temp\"))\n",
    "                  .sort(\"avg(temp)\", ascending=False))\n",
    "\n",
    "iot_avg_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------------+\n",
      "|cca3|    avg(c02_level)|\n",
      "+----+------------------+\n",
      "| GAB|            1523.0|\n",
      "| FLK|            1424.0|\n",
      "| MCO|            1421.5|\n",
      "| SMR|1379.6666666666667|\n",
      "| LBR|            1374.5|\n",
      "| SYR|            1345.8|\n",
      "| MRT|1344.4285714285713|\n",
      "| COD|          1333.375|\n",
      "| TON|            1323.0|\n",
      "| TLS|            1310.0|\n",
      "| GIN|            1308.0|\n",
      "| BWA|1302.6666666666667|\n",
      "| HTI|1291.3333333333333|\n",
      "| LAO|            1291.0|\n",
      "| MDV|1284.7272727272727|\n",
      "| AND|            1279.0|\n",
      "| LSO|            1274.6|\n",
      "| MOZ|            1264.0|\n",
      "| FSM|            1261.0|\n",
      "| LBY|         1260.5625|\n",
      "+----+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_avg_df = (iot_data_df\n",
    "                  .select(\"c02_level\", \"cca3\")\n",
    "                  .groupBy(\"cca3\")\n",
    "                  .agg(avg(\"c02_level\"))\n",
    "                  .sort(\"avg(c02_level)\", ascending=False))\n",
    "\n",
    "iot_avg_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----------------+\n",
      "|cca3|    avg(humidity)|\n",
      "+----+-----------------+\n",
      "| COK|85.66666666666667|\n",
      "| WSM|             81.0|\n",
      "| GGY|           79.625|\n",
      "| MHL|             75.0|\n",
      "| AND|             75.0|\n",
      "| GRD|73.83333333333333|\n",
      "| BWA|            73.75|\n",
      "| LBR|             72.0|\n",
      "| JEY|70.79411764705883|\n",
      "| SWZ|70.54545454545455|\n",
      "| DMA|70.46153846153847|\n",
      "| MAC|70.27272727272727|\n",
      "| MDV|69.72727272727273|\n",
      "| TKM|             69.0|\n",
      "| MCO|             69.0|\n",
      "| BHS|68.61111111111111|\n",
      "| ZWE| 68.4074074074074|\n",
      "| FSM|68.33333333333333|\n",
      "| PYF|            68.05|\n",
      "| ALB|         67.21875|\n",
      "+----+-----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "iot_avg_df = (iot_data_df\n",
    "                  .select(\"humidity\", \"cca3\")\n",
    "                  .groupBy(\"cca3\")\n",
    "                  .agg(avg(\"humidity\"))\n",
    "                  .sort(\"avg(humidity)\", ascending=False))\n",
    "\n",
    "iot_avg_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}