sparkdataframe.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "sparkdataframe.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyMFrGIUoyQvZ+AUkX2KotLJ",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/busranurorhan/Complete-Python-3-Bootcamp/blob/master/sparkdataframe.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AoB5GKHMwngs"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cEVNe8wgw4nJ",
        "outputId": "1b89ad10-586b-435b-e83c-8831b3058883"
      },
      "source": [
        "!apt-get update\n",
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "!update-alternatives --set java /usr/lib/jvm/java-8-openjdk-amd64/jre/bin/java\n",
        "!java -version\n",
        "!pip install pyspark"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rGet:1 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Connecting to security.u\r0% [Waiting for headers] [Connecting to security.ubuntu.com (91.189.91.38)] [Co\r                                                                               \rHit:2 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "\r0% [Waiting for headers] [Connecting to security.ubuntu.com (91.189.91.38)] [Wa\r0% [1 InRelease gpgv 15.9 kB] [Waiting for headers] [Connecting to security.ubu\r                                                                               \rHit:3 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease\n",
            "\r0% [1 InRelease gpgv 15.9 kB] [Waiting for headers] [Connecting to security.ubu\r                                                                               \rHit:4 http://archive.ubuntu.com/ubuntu bionic-updates InRelease\n",
            "\r0% [1 InRelease gpgv 15.9 kB] [Waiting for headers] [Connecting to security.ubu\r                                                                               \rHit:5 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "\r0% [1 InRelease gpgv 15.9 kB] [Waiting for headers] [Connecting to security.ubu\r                                                                               \rHit:6 http://archive.ubuntu.com/ubuntu bionic-backports InRelease\n",
            "\r0% [1 InRelease gpgv 15.9 kB] [Connecting to security.ubuntu.com (91.189.91.38)\r                                                                               \rHit:7 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Hit:8 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Hit:9 http://security.ubuntu.com/ubuntu bionic-security InRelease\n",
            "Ign:10 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:11 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [919 kB]\n",
            "Ign:12 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:13 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release\n",
            "Hit:14 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Fetched 935 kB in 2s (465 kB/s)\n",
            "Reading package lists... Done\n",
            "openjdk version \"1.8.0_292\"\n",
            "OpenJDK Runtime Environment (build 1.8.0_292-8u292-b10-0ubuntu1~18.04-b10)\n",
            "OpenJDK 64-Bit Server VM (build 25.292-b10, mixed mode)\n",
            "Requirement already satisfied: pyspark in /usr/local/lib/python3.7/dist-packages (3.1.2)\n",
            "Requirement already satisfied: py4j==0.10.9 in /usr/local/lib/python3.7/dist-packages (from pyspark) (0.10.9)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Dphe5-5qxDIK",
        "outputId": "083fcde2-2f0b-4f0e-91c7-67884c7ddbe3"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "\n",
        "spark = SparkSession.Builder().appName(\"Spark ML Introduction\").getOrCreate()  #for döngusu kullanamayız RDD ve dataframe de yani dağıtık veri yapısında for kullanamayız\n",
        "pointsDF = spark.read.csv(\"points.txt\")\n",
        "#pointsDF = spark.read.option(\"inferSchema\", \"true\").csv(\"points.txt\") # bu tüm satırlara bakar ve tipini ona göe söyler , dosyada boşluk veya sorun varsa anlayamaz #datanın teki bile floatsa ona float diyor\n",
        "\n",
        "pointsDF = pointsDF.withColumnRenamed(\"_c0\",\"x\")\n",
        "pointsDF = pointsDF.withColumnRenamed(\"_c1\",\"y\")\n",
        "pointsDF = pointsDF.withColumn(\"z\",pointsDF[\"x\"].cast(\"float\"))\n",
        "pointsDF.printSchema()\n",
        "pointsDF.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- x: string (nullable = true)\n",
            " |-- y: string (nullable = true)\n",
            " |-- z: float (nullable = true)\n",
            "\n",
            "+---+---+----+\n",
            "|  x|  y|   z|\n",
            "+---+---+----+\n",
            "|  5|  5| 5.0|\n",
            "| 15| 17|15.0|\n",
            "| 15| 16|15.0|\n",
            "|  5|  6| 5.0|\n",
            "|  6|  6| 6.0|\n",
            "| 16| 16|16.0|\n",
            "|  8|  8| 8.0|\n",
            "|  7|  6| 7.0|\n",
            "| 17| 17|17.0|\n",
            "|  6|  7| 6.0|\n",
            "|  7|  8| 7.0|\n",
            "| 18| 18|18.0|\n",
            "|  8|  6| 8.0|\n",
            "| 17| 16|17.0|\n",
            "|  5|  8| 5.0|\n",
            "| 17| 16|17.0|\n",
            "|  8|  8| 8.0|\n",
            "| 16| 17|16.0|\n",
            "| 17| 18|17.0|\n",
            "| 18| 16|18.0|\n",
            "+---+---+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FWu5o53mIVgB",
        "outputId": "e5e3a343-bf78-4a0c-fee9-a53da8ec7b6f"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "\n",
        "spark = SparkSession.Builder().appName(\"Spark ML Introduction\").getOrCreate()\n",
        "pointsDF = spark.read.option(\"inferSchema\", \"true\").option('header','true').csv(\"points.txt\") #ilk basındaki satırı başlık olarak alıyor header true dersek\n",
        "pointsDF.printSchema()\n",
        "pointsDF.show()\n",
        "\n",
        "#pointsDF.rdd.map(lambda x : tuple(x)).take(1)\n",
        "pointsDF.rdd.map(lambda x : list(x)).take(1)\n",
        "\n",
        "rdd = pointsDF.rdd # data frame ---> RDD\n",
        "df = rdd.toDF()  # RDD --> DataFrame\n",
        "df.show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- 50: integer (nullable = true)\n",
            " |-- 51: integer (nullable = true)\n",
            "\n",
            "+---+---+\n",
            "| 50| 51|\n",
            "+---+---+\n",
            "| 15| 17|\n",
            "| 15| 16|\n",
            "|  5|  6|\n",
            "|  6|  6|\n",
            "| 16| 16|\n",
            "|  8|  8|\n",
            "|  7|  6|\n",
            "| 17| 17|\n",
            "|  6|  7|\n",
            "|  7|  8|\n",
            "| 18| 18|\n",
            "|  8|  6|\n",
            "| 17| 16|\n",
            "|  5|  8|\n",
            "| 17| 16|\n",
            "|  8|  8|\n",
            "| 16| 17|\n",
            "| 17| 18|\n",
            "| 18| 16|\n",
            "|  5| 15|\n",
            "+---+---+\n",
            "only showing top 20 rows\n",
            "\n",
            "+---+---+\n",
            "| 50| 51|\n",
            "+---+---+\n",
            "| 15| 17|\n",
            "| 15| 16|\n",
            "|  5|  6|\n",
            "|  6|  6|\n",
            "| 16| 16|\n",
            "|  8|  8|\n",
            "|  7|  6|\n",
            "| 17| 17|\n",
            "|  6|  7|\n",
            "|  7|  8|\n",
            "| 18| 18|\n",
            "|  8|  6|\n",
            "| 17| 16|\n",
            "|  5|  8|\n",
            "| 17| 16|\n",
            "|  8|  8|\n",
            "| 16| 17|\n",
            "| 17| 18|\n",
            "| 18| 16|\n",
            "|  5| 15|\n",
            "+---+---+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 902
        },
        "id": "D5lHpoPWKeGN",
        "outputId": "3f89e530-80f3-49d1-eb08-2beb08663ca8"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from pyspark.sql.functions import UserDefinedFunction\n",
        "from pyspark.sql.types import FloatType\n",
        "\n",
        "spark = SparkSession.Builder().appName(\"Spark ML Introduction\").getOrCreate()\n",
        "dollarDF = spark.read.option(\"inferSchema\", \"true\")\\\n",
        "                         .option('header','false')\\\n",
        "                         .option('delimiter','\\t').csv(\"DollarDataset.txt\")\n",
        "\n",
        "dollarDF = dollarDF.withColumnRenamed('_c0','id')\n",
        "dollarDF = dollarDF.withColumnRenamed('_c1','tarih')\n",
        "dollarDF = dollarDF.withColumnRenamed('_c2','fiyat')\n",
        "# boş null olanları silmek\n",
        "dollarDF = dollarDF.filter(dollarDF['fiyat'].isNotNull()) #Bunu yaptıktan sonra index bozuldu 141'den 145'e mi ne atladı RDD ile zipwith index kullanabilriz\n",
        "\n",
        "dollarDF = dollarDF.drop('id')\n",
        "rdd = dollarDF.rdd.zipWithIndex()\n",
        "#dollarDF.printSchema()\n",
        "#rdd.take(5) ^burada çıkan sıralamayı değiştirmek istedik\n",
        "\n",
        "#rdd.map(lambda x : (x[1],x[0][0],x[0][1])).take(5)\n",
        "\n",
        "dollarDF = rdd.map(lambda x : (x[1],x[0][0],x[0][1])).toDF() # data frame çevirince _1 _2 otomatik atanıyor\n",
        "dollarDF = dollarDF.withColumnRenamed('_1','id')\n",
        "dollarDF = dollarDF.withColumnRenamed('_2','tarih')\n",
        "dollarDF = dollarDF.withColumnRenamed('_3','fiyat')\n",
        "\n",
        "# dollarDF.withColumn(\"numeric-fiyat\",dollarDF['fiyat'])  bunu her satıra uygula diye data frame de bir fonksiyon yok o yüzden fosksiyon yazalım\n",
        "\n",
        "def stringToFloat(f):\n",
        "  f = f.replace('.','') # noktayi siliyoruz\n",
        "  f = f.replace(',','.') # virgül olan yerleri nokta ile değiştiriyorusz\n",
        "  return float(f)\n",
        "\n",
        "stringToFloatUDF = UserDefinedFunction(stringToFloat,FloatType())\n",
        "\n",
        "dollarDF = dollarDF.withColumn(\"numeric-fiyat\",stringToFloatUDF('fiyat')) # bu çalışmıyor data frame anlamıyor bundan  , eger yeni ir kola ekleyeeksek ve bu mevcut kolanla işlemler yapılıp elde edilefcekse bu uderdefinedfunction yapmamız lazım\n",
        "\n",
        "#numeric fiyat yerine fiyat yazarsak üzerine yazar\n",
        "\n",
        "'''\n",
        "bu örneğin amacaı dataframe'ler ile RDD'ler arası dönüşümü öğrenmek\n",
        "\n",
        "'''\n",
        "'''\n",
        "dollarDF2 = dollarDF2.withColumn(\"newID\",dollarDF2['id']+1)\n",
        "dollarDF2 = dollarDF2.drop('id')\n",
        "dollarDF2 = dollarDF2.withColumnRenamed('newId','id')\n",
        "dollarDF2 = dollarDF2.select('id','tarih','fiyat')\n",
        "dollarDF2.show(2)   #çağırdığımız methot sırasına göre basıyor\n",
        "dollarDF.show(2)\n",
        "'''\n",
        "dollarDF2 =dollarDF\n",
        "# burada da \n",
        "dollarDF2 = dollarDF2.withColumn(\"id\",dollarDF2['id']+1)\n",
        "\n",
        "dollarDF2 = dollarDF2.withColumnRenamed('fiyat','fiyat2')\n",
        "dollarDF2 = dollarDF2.withColumnRenamed('tarih','tarih2')\n",
        "dollarDF2 = dollarDF2.select('id','tarih2','fiyat2')\n",
        "dollarDF2.show(2)   #çağırdığımız methot sırasına göre basıyor\n",
        "dollarDF.show(2)\n",
        "\n",
        "#id'ye göre join yapıcaz\n",
        "\n",
        "joinedDF = dollarDF2.join(dollarDF,\"id\")\n",
        "joinedDF.sort('id')\n",
        "#\n",
        "joinedDF.show()\n",
        "\n",
        "\n",
        "joinedDF.printSchema()  # bunu yaptığımızda değerleri string olarak görecek önce noktaları silecez sonra virgülleri  nokta ile değiştirmek gerekiyor çünkü virgül olarak \n",
        "\n",
        "\n",
        "#noktaları silmek için teker teker satırlarda dolaşmak lazım rdd'de lambda bunu yapıyordu data frame'de \n",
        "\n",
        "def oranHesapla(oncekiFiyat,sonrakiFiyat):\n",
        "  return (sonrakiFiyat-oncekiFiyat) / oncekiFiyat\n",
        "\n",
        "\n",
        "joinedDF = joinedDF.withColumn(\"artis-orani\", (joinedDF['fiyat'] - joinedDF['fiyat2']) / joinedDF['fiyat'])\n",
        "\n",
        "\n",
        "\n",
        "'''\n",
        "joinedDF = joinedDF.withColumn(\"artis-orani\", (joinedDF['fiyat'] - joinedDF['fiyat2']) / joinedDF['fiyat'])\n",
        "\n",
        "joinedDF.show() # bunun sonucu null geldi  çünkü string\n",
        "\n",
        "'''\n",
        "\n",
        "\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+------+\n",
            "| id|    tarih2|fiyat2|\n",
            "+---+----------+------+\n",
            "|  1|02-01-1950|  2,80|\n",
            "|  2|03-01-1950|  2,80|\n",
            "+---+----------+------+\n",
            "only showing top 2 rows\n",
            "\n",
            "+---+----------+-----+-------------+\n",
            "| id|     tarih|fiyat|numeric-fiyat|\n",
            "+---+----------+-----+-------------+\n",
            "|  0|02-01-1950| 2,80|          2.8|\n",
            "|  1|03-01-1950| 2,80|          2.8|\n",
            "+---+----------+-----+-------------+\n",
            "only showing top 2 rows\n",
            "\n",
            "+----+----------+------+----------+-----+-------------+\n",
            "|  id|    tarih2|fiyat2|     tarih|fiyat|numeric-fiyat|\n",
            "+----+----------+------+----------+-----+-------------+\n",
            "|  26|06-02-1950|  2,80|07-02-1950| 2,80|          2.8|\n",
            "|  29|09-02-1950|  2,80|10-02-1950| 2,80|          2.8|\n",
            "| 474|14-11-1951|  2,80|15-11-1951| 2,80|          2.8|\n",
            "| 964|22-10-1953|  2,80|23-10-1953| 2,80|          2.8|\n",
            "|1677|20-08-1956|  2,80|21-08-1956| 2,80|          2.8|\n",
            "|1697|18-09-1956|  2,80|19-09-1956| 2,80|          2.8|\n",
            "|1806|20-02-1957|  2,80|21-02-1957| 2,80|          2.8|\n",
            "|1950|23-09-1957|  2,80|24-09-1957| 2,80|          2.8|\n",
            "|2040|29-01-1958|  2,80|30-01-1958| 2,80|          2.8|\n",
            "|2214|08-10-1958|  2,80|09-10-1958| 2,80|          2.8|\n",
            "|2250|28-11-1958|  2,80|01-12-1958| 2,80|          2.8|\n",
            "|2453|21-09-1959|  2,80|22-09-1959| 2,80|          2.8|\n",
            "|2509|09-12-1959|  2,80|10-12-1959| 2,80|          2.8|\n",
            "|2529|07-01-1960|  2,80|08-01-1960| 2,80|          2.8|\n",
            "|2927|03-08-1961|  9,00|04-08-1961| 9,00|          9.0|\n",
            "|3091|27-03-1962|  9,00|28-03-1962| 9,00|          9.0|\n",
            "|3506|20-11-1963|  9,00|21-11-1963| 9,00|          9.0|\n",
            "|3764|24-11-1964|  9,00|25-11-1964| 9,00|          9.0|\n",
            "|4590|05-03-1968|  9,00|06-03-1968| 9,00|          9.0|\n",
            "|4823|05-02-1969|  9,00|06-02-1969| 9,00|          9.0|\n",
            "+----+----------+------+----------+-----+-------------+\n",
            "only showing top 20 rows\n",
            "\n",
            "root\n",
            " |-- id: long (nullable = true)\n",
            " |-- tarih2: string (nullable = true)\n",
            " |-- fiyat2: string (nullable = true)\n",
            " |-- tarih: string (nullable = true)\n",
            " |-- fiyat: string (nullable = true)\n",
            " |-- numeric-fiyat: float (nullable = true)\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'\\njoinedDF = joinedDF.withColumn(\"artis-orani\", (joinedDF[\\'fiyat\\'] - joinedDF[\\'fiyat2\\']) / joinedDF[\\'fiyat\\'])\\n\\njoinedDF.show() # bunun sonucu null geldi  çünkü string\\n\\n'"
            ]
          },
          "metadata": {},
          "execution_count": 59
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "21N6o5Ra4Nl5",
        "outputId": "a485683a-c4bd-407b-920a-7bd4f18d4a5a"
      },
      "source": [
        "\n",
        "from pyspark.sql import SparkSession\n",
        "from pyspark.sql.functions import UserDefinedFunction\n",
        "\n",
        "spark = SparkSession.Builder().appName('Spark DataFrame Introduction').getOrCreate()\n",
        "\n",
        "worldDF = spark.read.option('inferSchema','true')\\\n",
        "                        .option('header','false')\\\n",
        "                        .option('delimiter',',').csv('world.txt')\n",
        "\n",
        "\n",
        "worldDF = worldDF.withColumnRenamed('_c0','ulke')\n",
        "worldDF = worldDF.withColumnRenamed('_c1','kita')\n",
        "worldDF = worldDF.withColumnRenamed('_c2','baskent')\n",
        "worldDF = worldDF.withColumnRenamed('_c3','nufus')\n",
        "\n",
        "worldDF.show()\n",
        "\n",
        "cozum1DF = worldDF.groupBy('kita').count()\n",
        "cozum1DF = cozum1DF.sort('count',ascending =False)\n",
        "cozum1DF.show()\n",
        "\n",
        "cozum2DF = worldDF.groupBy('kita').mean()\n",
        "cozum2DF = cozum2DF.sort('avg(nufus)',ascending =False)\n",
        "cozum2DF = cozum2DF.withColumn('ortalama nufus',cozum2DF['avg(nufus)'].cast('int'))\n",
        "cozum2DF.show()\n",
        "\n",
        "cozum3DF = worldDF.groupBy('kita').sum('nufus')\n",
        "cozum3DF = cozum3DF.sort('sum(nufus)',ascending=False)\n",
        "cozum3DF = cozum3DF.sort('sum(nufus)',ascending=False)\n",
        "cozum3DF.show()\n",
        "worldDF.show()"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------------+-------------+----------------+---------+\n",
            "|               ulke|         kita|         baskent|    nufus|\n",
            "+-------------------+-------------+----------------+---------+\n",
            "|        Afghanistan|         Asia|           Kabul| 29863010|\n",
            "|            Albania|       Europe|          Tirana|  3129678|\n",
            "|            Algeria|       Africa|         Algiers| 32853800|\n",
            "|            Andorra|       Europe|Andorra la Vella|    67151|\n",
            "|             Angola|       Africa|          Luanda| 15941390|\n",
            "|Antigua and Barbuda|North America|      St. John's|    81479|\n",
            "|          Argentina|South America|    Buenos Aires| 38747150|\n",
            "|            Armenia|         Asia|         Yerevan|  3016312|\n",
            "|          Australia|       Oceana|        Canberra| 20155130|\n",
            "|            Austria|       Europe|          Vienna|  8189444|\n",
            "|         Azerbaijan|         Asia|            Baku|  8410801|\n",
            "|            Bahamas|North America|          Nassau|   323063|\n",
            "|            Bahrain|         Asia|          Manama|   726617|\n",
            "|         Bangladesh|         Asia|           Dhaka|141822300|\n",
            "|           Barbados|North America|      Bridgetown|   269556|\n",
            "|            Belarus|       Europe|           Minsk|  9755106|\n",
            "|            Belgium|       Europe|        Brussels| 10419050|\n",
            "|             Belize|North America|        Belmopan|   269736|\n",
            "|              Benin|       Africa|         Cotonou|  8438853|\n",
            "|             Bhutan|         Asia|         Thimphu|  2162546|\n",
            "+-------------------+-------------+----------------+---------+\n",
            "only showing top 20 rows\n",
            "\n",
            "+-------------+-----+\n",
            "|         kita|count|\n",
            "+-------------+-----+\n",
            "|       Africa|   55|\n",
            "|         Asia|   49|\n",
            "|       Europe|   46|\n",
            "|North America|   23|\n",
            "|       Oceana|   13|\n",
            "|South America|   12|\n",
            "+-------------+-----+\n",
            "\n",
            "+-------------+--------------------+--------------+\n",
            "|         kita|          avg(nufus)|ortalama nufus|\n",
            "+-------------+--------------------+--------------+\n",
            "|         Asia| 8.127662059183674E7|      81276620|\n",
            "|South America|       3.124975725E7|      31249757|\n",
            "|North America| 2.222895156521739E7|      22228951|\n",
            "|       Africa|1.6516486363636363E7|      16516486|\n",
            "|       Europe|1.4397671369565217E7|      14397671|\n",
            "|       Oceana|  2462989.3076923075|       2462989|\n",
            "+-------------+--------------------+--------------+\n",
            "\n",
            "+-------------+----------+\n",
            "|         kita|sum(nufus)|\n",
            "+-------------+----------+\n",
            "|         Asia|3982554409|\n",
            "|       Africa| 908406750|\n",
            "|       Europe| 662292883|\n",
            "|North America| 511265886|\n",
            "|South America| 374997087|\n",
            "|       Oceana|  32018861|\n",
            "+-------------+----------+\n",
            "\n",
            "+-------------------+-------------+----------------+---------+\n",
            "|               ulke|         kita|         baskent|    nufus|\n",
            "+-------------------+-------------+----------------+---------+\n",
            "|        Afghanistan|         Asia|           Kabul| 29863010|\n",
            "|            Albania|       Europe|          Tirana|  3129678|\n",
            "|            Algeria|       Africa|         Algiers| 32853800|\n",
            "|            Andorra|       Europe|Andorra la Vella|    67151|\n",
            "|             Angola|       Africa|          Luanda| 15941390|\n",
            "|Antigua and Barbuda|North America|      St. John's|    81479|\n",
            "|          Argentina|South America|    Buenos Aires| 38747150|\n",
            "|            Armenia|         Asia|         Yerevan|  3016312|\n",
            "|          Australia|       Oceana|        Canberra| 20155130|\n",
            "|            Austria|       Europe|          Vienna|  8189444|\n",
            "|         Azerbaijan|         Asia|            Baku|  8410801|\n",
            "|            Bahamas|North America|          Nassau|   323063|\n",
            "|            Bahrain|         Asia|          Manama|   726617|\n",
            "|         Bangladesh|         Asia|           Dhaka|141822300|\n",
            "|           Barbados|North America|      Bridgetown|   269556|\n",
            "|            Belarus|       Europe|           Minsk|  9755106|\n",
            "|            Belgium|       Europe|        Brussels| 10419050|\n",
            "|             Belize|North America|        Belmopan|   269736|\n",
            "|              Benin|       Africa|         Cotonou|  8438853|\n",
            "|             Bhutan|         Asia|         Thimphu|  2162546|\n",
            "+-------------------+-------------+----------------+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O4db5dCjK2YR"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MPY1OVjJCmqH",
        "outputId": "fe40d7e4-e8c3-44a3-a3ff-eea2d7e9f110"
      },
      "source": [
        "# sql ile çalışıyor\n",
        "\n",
        "from pyspark.sql import SparkSession\n",
        "from pyspark.sql.functions import UserDefinedFunction\n",
        "from pyspark.sql.types import StringType\n",
        "\n",
        "spark = SparkSession.Builder().appName('Spark DataFrame Introduction').getOrCreate()\n",
        "\n",
        "worldDF = spark.read.option('inferSchema','true')\\\n",
        "                        .option('header','false')\\\n",
        "                        .option('delimiter',',').csv('world.txt')\n",
        "\n",
        "\n",
        "worldDF = worldDF.withColumnRenamed('_c0','ulke')\n",
        "worldDF = worldDF.withColumnRenamed('_c1','kita')\n",
        "worldDF = worldDF.withColumnRenamed('_c2','baskent')\n",
        "worldDF = worldDF.withColumnRenamed('_c2','nufus')\n",
        "\n",
        "worldDF.createOrReplaceTempView('world')\n",
        "\n",
        "cozum1DF = spark.sql('select kita,count(*) as count from world group by kita order by count desc')\n",
        "cozum1DF.show()\n"
      ],
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------------+-----+\n",
            "|         kita|count|\n",
            "+-------------+-----+\n",
            "|       Africa|   55|\n",
            "|         Asia|   49|\n",
            "|       Europe|   46|\n",
            "|North America|   23|\n",
            "|       Oceana|   13|\n",
            "|South America|   12|\n",
            "+-------------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bv0oop8Qk6VS"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2JoRj0V__bb5",
        "outputId": "3e6a5d4d-dbb8-4670-ea66-d4982435d528"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from pyspark.sql.functions import UserDefinedFunction\n",
        "from pyspark.sql.types import StringType\n",
        "\n",
        "spark = SparkSession.Builder().appName('Spark DataFrame Introduction').getOrCreate()\n",
        "\n",
        "actorsDF = spark.read.option('inferSchema','true')\\\n",
        "                        .option('header','true')\\\n",
        "                        .option('delimiter',',').csv('join-actors.txt')\n",
        "\n",
        "seriesDF = spark.read.option('inferSchema','true')\\\n",
        "                        .option('header','true')\\\n",
        "                        .option('delimiter',',').csv('join-series.txt')\n",
        "\n",
        "seriesDF.show()\n",
        "actorsDF.show()"
      ],
      "execution_count": 39,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+------------------+\n",
            "|movieid|         moviename|\n",
            "+-------+------------------+\n",
            "|      1|     Lethal Weapon|\n",
            "|      2|     The Last Ship|\n",
            "|      3|Person of Interest|\n",
            "|      4|   Game of Thrones|\n",
            "|      5|      Flashforward|\n",
            "|      6|        Revolution|\n",
            "|      7|              Lost|\n",
            "|      8|  Leyle ile Mecnun|\n",
            "+-------+------------------+\n",
            "\n",
            "+---+--------------------+-------+\n",
            "| id|                name|movieid|\n",
            "+---+--------------------+-------+\n",
            "|  1|           Eric Dane|      2|\n",
            "|  2|        Adam Baldwin|      2|\n",
            "|  3|     Charles Parnell|      2|\n",
            "|  4|         Billy Burke|      6|\n",
            "|  5|    Tracy Spiridakos|      6|\n",
            "|  6|  Giancarlo Esposito|      6|\n",
            "|  7|        Damon Wayans|      1|\n",
            "|  8|     Clayne Crawford|      1|\n",
            "|  9|      Joseph Fiennes|      5|\n",
            "| 10|      Jack Davenport|      5|\n",
            "| 11|    Zachary Knighton|      5|\n",
            "| 12|        Jim Caviezel|      3|\n",
            "| 13|     Michael Emerson|      3|\n",
            "| 14|    Taraji P. Henson|      3|\n",
            "| 15|      Peter Dinklage|      4|\n",
            "| 16|       Emilia Clarke|      4|\n",
            "| 17|       Kit Harington|      4|\n",
            "| 18|Nikolaj Coster-Wa...|      4|\n",
            "| 19|     Ahmet Demirelli|   null|\n",
            "| 20|       Josh Holloway|      7|\n",
            "+---+--------------------+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sDuxMUg6Mdyr",
        "outputId": "abfd4fed-d11e-497d-88d7-94dd51fcace2"
      },
      "source": [
        "joinedDF = actorsDF.join(seriesDF,'movieid')\n",
        "joinedDF.show(40)"
      ],
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+---+--------------------+------------------+\n",
            "|movieid| id|                name|         moviename|\n",
            "+-------+---+--------------------+------------------+\n",
            "|      2|  1|           Eric Dane|     The Last Ship|\n",
            "|      2|  2|        Adam Baldwin|     The Last Ship|\n",
            "|      2|  3|     Charles Parnell|     The Last Ship|\n",
            "|      6|  4|         Billy Burke|        Revolution|\n",
            "|      6|  5|    Tracy Spiridakos|        Revolution|\n",
            "|      6|  6|  Giancarlo Esposito|        Revolution|\n",
            "|      1|  7|        Damon Wayans|     Lethal Weapon|\n",
            "|      1|  8|     Clayne Crawford|     Lethal Weapon|\n",
            "|      5|  9|      Joseph Fiennes|      Flashforward|\n",
            "|      5| 10|      Jack Davenport|      Flashforward|\n",
            "|      5| 11|    Zachary Knighton|      Flashforward|\n",
            "|      3| 12|        Jim Caviezel|Person of Interest|\n",
            "|      3| 13|     Michael Emerson|Person of Interest|\n",
            "|      3| 14|    Taraji P. Henson|Person of Interest|\n",
            "|      4| 15|      Peter Dinklage|   Game of Thrones|\n",
            "|      4| 16|       Emilia Clarke|   Game of Thrones|\n",
            "|      4| 17|       Kit Harington|   Game of Thrones|\n",
            "|      4| 18|Nikolaj Coster-Wa...|   Game of Thrones|\n",
            "|      7| 20|       Josh Holloway|              Lost|\n",
            "|      7| 21|    Evangeline Lilly|              Lost|\n",
            "|      7| 22|         Matthew Fox|              Lost|\n",
            "+-------+---+--------------------+------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yXmI-FaUMt4X",
        "outputId": "e632ab45-6023-4874-efee-ebe9abe91810"
      },
      "source": [
        "joinedDF = actorsDF.join(seriesDF,'movieid','left')\n",
        "joinedDF.show(40)"
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+---+--------------------+------------------+\n",
            "|movieid| id|                name|         moviename|\n",
            "+-------+---+--------------------+------------------+\n",
            "|      2|  1|           Eric Dane|     The Last Ship|\n",
            "|      2|  2|        Adam Baldwin|     The Last Ship|\n",
            "|      2|  3|     Charles Parnell|     The Last Ship|\n",
            "|      6|  4|         Billy Burke|        Revolution|\n",
            "|      6|  5|    Tracy Spiridakos|        Revolution|\n",
            "|      6|  6|  Giancarlo Esposito|        Revolution|\n",
            "|      1|  7|        Damon Wayans|     Lethal Weapon|\n",
            "|      1|  8|     Clayne Crawford|     Lethal Weapon|\n",
            "|      5|  9|      Joseph Fiennes|      Flashforward|\n",
            "|      5| 10|      Jack Davenport|      Flashforward|\n",
            "|      5| 11|    Zachary Knighton|      Flashforward|\n",
            "|      3| 12|        Jim Caviezel|Person of Interest|\n",
            "|      3| 13|     Michael Emerson|Person of Interest|\n",
            "|      3| 14|    Taraji P. Henson|Person of Interest|\n",
            "|      4| 15|      Peter Dinklage|   Game of Thrones|\n",
            "|      4| 16|       Emilia Clarke|   Game of Thrones|\n",
            "|      4| 17|       Kit Harington|   Game of Thrones|\n",
            "|      4| 18|Nikolaj Coster-Wa...|   Game of Thrones|\n",
            "|   null| 19|     Ahmet Demirelli|              null|\n",
            "|      7| 20|       Josh Holloway|              Lost|\n",
            "|      7| 21|    Evangeline Lilly|              Lost|\n",
            "|      7| 22|         Matthew Fox|              Lost|\n",
            "+-------+---+--------------------+------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cCxDoU9gM03t",
        "outputId": "8b4feaa7-b694-4b59-fef8-7d02ef0e546f"
      },
      "source": [
        "joinedDF = actorsDF.join(seriesDF,'movieid','right')\n",
        "joinedDF.show(40)"
      ],
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+----+--------------------+------------------+\n",
            "|movieid|  id|                name|         moviename|\n",
            "+-------+----+--------------------+------------------+\n",
            "|      1|   8|     Clayne Crawford|     Lethal Weapon|\n",
            "|      1|   7|        Damon Wayans|     Lethal Weapon|\n",
            "|      2|   3|     Charles Parnell|     The Last Ship|\n",
            "|      2|   2|        Adam Baldwin|     The Last Ship|\n",
            "|      2|   1|           Eric Dane|     The Last Ship|\n",
            "|      3|  14|    Taraji P. Henson|Person of Interest|\n",
            "|      3|  13|     Michael Emerson|Person of Interest|\n",
            "|      3|  12|        Jim Caviezel|Person of Interest|\n",
            "|      4|  18|Nikolaj Coster-Wa...|   Game of Thrones|\n",
            "|      4|  17|       Kit Harington|   Game of Thrones|\n",
            "|      4|  16|       Emilia Clarke|   Game of Thrones|\n",
            "|      4|  15|      Peter Dinklage|   Game of Thrones|\n",
            "|      5|  11|    Zachary Knighton|      Flashforward|\n",
            "|      5|  10|      Jack Davenport|      Flashforward|\n",
            "|      5|   9|      Joseph Fiennes|      Flashforward|\n",
            "|      6|   6|  Giancarlo Esposito|        Revolution|\n",
            "|      6|   5|    Tracy Spiridakos|        Revolution|\n",
            "|      6|   4|         Billy Burke|        Revolution|\n",
            "|      7|  22|         Matthew Fox|              Lost|\n",
            "|      7|  21|    Evangeline Lilly|              Lost|\n",
            "|      7|  20|       Josh Holloway|              Lost|\n",
            "|      8|null|                null|  Leyle ile Mecnun|\n",
            "+-------+----+--------------------+------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "i1lGVvXDNIyF",
        "outputId": "b5faf8fb-863a-4597-fe24-be6167adbe86"
      },
      "source": [
        "joinedDF = actorsDF.join(seriesDF,'movieid','outer')\n",
        "joinedDF.show(40)"
      ],
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+----+--------------------+------------------+\n",
            "|movieid|  id|                name|         moviename|\n",
            "+-------+----+--------------------+------------------+\n",
            "|   null|  19|     Ahmet Demirelli|              null|\n",
            "|      1|   7|        Damon Wayans|     Lethal Weapon|\n",
            "|      1|   8|     Clayne Crawford|     Lethal Weapon|\n",
            "|      6|   4|         Billy Burke|        Revolution|\n",
            "|      6|   5|    Tracy Spiridakos|        Revolution|\n",
            "|      6|   6|  Giancarlo Esposito|        Revolution|\n",
            "|      3|  12|        Jim Caviezel|Person of Interest|\n",
            "|      3|  13|     Michael Emerson|Person of Interest|\n",
            "|      3|  14|    Taraji P. Henson|Person of Interest|\n",
            "|      5|   9|      Joseph Fiennes|      Flashforward|\n",
            "|      5|  10|      Jack Davenport|      Flashforward|\n",
            "|      5|  11|    Zachary Knighton|      Flashforward|\n",
            "|      4|  15|      Peter Dinklage|   Game of Thrones|\n",
            "|      4|  16|       Emilia Clarke|   Game of Thrones|\n",
            "|      4|  17|       Kit Harington|   Game of Thrones|\n",
            "|      4|  18|Nikolaj Coster-Wa...|   Game of Thrones|\n",
            "|      8|null|                null|  Leyle ile Mecnun|\n",
            "|      7|  20|       Josh Holloway|              Lost|\n",
            "|      7|  21|    Evangeline Lilly|              Lost|\n",
            "|      7|  22|         Matthew Fox|              Lost|\n",
            "|      2|   1|           Eric Dane|     The Last Ship|\n",
            "|      2|   2|        Adam Baldwin|     The Last Ship|\n",
            "|      2|   3|     Charles Parnell|     The Last Ship|\n",
            "+-------+----+--------------------+------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hplefSIiNI3i",
        "outputId": "99a8aceb-8d63-4d0b-e9ad-a12079ccb826"
      },
      "source": [
        "actorsDF.createOrReplaceTempView('actors') #bu iki kod sanki db table yaratmıs gibi\n",
        "seriesDF.createOrReplaceTempView('series')\n",
        "sqlDF = spark.sql('select a.*,s.* from actors a full outer  join series s on s.movieid=a.movieid')\n",
        "sqlDF.show(40)"
      ],
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+--------------------+-------+-------+------------------+\n",
            "|  id|                name|movieid|movieid|         moviename|\n",
            "+----+--------------------+-------+-------+------------------+\n",
            "|  19|     Ahmet Demirelli|   null|   null|              null|\n",
            "|   7|        Damon Wayans|      1|      1|     Lethal Weapon|\n",
            "|   8|     Clayne Crawford|      1|      1|     Lethal Weapon|\n",
            "|   4|         Billy Burke|      6|      6|        Revolution|\n",
            "|   5|    Tracy Spiridakos|      6|      6|        Revolution|\n",
            "|   6|  Giancarlo Esposito|      6|      6|        Revolution|\n",
            "|  12|        Jim Caviezel|      3|      3|Person of Interest|\n",
            "|  13|     Michael Emerson|      3|      3|Person of Interest|\n",
            "|  14|    Taraji P. Henson|      3|      3|Person of Interest|\n",
            "|   9|      Joseph Fiennes|      5|      5|      Flashforward|\n",
            "|  10|      Jack Davenport|      5|      5|      Flashforward|\n",
            "|  11|    Zachary Knighton|      5|      5|      Flashforward|\n",
            "|  15|      Peter Dinklage|      4|      4|   Game of Thrones|\n",
            "|  16|       Emilia Clarke|      4|      4|   Game of Thrones|\n",
            "|  17|       Kit Harington|      4|      4|   Game of Thrones|\n",
            "|  18|Nikolaj Coster-Wa...|      4|      4|   Game of Thrones|\n",
            "|null|                null|   null|      8|  Leyle ile Mecnun|\n",
            "|  20|       Josh Holloway|      7|      7|              Lost|\n",
            "|  21|    Evangeline Lilly|      7|      7|              Lost|\n",
            "|  22|         Matthew Fox|      7|      7|              Lost|\n",
            "|   1|           Eric Dane|      2|      2|     The Last Ship|\n",
            "|   2|        Adam Baldwin|      2|      2|     The Last Ship|\n",
            "|   3|     Charles Parnell|      2|      2|     The Last Ship|\n",
            "+----+--------------------+-------+-------+------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gT5m-zq4RSwL",
        "outputId": "27a57d83-c754-4a4f-f0ac-0ad63fd41f09"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "from pyspark.sql.functions import UserDefinedFunction\n",
        "from pyspark.sql.types import StringType\n",
        "\n",
        "spark = SparkSession.Builder().appName('Spark DataFrame Introduction').getOrCreate()\n",
        "\n",
        "actorsDF = spark.read.option('inferSchema','true')\\\n",
        "                        .option('header','true')\\\n",
        "                        .option('delimiter',',').csv('join-actors.txt')\n",
        "\n",
        "seriesDF = spark.read.option('inferSchema','true')\\\n",
        "                        .option('header','true')\\\n",
        "                        .option('delimiter',',').csv('join-series.txt')\n",
        "\n",
        "seriesDF.show()\n",
        "actorsDF.show()\n",
        "\n",
        "joinedDF = actorsDF.join(seriesDF,'movieid','outer')\n",
        "joinedDF.show(40)\n",
        "\n",
        "actorsDF.createOrReplaceTempView('actors') #bu iki kod sanki db table yaratmıs gibi\n",
        "seriesDF.createOrReplaceTempView('series')\n",
        "sqlDF = spark.sql('select a.*,s.* from actors a full outer  join series s on s.movieid=a.movieid')\n",
        "sqlDF.show(40)\n",
        " "
      ],
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+------------------+\n",
            "|movieid|         moviename|\n",
            "+-------+------------------+\n",
            "|      1|     Lethal Weapon|\n",
            "|      2|     The Last Ship|\n",
            "|      3|Person of Interest|\n",
            "|      4|   Game of Thrones|\n",
            "|      5|      Flashforward|\n",
            "|      6|        Revolution|\n",
            "|      7|              Lost|\n",
            "|      8|  Leyle ile Mecnun|\n",
            "+-------+------------------+\n",
            "\n",
            "+---+--------------------+-------+\n",
            "| id|                name|movieid|\n",
            "+---+--------------------+-------+\n",
            "|  1|           Eric Dane|      2|\n",
            "|  2|        Adam Baldwin|      2|\n",
            "|  3|     Charles Parnell|      2|\n",
            "|  4|         Billy Burke|      6|\n",
            "|  5|    Tracy Spiridakos|      6|\n",
            "|  6|  Giancarlo Esposito|      6|\n",
            "|  7|        Damon Wayans|      1|\n",
            "|  8|     Clayne Crawford|      1|\n",
            "|  9|      Joseph Fiennes|      5|\n",
            "| 10|      Jack Davenport|      5|\n",
            "| 11|    Zachary Knighton|      5|\n",
            "| 12|        Jim Caviezel|      3|\n",
            "| 13|     Michael Emerson|      3|\n",
            "| 14|    Taraji P. Henson|      3|\n",
            "| 15|      Peter Dinklage|      4|\n",
            "| 16|       Emilia Clarke|      4|\n",
            "| 17|       Kit Harington|      4|\n",
            "| 18|Nikolaj Coster-Wa...|      4|\n",
            "| 19|     Ahmet Demirelli|   null|\n",
            "| 20|       Josh Holloway|      7|\n",
            "+---+--------------------+-------+\n",
            "only showing top 20 rows\n",
            "\n",
            "+-------+----+--------------------+------------------+\n",
            "|movieid|  id|                name|         moviename|\n",
            "+-------+----+--------------------+------------------+\n",
            "|   null|  19|     Ahmet Demirelli|              null|\n",
            "|      1|   7|        Damon Wayans|     Lethal Weapon|\n",
            "|      1|   8|     Clayne Crawford|     Lethal Weapon|\n",
            "|      6|   4|         Billy Burke|        Revolution|\n",
            "|      6|   5|    Tracy Spiridakos|        Revolution|\n",
            "|      6|   6|  Giancarlo Esposito|        Revolution|\n",
            "|      3|  12|        Jim Caviezel|Person of Interest|\n",
            "|      3|  13|     Michael Emerson|Person of Interest|\n",
            "|      3|  14|    Taraji P. Henson|Person of Interest|\n",
            "|      5|   9|      Joseph Fiennes|      Flashforward|\n",
            "|      5|  10|      Jack Davenport|      Flashforward|\n",
            "|      5|  11|    Zachary Knighton|      Flashforward|\n",
            "|      4|  15|      Peter Dinklage|   Game of Thrones|\n",
            "|      4|  16|       Emilia Clarke|   Game of Thrones|\n",
            "|      4|  17|       Kit Harington|   Game of Thrones|\n",
            "|      4|  18|Nikolaj Coster-Wa...|   Game of Thrones|\n",
            "|      8|null|                null|  Leyle ile Mecnun|\n",
            "|      7|  20|       Josh Holloway|              Lost|\n",
            "|      7|  21|    Evangeline Lilly|              Lost|\n",
            "|      7|  22|         Matthew Fox|              Lost|\n",
            "|      2|   1|           Eric Dane|     The Last Ship|\n",
            "|      2|   2|        Adam Baldwin|     The Last Ship|\n",
            "|      2|   3|     Charles Parnell|     The Last Ship|\n",
            "+-------+----+--------------------+------------------+\n",
            "\n",
            "+----+--------------------+-------+-------+------------------+\n",
            "|  id|                name|movieid|movieid|         moviename|\n",
            "+----+--------------------+-------+-------+------------------+\n",
            "|  19|     Ahmet Demirelli|   null|   null|              null|\n",
            "|   7|        Damon Wayans|      1|      1|     Lethal Weapon|\n",
            "|   8|     Clayne Crawford|      1|      1|     Lethal Weapon|\n",
            "|   4|         Billy Burke|      6|      6|        Revolution|\n",
            "|   5|    Tracy Spiridakos|      6|      6|        Revolution|\n",
            "|   6|  Giancarlo Esposito|      6|      6|        Revolution|\n",
            "|  12|        Jim Caviezel|      3|      3|Person of Interest|\n",
            "|  13|     Michael Emerson|      3|      3|Person of Interest|\n",
            "|  14|    Taraji P. Henson|      3|      3|Person of Interest|\n",
            "|   9|      Joseph Fiennes|      5|      5|      Flashforward|\n",
            "|  10|      Jack Davenport|      5|      5|      Flashforward|\n",
            "|  11|    Zachary Knighton|      5|      5|      Flashforward|\n",
            "|  15|      Peter Dinklage|      4|      4|   Game of Thrones|\n",
            "|  16|       Emilia Clarke|      4|      4|   Game of Thrones|\n",
            "|  17|       Kit Harington|      4|      4|   Game of Thrones|\n",
            "|  18|Nikolaj Coster-Wa...|      4|      4|   Game of Thrones|\n",
            "|null|                null|   null|      8|  Leyle ile Mecnun|\n",
            "|  20|       Josh Holloway|      7|      7|              Lost|\n",
            "|  21|    Evangeline Lilly|      7|      7|              Lost|\n",
            "|  22|         Matthew Fox|      7|      7|              Lost|\n",
            "|   1|           Eric Dane|      2|      2|     The Last Ship|\n",
            "|   2|        Adam Baldwin|      2|      2|     The Last Ship|\n",
            "|   3|     Charles Parnell|      2|      2|     The Last Ship|\n",
            "+----+--------------------+-------+-------+------------------+\n",
            "\n"
          ]
        }
      ]
    }
  ]
}