Tips/Part 1 - Syntax/Medium - Tips - Syntax.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()\n",
    "\n",
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pyspark.sql.functions as F"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tip #1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df1 = spark.range(20).withColumnRenamed(\"id\", \"column_1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2 = spark.range(20).withColumnRenamed(\"id\", \"column_2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df1.join(df2, df1[\"column_1\"]!=df2[\"column_2\"]).repartition(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+--------+\n",
      "|column_1|column_2|\n",
      "+--------+--------+\n",
      "|       1|      10|\n",
      "|       0|      18|\n",
      "|       2|      14|\n",
      "+--------+--------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+--------+-------------------+\n",
      "|column_1|column_2|column_1_multiplied|\n",
      "+--------+--------+-------------------+\n",
      "|       1|      10|                  2|\n",
      "|       0|      18|                  0|\n",
      "|       2|      14|                  4|\n",
      "+--------+--------+-------------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.withColumn('column_1_multiplied', 2 * df['column_1']).show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns_list = [F.col(column_name) for column_name in df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Column<b'column_1'>, Column<b'column_2'>]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns_list = [(2*column) for column in columns_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Column<b'(column_1 * 2)'>, Column<b'(column_2 * 2)'>]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+--------+--------------+--------------+\n",
      "|column_1|column_2|(column_1 * 2)|(column_2 * 2)|\n",
      "+--------+--------+--------------+--------------+\n",
      "|       1|      10|             2|            20|\n",
      "|       0|      18|             0|            36|\n",
      "|       2|      14|             4|            28|\n",
      "+--------+--------+--------------+--------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(df[\"*\"], *columns_list).show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tip #2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+--------+-------------------+-------------------+\n",
      "|column_1|column_2|column_1_multiplied|column_2_multiplied|\n",
      "+--------+--------+-------------------+-------------------+\n",
      "|       1|      10|                  2|                 20|\n",
      "|       0|      18|                  0|                 36|\n",
      "|       2|      14|                  4|                 28|\n",
      "+--------+--------+-------------------+-------------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with_column_df = df\n",
    "for idx, column in enumerate(columns_list):\n",
    "    with_column_df = with_column_df.withColumn(f\"column_{idx+1}_multiplied\", column)\n",
    "with_column_df.show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tip #3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-------------+-------------+\n",
      "|column_1|sum(column_1)|sum(column_2)|\n",
      "+--------+-------------+-------------+\n",
      "|      19|          361|          171|\n",
      "|       0|            0|          190|\n",
      "|       7|          133|          183|\n",
      "+--------+-------------+-------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy(\"column_1\").sum().show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+------------+------------+\n",
      "|column_1|column_1_sum|column_2_sum|\n",
      "+--------+------------+------------+\n",
      "|      19|         361|         171|\n",
      "|       0|           0|         190|\n",
      "|       7|         133|         183|\n",
      "+--------+------------+------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "column_1_summed = F.sum(\"column_1\").alias(\"column_1_sum\")\n",
    "column_2_summed = F.sum(\"column_2\").alias(\"column_2_sum\")\n",
    "df.groupBy(\"column_1\").agg(column_1_summed, column_2_summed).show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tip #4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+-------------------+\n",
      "|      sin_column_1|       sin_column_2|\n",
      "+------------------+-------------------+\n",
      "|0.8414709848078965|-0.5440211108893698|\n",
      "|               0.0|-0.7509872467716762|\n",
      "|0.9092974268256817| 0.9906073556948704|\n",
      "+------------------+-------------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sin1 = F.sin(df[\"column_1\"]).alias(f\"sin_column_1\")\n",
    "sin2 = F.sin(df[\"column_2\"]).alias(f\"sin_column_2\")\n",
    "df.select(sin1, sin2).show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.createOrReplaceTempView(\"data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+-------------------+\n",
      "|      sin_column_1|       sin_column_2|\n",
      "+------------------+-------------------+\n",
      "|0.8414709848078965|-0.5440211108893698|\n",
      "|               0.0|-0.7509872467716762|\n",
      "|0.9092974268256817| 0.9906073556948704|\n",
      "+------------------+-------------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"\"\"\n",
    "select sin(column_1) as sin_column_1, \n",
    "       sin(column_2) as sin_column_2\n",
    "from data\n",
    "\"\"\").show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+-------------------+\n",
      "|      sin_column_1|       sin_column_2|\n",
      "+------------------+-------------------+\n",
      "|0.8414709848078965|-0.5440211108893698|\n",
      "|               0.0|-0.7509872467716762|\n",
      "|0.9092974268256817| 0.9906073556948704|\n",
      "+------------------+-------------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sin1 = \"sin(column_1) as sin_column_1\"\n",
    "sin2 = \"sin(column_2) as sin_column_2\"\n",
    "df.selectExpr(sin1, sin2).show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+\n",
      "|       column_2_sin|\n",
      "+-------------------+\n",
      "|0.14987720966295234|\n",
      "|-0.9999902065507035|\n",
      "| 0.9906073556948704|\n",
      "+-------------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.selectExpr(\"sin(column_2) as column_2_sin\").show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tip #5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(column_1=1, column_2=10),\n",
       " Row(column_1=0, column_2=18),\n",
       " Row(column_1=2, column_2=14)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.take(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[pyspark.sql.types.Row]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rdd.map(type).take(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a_row = df.take(1)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(column_1=1, column_2=10)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a_row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a_row[\"column_1\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'column_1': 1, 'column_2': 10}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a_row.asDict()"
   ]
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}