Data Exploration in Spark.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e7e043b8",
   "metadata": {},
   "source": [
    "# Data Exploration in Spark\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6304c012",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import paskages\n",
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa33e64c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6599c7e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create sparksession\n",
    "spark = SparkSession.builder.appName(\"Dataframe\").getOrCreate()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "32ae062f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----------------+------------------+----------------------+------------------+----------------------+------------------+---------------------+-----------------+---------------------+---------------------+\n",
      "|number| air_pressure_9am|      air_temp_9am|avg_wind_direction_9am|avg_wind_speed_9am|max_wind_direction_9am|max_wind_speed_9am|rain_accumulation_9am|rain_duration_9am|relative_humidity_9am|relative_humidity_3pm|\n",
      "+------+-----------------+------------------+----------------------+------------------+----------------------+------------------+---------------------+-----------------+---------------------+---------------------+\n",
      "|     0|918.0600000000087| 74.82200000000041|                 271.1| 2.080354199999768|    295.39999999999986| 2.863283199999908|                  0.0|              0.0|    42.42000000000046|   36.160000000000494|\n",
      "|     1|917.3476881177097| 71.40384263106537|    101.93517935618371|2.4430092157340217|    140.47154847112498|3.5333236016106238|                  0.0|              0.0|   24.328697291802207|     19.4265967985621|\n",
      "|     2|923.0400000000084|60.637999999998776|    51.000000000000036|17.067852199999727|      63.6999999999999|22.100967200000003|                  0.0|             20.0|    8.900000000000047|   14.460000000000045|\n",
      "|     3|920.5027511759195| 70.13889486830053|    198.83213271746368| 4.337363056162703|    211.20334124325618| 5.190045360219378|                  0.0|              0.0|   12.189101868764444|   12.742547353761848|\n",
      "|     4|921.1600000000036| 44.29400000000284|                 277.8|1.8566601999998955|    136.49999999999991| 2.863283199999954|    8.899999999998613|          14730.0|    92.41000000000044|    76.74000000000046|\n",
      "|     5| 915.300000000006| 78.40400000000422|                 182.8|  9.93201359999979|    188.99999999999986|10.983375399999995|  0.02000000000043656|            170.0|   35.130000000000265|   33.930000000000256|\n",
      "|     6|915.5988675132801| 70.04330431686239|    177.87540718659943|3.7455865377244795|    186.60669587586753| 4.589632428699432|                  0.0|              0.0|   10.657421657822635|   21.385656725200974|\n",
      "|     7|918.0700000000023|51.710000000002346|                 242.4| 2.527742199999939|     271.5999999999999|3.6462121999999924|                  0.0|              0.0|    80.47000000000041|    74.92000000000041|\n",
      "|     8| 920.080000000006| 80.58200000000384|                  40.7| 4.518618799999764|     62.99999999999986| 5.883152199999977|                  0.0|              0.0|    29.58000000000045|   24.030000000000427|\n",
      "|     9|915.0100000000116|  47.4979999999998|    163.10000000000005| 4.943637399999545|    195.89999999999992| 6.576603599999951|                  0.0|              0.0|     88.6000000000012|     68.0500000000012|\n",
      "|    10| 919.650000000002|  77.0360000000015|                  70.6| 3.825167399999994|                  85.5| 4.764682199999966|                  0.0|              0.0|   22.070000000000228|    32.13000000000024|\n",
      "|    11|915.6400000000099| 45.71599999999757|    241.60000000000005| 5.860782799999633|     265.7999999999999| 8.030614600000042|   0.5499999999999545|           1770.0|    90.56000000000022|     79.0900000000002|\n",
      "|    12|917.3900000000112| 49.78399999999978|    204.10000000000005|1.2750557999995473|    211.79999999999993|  2.01324599999997|                  0.0|              0.0|     73.1500000000012|    58.43000000000119|\n",
      "|    13|920.8200000000083| 62.43799999999798|    213.60000000000005|2.6172197999996376|     165.6999999999999| 3.310671200000037|                  0.0|              0.0|    43.64000000000018|   27.990000000000173|\n",
      "|    14|911.0000000000076| 86.43199999999833|    202.90000000000003|1.2079475999998548|     162.8999999999999| 1.677704999999817|                  0.0|              0.0|   15.189999999999962|   24.369999999999948|\n",
      "|    15|922.3831311993919| 70.86526349026626|    36.174174777424966|1.8472780842202248|     58.42863248830409| 2.529142073698898|                  0.0|              0.0|   12.110889335294598|   14.801705962979918|\n",
      "|    16|917.8900000000048|              NULL|                 169.2|2.1922011999998263|     196.7999999999999|2.9303914000000866|                  0.0|              0.0|   48.989999999999846|    51.18999999999985|\n",
      "|    17|916.9152553899988| 77.01896058080769|    234.53934498620535|   2.2747252969339|     229.4741986768941| 2.906513109197139|                  0.0|              0.0|   21.031461768790646|    20.75568332171184|\n",
      "|    18|918.8000000000037| 67.08200000000228|                 176.1| 4.876529199999688|    183.39999999999984| 5.569980599999913|                  0.0|              0.0|    18.90000000000005|    45.87000000000005|\n",
      "|    19|922.0400000000093|  68.5759999999986|     58.30000000000005|  9.55173379999966|      81.8999999999999|12.571602800000015|                  0.0|              0.0|    7.540000000000102|    7.740000000000088|\n",
      "+------+-----------------+------------------+----------------------+------------------+----------------------+------------------+---------------------+-----------------+---------------------+---------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df = spark.read.csv(\"dataset/daily_weather.csv\" , header=True , inferSchema=True)\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5942b6e2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['number',\n",
       " 'air_pressure_9am',\n",
       " 'air_temp_9am',\n",
       " 'avg_wind_direction_9am',\n",
       " 'avg_wind_speed_9am',\n",
       " 'max_wind_direction_9am',\n",
       " 'max_wind_speed_9am',\n",
       " 'rain_accumulation_9am',\n",
       " 'rain_duration_9am',\n",
       " 'relative_humidity_9am',\n",
       " 'relative_humidity_3pm']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#name columns\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "97854b7e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- number: integer (nullable = true)\n",
      " |-- air_pressure_9am: double (nullable = true)\n",
      " |-- air_temp_9am: double (nullable = true)\n",
      " |-- avg_wind_direction_9am: double (nullable = true)\n",
      " |-- avg_wind_speed_9am: double (nullable = true)\n",
      " |-- max_wind_direction_9am: double (nullable = true)\n",
      " |-- max_wind_speed_9am: double (nullable = true)\n",
      " |-- rain_accumulation_9am: double (nullable = true)\n",
      " |-- rain_duration_9am: double (nullable = true)\n",
      " |-- relative_humidity_9am: double (nullable = true)\n",
      " |-- relative_humidity_3pm: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#printSchema\n",
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "29d4b933",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>summary</th>\n",
       "      <td>count</td>\n",
       "      <td>mean</td>\n",
       "      <td>stddev</td>\n",
       "      <td>min</td>\n",
       "      <td>max</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <td>1095</td>\n",
       "      <td>547.0</td>\n",
       "      <td>316.24357700987383</td>\n",
       "      <td>0</td>\n",
       "      <td>1094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>air_pressure_9am</th>\n",
       "      <td>1092</td>\n",
       "      <td>918.8825513138094</td>\n",
       "      <td>3.184161180386833</td>\n",
       "      <td>907.9900000000024</td>\n",
       "      <td>929.3200000000012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>air_temp_9am</th>\n",
       "      <td>1090</td>\n",
       "      <td>64.93300141287072</td>\n",
       "      <td>11.175514003175877</td>\n",
       "      <td>36.752000000000685</td>\n",
       "      <td>98.90599999999992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avg_wind_direction_9am</th>\n",
       "      <td>1091</td>\n",
       "      <td>142.2355107005759</td>\n",
       "      <td>69.13785928889189</td>\n",
       "      <td>15.500000000000046</td>\n",
       "      <td>343.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avg_wind_speed_9am</th>\n",
       "      <td>1092</td>\n",
       "      <td>5.50828424225493</td>\n",
       "      <td>4.5528134655317185</td>\n",
       "      <td>0.69345139999974</td>\n",
       "      <td>23.554978199999763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max_wind_direction_9am</th>\n",
       "      <td>1092</td>\n",
       "      <td>148.95351796516923</td>\n",
       "      <td>67.23801294602953</td>\n",
       "      <td>28.89999999999991</td>\n",
       "      <td>312.19999999999993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max_wind_speed_9am</th>\n",
       "      <td>1091</td>\n",
       "      <td>7.019513529175272</td>\n",
       "      <td>5.598209170780958</td>\n",
       "      <td>1.1855782000000479</td>\n",
       "      <td>29.84077959999996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rain_accumulation_9am</th>\n",
       "      <td>1089</td>\n",
       "      <td>0.20307895225211126</td>\n",
       "      <td>1.5939521253574893</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.01999999999907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rain_duration_9am</th>\n",
       "      <td>1092</td>\n",
       "      <td>294.1080522756142</td>\n",
       "      <td>1598.0787786601481</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17704.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>relative_humidity_9am</th>\n",
       "      <td>1095</td>\n",
       "      <td>34.24140205923536</td>\n",
       "      <td>25.472066802250055</td>\n",
       "      <td>6.090000000001012</td>\n",
       "      <td>92.6200000000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>relative_humidity_3pm</th>\n",
       "      <td>1095</td>\n",
       "      <td>35.34472714825898</td>\n",
       "      <td>22.524079453587273</td>\n",
       "      <td>5.3000000000006855</td>\n",
       "      <td>92.2500000000003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            0                    1                   2  \\\n",
       "summary                 count                 mean              stddev   \n",
       "number                   1095                547.0  316.24357700987383   \n",
       "air_pressure_9am         1092    918.8825513138094   3.184161180386833   \n",
       "air_temp_9am             1090    64.93300141287072  11.175514003175877   \n",
       "avg_wind_direction_9am   1091    142.2355107005759   69.13785928889189   \n",
       "avg_wind_speed_9am       1092     5.50828424225493  4.5528134655317185   \n",
       "max_wind_direction_9am   1092   148.95351796516923   67.23801294602953   \n",
       "max_wind_speed_9am       1091    7.019513529175272   5.598209170780958   \n",
       "rain_accumulation_9am    1089  0.20307895225211126  1.5939521253574893   \n",
       "rain_duration_9am        1092    294.1080522756142  1598.0787786601481   \n",
       "relative_humidity_9am    1095    34.24140205923536  25.472066802250055   \n",
       "relative_humidity_3pm    1095    35.34472714825898  22.524079453587273   \n",
       "\n",
       "                                         3                   4  \n",
       "summary                                min                 max  \n",
       "number                                   0                1094  \n",
       "air_pressure_9am         907.9900000000024   929.3200000000012  \n",
       "air_temp_9am            36.752000000000685   98.90599999999992  \n",
       "avg_wind_direction_9am  15.500000000000046               343.4  \n",
       "avg_wind_speed_9am        0.69345139999974  23.554978199999763  \n",
       "max_wind_direction_9am   28.89999999999991  312.19999999999993  \n",
       "max_wind_speed_9am      1.1855782000000479   29.84077959999996  \n",
       "rain_accumulation_9am                  0.0   24.01999999999907  \n",
       "rain_duration_9am                      0.0             17704.0  \n",
       "relative_humidity_9am    6.090000000001012    92.6200000000002  \n",
       "relative_humidity_3pm   5.3000000000006855    92.2500000000003  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print summary statistics\n",
    "df.describe().toPandas().transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4db64f3f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------------+\n",
      "|summary| air_pressure_9am|\n",
      "+-------+-----------------+\n",
      "|  count|             1092|\n",
      "|   mean|918.8825513138094|\n",
      "| stddev|3.184161180386833|\n",
      "|    min|907.9900000000024|\n",
      "|    max|929.3200000000012|\n",
      "+-------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# summary statistics for just one column\n",
    "df.describe(\"air_pressure_9am\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "8afb4b2b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#count the number of columns\n",
    "len(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f15b97ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1095"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#count the number of rows\n",
    "df.count()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3521ee1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "957276e2",
   "metadata": {},
   "source": [
    "Drop rows with missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "03fb9e20",
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop the rows with missing values in the air_pressure_9am column\n",
    "df2=df.na.drop(subset=['air_pressure_9am'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2351d87a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1092"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total number of rows\n",
    "df2.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2c7c8580",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7298253479609021"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Compute correlation between two columns\n",
    "#s compute the correlation between rain_accumulation_9am and rain_duration_9am\n",
    "df2.stat.corr('rain_accumulation_9am',\"rain_duration_9am\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1507fd49",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca9330fc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61e53ffa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60b5b0f4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3da1c2ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6079d942",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1e2da52",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6fed1aa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b86c6d1b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}