Working with Dates and Timestamps using PySpark.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d74c006e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import dayofmonth, hour, month, year, dayofyear, weekofyear, format_number, date_format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "526e6bce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://DESKTOP-BJS1Q67:4044\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.5.0</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>dates</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x1c9bd6b3ed0>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark = SparkSession.builder.appName('dates').getOrCreate()\n",
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "04217f93",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+\n",
      "|      Date|              Open|              High|               Low|             Close|   Volume|         Adj Close|\n",
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+\n",
      "|2010-01-04|        213.429998|        214.499996|212.38000099999996|        214.009998|123432400|         27.727039|\n",
      "|2010-01-05|        214.599998|        215.589994|        213.249994|        214.379993|150476200|27.774976000000002|\n",
      "|2010-01-06|        214.379993|            215.23|        210.750004|        210.969995|138040000|27.333178000000004|\n",
      "|2010-01-07|            211.75|        212.000006|        209.050005|            210.58|119282800|          27.28265|\n",
      "|2010-01-08|        210.299994|        212.000006|209.06000500000002|211.98000499999998|111902700|         27.464034|\n",
      "|2010-01-11|212.79999700000002|        213.000002|        208.450005|210.11000299999998|115557400|         27.221758|\n",
      "|2010-01-12|209.18999499999998|209.76999500000002|        206.419998|        207.720001|148614900|          26.91211|\n",
      "|2010-01-13|        207.870005|210.92999500000002|        204.099998|        210.650002|151473000|          27.29172|\n",
      "|2010-01-14|210.11000299999998|210.45999700000002|        209.020004|            209.43|108223500|         27.133657|\n",
      "|2010-01-15|210.92999500000002|211.59999700000003|        205.869999|            205.93|148516900|26.680197999999997|\n",
      "|2010-01-19|        208.330002|215.18999900000003|        207.240004|        215.039995|182501900|27.860484999999997|\n",
      "|2010-01-20|        214.910006|        215.549994|        209.500002|            211.73|153038200|         27.431644|\n",
      "|2010-01-21|        212.079994|213.30999599999998|        207.210003|        208.069996|152038600|         26.957455|\n",
      "|2010-01-22|206.78000600000001|        207.499996|            197.16|            197.75|220441900|         25.620401|\n",
      "|2010-01-25|202.51000200000001|        204.699999|        200.190002|        203.070002|266424900|26.309658000000002|\n",
      "|2010-01-26|205.95000100000001|        213.710005|        202.580004|        205.940001|466777500|         26.681494|\n",
      "|2010-01-27|        206.849995|            210.58|        199.530001|        207.880005|430642100|26.932840000000002|\n",
      "|2010-01-28|        204.930004|        205.500004|        198.699995|        199.289995|293375600|25.819922000000002|\n",
      "|2010-01-29|        201.079996|        202.199995|        190.250002|        192.060003|311488100|         24.883208|\n",
      "|2010-02-01|192.36999699999998|             196.0|191.29999899999999|        194.729998|187469100|         25.229131|\n",
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df = spark.read.csv('appl_stock.csv',inferSchema=True,header=True)\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6ef4c69d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Date: date (nullable = true)\n",
      " |-- Open: double (nullable = true)\n",
      " |-- High: double (nullable = true)\n",
      " |-- Low: double (nullable = true)\n",
      " |-- Close: double (nullable = true)\n",
      " |-- Volume: integer (nullable = true)\n",
      " |-- Adj Close: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f1ac88fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(Date=datetime.date(2010, 1, 4), Open=213.429998, High=214.499996, Low=212.38000099999996, Close=214.009998, Volume=123432400, Adj Close=27.727039),\n",
       " Row(Date=datetime.date(2010, 1, 5), Open=214.599998, High=215.589994, Low=213.249994, Close=214.379993, Volume=150476200, Adj Close=27.774976000000002)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fd1c345f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(Date=datetime.date(2010, 1, 4), Open=213.429998, High=214.499996, Low=212.38000099999996, Close=214.009998, Volume=123432400, Adj Close=27.727039)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2dd0a72f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------------------+\n",
      "|      Date|              Open|\n",
      "+----------+------------------+\n",
      "|2010-01-04|        213.429998|\n",
      "|2010-01-05|        214.599998|\n",
      "|2010-01-06|        214.379993|\n",
      "|2010-01-07|            211.75|\n",
      "|2010-01-08|        210.299994|\n",
      "|2010-01-11|212.79999700000002|\n",
      "|2010-01-12|209.18999499999998|\n",
      "|2010-01-13|        207.870005|\n",
      "|2010-01-14|210.11000299999998|\n",
      "|2010-01-15|210.92999500000002|\n",
      "|2010-01-19|        208.330002|\n",
      "|2010-01-20|        214.910006|\n",
      "|2010-01-21|        212.079994|\n",
      "|2010-01-22|206.78000600000001|\n",
      "|2010-01-25|202.51000200000001|\n",
      "|2010-01-26|205.95000100000001|\n",
      "|2010-01-27|        206.849995|\n",
      "|2010-01-28|        204.930004|\n",
      "|2010-01-29|        201.079996|\n",
      "|2010-02-01|192.36999699999998|\n",
      "+----------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(['Date','Open']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "107c4a4f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+\n",
      "|Year|\n",
      "+----+\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "|2010|\n",
      "+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(year('Date').alias('Year')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "55723bc9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+\n",
      "|Day of Month|\n",
      "+------------+\n",
      "|           4|\n",
      "|           5|\n",
      "|           6|\n",
      "|           7|\n",
      "|           8|\n",
      "|          11|\n",
      "|          12|\n",
      "|          13|\n",
      "|          14|\n",
      "|          15|\n",
      "|          19|\n",
      "|          20|\n",
      "|          21|\n",
      "|          22|\n",
      "|          25|\n",
      "|          26|\n",
      "|          27|\n",
      "|          28|\n",
      "|          29|\n",
      "|           1|\n",
      "+------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(dayofmonth('Date').alias('Day of Month')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b27c7cd0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+\n",
      "|Month|\n",
      "+-----+\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    1|\n",
      "|    2|\n",
      "+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(month('Date').alias('Month')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "21656977",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|Day of Year|\n",
      "+-----------+\n",
      "|          4|\n",
      "|          5|\n",
      "|          6|\n",
      "|          7|\n",
      "|          8|\n",
      "|         11|\n",
      "|         12|\n",
      "|         13|\n",
      "|         14|\n",
      "|         15|\n",
      "|         19|\n",
      "|         20|\n",
      "|         21|\n",
      "|         22|\n",
      "|         25|\n",
      "|         26|\n",
      "|         27|\n",
      "|         28|\n",
      "|         29|\n",
      "|         32|\n",
      "+-----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(dayofyear('Date').alias('Day of Year')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b6d14ca9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|year(Date)|\n",
      "+----------+\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "|      2010|\n",
      "+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(year('Date')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "6119022a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+----+\n",
      "|      Date|              Open|              High|               Low|             Close|   Volume|         Adj Close|Year|\n",
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+----+\n",
      "|2010-01-04|        213.429998|        214.499996|212.38000099999996|        214.009998|123432400|         27.727039|2010|\n",
      "|2010-01-05|        214.599998|        215.589994|        213.249994|        214.379993|150476200|27.774976000000002|2010|\n",
      "|2010-01-06|        214.379993|            215.23|        210.750004|        210.969995|138040000|27.333178000000004|2010|\n",
      "|2010-01-07|            211.75|        212.000006|        209.050005|            210.58|119282800|          27.28265|2010|\n",
      "|2010-01-08|        210.299994|        212.000006|209.06000500000002|211.98000499999998|111902700|         27.464034|2010|\n",
      "|2010-01-11|212.79999700000002|        213.000002|        208.450005|210.11000299999998|115557400|         27.221758|2010|\n",
      "|2010-01-12|209.18999499999998|209.76999500000002|        206.419998|        207.720001|148614900|          26.91211|2010|\n",
      "|2010-01-13|        207.870005|210.92999500000002|        204.099998|        210.650002|151473000|          27.29172|2010|\n",
      "|2010-01-14|210.11000299999998|210.45999700000002|        209.020004|            209.43|108223500|         27.133657|2010|\n",
      "|2010-01-15|210.92999500000002|211.59999700000003|        205.869999|            205.93|148516900|26.680197999999997|2010|\n",
      "|2010-01-19|        208.330002|215.18999900000003|        207.240004|        215.039995|182501900|27.860484999999997|2010|\n",
      "|2010-01-20|        214.910006|        215.549994|        209.500002|            211.73|153038200|         27.431644|2010|\n",
      "|2010-01-21|        212.079994|213.30999599999998|        207.210003|        208.069996|152038600|         26.957455|2010|\n",
      "|2010-01-22|206.78000600000001|        207.499996|            197.16|            197.75|220441900|         25.620401|2010|\n",
      "|2010-01-25|202.51000200000001|        204.699999|        200.190002|        203.070002|266424900|26.309658000000002|2010|\n",
      "|2010-01-26|205.95000100000001|        213.710005|        202.580004|        205.940001|466777500|         26.681494|2010|\n",
      "|2010-01-27|        206.849995|            210.58|        199.530001|        207.880005|430642100|26.932840000000002|2010|\n",
      "|2010-01-28|        204.930004|        205.500004|        198.699995|        199.289995|293375600|25.819922000000002|2010|\n",
      "|2010-01-29|        201.079996|        202.199995|        190.250002|        192.060003|311488100|         24.883208|2010|\n",
      "|2010-02-01|192.36999699999998|             196.0|191.29999899999999|        194.729998|187469100|         25.229131|2010|\n",
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "new_df = df.withColumn(\"Year\",year('Date')) # Always use withColumn while working with a new column\n",
    "new_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "1addfed8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------------+\n",
      "|Year|        avg(Close)|\n",
      "+----+------------------+\n",
      "|2015|120.03999980555547|\n",
      "|2013| 472.6348802857143|\n",
      "|2014| 295.4023416507935|\n",
      "|2012| 576.0497195640002|\n",
      "|2016|104.60400786904763|\n",
      "|2010| 259.8424600000002|\n",
      "|2011|364.00432532142867|\n",
      "+----+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "result = new_df.groupby('Year').mean().select(['Year','avg(Close)'])\n",
    "result.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "a59830cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---------+\n",
      "|Year|Avg Close|\n",
      "+----+---------+\n",
      "|2016|   104.60|\n",
      "|2015|   120.04|\n",
      "|2010|   259.84|\n",
      "|2014|   295.40|\n",
      "|2011|   364.00|\n",
      "|2013|   472.63|\n",
      "|2012|   576.05|\n",
      "+----+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "result = result.select(['Year',format_number('avg(Close)',2).alias('Avg Close')]).orderBy('Avg Close')\n",
    "result.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "51a62dd0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column<'Avg Close DESC NULLS LAST'>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result['Avg Close'].desc()"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}