Anime_Subs.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Anime Subs.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_SzL9oazdDfU"
      },
      "source": [
        "# Setup"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RCahRXW6utwr",
        "outputId": "e2100676-37e6-4eb3-ec3e-b24e015edfc0"
      },
      "source": [
        "!git clone https://github.com/harikc456/anime-subs-mapping.git"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Cloning into 'anime-subs-mapping'...\n",
            "remote: Enumerating objects: 1183, done.\u001b[K\n",
            "remote: Counting objects: 100% (1183/1183), done.\u001b[K\n",
            "remote: Compressing objects: 100% (1110/1110), done.\u001b[K\n",
            "remote: Total 1183 (delta 165), reused 1091 (delta 73), pack-reused 0\u001b[K\n",
            "Receiving objects: 100% (1183/1183), 9.23 MiB | 18.72 MiB/s, done.\n",
            "Resolving deltas: 100% (165/165), done.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YyAJnfNIqsKH"
      },
      "source": [
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "!wget -q http://archive.apache.org/dist/spark/spark-3.2.0/spark-3.2.0-bin-hadoop3.2.tgz\n",
        "!tar xf spark-3.2.0-bin-hadoop3.2.tgz\n",
        "!pip install -q findspark"
      ],
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DFZH9WokraOt"
      },
      "source": [
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.2.0-bin-hadoop3.2\""
      ],
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 217
        },
        "id": "xLVJvVG2rL-U",
        "outputId": "d984bd82-f25a-4c41-c3dd-a19ce6e6dbe4"
      },
      "source": [
        "import findspark\n",
        "findspark.init()\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.master(\"local[*]\").getOrCreate()\n",
        "spark.conf.set(\"spark.sql.repl.eagerEval.enabled\", True)\n",
        "spark"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "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://850ac3d04e6f:4040\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v3.2.0</code></dd>\n",
              "              <dt>Master</dt>\n",
              "                <dd><code>local[*]</code></dd>\n",
              "              <dt>AppName</dt>\n",
              "                <dd><code>pyspark-shell</code></dd>\n",
              "            </dl>\n",
              "        </div>\n",
              "        \n",
              "            </div>\n",
              "        "
            ],
            "text/plain": [
              "<pyspark.sql.session.SparkSession at 0x7f1e3b943510>"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NEO2qVw8Gjbu"
      },
      "source": [
        "# Read data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fX4v2nMhrPcE"
      },
      "source": [
        "from pyspark.sql.types import *\n",
        "from pyspark.sql.window import Window\n",
        "from pyspark.sql.functions import input_file_name, split, col, size, rank, \\\n",
        "col, levenshtein, length, regexp_replace, trim"
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kfE4VIuBAHPv"
      },
      "source": [
        "labels = [('en_text', StringType()),\n",
        "          ('jp_text', StringType()),\n",
        "          ('google_translated', StringType()),\n",
        "          ('score', DoubleType())]\n",
        "schema = StructType([StructField(x[0], x[1], True) for x in labels])\n",
        "\n",
        "df = spark.read.format('csv') \\\n",
        "    .option(\"quote\", \"\\\"\") \\\n",
        "    .option(\"escape\", \"\\\"\") \\\n",
        "    .option(\"multiLine\",\"true\")\\\n",
        "    .option(\"header\", True) \\\n",
        "    .schema(schema) \\\n",
        "    .load(\"/content/anime-subs-mapping/subs/*.csv\")"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pnfSG8_wATcA"
      },
      "source": [
        "# Add filename\n",
        "df = df.withColumn(\"path\", input_file_name())\n",
        "df = df.withColumn(\"path_splitted\", split(\"path\",\"/\"))\n",
        "df = df.withColumn(\"filename\", col(\"path_splitted\")[size(\"path_splitted\")-1])\n",
        "df = df.drop(\"path\").drop(\"path_splitted\")"
      ],
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "UMLGzzsU2jAq",
        "outputId": "c4bbc64e-2acd-4fd6-b35b-46967bcb2eda"
      },
      "source": [
        "df.printSchema()"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- en_text: string (nullable = true)\n",
            " |-- jp_text: string (nullable = true)\n",
            " |-- google_translated: string (nullable = true)\n",
            " |-- score: double (nullable = true)\n",
            " |-- filename: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ym-R_NVuGq7W"
      },
      "source": [
        "# Cleansing"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B8Y6QXD3sqeA",
        "outputId": "4b266fa1-d860-43f9-99c6-e2624c3a3de3"
      },
      "source": [
        "df.createOrReplaceTempView(\"subs\")\n",
        "print(df.count())"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "149167\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0zhFd528Gy-0"
      },
      "source": [
        "## Remove/Merge duplicate jp_text to eng_text mappings"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aNCWaz_0FH6i",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c5260869-1d15-4e68-b020-6d3b15562c76"
      },
      "source": [
        "# Same as SQL code, but using spark functions\n",
        "# window = Window.partitionBy(df['jp_text']).orderBy(df['score'].desc())\n",
        "# de_dup_df = df.select('*', rank().over(window).alias('rank')).filter(col('rank')==1)\n",
        "df = spark.sql(\"select *,RANK() OVER (PARTITION BY jp_text ORDER BY score) AS RANK from subs\")\n",
        "df = df.filter(col(\"RANK\")==1).dropDuplicates(['jp_text']).drop(\"RANK\")\n",
        "print(df.count())"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "140166\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_P7vFn42pINQ"
      },
      "source": [
        "## Remove rows where en_text has less than 3 characters length"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wyq6YwxSrjNZ",
        "outputId": "71dbccaf-671c-47a4-d167-2b11dc1af8c8"
      },
      "source": [
        "df = df.filter(length(col('en_text'))>3)\n",
        "print(df.count())"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "139773\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "juwFlNGmsgrh"
      },
      "source": [
        "## Remove rows with score 0 and number of words greater than 1"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "l1TQmh9GsiAe",
        "outputId": "6e1b9bdb-1d65-4e0b-f9ef-37db720ec9bb"
      },
      "source": [
        "df = df.filter((col('score')!=0.0) | ((col('score')==0.0) & (size(split(col('en_text'), ' '))<=1)))\n",
        "print(df.count())"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "130349\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0kTSWFdQtwqC"
      },
      "source": [
        "## Remove words inside {} and <> in jp_text and en_text"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HBc-5roUtzY3",
        "outputId": "36c5129a-3e8d-4508-dbbc-e95ecf1ac7d8"
      },
      "source": [
        "print(df.where('en_text like \"%{%}%\"').count())\n",
        "print(df.where('jp_text like \"%{%}%\"').count())\n",
        "print(df.where('en_text like \"%<%>%\"').count())\n",
        "print(df.where('jp_text like \"%<%>%\"').count())\n",
        "print(df.select(\"en_text\").where('jp_text == \"OKってこと？\"').show(truncate=False))\n",
        "print(df.select(\"jp_text\").where('en_text == \"No no!\"').show(truncate=False))"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "0\n",
            "2852\n",
            "1138\n",
            "186\n",
            "+---------------------------+\n",
            "|en_text                    |\n",
            "+---------------------------+\n",
            "|<i>... it's all right?!</i>|\n",
            "+---------------------------+\n",
            "\n",
            "None\n",
            "+-------------+\n",
            "|jp_text      |\n",
            "+-------------+\n",
            "|{\\a6}いやいや|\n",
            "+-------------+\n",
            "\n",
            "None\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OV12CmIM3UN2"
      },
      "source": [
        "df = df.withColumn('en_text', trim(regexp_replace('en_text', r'\\{.*?\\}', ''))) \\\n",
        "       .withColumn('jp_text', trim(regexp_replace('jp_text', r'\\{.*?\\}', ''))) \\\n",
        "       .withColumn('en_text', trim(regexp_replace('en_text', r'<.*?>', ''))) \\\n",
        "       .withColumn('jp_text', trim(regexp_replace('jp_text', r'<.*?>', '')))"
      ],
      "execution_count": 26,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2J5wPWeI3XFE",
        "outputId": "902a632e-319f-4f1c-fcf8-923265c91aed"
      },
      "source": [
        "print(df.where('en_text like \"%{%}%\"').count())\n",
        "print(df.where('jp_text like \"%{%}%\"').count())\n",
        "print(df.where('en_text like \"%<%>%\"').count())\n",
        "print(df.where('jp_text like \"%<%>%\"').count())\n",
        "print(df.select(\"en_text\").where('jp_text == \"OKってこと？\"').show(truncate=False))\n",
        "print(df.select(\"jp_text\").where('en_text == \"No no!\"').show(truncate=False))"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "0\n",
            "0\n",
            "0\n",
            "75\n",
            "+--------------------+\n",
            "|en_text             |\n",
            "+--------------------+\n",
            "|... it's all right?!|\n",
            "+--------------------+\n",
            "\n",
            "None\n",
            "+--------+\n",
            "|jp_text |\n",
            "+--------+\n",
            "|いやいや|\n",
            "+--------+\n",
            "\n",
            "None\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I1Votl1iq8un"
      },
      "source": [
        "# EDA"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dobN_xVx23-Y",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "aaf017cc-7a76-4e3f-cf11-1e6fcb9198e3"
      },
      "source": [
        "df.where(\"TRIM(LOWER(en_text))!=TRIM(LOWER(google_translated))\").show(50, False)"
      ],
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------------------------------------------------------------------------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------+----------------------------------+\n",
            "|en_text                                                                               |jp_text                                                       |google_translated                                                                          |score              |filename                          |\n",
            "+--------------------------------------------------------------------------------------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------+----------------------------------+\n",
            "|That'll be 1500 yen.                                                                  |\\t1500円になります                                            |It will be 1500 yen                                                                        |0.375              |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Oh, that action game where you fight by kicking?                                      |\\tあの蹴りで戦うアクションゲーム                              |Action game fighting with that kick                                                        |0.21428571428571427|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|There was an error in them.                                                           |\\tあれ 間違えがあったのよね                                   |There was a mistake                                                                        |0.2222222222222222 |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|If I perfect this, I'll definitely be a starter on the team!                          |\\tあんまり力になれないかもしれんけど                          |I may not be a power                                                                       |0.17647058823529413|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Miss Flat Chest.                                                                      |\\tいうじゃねえか フラットチェストが                           |It is not a flat chest                                                                     |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Did you hear a cat just now?                                                          |\\tいま 猫の声しなかった                                       |Now did not call a cat                                                                     |0.4                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|No really, you're normal!                                                             |\\tいや マジで普通ですよ 気にすることないです                  |No, I'm really normal I can't worry                                                        |0.3333333333333333 |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Ugh.                                                                                  |\\tいやだな                                                    |No longer                                                                                  |0.0                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|No, you're really helping me out.                                                     |\\tいや本当 うん すごい助かる                                  |No, it's really helpful                                                                    |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Where are you going, Flat Chest?                                                      |\\tおい どこ行く フラットチェスト                              |Flat chest going to go                                                                     |0.3                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|That's quite a scary story.                                                           |\\tおっかね話だな 俺が行こう                                   |I'm a story I'm going                                                                      |0.2                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Oh, it's been a while, Ringo.                                                         |\\tおや お久しぶりです 林檎ちゃん                              |It is a long time no see                                                                   |0.14285714285714285|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Sup.                                                                                  |\\tおーす                                                      |Hey                                                                                        |0.0                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|You should really fix your personality!                                               |\\tお前も本当そういう性格直したほうがいいって                  |You should just fix your character really                                                  |0.5555555555555556 |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|This is all your fault!                                                               |\\tこうなったのも全部お前が悪い                                |All you have made this happiness                                                           |0.2                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|All the high school boys around here                                                  |\\tここら一帯の男子高校生は羽原の名前を聞いただけて震えやがるぜ|Men's high school students in the band will be trembled by hearing the name of the feathers|0.15               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|he successfully became a starter                                                      |\\tこの悔しさをバネに 彼は見事レギュラー入りを果たした         |He played a regular regulatory in spring                                                   |0.2                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Lately,                                                                               |\\tこの間ね                                                    |This time                                                                                  |0.0                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|This is my secret move!                                                               |\\tこれが俺の奥義だ                                            |This is my mystery                                                                         |0.42857142857142855|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|How many has it been?                                                                 |\\tこれで何人目だ                                              |How many people are                                                                        |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Can't you let me off with this?                                                       |\\tこれで勘弁してくれないかな                                  |I wonder if this will forgive me                                                           |0.14285714285714285|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|I'm sorry.                                                                            |\\tごめん                                                      |sorry                                                                                      |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Daily Lives of High School Boys was brought to you by the following sp-sponsors...    |\\tご覧のスポンサーの提供でお送りし しました イェー ホー       |Jaho, which was sent by offer of sponsor of view                                           |0.13636363636363635|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|I'm sure he does!                                                                     |\\tしてねぇよ                                                  |I'm sorry                                                                                  |0.2857142857142857 |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|I have a favor to ask.                                                                |\\tすいません 頼みたいことがあるんですけど                     |I'm sorry I have something I want to ask                                                   |0.36363636363636365|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|You stupid bitch, leave that kind of stuff to us!                                     |\\tそういうことなら私たちに任せときなさい                      |If so, let's leave it to us                                                                |0.23529411764705882|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|There was supposedly a volleyball team called the Witches of the East a long time ago.|\\tそういえばさ 昔バレーボールに東洋の魔女ってのがいたんだって |Speaking of which ago, the Oriental witch was in the old-fashioned volleyball              |0.22727272727272727|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|You should do something about that yourself.                                          |\\tそうか それは自分で何とかすべきだよ                         |So, it should be something                                                                 |0.16666666666666666|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|I'll ask him.                                                                         |\\tそうだ クイズでもしましょう                                 |That's right.                                                                              |0.125              |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|And then...                                                                           |\\tそしたらですね 殴られたんです                               |Then I was beaten                                                                          |0.16666666666666666|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|That's it!                                                                            |\\tそれだ                                                      |It                                                                                         |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Later he\\Nnimbly\\Nretrieves it                                                        |\\tそれにバウンドさせる反射シュートだ                          |It is a reflective shot to bounce it                                                       |0.1111111111111111 |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Are you serious?                                                                      |\\tそれは本気で言ってるんですか                                |Do you really say that?                                                                    |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Please cut me some slack!                                                             |\\tそんな ちょっと勘弁してくださいよ                           |Please forgive me such a little                                                            |0.2                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Takahiro!                                                                             |\\tたかひろ君                                                  |Hiro-kun                                                                                   |0.0                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|I wouldn't mind some magical boys.                                                    |\\tだから 魔法少女って言ってんじゃ                             |So I say a magic girl                                                                      |0.07692307692307693|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Hey, Mitsuo!                                                                          |\\tちょ ミツオ君                                               |Cho Mitsuo                                                                                 |0.2                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Yeah.                                                                                 |\\tちょっとね                                                  |A little bit                                                                               |0.0                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Wait a minute.                                                                        |\\tちょっと待って                                              |Wait a minute                                                                              |0.75               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|But it looks like I forgot to contact your group.                                     |\\tどうもあんたらグループだけ連絡忘れたみたい                  |It seems that only a group forgot to contact you                                           |0.3125             |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|What did you want him to say?                                                         |\\tな な 何言ってんのよあんたは                                |What's wrong?                                                                              |0.2                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Let's become people who can say \"No!\"                                                 |\\tなあ NOと言える人間になろうぜ                               |Let's become a person who can say NO                                                       |0.5384615384615384 |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Nope.                                                                                 |\\tないな                                                      |Not                                                                                        |0.0                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|What's wrong?                                                                         |\\tもう なんなんのよ                                           |What's more                                                                                |0.4                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|That's not what I mean!                                                               |\\tもう人を助けないと誓え 分かった                             |I sweared that I could not help people anymore                                             |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Perhaps it's better for her to leave town.                                            |\\tもう町を出たほうがいいかもね                                |It may be better to leave the town anymore                                                 |0.35714285714285715|Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|I shouldn't have said that.                                                           |\\tやばい 変なこと言っちゃったな                               |I said that it was a big deal                                                              |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|I don't really know.                                                                  |\\tよくわかんないけど                                          |I do not know well                                                                         |0.375              |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|Do you think I'm tiny?                                                                |\\tわたしって 小さいと思う                                     |I think it is small                                                                        |0.2                |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "|They don't cosplay in the Olympics!                                                   |\\tオリンピックでコスプレしないでしょう                        |I will not cosplay at the Olympics                                                         |0.25               |Danshi_Koukousei_no_Nichijou-6.csv|\n",
            "+--------------------------------------------------------------------------------------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------+-------------------+----------------------------------+\n",
            "only showing top 50 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MutEHpLti6nZ",
        "outputId": "bc2d317c-5997-4bf4-a820-acf5b1126a6d"
      },
      "source": [
        "df.withColumn(\"levenshtein\", levenshtein(col(\"en_text\"), col(\"google_translated\"))).select(\"en_text\", \"google_translated\",\"levenshtein\").filter(col(\"levenshtein\")<5).show(50,False)"
      ],
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------------------------+-------------------------+-----------+\n",
            "|en_text                      |google_translated        |levenshtein|\n",
            "+-----------------------------+-------------------------+-----------+\n",
            "|Sup.                         |Hey                      |4          |\n",
            "|Wait a minute.               |Wait a minute            |1          |\n",
            "|Nope.                        |Not                      |3          |\n",
            "|Meow.                        |Merm                     |3          |\n",
            "|Is she a monster?            |Is it a monster?         |3          |\n",
            "|Yoshitake!                   |Yoshitake                |1          |\n",
            "|Did something happen?        |Did something happen     |1          |\n",
            "|Excuse me.                   |Excuse me                |1          |\n",
            "|I was confessed to at school.|I was confessed at school|4          |\n",
            "|Is it nearby?                |Is it nearby?            |0          |\n",
            "|Yuuki!                       |Yabe!                    |4          |\n",
            "|December.                    |December                 |1          |\n",
            "|18th Floor...                |18th floor ...           |2          |\n",
            "|3.5.                         |3.5                      |1          |\n",
            "|666.                         |666                      |1          |\n",
            "|About 87 times?              |About 87 times?          |0          |\n",
            "|Barbero...                   |Barbero                  |3          |\n",
            "|終了～！                     |BUT!                     |4          |\n",
            "|Excellent!                   |Excellent                |1          |\n",
            "|Fio?                         |Fio?                     |0          |\n",
            "|Frate?                       |Frate?                   |0          |\n",
            "|Ganzo.                       |Ganzo                    |1          |\n",
            "|Good morning, Don Orco!      |Good Morning Don Orco    |3          |\n",
            "|Happy?                       |HAPPY?                   |4          |\n",
            "|Hi Frau-chan.                |HI Flau-chan             |3          |\n",
            "|Hurry!                       |HURRY!                   |4          |\n",
            "|Lacrima?                     |Lacrima?                 |0          |\n",
            "|Lawless Heaven?              |Lawless Heaven           |1          |\n",
            "|Nero Vanetti...              |Nero Vanetti             |3          |\n",
            "|Nero Vanetti...              |Nero Vanetti ...         |1          |\n",
            "|TM armor!                    |TM armor                 |1          |\n",
            "|Vanetti...                   |Vanetti                  |3          |\n",
            "|Vanno Clemente.              |Vanno Clemente           |1          |\n",
            "|Vincente...                  |Vincente                 |3          |\n",
            "|A-TEC?                       |A-TEC ...                |4          |\n",
            "|Stop!                        |STOP!                    |3          |\n",
            "|Iris!                        |Iris!                    |0          |\n",
            "|Help me...                   |help ...                 |3          |\n",
            "|Help me!                     |help!                    |4          |\n",
            "|5 billion?                   |50 billion?!             |2          |\n",
            "|All systems go!              |All Systems GO!          |3          |\n",
            "|EECOM!                       |EECOM!                   |0          |\n",
            "|Kilo 02, check.              |kilo 02 Check            |4          |\n",
            "|Th-Thank you.                |Thank you                |4          |\n",
            "|Who is he?                   |What is he               |3          |\n",
            "|Him?                         |?                        |3          |\n",
            "|Thank you.                   |Thank you                |1          |\n",
            "|Thank you, Iris.             |thank you Iris           |3          |\n",
            "|What's that?                 |What is that?            |2          |\n",
            "|Huh?                         |?                        |3          |\n",
            "+-----------------------------+-------------------------+-----------+\n",
            "only showing top 50 rows\n",
            "\n"
          ]
        }
      ]
    }
  ]
}