Spark DataFrame Basic Operations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f1c54ee3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "49714f50",
   "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:4041\">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>ops</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x1807f856e50>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark = SparkSession.builder.appName('ops').getOrCreate()\n",
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f9bb7dbe",
   "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 = spark.read.csv('appl_stock.csv',inferSchema=True,header=True)\n",
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6a32fc00",
   "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.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ffc75100",
   "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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d9fdad91",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+------------------+\n",
      "|              Open|             Close|\n",
      "+------------------+------------------+\n",
      "|        213.429998|        214.009998|\n",
      "|        214.599998|        214.379993|\n",
      "|        214.379993|        210.969995|\n",
      "|            211.75|            210.58|\n",
      "|        210.299994|211.98000499999998|\n",
      "|212.79999700000002|210.11000299999998|\n",
      "|209.18999499999998|        207.720001|\n",
      "|        207.870005|        210.650002|\n",
      "|210.11000299999998|            209.43|\n",
      "|210.92999500000002|            205.93|\n",
      "|        208.330002|        215.039995|\n",
      "|        214.910006|            211.73|\n",
      "|        212.079994|        208.069996|\n",
      "|206.78000600000001|            197.75|\n",
      "|202.51000200000001|        203.070002|\n",
      "|205.95000100000001|        205.940001|\n",
      "|        206.849995|        207.880005|\n",
      "|        204.930004|        199.289995|\n",
      "|        201.079996|        192.060003|\n",
      "|192.36999699999998|        194.729998|\n",
      "+------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.filter(\"Close < 500\").select(['Open','Close']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8b195961",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+------------------+---------+\n",
      "|              Open|             Close|   Volume|\n",
      "+------------------+------------------+---------+\n",
      "|        213.429998|        214.009998|123432400|\n",
      "|        214.599998|        214.379993|150476200|\n",
      "|        214.379993|        210.969995|138040000|\n",
      "|            211.75|            210.58|119282800|\n",
      "|        210.299994|211.98000499999998|111902700|\n",
      "|212.79999700000002|210.11000299999998|115557400|\n",
      "|209.18999499999998|        207.720001|148614900|\n",
      "|        207.870005|        210.650002|151473000|\n",
      "|210.11000299999998|            209.43|108223500|\n",
      "|210.92999500000002|            205.93|148516900|\n",
      "|        208.330002|        215.039995|182501900|\n",
      "|        214.910006|            211.73|153038200|\n",
      "|        212.079994|        208.069996|152038600|\n",
      "|206.78000600000001|            197.75|220441900|\n",
      "|202.51000200000001|        203.070002|266424900|\n",
      "|205.95000100000001|        205.940001|466777500|\n",
      "|        206.849995|        207.880005|430642100|\n",
      "|        204.930004|        199.289995|293375600|\n",
      "|        201.079996|        192.060003|311488100|\n",
      "|192.36999699999998|        194.729998|187469100|\n",
      "+------------------+------------------+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.filter(df['Close'] < 500).select(['Open','Close','Volume']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "48453ddf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+\n",
      "|      Date|              Open|              High|               Low|             Close|   Volume|         Adj Close|\n",
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+\n",
      "|2010-02-01|192.36999699999998|             196.0|191.29999899999999|        194.729998|187469100|         25.229131|\n",
      "|2010-02-02|        195.909998|        196.319994|193.37999299999998|        195.859997|174585600|25.375532999999997|\n",
      "|2010-02-03|        195.169994|        200.200003|        194.420004|        199.229994|153832000|25.812148999999998|\n",
      "|2010-02-04|        196.730003|        198.370001|        191.570005|        192.050003|189413000|         24.881912|\n",
      "|2010-02-05|192.63000300000002|             196.0|        190.850002|        195.460001|212576700|25.323710000000002|\n",
      "|2010-02-08|        195.690006|197.88000300000002|        193.999994|194.11999699999998|119567700|           25.1501|\n",
      "|2010-02-09|        196.419996|        197.499994|        194.749998|196.19000400000002|158221700|         25.418289|\n",
      "|2010-02-10|        195.889997|             196.6|            194.26|195.12000700000002| 92590400|          25.27966|\n",
      "|2010-02-11|        194.880001|        199.750006|194.05999599999998|        198.669994|137586400|         25.739595|\n",
      "|2010-02-23|        199.999998|        201.330002|        195.709993|        197.059998|143773700|         25.531005|\n",
      "|2014-06-09|         92.699997|         93.879997|             91.75|         93.699997| 75415000|         88.906324|\n",
      "|2014-06-10|         94.730003|         95.050003|             93.57|             94.25| 62777000|         89.428189|\n",
      "|2014-06-11|         94.129997|         94.760002|         93.470001|         93.860001| 45681000|         89.058142|\n",
      "|2014-06-12|         94.040001|         94.120003|         91.900002|         92.290001| 54749000|         87.568463|\n",
      "|2014-06-13|         92.199997|         92.440002|         90.879997|         91.279999| 54525000|         86.610132|\n",
      "|2014-06-16|         91.510002|             92.75|         91.449997|         92.199997| 35561000|         87.483064|\n",
      "|2014-06-17|         92.309998|         92.699997|         91.800003| 92.08000200000001| 29726000| 87.36920699999999|\n",
      "|2014-06-18|         92.269997|         92.290001|         91.349998|             92.18| 33514000|          87.46409|\n",
      "|2014-06-19|         92.290001|         92.300003|         91.339996|         91.860001| 35528000|         87.160461|\n",
      "|2014-06-20|         91.849998|         92.550003|         90.900002|         90.910004|100898000|         86.259066|\n",
      "+----------+------------------+------------------+------------------+------------------+---------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.filter((df['Close'] < 200) & ~(df['Open'] > 200)).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "419ea4a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------------------+----------+------+------+---------+---------+\n",
      "|      Date|              Open|      High|   Low| Close|   Volume|Adj Close|\n",
      "+----------+------------------+----------+------+------+---------+---------+\n",
      "|2010-01-22|206.78000600000001|207.499996|197.16|197.75|220441900|25.620401|\n",
      "+----------+------------------+----------+------+------+---------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.filter(df['Low']==197.16).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "afa438b1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(Date=datetime.date(2010, 1, 22), Open=206.78000600000001, High=207.499996, Low=197.16, Close=197.75, Volume=220441900, Adj Close=25.620401)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df.filter(df['Low'] == 197.16).collect()\n",
    "result[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e8d537ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(Date=datetime.date(2010, 1, 22), Open=206.78000600000001, High=207.499996, Low=197.16, Close=197.75, Volume=220441900, Adj Close=25.620401)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row = result[0]\n",
    "row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7bdf8e2d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Date': datetime.date(2010, 1, 22),\n",
       " 'Open': 206.78000600000001,\n",
       " 'High': 207.499996,\n",
       " 'Low': 197.16,\n",
       " 'Close': 197.75,\n",
       " 'Volume': 220441900,\n",
       " 'Adj Close': 25.620401}"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row.asDict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3ecdb0be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row.count(197.16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7be5ea4f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row.index(220441900)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1dc4536c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25.620401"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row.asDict()['Adj Close']"
   ]
  }
 ],
 "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
}