APPeal Data cleaning v.2.0 - 3.01.22.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Let's Go!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To do:\n",
    "- Data Understanding\n",
    "- K-Means\n",
    "- Classificatore\n",
    "\n",
    "## Done:\n",
    "- Droppare ciò che non è Currency == USD \n",
    "- Convertire in float: Rating, Rating Count, Price\n",
    "- Convertire in bool: Editors Choice, In App Purchases, Ad Supported \n",
    "- Installs: Togliere i + alla fine e convertire in float \n",
    "- Minimum Android: Prendere i primi tre valori (e.g.: 4.2), convertire in float \n",
    "- Content Rating: Mettere in scala 0-4, convertire in int\n",
    "- Size: Togliere M, k, eventualmente G. Tutto deve essere in scala MB. Convertire in float\n",
    "- Rimuovere nan\n",
    "- FEATURE: Fixare data\n",
    "- FEATURE: stdev dalla media per categoria\n",
    "\n",
    "\n",
    "## Next:\n",
    "- Sperimentazioni di clustering / classificazioni\n",
    "- Applicazioni esoteriche\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from math import ceil\n",
    "\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from pyspark import SparkContext\n",
    "\n",
    "from pyspark.sql.functions import regexp_replace\n",
    "from pyspark.sql.functions import col\n",
    "from pyspark.sql.functions import udf\n",
    "from pyspark.sql.functions import from_unixtime\n",
    "from pyspark.sql.functions import unix_timestamp\n",
    "from pyspark.sql import functions as F\n",
    "\n",
    "from pyspark.sql.types import FloatType\n",
    "from pyspark.sql.types import BooleanType\n",
    "from pyspark.sql.types import IntegerType\n",
    "from pyspark.sql.types import DateType\n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "\n",
    "sc = SparkContext(appName=\"MY-APP-NAME\", master=\"local[*]\")\n",
    "sc.setLogLevel(\"ERROR\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pandas as pd\n",
    "\n",
    "# df = pd.read_csv(\"Google-Playstore.csv\")\n",
    "\n",
    "# df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.Size.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sizeConverter(value):\n",
    "    if value is not None:\n",
    "        if value[len(value)-1:] == 'M':\n",
    "            value = value.replace('M', '')\n",
    "            return float(value)\n",
    "        elif value[len(value)-1:] == 'G':\n",
    "            value = value.replace('G', '')\n",
    "            return float(value) * 1024\n",
    "        elif value[len(value)-1:] == 'k':\n",
    "            value = value.replace('k', '')\n",
    "            return float(value) / 1024\n",
    "    \n",
    "def timeConverter(value):\n",
    "    if value is not None:\n",
    "        if \"@\" in str(value):\n",
    "            return None\n",
    "        else:\n",
    "            d = datetime.strptime(value, '%b %d, %Y')\n",
    "            return(d.strftime('%Y-%m-%d'))\n",
    "        \n",
    "def getYear(value):\n",
    "    d = datetime.strptime(value, '%Y-%m-%d')\n",
    "    return int(d.strftime('%Y'))\n",
    "\n",
    "def getQuarter(value):\n",
    "    d = datetime.strptime(value, '%Y-%m-%d')\n",
    "    return ceil(int(d.strftime('%m'))/3)\n",
    "\n",
    "def calculate_stdev(group_feat, feat, dataframe):\n",
    "    avg_rating_feat = dataframe.groupBy(group_feat).agg(F.mean(feat).alias(\"temp\"))\n",
    "    data_avg = dataframe.join(avg_rating_feat, [group_feat], 'outer')\n",
    "    dataframe = data_avg.withColumn(str(feat) + \"_stdev_from_\"+ str(group_feat), (col(feat) - col(\"temp\")))\n",
    "    dataframe = dataframe.drop(\"temp\")\n",
    "    return dataframe\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Preparation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.ml import Pipeline\n",
    "from pyspark.ml.classification import RandomForestClassifier\n",
    "from pyspark.ml.feature import IndexToString, StringIndexer, VectorIndexer\n",
    "from pyspark.ml.evaluation import MulticlassClassificationEvaluator\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "spark = SparkSession.builder \\\n",
    "    .master(\"local\") \\\n",
    "    .appName(\"AppName\") \\\n",
    "    .getOrCreate()\n",
    "\n",
    "data = spark.read.option(\"header\",True).option('inferSchema', True).csv(\"Google-Playstore.csv\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#for col in data.columns:\n",
    " #   newcol = '_'.join(col.split(' '))\n",
    "  #  data = data.withColumnRenamed(col, newcol)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- App Name: string (nullable = true)\n",
      " |-- App Id: string (nullable = true)\n",
      " |-- Category: string (nullable = true)\n",
      " |-- Rating: string (nullable = true)\n",
      " |-- Rating Count: string (nullable = true)\n",
      " |-- Installs: string (nullable = true)\n",
      " |-- Minimum Installs: string (nullable = true)\n",
      " |-- Maximum Installs: string (nullable = true)\n",
      " |-- Free: string (nullable = true)\n",
      " |-- Price: string (nullable = true)\n",
      " |-- Currency: string (nullable = true)\n",
      " |-- Size: string (nullable = true)\n",
      " |-- Minimum Android: string (nullable = true)\n",
      " |-- Developer Id: string (nullable = true)\n",
      " |-- Developer Website: string (nullable = true)\n",
      " |-- Developer Email: string (nullable = true)\n",
      " |-- Released: string (nullable = true)\n",
      " |-- Last Updated: string (nullable = true)\n",
      " |-- Content Rating: string (nullable = true)\n",
      " |-- Privacy Policy: string (nullable = true)\n",
      " |-- Ad Supported: string (nullable = true)\n",
      " |-- In App Purchases: string (nullable = true)\n",
      " |-- Editors Choice: string (nullable = true)\n",
      " |-- Scraped Time: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "data.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------------------+--------------------+-----------------+------+------------+--------+----------------+----------------+----+-----+--------+----+---------------+--------------------+--------------------+--------------------+------------+------------+--------------+--------------------+------------+----------------+--------------+-------------------+\n",
      "|                          App Name|              App Id|         Category|Rating|Rating Count|Installs|Minimum Installs|Maximum Installs|Free|Price|Currency|Size|Minimum Android|        Developer Id|   Developer Website|     Developer Email|    Released|Last Updated|Content Rating|      Privacy Policy|Ad Supported|In App Purchases|Editors Choice|       Scraped Time|\n",
      "+----------------------------------+--------------------+-----------------+------+------------+--------+----------------+----------------+----+-----+--------+----+---------------+--------------------+--------------------+--------------------+------------+------------+--------------+--------------------+------------+----------------+--------------+-------------------+\n",
      "|                           Gakondo| com.ishakwe.gakondo|        Adventure|   0.0|           0|     10+|              10|              15|True|    0|     USD| 10M|     7.1 and up|Jean Confident Ir...|https://beniyizib...|jean21101999@gmai...|Feb 26, 2020|Feb 26, 2020|      Everyone|https://beniyizib...|       False|           False|         False|2021-06-15 20:19:35|\n",
      "|               Ampere Battery Info|com.webserveis.ba...|            Tools|   4.4|          64|  5,000+|            5000|            7662|True|    0|     USD|2.9M|     5.0 and up|          Webserveis|https://webservei...|webserveis@gmail.com|May 21, 2020|May 06, 2021|      Everyone|https://dev4phone...|        True|           False|         False|2021-06-15 20:19:35|\n",
      "|                            Vibook|com.doantiepvien.crm|     Productivity|   0.0|           0|     50+|              50|              58|True|    0|     USD|3.7M|   4.0.3 and up|          Cabin Crew|                null| vnacrewit@gmail.com| Aug 9, 2019|Aug 19, 2019|      Everyone|https://www.vietn...|       False|           False|         False|2021-06-15 20:19:35|\n",
      "|              Smart City Trichy...|cst.stJoseph.ug17...|    Communication|   5.0|           5|     10+|              10|              19|True|    0|     USD|1.8M|   4.0.3 and up| Climate Smart Tech2|http://www.climat...|climatesmarttech2...|Sep 10, 2018|Oct 13, 2018|      Everyone|                null|        True|           False|         False|2021-06-15 20:19:35|\n",
      "|                           GROW.me|com.horodyski.grower|            Tools|   0.0|           0|    100+|             100|             478|True|    0|     USD|6.2M|     4.1 and up|Rafal Milek-Horod...|http://www.horody...|rmilekhorodyski@g...|Feb 21, 2020|Nov 12, 2018|      Everyone|http://www.horody...|       False|           False|         False|2021-06-15 20:19:35|\n",
      "|                            IMOCCI|          com.imocci|           Social|   0.0|           0|     50+|              50|              89|True|    0|     USD| 46M|     6.0 and up|         Imocci GmbH|http://www.imocci...|     info@imocci.com|Dec 24, 2018|Dec 20, 2019|          Teen|https://www.imocc...|       False|            True|         False|2021-06-15 20:19:35|\n",
      "|              unlimited 4G data...|getfreedata.super...| Libraries & Demo|   4.5|          12|  1,000+|            1000|            2567|True|    0|     USD|2.5M|     4.1 and up|android developer779|                null|aitomgharfatimezz...|Sep 23, 2019|Sep 27, 2019|      Everyone|https://sites.goo...|        True|           False|         False|2021-06-15 20:19:35|\n",
      "|              The Everyday Cale...|com.mozaix.simone...|        Lifestyle|   2.0|          39|    500+|             500|             702|True|    0|     USD| 16M|     5.0 and up|          Mozaix LLC|                null|elementuser03@gma...|Jun 21, 2019|Jun 21, 2019|      Everyone|https://www.freep...|       False|           False|         False|2021-06-15 20:19:35|\n",
      "|                         WhatsOpen|   com.whatsopen.app|    Communication|   0.0|           0|     10+|              10|              18|True|    0|     USD|1.3M|     4.4 and up|Yilver Molina Hur...|http://yilvermoli...|yilver.mh1996@gma...|        null|Dec 07, 2018|          Teen|http://elcafedela...|       False|           False|         False|2021-06-15 20:19:35|\n",
      "|              Neon 3d Iron Tech...|com.ikeyboard.the...|  Personalization|   4.7|         820| 50,000+|           50000|           62433|True|    0|     USD|3.5M|     4.1 and up|Free 2021 Themes ...|https://trendytem...|trendyteme.888@gm...|Sep 22, 2019|Oct 07, 2020|      Everyone|http://bit.ly/Emo...|        True|           False|         False|2021-06-15 20:19:35|\n",
      "|                   Dodge The Cars!|com.MrScratchEnte...|           Racing|   4.9|          55|    100+|             100|             329|True|    0|     USD| 51M|     4.4 and up|           MrScratch|                null|mithalaarush@gmai...|Jul 30, 2020|Jul 30, 2020|      Everyone|https://docs.goog...|       False|           False|         False|2021-06-15 20:19:35|\n",
      "|                           Parents|com.eqra.android....|Maps & Navigation|   0.0|           0|    100+|             100|             330|True|    0|     USD|2.7M|     4.0 and up|           Eqra Tech|http://www.hafizq...|hhiyassat@eqratec...|Jan 10, 2018|Jun 27, 2018|      Everyone|https://docs.goog...|       False|           False|         False|2021-06-15 20:19:36|\n",
      "|桃園機場捷運時刻表 - 捷運轉乘路...|com.jieapp.taoyua...|   Travel & Local|   3.9|         118| 10,000+|           10000|           37763|True|    0|     USD|7.6M|     4.2 and up|             Jie App|https://jie-app.h...| jie.myapp@gmail.com| Apr 3, 2018|Jun 11, 2021|      Everyone|http://appsetting...|        True|           False|         False|2021-06-15 20:19:36|\n",
      "|                      be.MOBILISED|com.hastobe.bemob...|Maps & Navigation|   0.0|           0|    100+|             100|             274|True|    0|     USD| 15M|     6.0 and up|      has.to.be gmbh|http://www.has-to...|support@has-to-be...| Feb 9, 2020|May 14, 2021|      Everyone|https://has-to-be...|       False|           False|         False|2021-06-15 20:19:36|\n",
      "|                 Caliway Conductor|com.caliwayconduc...|   Travel & Local|   3.7|        1572| 10,000+|           10000|           42856|True|    0|     USD| 29M|     4.2 and up|             WayCali|https://www.faceb...|waycalicol@gmail.com| Sep 5, 2018|May 30, 2020|      Everyone|https://www.faceb...|       False|           False|         False|2021-06-15 20:19:36|\n",
      "|              Readymade Grocery...|com.ionicfirebase...|     Food & Drink|   4.2|          16|  1,000+|            1000|            4313|True|    0|     USD| 12M|     4.4 and up|     pietechsolution|https://www.ionic...|info@ionicfirebas...| Apr 5, 2020|Mar 23, 2021|      Everyone|https://www.ionic...|       False|           False|         False|2021-06-15 20:19:36|\n",
      "|              OTENTIK Discovery FR|com.camineo.otent...|   Travel & Local|   3.4|           5|    500+|             500|             949|True|    0|     USD|2.9M|     4.4 and up|     Hotels Attitude|http://www.hotels...|info@hotels-attit...|Nov 28, 2016|Oct 30, 2019|      Everyone|http://camineo.co...|       False|           False|         False|2021-06-15 20:19:36|\n",
      "|              All in one shoppi...|com.tkapplication...|            Tools|   5.0|           6|     50+|              50|              62|True|    0|     USD|2.0M|     4.1 and up|     TK Applications|                null|tkapplications001...|Apr 24, 2019|May 05, 2019|      Everyone|                null|       False|           False|         False|2021-06-15 20:19:36|\n",
      "|                    REDMOND  Robot|    com.grit.redmond|        Lifestyle|   3.7|         328| 10,000+|           10000|           31235|True|    0|     USD| 70M|     4.1 and up|     Grit Technology|                null|info@redmond.company| Jul 1, 2020|May 26, 2021|      Everyone|https://redmond.c...|       False|           False|         False|2021-06-15 20:19:36|\n",
      "|              Contemporary Love...|com.felingdev.low...|Books & Reference|   0.0|           0|      1+|               1|               2|True|    0|     XXX| 11M|     4.2 and up|    Human Droid Apps|                null|farukabdillah4@gm...|Dec 26, 2020|Mar 23, 2021|    Mature 17+|https://docs.goog...|        True|           False|         False|2021-06-15 20:19:36|\n",
      "+----------------------------------+--------------------+-----------------+------+------------+--------+----------------+----------------+----+-----+--------+----+---------------+--------------------+--------------------+--------------------+------------+------------+--------------+--------------------+------------+----------------+--------------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "data.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------------------+--------------------+------+--------+--------+-----+----+---------------+--------------------+---+------------+----------------+--------------+---------------+------------------+-------------------+----------------------+--------------------+--------------------------+----------------------------+----------------------------+-------------------------+------------------------+\n",
      "|     Category|            App Name|              App Id|Rating|nReviews|Installs|Price|Size|Minimum Android|        Developer Id|Age|Ad Supported|In App Purchases|Editors Choice|Year of Release|Quarter of Release|Year of Last Update|Reviews_Installs_Ratio|Rating_Reviews_Ratio|Rating_stdev_from_Category|nReviews_stdev_from_Category|Installs_stdev_from_Category|Price_stdev_from_Category|Size_stdev_from_Category|\n",
      "+-------------+--------------------+--------------------+------+--------+--------+-----+----+---------------+--------------------+---+------------+----------------+--------------+---------------+------------------+-------------------+----------------------+--------------------+--------------------------+----------------------------+----------------------------+-------------------------+------------------------+\n",
      "|Music & Audio|        Radio Louvor|hoostcomv2.francisco|   0.0|     0.0|   100.0|  0.0| 4.6|            5.0|               Hoost|  0|       false|           false|         false|           2020|                 1|               2021|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54183.57933449595|     -0.04038149630776619|     -13.434784809273202|\n",
      "|Music & Audio|Radio Estado de M...|com.radioestadode...|   4.7|    12.0|   500.0|  0.0|11.0|            4.2|Radio.app: FM AM ...|  2|        true|           false|         false|           2018|                 3|               2019|                 0.024|  0.3916666507720947|        2.4708291575954733|          -694.3832414491449|          -53783.57933449595|     -0.04038149630776619|     -7.0347848092732015|\n",
      "|Music & Audio|Quebec Radio Stat...|quebec.radiostations|   0.0|     0.0|     5.0|  0.0| 9.0|            4.1|            emmcapps|  2|        true|           false|         false|           2020|                 3|               2020|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54278.57933449595|     -0.04038149630776619|      -9.034784809273201|\n",
      "|Music & Audio|Thánh Ca - Thanh ...|com.chikik.jesusv...|   0.0|     0.0|  1000.0|  0.0|28.0|            4.2|        CHIKIK HOUSE|  0|        true|            true|         false|           2020|                 2|               2021|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -53283.57933449595|     -0.04038149630776619|       9.965215190726799|\n",
      "|Music & Audio|Non Stop Play UK ...|com.merakiapp.non...|   0.0|     0.0|     1.0|  0.0| 5.7|            4.4|           merakiapp|  2|        true|           false|         false|           2020|                 1|               2020|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54282.57933449595|     -0.04038149630776619|     -12.334784809273202|\n",
      "|Music & Audio|Emisoras Unidas 8...|com.appejulcotton...|   0.0|     0.0|    10.0|  0.0| 7.4|            4.4|      raquelgonzalez|  2|        true|           false|         false|           2019|                 4|               2019|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54273.57933449595|     -0.04038149630776619|     -10.634784809273201|\n",
      "|Music & Audio|Asif Habibi chans...|com.oatjohndrip.a...|   0.0|     0.0|   500.0|  0.0| 3.8|            4.1|        OatJohn Drip|  0|        true|           false|         false|           2020|                 1|               2020|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -53783.57933449595|     -0.04038149630776619|       -14.2347848092732|\n",
      "|Music & Audio|   Radio Netherlands|com.coworker.radi...|   0.0|     0.0|   100.0|  0.0| 4.2|            4.0|            coworker|  0|        true|           false|         false|           2017|                 1|               2017|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54183.57933449595|     -0.04038149630776619|     -13.834784809273202|\n",
      "|Music & Audio|   Festival Art Rock|com.greencopper.a...|   4.2|    35.0| 10000.0|  0.0|58.0|            5.0|Patron Technology...|  0|       false|           false|         false|           2015|                 2|               2019|                0.0035| 0.11999999455043248|        1.9708291575954733|          -671.3832414491449|          -44283.57933449595|     -0.04038149630776619|      39.965215190726795|\n",
      "|Music & Audio|Piano Orchestra S...|com.SugarDaddy.Pi...|   0.0|     0.0|  1000.0|  0.0|11.0|            2.2|Sugar Daddy Devel...|  0|        true|           false|         false|           2018|                 2|               2018|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -53283.57933449595|     -0.04038149630776619|     -7.0347848092732015|\n",
      "|Music & Audio|Red Bay Alabama R...|com.redbayalabama...|   0.0|     0.0|     1.0|  0.0| 5.6|            4.4|              henvel|  2|        true|           false|         false|           2020|                 3|               2020|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54282.57933449595|     -0.04038149630776619|     -12.434784809273202|\n",
      "|Music & Audio|sundarara kanda b...|sundarakanda.vipu...|   4.8|   127.0| 10000.0|  0.0|51.0|            4.0|            VSK apps|  0|        true|           false|         false|           2017|                 1|               2017|                0.0127|  0.0377952770924005|           2.5708295390652|          -579.3832414491449|          -44283.57933449595|     -0.04038149630776619|      32.965215190726795|\n",
      "|Music & Audio|       Radio of Life|     com.ktfj.player|   0.0|     0.0|    10.0|  0.0| 2.1|            6.0|Skagit Valley Fam...|  0|        true|           false|         false|           2017|                 2|               2019|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54273.57933449595|     -0.04038149630776619|     -15.934784809273202|\n",
      "|Music & Audio|Radio nuova san g...|com.duendeal.radi...|   0.0|     0.0|    10.0|  0.0| 6.2|            4.4|            duendeal|  2|        true|           false|         false|           2019|                 4|               2020|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54273.57933449595|     -0.04038149630776619|     -11.834784809273202|\n",
      "|Music & Audio|H. Muammar ZA Bim...|com.rika.noviana....|   4.3|   132.0| 10000.0|  0.0|69.0|            5.0| Rika Noviana Mobile|  0|        true|           false|         false|           2017|                 2|               2021|                0.0132| 0.03257575902071866|           2.0708295390652|          -574.3832414491449|          -44283.57933449595|     -0.04038149630776619|      50.965215190726795|\n",
      "|Music & Audio| Radio FM Zu Romania|com.radiosamfm.ra...|   0.0|     0.0|    50.0|  0.0| 7.5|            4.4|Radios, Images, R...|  0|        true|           false|         false|           2019|                 1|               2020|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54233.57933449595|     -0.04038149630776619|     -10.534784809273201|\n",
      "|Music & Audio|        Enigma Radio| com.app.enigmaradio|   0.0|     0.0|    50.0|  0.0| 5.4|            4.1|APPSTREAMING.NET ...|  0|       false|           false|         false|           2021|                 1|               2021|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -54233.57933449595|     -0.04038149630776619|     -12.634784809273201|\n",
      "|Music & Audio|Offline Guitar Ch...|com.chordgitarind...|   4.2|    47.0| 10000.0|  0.0| 6.6|            4.1|          Hexastudio|  2|        true|           false|         false|           2018|                 3|               2018|                0.0047| 0.08936169806947099|        1.9708291575954733|          -659.3832414491449|          -44283.57933449595|     -0.04038149630776619|     -11.434784809273202|\n",
      "|Music & Audio|            RePlayer|  com.ecler.replayer|   0.0|     0.0|  1000.0|  0.0| 4.9|            4.4|NEEC Audio Barcel...|  0|       false|           false|         false|           2018|                 4|               2018|                   0.0|                null|       -2.2291706516696634|          -706.3832414491449|          -53283.57933449595|     -0.04038149630776619|     -13.134784809273201|\n",
      "|Music & Audio|Philly's Favor 100.7|   com.w264bh.player|   5.0|    10.0|  1000.0|  0.0| 2.1|            6.0| JAM Media Solutions|  0|        true|           false|         false|           2020|                 2|               2020|                  0.01|                 0.5|        2.7708293483303366|          -696.3832414491449|          -53283.57933449595|     -0.04038149630776619|     -15.934784809273202|\n",
      "+-------------+--------------------+--------------------+------+--------+--------+-----+----+---------------+--------------------+---+------------+----------------+--------------+---------------+------------------+-------------------+----------------------+--------------------+--------------------------+----------------------------+----------------------------+-------------------------+------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Currency\n",
    "data = data.filter(data.Currency == 'USD')\n",
    "\n",
    "# Missing values mascherati\n",
    "data = data.withColumn(\"Content Rating\", regexp_replace(col(\"Content Rating\"), \"Unrated\", \"-1\"))\n",
    "data = data.withColumn(\"Minimum Android\", regexp_replace(col(\"Minimum Android\"), \"Varies with device\", \"0.0\"))\n",
    "data = data.fillna(\"0.0\", subset=\"Minimum Android\")\n",
    "\n",
    "#data = data.dropna(subset=['Content Rating', 'Minimum Android'])\n",
    "\n",
    "# Prepariamo Installs e Content Rating alla trasformazione in numeriche\n",
    "data = data.withColumn(\"Installs\", regexp_replace(col(\"Installs\"), \"\\+\", \"\"))\n",
    "data = data.withColumn(\"Installs\", regexp_replace(col(\"Installs\"), \",\", \"\"))\n",
    "data = data.withColumn(\"Rating\", regexp_replace(col(\"Rating\"), \",\", \"\"))\n",
    "data = data.withColumn(\"Size\", regexp_replace(col(\"Size\"), \",\", \".\"))\n",
    "\n",
    "#data = data.withColumn(\"Content Rating\", regexp_replace(col(\"Content Rating\"), \"-1\", \"-1\"))\n",
    "data = data.withColumn(\"Content Rating\", regexp_replace(col(\"Content Rating\"), \"Everyone\", \"0\"))\n",
    "data = data.withColumn(\"Content Rating\", regexp_replace(col(\"Content Rating\"), \"Everyone 10+\", \"1\"))\n",
    "data = data.withColumn(\"Content Rating\", regexp_replace(col(\"Content Rating\"), \"Teen\", \"2\"))\n",
    "data = data.withColumn(\"Content Rating\", regexp_replace(col(\"Content Rating\"), \"Mature 17+\", \"3\"))\n",
    "data = data.withColumn(\"Content Rating\", regexp_replace(col(\"Content Rating\"), \"Adults only 18+\", \"4\"))\n",
    "\n",
    "# Convertiamo in float...\n",
    "data = data.withColumn(\"Price\", data[\"Price\"].cast(FloatType()).alias(\"Price\"))\n",
    "data = data.withColumn(\"Rating\", data[\"Rating\"].cast(FloatType()).alias(\"Rating\"))\n",
    "data = data.withColumn(\"Rating Count\", data[\"Rating Count\"].cast(FloatType()).alias(\"Rating Count\"))\n",
    "data = data.withColumn(\"Installs\", data[\"Installs\"].cast(FloatType()).alias(\"Installs\"))\n",
    "data = data.withColumn(\"Minimum Android\", data[\"Minimum Android\"][0:3].cast(FloatType()).alias(\"Minimum Android\"))\n",
    "data = data.withColumn(\"Content Rating\", data[\"Content Rating\"].cast(IntegerType()).alias(\"Content Rating\"))\n",
    "\n",
    "#... e in boolean\n",
    "data = data.withColumn(\"Editors Choice\", data[\"Editors Choice\"].cast(BooleanType()).alias(\"Editors Choice\"))\n",
    "data = data.withColumn(\"In App Purchases\", data[\"In App Purchases\"].cast(BooleanType()).alias(\"In App Purchases\"))\n",
    "data = data.withColumn(\"Ad Supported\", data[\"Ad Supported\"].cast(BooleanType()).alias(\"Ad Supported\"))\n",
    "\n",
    "# Applichiamo sizeConverter e timeConverter\n",
    "data = data.withColumn(\"Size\", udf(sizeConverter)(\"Size\"))\n",
    "data = data.withColumn(\"Released\", udf(timeConverter)(\"Released\"))\n",
    "data = data.withColumn(\"Last Updated\", udf(timeConverter)(\"Last Updated\"))\n",
    "\n",
    "# Drop di colonne inutili\n",
    "data = data.drop('Free', 'Privacy Policy', 'Minimum Installs', 'Maximum Installs', 'Scraped Time', 'Currency', \n",
    "          'Developer Email', 'Developer Website')\n",
    "\n",
    "#Drop di missing values\n",
    "data = data.dropna(how='any')\n",
    "\n",
    "#Creazione di feature relative all'anno\n",
    "data = data.withColumn(\"Year of Release\", udf(getYear)(\"Released\"))\n",
    "data = data.withColumn(\"Quarter of Release\", udf(getQuarter)(\"Released\"))\n",
    "data = data.withColumn(\"Year of Last Update\", udf(getYear)(\"Last Updated\"))\n",
    "data = data.drop('Released', 'Last Updated')\n",
    "\n",
    "#rinominiamo due feat per la nostra sanità mentale\n",
    "data = data.withColumnRenamed('Content Rating', 'Age')\n",
    "data = data.withColumnRenamed('Rating Count', 'nReviews')\n",
    "\n",
    "#nuova feat\n",
    "data = data.withColumn(\"Reviews_Installs_Ratio\", (col(\"nReviews\") / col(\"Installs\")))\n",
    "data = data.withColumn(\"Rating_Reviews_Ratio\", (col(\"Rating\") / col(\"nReviews\")))\n",
    "data = data.na.drop(subset='Reviews_Installs_Ratio')\n",
    "\n",
    "#feature statistiche\n",
    "data = calculate_stdev(\"Category\", \"Rating\", data)\n",
    "data = calculate_stdev(\"Category\", \"nReviews\", data)\n",
    "data = calculate_stdev(\"Category\", \"Installs\", data)\n",
    "data = calculate_stdev(\"Category\", \"Price\", data)\n",
    "data = calculate_stdev(\"Category\", \"Size\", data)\n",
    "\n",
    "data.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2089852"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'block' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-12-5fcbd1e0e617>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mblock\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'block' is not defined"
     ]
    }
   ],
   "source": [
    "block"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.repartition(1).write.json(\"cleaned_final_v3.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}