Spark DataFrame Dates and Timestamps.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder.appName('dates').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.csv('appl_stock.csv',header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+------------------+\n",
      "|               Date|              Open|\n",
      "+-------------------+------------------+\n",
      "|2010-01-04 00:00:00|        213.429998|\n",
      "|2010-01-05 00:00:00|        214.599998|\n",
      "|2010-01-06 00:00:00|        214.379993|\n",
      "|2010-01-07 00:00:00|            211.75|\n",
      "|2010-01-08 00:00:00|        210.299994|\n",
      "|2010-01-11 00:00:00|212.79999700000002|\n",
      "|2010-01-12 00:00:00|209.18999499999998|\n",
      "|2010-01-13 00:00:00|        207.870005|\n",
      "|2010-01-14 00:00:00|210.11000299999998|\n",
      "|2010-01-15 00:00:00|210.92999500000002|\n",
      "|2010-01-19 00:00:00|        208.330002|\n",
      "|2010-01-20 00:00:00|        214.910006|\n",
      "|2010-01-21 00:00:00|        212.079994|\n",
      "|2010-01-22 00:00:00|206.78000600000001|\n",
      "|2010-01-25 00:00:00|202.51000200000001|\n",
      "|2010-01-26 00:00:00|205.95000100000001|\n",
      "|2010-01-27 00:00:00|        206.849995|\n",
      "|2010-01-28 00:00:00|        204.930004|\n",
      "|2010-01-29 00:00:00|        201.079996|\n",
      "|2010-02-01 00:00:00|192.36999699999998|\n",
      "+-------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(['Date','Open']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import (dayofmonth,hour,dayofyear\n",
    "                                   ,month,year,weekofyear,\n",
    "                                   format_number,date_format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.select(weekofyear(df['Date'])).show()\n",
    "#df.select(year(df['Date'])).show()\n",
    "newdf = df.withColumn('Year',year(df['Date']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = newdf.groupBy('Year').mean('Close')\n",
    "#newdf.groupBy('Year').mean().select(['Year','avg(Close)'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "new = result.withColumnRenamed('avg(Close)','Average Closing Price')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---------------------------------------+\n",
      "|Year|format_number(Average Closing Price, 2)|\n",
      "+----+---------------------------------------+\n",
      "|2015|                                 120.04|\n",
      "|2013|                                 472.63|\n",
      "|2014|                                 295.40|\n",
      "|2012|                                 576.05|\n",
      "|2016|                                 104.60|\n",
      "|2010|                                 259.84|\n",
      "|2011|                                 364.00|\n",
      "+----+---------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "new.select(['Year',format_number('Average Closing Price',2)]).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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}