finalProject.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "42788ccd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/opt/spark/python/pyspark'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import findspark\n",
    "findspark.find()\n",
    "findspark.init()\n",
    "import pyspark\n",
    "findspark.find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "20da56b2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "22/05/04 20:16:45 WARN Utils: Your hostname, eternal resolves to a loopback address: 127.0.1.1; using 192.168.1.10 instead (on interface wlp0s20f3)\n",
      "22/05/04 20:16:45 WARN Utils: Set SPARK_LOCAL_IP if you need to bind to another address\n",
      "Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties\n",
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "22/05/04 20:16:46 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.appName('finalProject').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c7dfca4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "Dataset = spark.read.load(\"/home/eternal/bigData/finalProject/Data Set - Sheet1.csv\", format = \"csv\",sep = \",\", inferScheme = True, header = True,minPartitions = 2 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "36dea528",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-------+----------------+--------------------+----------+\n",
      "|SL No.| Emp ID|RFID Card Number|       Employee Name|   Contact|\n",
      "+------+-------+----------------+--------------------+----------+\n",
      "|     1|4000001|         4000001|        Shakil Ahmed|1812345678|\n",
      "|     2|4000002|         4000002|        Shatabdi Roy|1812345678|\n",
      "|     3|4000003|         4000003|    Md. Pieash Ahmed|         0|\n",
      "|     4|4000004|         4000004|        Fahima Islam|1812345678|\n",
      "|     5|4000005|         4000005|  Tanjina Akter Jame|1812345678|\n",
      "|     6|4000006|         4000006|    Afrin Jahan Urme|1812345678|\n",
      "|     7|4000007|         4000007|      Anika Tabassum|1812345678|\n",
      "|     8|4000008|         4000008|     Afsana Alam Mim|1812345678|\n",
      "|     9|4000009|         4000009| Sayma Islam Sochona|1812345678|\n",
      "|    10|4000010|         4000010|A. K. M. Tasnim Alam|1812345678|\n",
      "|    11|4000011|         4000011|   Md Regone Hossain|1812345678|\n",
      "|    12|4000012|         4000012| Silvia Kh. Shuvessa|1812345678|\n",
      "|    13|4000013|         4000013|      Yeasin Bhuiyan|1812345678|\n",
      "|    14|4000014|         4000014|   Md. Mehedi Hassan|1812345678|\n",
      "|    15|4000015|         4000015|       Riyad Hossain|1812345678|\n",
      "|    16|4000016|         4000016|Md. Saiful Islam ...|1812345678|\n",
      "|    17|4000017|         4000017|Abdullah Mohammod...|1812345678|\n",
      "|    18|4000018|         4000018|             Al Amin|1812345678|\n",
      "|    19|4000019|         4000019|   Zahid Hasan Nirob|1812345678|\n",
      "|    20|4000020|         4000020|      Sharzan Rahman|1812345678|\n",
      "+------+-------+----------------+--------------------+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "Dataset.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4761158a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing all the necessary library \n",
    "\n",
    "from pyspark.sql import functions as F\n",
    "from pyspark.sql.functions import rand,when\n",
    "from pyspark.sql.functions import *\n",
    "from datetime import datetime\n",
    "from pyspark.sql.types import StringType\n",
    "from pyspark.sql.functions import udf\n",
    "from random import randint\n",
    "import random\n",
    "import string\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05ff0a9d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb47960c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1fc39c7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "cbc99e89",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Random UserId generator with string and number \n",
    "\n",
    "def ran(r):\n",
    "    return \"\".join([random.choice(string.ascii_lowercase + string.digits) for i in range(r)])\n",
    "\n",
    "\n",
    "def randIdGenerator():\n",
    "    return \"\".join(ran(8)+\"-\"+ran(4)+\"-\"+ran(4)+\"-\"+ran(12))\n",
    "    \n",
    "randIdGenerator = F.udf(randIdGenerator, StringType())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f7a12489",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Random Date Of Birth Generator\n",
    "\n",
    "def randDateOfBirth():\n",
    "    return \"\".join(str(random.randint(1969, 1998)))+\"-\"+\"\".join(str(random.randint(1, 12)).zfill(2))+\"-\"+\"\".join(str(random.randint(1, 28)).zfill(2))\n",
    "\n",
    "randDateOfBirth = F.udf(randDateOfBirth , StringType())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "432b809b",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#lon-lat-generator\n",
    "\n",
    "def lonlat():\n",
    "    return float(\"{0:.5f}\".format(random.uniform(20.11, 100.1)))\n",
    "\n",
    "\n",
    "lonlat = F.udf(lonlat, StringType())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0e3886ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Assingning 'N' if the number is 0\n",
    "\n",
    "def otpV(contact_number):\n",
    "    if contact_number == '0':\n",
    "        return 'N'\n",
    "    else:\n",
    "        return 'Y'\n",
    "    \n",
    "\n",
    "otpV = F.udf(otpV, StringType())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecad14fa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "893df4c3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be941b82",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Table 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ed61e41c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/spark/python/pyspark/sql/column.py:322: FutureWarning: A column as 'key' in getItem is deprecated as of Spark 3.0, and will not be supported in the future release. Use `column[key]` or `column.key` syntax instead.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "\n",
    "cdf = Dataset.withColumn('user_id',randIdGenerator())\\\n",
    "    .withColumn(\"split_list\", F.split(F.col(\"Employee Name\"), \" \")).withColumn(\"first_name\", col(\"split_list\")[0])\\\n",
    "    .withColumn(\"last_name\", col(\"split_list\")[F.size(\"split_list\") - 1]).drop(\"split_list\")\\\n",
    "    .withColumn('date_of_birth',randDateOfBirth())\\\n",
    "    .withColumn('address',F.array(F.lit(\"Dhaka, Bangladesh\"),F.lit(\"Sylhet, Bangladesh\"),F.lit(\"Chittagong, Bangladesh\"),F.lit(\"Rangpur, Bangladesh\"),).getItem((F.rand()*4).cast(\"int\")))\\\n",
    "    .withColumn('lon',lonlat())\\\n",
    "    .withColumn('lat',random.uniform(F.col('lon'),10))\\\n",
    "    .withColumn('created_date',lit('2022-03-12'))\\\n",
    "    .withColumn('updated_date',current_date())\\\n",
    "    .withColumn(\"is_otp_verified\",otpV('Contact'))\\\n",
    "    .withColumn('contact_number',col('Contact')).drop('SL No.','Emp ID','RFID Card Number','Employee Name')\n",
    "\n",
    "users_personalinfo = cdf.select('user_id','first_name','last_name','date_of_birth','contact_number','address','lat','lon','created_date','updated_date','is_otp_verified')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "43bc2f51",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "[Stage 2:>                                                          (0 + 1) / 1]\r",
      "\r",
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>contact_number</th>\n",
       "      <th>address</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>is_otp_verified</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qxduhdo6-sr4y-r565-aczxcao9r5ja</td>\n",
       "      <td>Shakil</td>\n",
       "      <td>Ahmed</td>\n",
       "      <td>1994-10-13</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>58.802606</td>\n",
       "      <td>68.41463</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a9gkiha8-txa4-j9b3-b6mynpvvp836</td>\n",
       "      <td>Shatabdi</td>\n",
       "      <td>Roy</td>\n",
       "      <td>1985-11-21</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>59.380429</td>\n",
       "      <td>69.10626</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>u2hfniem-cyka-q8bd-ivgjiskuhr4p</td>\n",
       "      <td>Md.</td>\n",
       "      <td>Ahmed</td>\n",
       "      <td>1971-03-23</td>\n",
       "      <td>0</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>26.902584</td>\n",
       "      <td>30.23167</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>785assei-c51d-r9wl-10phhplib43k</td>\n",
       "      <td>Fahima</td>\n",
       "      <td>Islam</td>\n",
       "      <td>1994-04-06</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>65.771652</td>\n",
       "      <td>76.75628</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3lrjwx5u-xz3t-vcya-tj7z63yn6tz5</td>\n",
       "      <td>Tanjina</td>\n",
       "      <td>Jame</td>\n",
       "      <td>1985-10-06</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Dhaka, Bangladesh</td>\n",
       "      <td>81.848066</td>\n",
       "      <td>95.99906</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ob5r2kpp-qvnu-gjhs-7dw3y5froww8</td>\n",
       "      <td>Afrin</td>\n",
       "      <td>Urme</td>\n",
       "      <td>1976-10-11</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>43.150149</td>\n",
       "      <td>49.67931</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ban2vdgg-341g-jdhk-g85sk8nhiwe0</td>\n",
       "      <td>Anika</td>\n",
       "      <td>Tabassum</td>\n",
       "      <td>1994-12-19</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Dhaka, Bangladesh</td>\n",
       "      <td>62.497115</td>\n",
       "      <td>72.8368</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>pvev1l6x-u4gx-7dg8-p06r6dzeyl73</td>\n",
       "      <td>Afsana</td>\n",
       "      <td>Mim</td>\n",
       "      <td>1969-10-12</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Rangpur, Bangladesh</td>\n",
       "      <td>18.566530</td>\n",
       "      <td>20.25377</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3ev3jr1s-s8i4-oycy-n1x7ws9nh6gf</td>\n",
       "      <td>Sayma</td>\n",
       "      <td>Sochona</td>\n",
       "      <td>1974-10-15</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>58.525612</td>\n",
       "      <td>68.08308</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>g7vb3pdd-0dqh-4vne-c6ir5abam4zt</td>\n",
       "      <td>A.</td>\n",
       "      <td>Alam</td>\n",
       "      <td>1993-11-26</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Rangpur, Bangladesh</td>\n",
       "      <td>31.292900</td>\n",
       "      <td>35.48669</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-05-04</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           user_id first_name last_name date_of_birth  \\\n",
       "0  qxduhdo6-sr4y-r565-aczxcao9r5ja     Shakil     Ahmed    1994-10-13   \n",
       "1  a9gkiha8-txa4-j9b3-b6mynpvvp836   Shatabdi       Roy    1985-11-21   \n",
       "2  u2hfniem-cyka-q8bd-ivgjiskuhr4p        Md.     Ahmed    1971-03-23   \n",
       "3  785assei-c51d-r9wl-10phhplib43k     Fahima     Islam    1994-04-06   \n",
       "4  3lrjwx5u-xz3t-vcya-tj7z63yn6tz5    Tanjina      Jame    1985-10-06   \n",
       "5  ob5r2kpp-qvnu-gjhs-7dw3y5froww8      Afrin      Urme    1976-10-11   \n",
       "6  ban2vdgg-341g-jdhk-g85sk8nhiwe0      Anika  Tabassum    1994-12-19   \n",
       "7  pvev1l6x-u4gx-7dg8-p06r6dzeyl73     Afsana       Mim    1969-10-12   \n",
       "8  3ev3jr1s-s8i4-oycy-n1x7ws9nh6gf      Sayma   Sochona    1974-10-15   \n",
       "9  g7vb3pdd-0dqh-4vne-c6ir5abam4zt         A.      Alam    1993-11-26   \n",
       "\n",
       "  contact_number                 address        lat       lon created_date  \\\n",
       "0     1812345678  Chittagong, Bangladesh  58.802606  68.41463   2022-03-12   \n",
       "1     1812345678      Sylhet, Bangladesh  59.380429  69.10626   2022-03-12   \n",
       "2              0  Chittagong, Bangladesh  26.902584  30.23167   2022-03-12   \n",
       "3     1812345678  Chittagong, Bangladesh  65.771652  76.75628   2022-03-12   \n",
       "4     1812345678       Dhaka, Bangladesh  81.848066  95.99906   2022-03-12   \n",
       "5     1812345678      Sylhet, Bangladesh  43.150149  49.67931   2022-03-12   \n",
       "6     1812345678       Dhaka, Bangladesh  62.497115   72.8368   2022-03-12   \n",
       "7     1812345678     Rangpur, Bangladesh  18.566530  20.25377   2022-03-12   \n",
       "8     1812345678      Sylhet, Bangladesh  58.525612  68.08308   2022-03-12   \n",
       "9     1812345678     Rangpur, Bangladesh  31.292900  35.48669   2022-03-12   \n",
       "\n",
       "  updated_date is_otp_verified  \n",
       "0   2022-05-04               Y  \n",
       "1   2022-05-04               Y  \n",
       "2   2022-05-04               N  \n",
       "3   2022-05-04               Y  \n",
       "4   2022-05-04               Y  \n",
       "5   2022-05-04               Y  \n",
       "6   2022-05-04               Y  \n",
       "7   2022-05-04               Y  \n",
       "8   2022-05-04               Y  \n",
       "9   2022-05-04               Y  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_personalinfo.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4749dfb7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "fc333476",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success\n"
     ]
    }
   ],
   "source": [
    "users_personalinfo.write.option(\"header\",True).csv(\"/home/eternal/bigData/finalProject/DataSet/users_personalinfo\")\n",
    "print('Success')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "e8b2b890",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>date_of_birth</th>\n",
       "      <th>contact_number</th>\n",
       "      <th>address</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>is_otp_verified</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>yzscg2ft-2np9-70ci-z1vnjh5boimt</td>\n",
       "      <td>Shakil</td>\n",
       "      <td>Ahmed</td>\n",
       "      <td>1985-04-27</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Dhaka, Bangladesh</td>\n",
       "      <td>55.36257268487463</td>\n",
       "      <td>100.03733</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c3rocgh0-gcs7-jm0h-k7hszjupk4td</td>\n",
       "      <td>Shatabdi</td>\n",
       "      <td>Roy</td>\n",
       "      <td>1976-11-03</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Rangpur, Bangladesh</td>\n",
       "      <td>34.30316984256811</td>\n",
       "      <td>58.23784</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>gshv586c-c972-t0cp-j8kkmzmd7z1k</td>\n",
       "      <td>Md.</td>\n",
       "      <td>Ahmed</td>\n",
       "      <td>1987-09-15</td>\n",
       "      <td>0</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>16.352601031981536</td>\n",
       "      <td>22.60888</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>t7y7njd6-d973-w4j9-xhkenvoywbcq</td>\n",
       "      <td>Fahima</td>\n",
       "      <td>Islam</td>\n",
       "      <td>1988-09-28</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Rangpur, Bangladesh</td>\n",
       "      <td>38.837747880288084</td>\n",
       "      <td>67.23824</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>o3vqa7x1-eyg5-016l-0hc9na18qkcm</td>\n",
       "      <td>Tanjina</td>\n",
       "      <td>Jame</td>\n",
       "      <td>1974-12-26</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Dhaka, Bangladesh</td>\n",
       "      <td>37.057405554823575</td>\n",
       "      <td>63.70455</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>gql3arpr-u6b3-lkjg-cm3kxhqviasn</td>\n",
       "      <td>Afrin</td>\n",
       "      <td>Urme</td>\n",
       "      <td>1997-09-18</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Dhaka, Bangladesh</td>\n",
       "      <td>15.57472370351634</td>\n",
       "      <td>21.06492</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>yc6csh7s-xmjf-qslg-620id8jcrgde</td>\n",
       "      <td>Anika</td>\n",
       "      <td>Tabassum</td>\n",
       "      <td>1989-09-03</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>42.31219372819538</td>\n",
       "      <td>74.13445</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7ci6hvbr-rojr-0paj-4da6h5vn4d9w</td>\n",
       "      <td>Afsana</td>\n",
       "      <td>Mim</td>\n",
       "      <td>1985-05-25</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Dhaka, Bangladesh</td>\n",
       "      <td>17.89357865968585</td>\n",
       "      <td>25.66747</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>npqqq41s-tnza-j5c2-k490g5dv4d59</td>\n",
       "      <td>Sayma</td>\n",
       "      <td>Sochona</td>\n",
       "      <td>1985-12-23</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Rangpur, Bangladesh</td>\n",
       "      <td>28.345759876206678</td>\n",
       "      <td>46.41335</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>w4e6t48q-teg1-gad2-63mj1amc7vqp</td>\n",
       "      <td>A.</td>\n",
       "      <td>Alam</td>\n",
       "      <td>1971-07-21</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>25.900698107178975</td>\n",
       "      <td>41.5603</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>gisobdtu-k0ug-twcj-ugsphpb5pd7r</td>\n",
       "      <td>Md</td>\n",
       "      <td>Hossain</td>\n",
       "      <td>1975-03-22</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>41.037555300288886</td>\n",
       "      <td>71.6045</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ux807bho-18dt-fi4h-t51sb6p5bsqh</td>\n",
       "      <td>Silvia</td>\n",
       "      <td>Shuvessa</td>\n",
       "      <td>1978-01-12</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Rangpur, Bangladesh</td>\n",
       "      <td>34.312258748372344</td>\n",
       "      <td>58.25588</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>p77icx8y-yesk-xheq-69bo3s73mi5m</td>\n",
       "      <td>Yeasin</td>\n",
       "      <td>Bhuiyan</td>\n",
       "      <td>1997-10-15</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>28.81536006035383</td>\n",
       "      <td>47.34543</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>e33j3n0f-hpcc-pgxh-oe1ca3dgx4j7</td>\n",
       "      <td>Md.</td>\n",
       "      <td>Hassan</td>\n",
       "      <td>1986-01-01</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>17.816181890858406</td>\n",
       "      <td>25.51385</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>txs810gs-gcxi-05ag-zi9nswhgphnx</td>\n",
       "      <td>Riyad</td>\n",
       "      <td>Hossain</td>\n",
       "      <td>1969-10-05</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>28.640282745111428</td>\n",
       "      <td>46.99793</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>8oingusn-t38w-bkpi-mrnjiw1ttm8z</td>\n",
       "      <td>Md.</td>\n",
       "      <td>Shakil</td>\n",
       "      <td>1979-08-08</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>18.61635323716382</td>\n",
       "      <td>27.10206</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>oo4aoouu-ey1q-s8p7-ajd1ghi5zz8b</td>\n",
       "      <td>Abdullah</td>\n",
       "      <td>Boiah</td>\n",
       "      <td>1986-02-08</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>15.946774334347715</td>\n",
       "      <td>21.80338</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>v7l41h78-81ep-wbp1-0q3ljq5ib51w</td>\n",
       "      <td>Al</td>\n",
       "      <td>Amin</td>\n",
       "      <td>1994-12-20</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>32.19793440197493</td>\n",
       "      <td>54.05929</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>8j9c04iw-kdxm-6um0-pzirr21rz907</td>\n",
       "      <td>Zahid</td>\n",
       "      <td>Nirob</td>\n",
       "      <td>1995-05-18</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Sylhet, Bangladesh</td>\n",
       "      <td>54.93854802284785</td>\n",
       "      <td>99.19571</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>px9cg3nz-l1lc-jgzh-bpxkldmhuv07</td>\n",
       "      <td>Sharzan</td>\n",
       "      <td>Rahman</td>\n",
       "      <td>1994-04-02</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>Chittagong, Bangladesh</td>\n",
       "      <td>28.419932199515987</td>\n",
       "      <td>46.56057</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            user_id first_name last_name date_of_birth  \\\n",
       "0   yzscg2ft-2np9-70ci-z1vnjh5boimt     Shakil     Ahmed    1985-04-27   \n",
       "1   c3rocgh0-gcs7-jm0h-k7hszjupk4td   Shatabdi       Roy    1976-11-03   \n",
       "2   gshv586c-c972-t0cp-j8kkmzmd7z1k        Md.     Ahmed    1987-09-15   \n",
       "3   t7y7njd6-d973-w4j9-xhkenvoywbcq     Fahima     Islam    1988-09-28   \n",
       "4   o3vqa7x1-eyg5-016l-0hc9na18qkcm    Tanjina      Jame    1974-12-26   \n",
       "5   gql3arpr-u6b3-lkjg-cm3kxhqviasn      Afrin      Urme    1997-09-18   \n",
       "6   yc6csh7s-xmjf-qslg-620id8jcrgde      Anika  Tabassum    1989-09-03   \n",
       "7   7ci6hvbr-rojr-0paj-4da6h5vn4d9w     Afsana       Mim    1985-05-25   \n",
       "8   npqqq41s-tnza-j5c2-k490g5dv4d59      Sayma   Sochona    1985-12-23   \n",
       "9   w4e6t48q-teg1-gad2-63mj1amc7vqp         A.      Alam    1971-07-21   \n",
       "10  gisobdtu-k0ug-twcj-ugsphpb5pd7r         Md   Hossain    1975-03-22   \n",
       "11  ux807bho-18dt-fi4h-t51sb6p5bsqh     Silvia  Shuvessa    1978-01-12   \n",
       "12  p77icx8y-yesk-xheq-69bo3s73mi5m     Yeasin   Bhuiyan    1997-10-15   \n",
       "13  e33j3n0f-hpcc-pgxh-oe1ca3dgx4j7        Md.    Hassan    1986-01-01   \n",
       "14  txs810gs-gcxi-05ag-zi9nswhgphnx      Riyad   Hossain    1969-10-05   \n",
       "15  8oingusn-t38w-bkpi-mrnjiw1ttm8z        Md.    Shakil    1979-08-08   \n",
       "16  oo4aoouu-ey1q-s8p7-ajd1ghi5zz8b   Abdullah     Boiah    1986-02-08   \n",
       "17  v7l41h78-81ep-wbp1-0q3ljq5ib51w         Al      Amin    1994-12-20   \n",
       "18  8j9c04iw-kdxm-6um0-pzirr21rz907      Zahid     Nirob    1995-05-18   \n",
       "19  px9cg3nz-l1lc-jgzh-bpxkldmhuv07    Sharzan    Rahman    1994-04-02   \n",
       "\n",
       "   contact_number                 address                 lat        lon  \\\n",
       "0      1812345678       Dhaka, Bangladesh   55.36257268487463  100.03733   \n",
       "1      1812345678     Rangpur, Bangladesh   34.30316984256811   58.23784   \n",
       "2               0      Sylhet, Bangladesh  16.352601031981536   22.60888   \n",
       "3      1812345678     Rangpur, Bangladesh  38.837747880288084   67.23824   \n",
       "4      1812345678       Dhaka, Bangladesh  37.057405554823575   63.70455   \n",
       "5      1812345678       Dhaka, Bangladesh   15.57472370351634   21.06492   \n",
       "6      1812345678      Sylhet, Bangladesh   42.31219372819538   74.13445   \n",
       "7      1812345678       Dhaka, Bangladesh   17.89357865968585   25.66747   \n",
       "8      1812345678     Rangpur, Bangladesh  28.345759876206678   46.41335   \n",
       "9      1812345678  Chittagong, Bangladesh  25.900698107178975    41.5603   \n",
       "10     1812345678  Chittagong, Bangladesh  41.037555300288886    71.6045   \n",
       "11     1812345678     Rangpur, Bangladesh  34.312258748372344   58.25588   \n",
       "12     1812345678      Sylhet, Bangladesh   28.81536006035383   47.34543   \n",
       "13     1812345678  Chittagong, Bangladesh  17.816181890858406   25.51385   \n",
       "14     1812345678  Chittagong, Bangladesh  28.640282745111428   46.99793   \n",
       "15     1812345678      Sylhet, Bangladesh   18.61635323716382   27.10206   \n",
       "16     1812345678  Chittagong, Bangladesh  15.946774334347715   21.80338   \n",
       "17     1812345678      Sylhet, Bangladesh   32.19793440197493   54.05929   \n",
       "18     1812345678      Sylhet, Bangladesh   54.93854802284785   99.19571   \n",
       "19     1812345678  Chittagong, Bangladesh  28.419932199515987   46.56057   \n",
       "\n",
       "   created_date updated_date is_otp_verified  \n",
       "0    2022-03-12   2022-04-06               Y  \n",
       "1    2022-03-12   2022-04-06               Y  \n",
       "2    2022-03-12   2022-04-06               N  \n",
       "3    2022-03-12   2022-04-06               Y  \n",
       "4    2022-03-12   2022-04-06               Y  \n",
       "5    2022-03-12   2022-04-06               Y  \n",
       "6    2022-03-12   2022-04-06               Y  \n",
       "7    2022-03-12   2022-04-06               Y  \n",
       "8    2022-03-12   2022-04-06               Y  \n",
       "9    2022-03-12   2022-04-06               Y  \n",
       "10   2022-03-12   2022-04-06               Y  \n",
       "11   2022-03-12   2022-04-06               Y  \n",
       "12   2022-03-12   2022-04-06               Y  \n",
       "13   2022-03-12   2022-04-06               Y  \n",
       "14   2022-03-12   2022-04-06               Y  \n",
       "15   2022-03-12   2022-04-06               Y  \n",
       "16   2022-03-12   2022-04-06               Y  \n",
       "17   2022-03-12   2022-04-06               Y  \n",
       "18   2022-03-12   2022-04-06               Y  \n",
       "19   2022-03-12   2022-04-06               Y  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datasetTable1 = spark.read.load(\"/home/eternal/bigData/finalProject/DataSet/users_personalinfo/part-00000-81eaa0eb-0a7f-4b96-b048-11a09d8f2d2a-c000.csv\", format = \"csv\",sep = \",\", inferScheme = True, header = True,minPartitions = 2 )\n",
    "datasetTable1.toPandas().head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dff41851",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "117611bc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5415ccc0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9b2ed47",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Table 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "caa9b1d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Assigning PostCode from City\n",
    "\n",
    "def postCode(City):\n",
    "    if City == 'Dhaka':\n",
    "        return '1209'\n",
    "    elif City == 'Sylhet':\n",
    "        return '3352'\n",
    "    elif City == 'Chittagong':\n",
    "        return '4620'\n",
    "    else:\n",
    "        return '5201'\n",
    "    \n",
    "    \n",
    "# Set address by Specific city\n",
    "def address(City):\n",
    "    if City == 'Dhaka':\n",
    "        return 'Dhanmondi,Dhaka,Bangladesh'\n",
    "    elif City == 'Sylhet':\n",
    "        return 'Hobiganj,Sylhet,Bangladesh'\n",
    "    elif City == 'Chittagong':\n",
    "        return 'Bandarban,Chittagong,Bangladesh'\n",
    "    else:\n",
    "        return 'Dinajpur,Bangladesh'\n",
    "\n",
    "\n",
    "address = F.udf(address, StringType())\n",
    "postCode = F.udf(postCode, StringType())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8fa9631b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "50f67372",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/spark/python/pyspark/sql/column.py:322: FutureWarning: A column as 'key' in getItem is deprecated as of Spark 3.0, and will not be supported in the future release. Use `column[key]` or `column.key` syntax instead.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "cdf = Dataset.withColumn('user_id',randIdGenerator())\\\n",
    "    .withColumn('country',lit('Bangladesh'))\\\n",
    "    .withColumn(\"City\",F.array(F.lit(\"Dhaka\"),F.lit(\"Sylhet\"),F.lit(\"Chittagong\"),F.lit(\"Rangpur\"),).getItem((F.rand()*4).cast(\"int\")))\\\n",
    "    .withColumn('postcode',postCode('City'))\\\n",
    "    .withColumn('address',address('City'))\\\n",
    "    .withColumn('created_date',lit('2021-09-12'))\\\n",
    "    .withColumn('updated_date',current_date())\\\n",
    "    .withColumnRenamed('Contact','contact')\\\n",
    "    .withColumn('currency',lit('BDT')).drop('SL No.','Emp ID','RFID Card Number','Employee Name')\n",
    "\n",
    "users_billinginfo = cdf.select(\"user_id\",\"address\",\"city\",\"postcode\",\"country\",\"contact\",\"currency\",\"created_date\",\"updated_date\")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "a9b54dbb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>address</th>\n",
       "      <th>city</th>\n",
       "      <th>postcode</th>\n",
       "      <th>country</th>\n",
       "      <th>contact</th>\n",
       "      <th>currency</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>xh7yiw6x-djg3-w9rf-j2hxbp9h9pua</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>igp9eg0u-wqw2-jcox-9qpjporw7fk4</td>\n",
       "      <td>Dhanmondi,Dhaka,Bangladesh</td>\n",
       "      <td>Dhaka</td>\n",
       "      <td>1209</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7gff5jkj-pegw-vm5k-2tnjqembuju9</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>0</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b64sb634-f3uh-9x4a-x4iy3g35hlrw</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>yo3hsqxr-tif3-kvc8-3p4w6kehxfze</td>\n",
       "      <td>Hobiganj,Sylhet,Bangladesh</td>\n",
       "      <td>Sylhet</td>\n",
       "      <td>3352</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>io0pc3li-e4sf-5sbe-a45eh1j4niu9</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ktqmzfy4-2xu9-d420-i6ao0740dkq5</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ehteepz1-tufg-yac0-9qcdzoekwypt</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6xve50dg-dvya-9x7o-gbe19sre7gq9</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>wbzxhl92-uiav-j6ko-2v88qpdqgawb</td>\n",
       "      <td>Dinajpur,Bangladesh</td>\n",
       "      <td>Rangpur</td>\n",
       "      <td>5201</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           user_id                          address  \\\n",
       "0  xh7yiw6x-djg3-w9rf-j2hxbp9h9pua  Bandarban,Chittagong,Bangladesh   \n",
       "1  igp9eg0u-wqw2-jcox-9qpjporw7fk4       Dhanmondi,Dhaka,Bangladesh   \n",
       "2  7gff5jkj-pegw-vm5k-2tnjqembuju9  Bandarban,Chittagong,Bangladesh   \n",
       "3  b64sb634-f3uh-9x4a-x4iy3g35hlrw  Bandarban,Chittagong,Bangladesh   \n",
       "4  yo3hsqxr-tif3-kvc8-3p4w6kehxfze       Hobiganj,Sylhet,Bangladesh   \n",
       "5  io0pc3li-e4sf-5sbe-a45eh1j4niu9  Bandarban,Chittagong,Bangladesh   \n",
       "6  ktqmzfy4-2xu9-d420-i6ao0740dkq5  Bandarban,Chittagong,Bangladesh   \n",
       "7  ehteepz1-tufg-yac0-9qcdzoekwypt  Bandarban,Chittagong,Bangladesh   \n",
       "8  6xve50dg-dvya-9x7o-gbe19sre7gq9  Bandarban,Chittagong,Bangladesh   \n",
       "9  wbzxhl92-uiav-j6ko-2v88qpdqgawb              Dinajpur,Bangladesh   \n",
       "\n",
       "         city postcode     country     contact currency created_date  \\\n",
       "0  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "1       Dhaka     1209  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "2  Chittagong     4620  Bangladesh           0      BDT   2021-09-12   \n",
       "3  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "4      Sylhet     3352  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "5  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "6  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "7  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "8  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "9     Rangpur     5201  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "\n",
       "  updated_date  \n",
       "0   2022-04-06  \n",
       "1   2022-04-06  \n",
       "2   2022-04-06  \n",
       "3   2022-04-06  \n",
       "4   2022-04-06  \n",
       "5   2022-04-06  \n",
       "6   2022-04-06  \n",
       "7   2022-04-06  \n",
       "8   2022-04-06  \n",
       "9   2022-04-06  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_billinginfo.limit(10).toPandas()\n",
    "\n",
    "# user_id, address, city, postcode, country, contact, currency, created_date, updated_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "ea8a0f27",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- user_id: string (nullable = true)\n",
      " |-- address: string (nullable = true)\n",
      " |-- city: string (nullable = true)\n",
      " |-- postcode: string (nullable = true)\n",
      " |-- country: string (nullable = false)\n",
      " |-- contact: string (nullable = true)\n",
      " |-- currency: string (nullable = false)\n",
      " |-- created_date: string (nullable = false)\n",
      " |-- updated_date: date (nullable = false)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "users_billinginfo.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "56a4a9a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "users_billinginfo.write.option(\"header\",True).csv(\"/home/eternal/bigData/finalProject/DataSet/users_billinginfo\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "128f62c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>address</th>\n",
       "      <th>city</th>\n",
       "      <th>postcode</th>\n",
       "      <th>country</th>\n",
       "      <th>contact</th>\n",
       "      <th>currency</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4gc5gjfg-3ki7-i9rf-5g9x5i5tvuot</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8ukdvgkf-sx0b-whuy-2t3l3cex41xm</td>\n",
       "      <td>Dhanmondi,Dhaka,Bangladesh</td>\n",
       "      <td>Dhaka</td>\n",
       "      <td>1209</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>pnejbl5h-oya1-uivo-hkjez50dks1y</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>0</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qk687pjk-zdsn-c7co-zggmajc35wtd</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>yxwnz3mc-5r5m-l8dh-5dzpr09xqdpu</td>\n",
       "      <td>Hobiganj,Sylhet,Bangladesh</td>\n",
       "      <td>Sylhet</td>\n",
       "      <td>3352</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ftsnmcn6-4myg-fh6d-7lzwaf8n2syr</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>jedwr8wc-mr4a-478j-un302luxowsj</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>m9dd30eg-p5li-m2p6-5cb70u60a1wc</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0cds7edj-zuco-mhii-s4k8tardbfzm</td>\n",
       "      <td>Bandarban,Chittagong,Bangladesh</td>\n",
       "      <td>Chittagong</td>\n",
       "      <td>4620</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>nag8e0vk-e343-gsco-yaa97n8r66ej</td>\n",
       "      <td>Dinajpur,Bangladesh</td>\n",
       "      <td>Rangpur</td>\n",
       "      <td>5201</td>\n",
       "      <td>Bangladesh</td>\n",
       "      <td>1812345678</td>\n",
       "      <td>BDT</td>\n",
       "      <td>2021-09-12</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           user_id                          address  \\\n",
       "0  4gc5gjfg-3ki7-i9rf-5g9x5i5tvuot  Bandarban,Chittagong,Bangladesh   \n",
       "1  8ukdvgkf-sx0b-whuy-2t3l3cex41xm       Dhanmondi,Dhaka,Bangladesh   \n",
       "2  pnejbl5h-oya1-uivo-hkjez50dks1y  Bandarban,Chittagong,Bangladesh   \n",
       "3  qk687pjk-zdsn-c7co-zggmajc35wtd  Bandarban,Chittagong,Bangladesh   \n",
       "4  yxwnz3mc-5r5m-l8dh-5dzpr09xqdpu       Hobiganj,Sylhet,Bangladesh   \n",
       "5  ftsnmcn6-4myg-fh6d-7lzwaf8n2syr  Bandarban,Chittagong,Bangladesh   \n",
       "6  jedwr8wc-mr4a-478j-un302luxowsj  Bandarban,Chittagong,Bangladesh   \n",
       "7  m9dd30eg-p5li-m2p6-5cb70u60a1wc  Bandarban,Chittagong,Bangladesh   \n",
       "8  0cds7edj-zuco-mhii-s4k8tardbfzm  Bandarban,Chittagong,Bangladesh   \n",
       "9  nag8e0vk-e343-gsco-yaa97n8r66ej              Dinajpur,Bangladesh   \n",
       "\n",
       "         city postcode     country     contact currency created_date  \\\n",
       "0  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "1       Dhaka     1209  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "2  Chittagong     4620  Bangladesh           0      BDT   2021-09-12   \n",
       "3  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "4      Sylhet     3352  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "5  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "6  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "7  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "8  Chittagong     4620  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "9     Rangpur     5201  Bangladesh  1812345678      BDT   2021-09-12   \n",
       "\n",
       "  updated_date  \n",
       "0   2022-04-06  \n",
       "1   2022-04-06  \n",
       "2   2022-04-06  \n",
       "3   2022-04-06  \n",
       "4   2022-04-06  \n",
       "5   2022-04-06  \n",
       "6   2022-04-06  \n",
       "7   2022-04-06  \n",
       "8   2022-04-06  \n",
       "9   2022-04-06  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datasetTable2 = spark.read.load(\"/home/eternal/bigData/finalProject/DataSet/users_billinginfo/part-00000-d8c2d438-9868-48ac-baae-a689d1988d40-c000.csv\", format = \"csv\",sep = \",\", inferScheme = True, header = True,minPartitions = 2 )\n",
    "datasetTable2.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e77ad1d3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a021b482",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8314ae2b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a568d0ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "ef4d1549",
   "metadata": {},
   "outputs": [],
   "source": [
    "#table 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "66626ec7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/spark/python/pyspark/sql/column.py:322: FutureWarning: A column as 'key' in getItem is deprecated as of Spark 3.0, and will not be supported in the future release. Use `column[key]` or `column.key` syntax instead.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "cdf = Dataset.withColumn('unique_user_id',round(rand()*100300,1000).cast(\"int\"))\\\n",
    "    .withColumn(\"user_id\",randIdGenerator())\\\n",
    "    .withColumn(\"activated\",F.lit('1'))\\\n",
    "    .withColumn(\"tag\",round(rand()*123456,1).cast(\"int\"))\\\n",
    "    .withColumn('created_date',lit('2020-11-12'))\\\n",
    "    .withColumn('updated_date',current_date())\\\n",
    "    .withColumn('valid_until',F.array(F.lit(\"05/29/2025\"),F.lit(\"08/17/2028\"),F.lit(\"01/08/2023\"),).getItem((F.rand()*3).cast(\"int\")))\\\n",
    "    .withColumn('last_used',F.array(F.lit(\"08/19/2021\"),F.lit(\"08/21/2021\"),F.lit(\"06/22/2021\"),).getItem((F.rand()*3).cast(\"int\")))\\\n",
    "    .withColumn('reference_number',round(rand()*(12534-123)+125).cast(\"int\"))\\\n",
    "    .withColumn('employee_code',col('reference_number'))\\\n",
    "    .drop('SL No.','Emp ID','RFID Card Number','Employee Name')\n",
    "\n",
    "users_rfidinfo = cdf.select(\"user_id\",\"tag\",\"activated\",\"created_date\",\"updated_date\",\"valid_until\",\"last_used\",\"reference_number\",\"employee_code\",\"unique_user_id\")\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05b1b5bf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af14ccaa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "854fed8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>tag</th>\n",
       "      <th>activated</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>valid_until</th>\n",
       "      <th>last_used</th>\n",
       "      <th>reference_number</th>\n",
       "      <th>employee_code</th>\n",
       "      <th>unique_user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rl5lvspx-6bbk-ru14-pcg6yxvd1e4y</td>\n",
       "      <td>15370</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>01/08/2023</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>12203</td>\n",
       "      <td>12203</td>\n",
       "      <td>12592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>l9xntq2o-5q44-fx63-uw09wiquajvu</td>\n",
       "      <td>79439</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>08/17/2028</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>12275</td>\n",
       "      <td>12275</td>\n",
       "      <td>4044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>aim84pdn-0j10-zob3-jvft1bwlcwbh</td>\n",
       "      <td>119545</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>11919</td>\n",
       "      <td>11919</td>\n",
       "      <td>55408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>gm2ge4hz-vdmj-61bx-h4q2i5qyhlbd</td>\n",
       "      <td>10190</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>11249</td>\n",
       "      <td>11249</td>\n",
       "      <td>22118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>znj2rmdn-whi0-7zyy-9jnqbxhd4jf0</td>\n",
       "      <td>59668</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>08/19/2021</td>\n",
       "      <td>7010</td>\n",
       "      <td>7010</td>\n",
       "      <td>77776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9s0ezlgx-m6n8-fuk0-0w7o91mgdraz</td>\n",
       "      <td>18786</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>01/08/2023</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>4544</td>\n",
       "      <td>4544</td>\n",
       "      <td>95612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>03xwaxll-pvnq-k06e-84qiqrkdqi3a</td>\n",
       "      <td>44478</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>08/17/2028</td>\n",
       "      <td>08/19/2021</td>\n",
       "      <td>5434</td>\n",
       "      <td>5434</td>\n",
       "      <td>74001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9zxp1eng-hujy-nv4u-qt9xpkn7b5ll</td>\n",
       "      <td>71443</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>11876</td>\n",
       "      <td>11876</td>\n",
       "      <td>57323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0i6i9v68-r4j8-0zot-0rnaxcobpgy4</td>\n",
       "      <td>61589</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>11814</td>\n",
       "      <td>11814</td>\n",
       "      <td>43101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>zqkhkwjo-vx82-r97f-9hn5ce7uclqm</td>\n",
       "      <td>37006</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>08/17/2028</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>9843</td>\n",
       "      <td>9843</td>\n",
       "      <td>31321</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           user_id     tag activated created_date  \\\n",
       "0  rl5lvspx-6bbk-ru14-pcg6yxvd1e4y   15370         1   2020-11-12   \n",
       "1  l9xntq2o-5q44-fx63-uw09wiquajvu   79439         1   2020-11-12   \n",
       "2  aim84pdn-0j10-zob3-jvft1bwlcwbh  119545         1   2020-11-12   \n",
       "3  gm2ge4hz-vdmj-61bx-h4q2i5qyhlbd   10190         1   2020-11-12   \n",
       "4  znj2rmdn-whi0-7zyy-9jnqbxhd4jf0   59668         1   2020-11-12   \n",
       "5  9s0ezlgx-m6n8-fuk0-0w7o91mgdraz   18786         1   2020-11-12   \n",
       "6  03xwaxll-pvnq-k06e-84qiqrkdqi3a   44478         1   2020-11-12   \n",
       "7  9zxp1eng-hujy-nv4u-qt9xpkn7b5ll   71443         1   2020-11-12   \n",
       "8  0i6i9v68-r4j8-0zot-0rnaxcobpgy4   61589         1   2020-11-12   \n",
       "9  zqkhkwjo-vx82-r97f-9hn5ce7uclqm   37006         1   2020-11-12   \n",
       "\n",
       "  updated_date valid_until   last_used  reference_number  employee_code  \\\n",
       "0   2022-04-12  01/08/2023  06/22/2021             12203          12203   \n",
       "1   2022-04-12  08/17/2028  06/22/2021             12275          12275   \n",
       "2   2022-04-12  05/29/2025  08/21/2021             11919          11919   \n",
       "3   2022-04-12  05/29/2025  08/21/2021             11249          11249   \n",
       "4   2022-04-12  05/29/2025  08/19/2021              7010           7010   \n",
       "5   2022-04-12  01/08/2023  08/21/2021              4544           4544   \n",
       "6   2022-04-12  08/17/2028  08/19/2021              5434           5434   \n",
       "7   2022-04-12  05/29/2025  08/21/2021             11876          11876   \n",
       "8   2022-04-12  05/29/2025  06/22/2021             11814          11814   \n",
       "9   2022-04-12  08/17/2028  08/21/2021              9843           9843   \n",
       "\n",
       "   unique_user_id  \n",
       "0           12592  \n",
       "1            4044  \n",
       "2           55408  \n",
       "3           22118  \n",
       "4           77776  \n",
       "5           95612  \n",
       "6           74001  \n",
       "7           57323  \n",
       "8           43101  \n",
       "9           31321  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_rfidinfo.toPandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "84e6066b",
   "metadata": {},
   "outputs": [],
   "source": [
    "users_rfidinfo.write.option(\"header\",True).csv(\"/home/eternal/bigData/finalProject/DataSet/users_rfidinfo\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0c8408db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>tag</th>\n",
       "      <th>activated</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>valid_until</th>\n",
       "      <th>last_used</th>\n",
       "      <th>reference_number</th>\n",
       "      <th>employee_code</th>\n",
       "      <th>unique_user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7s0hczms-ftjy-mkc2-uxw96xhc43we</td>\n",
       "      <td>15370</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>01/08/2023</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>12203</td>\n",
       "      <td>12203</td>\n",
       "      <td>12592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ooz53cs5-zu11-vs89-9r3x9n4aodui</td>\n",
       "      <td>79439</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>08/17/2028</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>12275</td>\n",
       "      <td>12275</td>\n",
       "      <td>4044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>o74twatg-4dvt-yk9a-20bxd2eecfhv</td>\n",
       "      <td>119545</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>11919</td>\n",
       "      <td>11919</td>\n",
       "      <td>55408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dlm9thbn-gqrp-yf04-wzorzyoc3ccv</td>\n",
       "      <td>10190</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>11249</td>\n",
       "      <td>11249</td>\n",
       "      <td>22118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qhttt0te-y7sw-tp2b-dkuauu6sce20</td>\n",
       "      <td>59668</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>08/19/2021</td>\n",
       "      <td>7010</td>\n",
       "      <td>7010</td>\n",
       "      <td>77776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>v9yiv3mq-iu2d-8hwe-xzjv1pu2my9n</td>\n",
       "      <td>18786</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>01/08/2023</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>4544</td>\n",
       "      <td>4544</td>\n",
       "      <td>95612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6n66xgzv-59su-5v8x-j40hbd5i5rck</td>\n",
       "      <td>44478</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>08/17/2028</td>\n",
       "      <td>08/19/2021</td>\n",
       "      <td>5434</td>\n",
       "      <td>5434</td>\n",
       "      <td>74001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>rlt1feag-adwj-pn4d-ligxk3vo9bn0</td>\n",
       "      <td>71443</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>11876</td>\n",
       "      <td>11876</td>\n",
       "      <td>57323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>j6ttsasv-6s2f-i1nl-mg4g0tvpxlwc</td>\n",
       "      <td>61589</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>05/29/2025</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>11814</td>\n",
       "      <td>11814</td>\n",
       "      <td>43101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3ix4ltaq-q2w2-vubp-7nxrzqubfbl2</td>\n",
       "      <td>37006</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-12</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>08/17/2028</td>\n",
       "      <td>08/21/2021</td>\n",
       "      <td>9843</td>\n",
       "      <td>9843</td>\n",
       "      <td>31321</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           user_id     tag activated created_date  \\\n",
       "0  7s0hczms-ftjy-mkc2-uxw96xhc43we   15370         1   2020-11-12   \n",
       "1  ooz53cs5-zu11-vs89-9r3x9n4aodui   79439         1   2020-11-12   \n",
       "2  o74twatg-4dvt-yk9a-20bxd2eecfhv  119545         1   2020-11-12   \n",
       "3  dlm9thbn-gqrp-yf04-wzorzyoc3ccv   10190         1   2020-11-12   \n",
       "4  qhttt0te-y7sw-tp2b-dkuauu6sce20   59668         1   2020-11-12   \n",
       "5  v9yiv3mq-iu2d-8hwe-xzjv1pu2my9n   18786         1   2020-11-12   \n",
       "6  6n66xgzv-59su-5v8x-j40hbd5i5rck   44478         1   2020-11-12   \n",
       "7  rlt1feag-adwj-pn4d-ligxk3vo9bn0   71443         1   2020-11-12   \n",
       "8  j6ttsasv-6s2f-i1nl-mg4g0tvpxlwc   61589         1   2020-11-12   \n",
       "9  3ix4ltaq-q2w2-vubp-7nxrzqubfbl2   37006         1   2020-11-12   \n",
       "\n",
       "  updated_date valid_until   last_used reference_number employee_code  \\\n",
       "0   2022-04-12  01/08/2023  06/22/2021            12203         12203   \n",
       "1   2022-04-12  08/17/2028  06/22/2021            12275         12275   \n",
       "2   2022-04-12  05/29/2025  08/21/2021            11919         11919   \n",
       "3   2022-04-12  05/29/2025  08/21/2021            11249         11249   \n",
       "4   2022-04-12  05/29/2025  08/19/2021             7010          7010   \n",
       "5   2022-04-12  01/08/2023  08/21/2021             4544          4544   \n",
       "6   2022-04-12  08/17/2028  08/19/2021             5434          5434   \n",
       "7   2022-04-12  05/29/2025  08/21/2021            11876         11876   \n",
       "8   2022-04-12  05/29/2025  06/22/2021            11814         11814   \n",
       "9   2022-04-12  08/17/2028  08/21/2021             9843          9843   \n",
       "\n",
       "  unique_user_id  \n",
       "0          12592  \n",
       "1           4044  \n",
       "2          55408  \n",
       "3          22118  \n",
       "4          77776  \n",
       "5          95612  \n",
       "6          74001  \n",
       "7          57323  \n",
       "8          43101  \n",
       "9          31321  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datasetTable3 = spark.read.load(\"/home/eternal/bigData/finalProject/DataSet/users_rfidinfo/part-00000-720adf36-16f4-4453-9a6b-bf67be710860-c000.csv\", format = \"csv\",sep = \",\", inferScheme = True, header = True,minPartitions = 2 )\n",
    "datasetTable3.toPandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64a4dbcc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "032c931f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0da13ba",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37fd3b11",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Table 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "eced3508",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter Product Type Id: 574\n"
     ]
    }
   ],
   "source": [
    "y = input(\"Enter Product Type Id: \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a4258519",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/spark/python/pyspark/sql/column.py:322: FutureWarning: A column as 'key' in getItem is deprecated as of Spark 3.0, and will not be supported in the future release. Use `column[key]` or `column.key` syntax instead.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "\n",
    "cdf = Dataset.withColumn('id',round(rand()*100300,1000).cast(\"int\"))\\\n",
    "    .withColumn(\"quantity\",round(rand()*(1250-240)+10).cast(\"int\"))\\\n",
    "    .withColumn('created_date',lit('2021-05-29'))\\\n",
    "    .withColumn('updated_date',current_date())\\\n",
    "    .withColumn('product_type_id',lit(y))\\\n",
    "    .withColumn('rfid_id',randIdGenerator())\\\n",
    "    .withColumn('credit_type',lit('NORMAL'))\\\n",
    "    .withColumn('expire_date',F.array(F.lit(\"05/29/2025\"),F.lit(\"08/17/2028\"),F.lit(\"01/08/2023\"),).getItem((F.rand()*3).cast(\"int\"))).drop('SL No.','Employee Name','Contact')\n",
    "\n",
    "users_rfiditem = cdf.select(\"id\",\"quantity\",\"created_date\",\"updated_date\",\"product_type_id\",\"rfid_id\",\"credit_type\",\"expire_date\")\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "fa4b453f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>product_type_id</th>\n",
       "      <th>rfid_id</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>expire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4942</td>\n",
       "      <td>658</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>u2pbu4bl-errn-tlbs-yi7tawgphcq5</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>05/29/2025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19605</td>\n",
       "      <td>353</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>kx0hyrue-8xwb-r5ne-siinw0ur73te</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>08/17/2028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29822</td>\n",
       "      <td>490</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>hopapou2-xgw8-44u8-c831u8c6shrl</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9319</td>\n",
       "      <td>730</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>g7bor9ge-ijo4-4e3e-z86w0hr71aq7</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>08/17/2028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22601</td>\n",
       "      <td>16</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>zavie6ff-09fd-hcjl-zbttt09j2xs0</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>34065</td>\n",
       "      <td>114</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>5ilw02aw-9nze-rivk-7ockd2uqcgib</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>68154</td>\n",
       "      <td>466</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>4lyj6lqy-fphc-8qv5-cksmb2x5vimy</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>05/29/2025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>30975</td>\n",
       "      <td>752</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>aw4sp5i5-z9fb-qfld-ege3k7leywnb</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>67023</td>\n",
       "      <td>138</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>aq43bpeu-noqh-sdgm-c4r15msc8f7t</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>18245</td>\n",
       "      <td>154</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>2bnkd0gb-mx03-mbqu-12aj6ylfgit4</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>05/29/2025</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  quantity created_date updated_date product_type_id  \\\n",
       "0   4942       658   2021-05-29   2022-04-12             574   \n",
       "1  19605       353   2021-05-29   2022-04-12             574   \n",
       "2  29822       490   2021-05-29   2022-04-12             574   \n",
       "3   9319       730   2021-05-29   2022-04-12             574   \n",
       "4  22601        16   2021-05-29   2022-04-12             574   \n",
       "5  34065       114   2021-05-29   2022-04-12             574   \n",
       "6  68154       466   2021-05-29   2022-04-12             574   \n",
       "7  30975       752   2021-05-29   2022-04-12             574   \n",
       "8  67023       138   2021-05-29   2022-04-12             574   \n",
       "9  18245       154   2021-05-29   2022-04-12             574   \n",
       "\n",
       "                           rfid_id credit_type expire_date  \n",
       "0  u2pbu4bl-errn-tlbs-yi7tawgphcq5      NORMAL  05/29/2025  \n",
       "1  kx0hyrue-8xwb-r5ne-siinw0ur73te      NORMAL  08/17/2028  \n",
       "2  hopapou2-xgw8-44u8-c831u8c6shrl      NORMAL  01/08/2023  \n",
       "3  g7bor9ge-ijo4-4e3e-z86w0hr71aq7      NORMAL  08/17/2028  \n",
       "4  zavie6ff-09fd-hcjl-zbttt09j2xs0      NORMAL  01/08/2023  \n",
       "5  5ilw02aw-9nze-rivk-7ockd2uqcgib      NORMAL  01/08/2023  \n",
       "6  4lyj6lqy-fphc-8qv5-cksmb2x5vimy      NORMAL  05/29/2025  \n",
       "7  aw4sp5i5-z9fb-qfld-ege3k7leywnb      NORMAL  01/08/2023  \n",
       "8  aq43bpeu-noqh-sdgm-c4r15msc8f7t      NORMAL  01/08/2023  \n",
       "9  2bnkd0gb-mx03-mbqu-12aj6ylfgit4      NORMAL  05/29/2025  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_rfiditem.toPandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bb0e6c09",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Dumping The created dataset as a csv file.\n",
    "\n",
    "users_rfiditem.write.option(\"header\",True).csv(\"/home/eternal/bigData/finalProject/DataSet/users_rfiditem\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "61b75d2c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>product_type_id</th>\n",
       "      <th>rfid_id</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>expire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4942</td>\n",
       "      <td>658</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>dlj01jgs-h2by-z75p-mzezs6j1r9fi</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>05/29/2025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19605</td>\n",
       "      <td>353</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>tt7320o8-3xbs-p95b-9meda6mqn2in</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>08/17/2028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29822</td>\n",
       "      <td>490</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>zhzy36hy-nx9k-fszs-aakw0qtfyenv</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9319</td>\n",
       "      <td>730</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>96vvubxl-2epz-3hae-ooaosovtstj8</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>08/17/2028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22601</td>\n",
       "      <td>16</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>f5khcg3j-t9ex-68gy-38nuwp82zq9s</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>34065</td>\n",
       "      <td>114</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>htzkjd3u-hm1r-zdjf-hprsg3qnljxn</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>68154</td>\n",
       "      <td>466</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>urqndiwn-wcz5-585o-e1tkoztcgjpk</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>05/29/2025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>30975</td>\n",
       "      <td>752</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>xp1urqoh-d2og-4xp0-28mtgbk3s4m8</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>67023</td>\n",
       "      <td>138</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>hsmad7wq-o49o-7k5p-2tcjoj6pbye6</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>01/08/2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>18245</td>\n",
       "      <td>154</td>\n",
       "      <td>2021-05-29</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>23gsidon-aqm3-eh7o-uu26b5yp18o5</td>\n",
       "      <td>NORMAL</td>\n",
       "      <td>05/29/2025</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id quantity created_date updated_date product_type_id  \\\n",
       "0   4942      658   2021-05-29   2022-04-12             574   \n",
       "1  19605      353   2021-05-29   2022-04-12             574   \n",
       "2  29822      490   2021-05-29   2022-04-12             574   \n",
       "3   9319      730   2021-05-29   2022-04-12             574   \n",
       "4  22601       16   2021-05-29   2022-04-12             574   \n",
       "5  34065      114   2021-05-29   2022-04-12             574   \n",
       "6  68154      466   2021-05-29   2022-04-12             574   \n",
       "7  30975      752   2021-05-29   2022-04-12             574   \n",
       "8  67023      138   2021-05-29   2022-04-12             574   \n",
       "9  18245      154   2021-05-29   2022-04-12             574   \n",
       "\n",
       "                           rfid_id credit_type expire_date  \n",
       "0  dlj01jgs-h2by-z75p-mzezs6j1r9fi      NORMAL  05/29/2025  \n",
       "1  tt7320o8-3xbs-p95b-9meda6mqn2in      NORMAL  08/17/2028  \n",
       "2  zhzy36hy-nx9k-fszs-aakw0qtfyenv      NORMAL  01/08/2023  \n",
       "3  96vvubxl-2epz-3hae-ooaosovtstj8      NORMAL  08/17/2028  \n",
       "4  f5khcg3j-t9ex-68gy-38nuwp82zq9s      NORMAL  01/08/2023  \n",
       "5  htzkjd3u-hm1r-zdjf-hprsg3qnljxn      NORMAL  01/08/2023  \n",
       "6  urqndiwn-wcz5-585o-e1tkoztcgjpk      NORMAL  05/29/2025  \n",
       "7  xp1urqoh-d2og-4xp0-28mtgbk3s4m8      NORMAL  01/08/2023  \n",
       "8  hsmad7wq-o49o-7k5p-2tcjoj6pbye6      NORMAL  01/08/2023  \n",
       "9  23gsidon-aqm3-eh7o-uu26b5yp18o5      NORMAL  05/29/2025  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Opening the created file from folder\n",
    "datasetTable4 = spark.read.load(\"/home/eternal/bigData/finalProject/DataSet/users_rfiditem/part-00000-6c029b21-5d9a-4ca2-9c94-c8d4cd5cf7b5-c000.csv\", format = \"csv\",sep = \",\", inferScheme = True, header = True,minPartitions = 2 )\n",
    "datasetTable4.toPandas().head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e89f589b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73478c08",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be80b1cf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca04b2da",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30cac3f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Table 5 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c27a7ab3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter inventory type id: 574\n"
     ]
    }
   ],
   "source": [
    "inventory_type_id = input(\"Enter inventory type id: \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e6dfb204",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "cdf = Dataset.withColumn('id',round(rand()*100300,1000).cast(\"int\"))\\\n",
    "    .withColumn('user_id',randIdGenerator())\\\n",
    "    .withColumn(\"per_day\",round(rand()*9,5).cast(\"int\"))\\\n",
    "    .withColumn(\"per_week\",round(rand()*12,18).cast(\"int\"))\\\n",
    "    .withColumn(\"per_month\",round(rand()*12,28).cast(\"int\"))\\\n",
    "    .withColumn('created_date',lit('2021-01-29'))\\\n",
    "    .withColumn('updated_date',current_date())\\\n",
    "    .withColumn('inventory_type_id',lit(inventory_type_id)).drop('Employee Name','Contact','Emp ID')\n",
    "\n",
    "users_productlimit = cdf.select(\"id\",\"per_day\",\"per_week\",\"per_month\",\n",
    "                                \"created_date\",\"updated_date\",\"inventory_type_id\",\"user_id\")\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e8bceb47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>per_day</th>\n",
       "      <th>per_week</th>\n",
       "      <th>per_month</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>inventory_type_id</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>67294</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>g1hghdvs-mnc4-envl-0eyta639ep7i</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>67918</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>fyd9t08x-wzby-ylbt-0fu7nzf8fakv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>77900</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>qviki5r5-5tcr-1nxo-mg49c3hwtrcl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59437</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>jqn2ya26-2ke8-rdq5-oel433j30b7q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14404</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>g7s6hl5w-34m1-ep4t-5291v2c99gxk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>33804</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>9jrzklb9-h0sr-h7qx-ecwqldqs5uw4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>55346</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>lpd65pa4-8i0y-rq34-ev70u4x0im71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>42820</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>w2gvnlkl-4i61-emp9-mgjxhe723bzk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11067</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>dub6fisr-k2v8-h46q-aiz942xsj8bk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>98507</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>ymz8157t-lvav-c2hg-zpuvkda3j50u</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  per_day  per_week  per_month created_date updated_date  \\\n",
       "0  67294        2         2         10   2021-01-29   2022-04-10   \n",
       "1  67918        6        11          8   2021-01-29   2022-04-10   \n",
       "2  77900        7         8         10   2021-01-29   2022-04-10   \n",
       "3  59437        7         1          2   2021-01-29   2022-04-10   \n",
       "4  14404        0        10          5   2021-01-29   2022-04-10   \n",
       "5  33804        1        11          9   2021-01-29   2022-04-10   \n",
       "6  55346        1        11         11   2021-01-29   2022-04-10   \n",
       "7  42820        7         5          9   2021-01-29   2022-04-10   \n",
       "8  11067        4        11         10   2021-01-29   2022-04-10   \n",
       "9  98507        3        10          4   2021-01-29   2022-04-10   \n",
       "\n",
       "  inventory_type_id                          user_id  \n",
       "0               574  g1hghdvs-mnc4-envl-0eyta639ep7i  \n",
       "1               574  fyd9t08x-wzby-ylbt-0fu7nzf8fakv  \n",
       "2               574  qviki5r5-5tcr-1nxo-mg49c3hwtrcl  \n",
       "3               574  jqn2ya26-2ke8-rdq5-oel433j30b7q  \n",
       "4               574  g7s6hl5w-34m1-ep4t-5291v2c99gxk  \n",
       "5               574  9jrzklb9-h0sr-h7qx-ecwqldqs5uw4  \n",
       "6               574  lpd65pa4-8i0y-rq34-ev70u4x0im71  \n",
       "7               574  w2gvnlkl-4i61-emp9-mgjxhe723bzk  \n",
       "8               574  dub6fisr-k2v8-h46q-aiz942xsj8bk  \n",
       "9               574  ymz8157t-lvav-c2hg-zpuvkda3j50u  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_productlimit.toPandas().head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "705fefc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Dumping The created dataset as a csv file.\n",
    "\n",
    "users_productlimit.write.option(\"header\",True).csv(\"/home/eternal/bigData/finalProject/DataSet/users_productlimit\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8bcfa7e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>per_day</th>\n",
       "      <th>per_week</th>\n",
       "      <th>per_month</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>inventory_type_id</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>67294</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>59ekghdz-qt0i-wx30-njs5ng9ul39a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>67918</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>ug9jzfbh-b222-8put-zkxow2zioxxa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>77900</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>b8qh5vii-ni9u-vj2t-v897nnlthl9c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59437</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>pl4rjqbp-hyh5-8wb4-8s2hsu6gp4q5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14404</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>1xtme1qr-l07j-oqcl-8jvscpwuqxxc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>33804</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>z4znwbrp-9n8x-4yrk-193zjdhd8esi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>55346</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>pd8vmd6n-vlum-svbr-ndh7rcc4k3gn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>42820</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>um4naita-t2of-erck-b93x65jfi4ku</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11067</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>fdfj9w31-vdi2-agau-k36gq45l6oyv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>98507</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>2022-04-10</td>\n",
       "      <td>574</td>\n",
       "      <td>bf7m3f49-vj7z-z16h-u5cpcely2pt3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id per_day per_week per_month created_date updated_date  \\\n",
       "0  67294       2        2        10   2021-01-29   2022-04-10   \n",
       "1  67918       6       11         8   2021-01-29   2022-04-10   \n",
       "2  77900       7        8        10   2021-01-29   2022-04-10   \n",
       "3  59437       7        1         2   2021-01-29   2022-04-10   \n",
       "4  14404       0       10         5   2021-01-29   2022-04-10   \n",
       "5  33804       1       11         9   2021-01-29   2022-04-10   \n",
       "6  55346       1       11        11   2021-01-29   2022-04-10   \n",
       "7  42820       7        5         9   2021-01-29   2022-04-10   \n",
       "8  11067       4       11        10   2021-01-29   2022-04-10   \n",
       "9  98507       3       10         4   2021-01-29   2022-04-10   \n",
       "\n",
       "  inventory_type_id                          user_id  \n",
       "0               574  59ekghdz-qt0i-wx30-njs5ng9ul39a  \n",
       "1               574  ug9jzfbh-b222-8put-zkxow2zioxxa  \n",
       "2               574  b8qh5vii-ni9u-vj2t-v897nnlthl9c  \n",
       "3               574  pl4rjqbp-hyh5-8wb4-8s2hsu6gp4q5  \n",
       "4               574  1xtme1qr-l07j-oqcl-8jvscpwuqxxc  \n",
       "5               574  z4znwbrp-9n8x-4yrk-193zjdhd8esi  \n",
       "6               574  pd8vmd6n-vlum-svbr-ndh7rcc4k3gn  \n",
       "7               574  um4naita-t2of-erck-b93x65jfi4ku  \n",
       "8               574  fdfj9w31-vdi2-agau-k36gq45l6oyv  \n",
       "9               574  bf7m3f49-vj7z-z16h-u5cpcely2pt3  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#Opening the created file from folder\n",
    "datasetTable5 = spark.read.load(\"/home/eternal/bigData/finalProject/DataSet/users_productlimit/part-00000-25b0b47f-8d79-44e7-b55d-4edb9375f1ee-c000.csv\", format = \"csv\",sep = \",\", inferScheme = True, header = True,minPartitions = 2 )\n",
    "datasetTable5.toPandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f33458ed",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eec793d7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad482a12",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85326216",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Table 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "49486836",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter agent_name_id: 574\n"
     ]
    }
   ],
   "source": [
    "agent_name_id = input(\"Enter agent_name_id: \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "dff894c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "cdf = Dataset.withColumn('id',round(rand()*100300,1000).cast(\"int\"))\\\n",
    "    .withColumn(\"fixed_credit_limit\",round(rand()*90,100).cast(\"int\"))\\\n",
    "    .withColumn(\"credit_limit\",round(rand()*12,5).cast(\"int\"))\\\n",
    "    .withColumn(\"due_amount\",round(rand()*120,500).cast(\"int\"))\\\n",
    "    .withColumn('created_date',lit('2020-01-14'))\\\n",
    "    .withColumn('updated_date',current_date())\\\n",
    "    .withColumn('agent_name_id',lit(agent_name_id))\\\n",
    "    .withColumn('rfid_id',randIdGenerator()).drop('Employee Name','Contact','Emp ID')\n",
    "\n",
    "users_postpaidrfid = cdf.select(\"id\",\"fixed_credit_limit\",\"credit_limit\",\"due_amount\",\"created_date\",\"updated_date\",\"agent_name_id\",\"rfid_id\")\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5f671162",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>fixed_credit_limit</th>\n",
       "      <th>credit_limit</th>\n",
       "      <th>due_amount</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>agent_name_id</th>\n",
       "      <th>rfid_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>88470</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>72</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>8dkuqzik-dttz-tve2-vrjn7jhpyz3z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8162</td>\n",
       "      <td>32</td>\n",
       "      <td>6</td>\n",
       "      <td>113</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>c28kefbp-dnkh-i6zw-0ypou1skquly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19992</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>57</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>zys5w4ds-fidv-zy4h-a0er7hlg4at9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>66694</td>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>47</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>ddjd0fa9-7fg5-p27j-3nsdcjk20a6z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89844</td>\n",
       "      <td>26</td>\n",
       "      <td>7</td>\n",
       "      <td>38</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>w4c1ykac-nk3r-c9g8-t683lhgk3v84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>87676</td>\n",
       "      <td>65</td>\n",
       "      <td>7</td>\n",
       "      <td>69</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>x99i505t-lxw8-kojw-cogxsfbkgq50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>43573</td>\n",
       "      <td>48</td>\n",
       "      <td>7</td>\n",
       "      <td>69</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>mjh8yfvi-i397-r8jd-2ddd158l881g</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>28486</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>108</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>cw5vikp0-3jxh-4f6g-wba9rapse3o8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>76561</td>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>93</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>kmmz1rc9-6e12-n6w7-b18qjaxjr9es</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>14935</td>\n",
       "      <td>56</td>\n",
       "      <td>6</td>\n",
       "      <td>68</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>oebqg3xb-se93-k5v3-jg72dyektpjv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  fixed_credit_limit  credit_limit  due_amount created_date  \\\n",
       "0  88470                  87             0          72   2020-01-14   \n",
       "1   8162                  32             6         113   2020-01-14   \n",
       "2  19992                   5             5          57   2020-01-14   \n",
       "3  66694                  36            10          47   2020-01-14   \n",
       "4  89844                  26             7          38   2020-01-14   \n",
       "5  87676                  65             7          69   2020-01-14   \n",
       "6  43573                  48             7          69   2020-01-14   \n",
       "7  28486                  23             0         108   2020-01-14   \n",
       "8  76561                  50             3          93   2020-01-14   \n",
       "9  14935                  56             6          68   2020-01-14   \n",
       "\n",
       "  updated_date agent_name_id                          rfid_id  \n",
       "0   2022-04-12           574  8dkuqzik-dttz-tve2-vrjn7jhpyz3z  \n",
       "1   2022-04-12           574  c28kefbp-dnkh-i6zw-0ypou1skquly  \n",
       "2   2022-04-12           574  zys5w4ds-fidv-zy4h-a0er7hlg4at9  \n",
       "3   2022-04-12           574  ddjd0fa9-7fg5-p27j-3nsdcjk20a6z  \n",
       "4   2022-04-12           574  w4c1ykac-nk3r-c9g8-t683lhgk3v84  \n",
       "5   2022-04-12           574  x99i505t-lxw8-kojw-cogxsfbkgq50  \n",
       "6   2022-04-12           574  mjh8yfvi-i397-r8jd-2ddd158l881g  \n",
       "7   2022-04-12           574  cw5vikp0-3jxh-4f6g-wba9rapse3o8  \n",
       "8   2022-04-12           574  kmmz1rc9-6e12-n6w7-b18qjaxjr9es  \n",
       "9   2022-04-12           574  oebqg3xb-se93-k5v3-jg72dyektpjv  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_postpaidrfid.toPandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a49d4c1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Dumping The created dataset as a csv file.\n",
    "\n",
    "users_postpaidrfid.write.option(\"header\",True).csv(\"/home/eternal/bigData/finalProject/DataSet/users_postpaidrfid\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "35f9f500",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>fixed_credit_limit</th>\n",
       "      <th>credit_limit</th>\n",
       "      <th>due_amount</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "      <th>agent_name_id</th>\n",
       "      <th>rfid_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>88470</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>72</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>0s95m8dp-wdj5-smxu-fn7mxmqr6fbm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8162</td>\n",
       "      <td>32</td>\n",
       "      <td>6</td>\n",
       "      <td>113</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>42wu3gqo-7feo-4k6a-0f13zxohhejt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19992</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>57</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>l4nnrt47-k2uw-dp6p-6pmveusb4pe6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>66694</td>\n",
       "      <td>36</td>\n",
       "      <td>10</td>\n",
       "      <td>47</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>ck44jvr2-zcon-0rqt-api3z2uepqwm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89844</td>\n",
       "      <td>26</td>\n",
       "      <td>7</td>\n",
       "      <td>38</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>7khqjcut-1yl5-ixes-89ujhsydusc9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>87676</td>\n",
       "      <td>65</td>\n",
       "      <td>7</td>\n",
       "      <td>69</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>6ohby3ni-s7yu-59i2-ktnjsbpis56o</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>43573</td>\n",
       "      <td>48</td>\n",
       "      <td>7</td>\n",
       "      <td>69</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>r3u2yu8a-yco0-q6om-411g5zc9uzxy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>28486</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>108</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>9ti3h764-oqym-ufse-9bv3mdt3pizf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>76561</td>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>93</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>s6j7ds7f-atvl-qs62-1wybv5ju32mg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>14935</td>\n",
       "      <td>56</td>\n",
       "      <td>6</td>\n",
       "      <td>68</td>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>2022-04-12</td>\n",
       "      <td>574</td>\n",
       "      <td>3crtm8nm-e17m-dviq-tebsaj6wa01f</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id fixed_credit_limit credit_limit due_amount created_date updated_date  \\\n",
       "0  88470                 87            0         72   2020-01-14   2022-04-12   \n",
       "1   8162                 32            6        113   2020-01-14   2022-04-12   \n",
       "2  19992                  5            5         57   2020-01-14   2022-04-12   \n",
       "3  66694                 36           10         47   2020-01-14   2022-04-12   \n",
       "4  89844                 26            7         38   2020-01-14   2022-04-12   \n",
       "5  87676                 65            7         69   2020-01-14   2022-04-12   \n",
       "6  43573                 48            7         69   2020-01-14   2022-04-12   \n",
       "7  28486                 23            0        108   2020-01-14   2022-04-12   \n",
       "8  76561                 50            3         93   2020-01-14   2022-04-12   \n",
       "9  14935                 56            6         68   2020-01-14   2022-04-12   \n",
       "\n",
       "  agent_name_id                          rfid_id  \n",
       "0           574  0s95m8dp-wdj5-smxu-fn7mxmqr6fbm  \n",
       "1           574  42wu3gqo-7feo-4k6a-0f13zxohhejt  \n",
       "2           574  l4nnrt47-k2uw-dp6p-6pmveusb4pe6  \n",
       "3           574  ck44jvr2-zcon-0rqt-api3z2uepqwm  \n",
       "4           574  7khqjcut-1yl5-ixes-89ujhsydusc9  \n",
       "5           574  6ohby3ni-s7yu-59i2-ktnjsbpis56o  \n",
       "6           574  r3u2yu8a-yco0-q6om-411g5zc9uzxy  \n",
       "7           574  9ti3h764-oqym-ufse-9bv3mdt3pizf  \n",
       "8           574  s6j7ds7f-atvl-qs62-1wybv5ju32mg  \n",
       "9           574  3crtm8nm-e17m-dviq-tebsaj6wa01f  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#Opening the created file from folder\n",
    "\n",
    "datasetTable6 = spark.read.load(\"/home/eternal/bigData/finalProject/DataSet/users_postpaidrfid/part-00000-c5eb94cd-2410-4296-bc0b-cb9281110316-c000.csv\", format = \"csv\",sep = \",\", inferScheme = True, header = True,minPartitions = 2 )\n",
    "datasetTable6.toPandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d17baaf4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27fb9930",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca535651",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Table 7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "0cd86471",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "cdf = Dataset.withColumn('user_id',randIdGenerator())\\\n",
    "    .withColumn(\"per_day\",round(rand()*9,5).cast(\"int\"))\\\n",
    "    .withColumn(\"per_week\",round(rand()*12,7).cast(\"int\"))\\\n",
    "    .withColumn(\"per_month\",round(rand()*15,28).cast(\"int\"))\\\n",
    "    .withColumn('created_date',lit('2021-08-17'))\\\n",
    "    .withColumn('updated_date',current_date()).drop('Employee Name',\n",
    "                                                    'Contact','Emp ID')\n",
    "\n",
    "users_checkout_limit = cdf.select(\"user_id\",\"per_day\",\"per_week\",\n",
    "                                  \"per_month\",\"created_date\",\"updated_date\")\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "298d8a90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>per_day</th>\n",
       "      <th>per_week</th>\n",
       "      <th>per_month</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>riyzah7o-8413-xkoe-v5esmotmh0th</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ga7wjx1u-hrca-au1c-5bp93e0v7par</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>lnru6cer-copc-ka1b-409w6hgnsx8z</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aahng84s-yxwd-dotq-7pbr7iek046f</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>869bm00x-yhr9-0d5n-2sxyam7ci13u</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>zitesca9-3pmq-l9id-zg4638utdq1l</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>so0ii4vn-pu6y-seae-f0mejn9dx62h</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>gnbb1xtn-gigp-rtam-qrvndx477qli</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>kuug9cx6-byw8-qs9i-l6pgrs3ngogc</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>v24dlg92-9p6u-r6q0-wbif5fx12x60</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           user_id  per_day  per_week  per_month created_date  \\\n",
       "0  riyzah7o-8413-xkoe-v5esmotmh0th        2         6          1   2021-08-17   \n",
       "1  ga7wjx1u-hrca-au1c-5bp93e0v7par        7         2          7   2021-08-17   \n",
       "2  lnru6cer-copc-ka1b-409w6hgnsx8z        2         7          1   2021-08-17   \n",
       "3  aahng84s-yxwd-dotq-7pbr7iek046f        5        10          8   2021-08-17   \n",
       "4  869bm00x-yhr9-0d5n-2sxyam7ci13u        1         2         10   2021-08-17   \n",
       "5  zitesca9-3pmq-l9id-zg4638utdq1l        2         6          3   2021-08-17   \n",
       "6  so0ii4vn-pu6y-seae-f0mejn9dx62h        4         6          0   2021-08-17   \n",
       "7  gnbb1xtn-gigp-rtam-qrvndx477qli        0         9          1   2021-08-17   \n",
       "8  kuug9cx6-byw8-qs9i-l6pgrs3ngogc        2         1         14   2021-08-17   \n",
       "9  v24dlg92-9p6u-r6q0-wbif5fx12x60        0         1          3   2021-08-17   \n",
       "\n",
       "  updated_date  \n",
       "0   2022-04-06  \n",
       "1   2022-04-06  \n",
       "2   2022-04-06  \n",
       "3   2022-04-06  \n",
       "4   2022-04-06  \n",
       "5   2022-04-06  \n",
       "6   2022-04-06  \n",
       "7   2022-04-06  \n",
       "8   2022-04-06  \n",
       "9   2022-04-06  "
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_checkout_limit.toPandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "d7c4f26b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Dumping The created dataset as a csv file.\n",
    "\n",
    "users_checkout_limit.write.option(\"header\",True).csv(\"/home/eternal/bigData/finalProject/DataSet/users_checkout_limit\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "e4881619",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>per_day</th>\n",
       "      <th>per_week</th>\n",
       "      <th>per_month</th>\n",
       "      <th>created_date</th>\n",
       "      <th>updated_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>glsqwzgy-tudc-2r60-ap42fnvgfgjp</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cyh35ham-7q3q-fief-m57ikg069swp</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0q0crb55-jpuh-hd4b-tjajk487dbq7</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6f0pajel-vg8o-m4y7-dc678y6pg4ep</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5kmapnf4-h6rf-lfp5-i565a1nqj3yi</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>uvuqzm2u-c48v-om30-vk46zahkqgb4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>yqb344a8-ffuo-3jno-21bqhj11b7h0</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>xtuabkxq-3lqx-nair-ewsg8qv12902</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>t5628nmg-f4sy-b312-85nesbr6ssrj</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2yyked5b-4xvt-28u1-h895bxngrghs</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2021-08-17</td>\n",
       "      <td>2022-04-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           user_id per_day per_week per_month created_date  \\\n",
       "0  glsqwzgy-tudc-2r60-ap42fnvgfgjp       2        6         1   2021-08-17   \n",
       "1  cyh35ham-7q3q-fief-m57ikg069swp       7        2         7   2021-08-17   \n",
       "2  0q0crb55-jpuh-hd4b-tjajk487dbq7       2        7         1   2021-08-17   \n",
       "3  6f0pajel-vg8o-m4y7-dc678y6pg4ep       5       10         8   2021-08-17   \n",
       "4  5kmapnf4-h6rf-lfp5-i565a1nqj3yi       1        2        10   2021-08-17   \n",
       "5  uvuqzm2u-c48v-om30-vk46zahkqgb4       2        6         3   2021-08-17   \n",
       "6  yqb344a8-ffuo-3jno-21bqhj11b7h0       4        6         0   2021-08-17   \n",
       "7  xtuabkxq-3lqx-nair-ewsg8qv12902       0        9         1   2021-08-17   \n",
       "8  t5628nmg-f4sy-b312-85nesbr6ssrj       2        1        14   2021-08-17   \n",
       "9  2yyked5b-4xvt-28u1-h895bxngrghs       0        1         3   2021-08-17   \n",
       "\n",
       "  updated_date  \n",
       "0   2022-04-06  \n",
       "1   2022-04-06  \n",
       "2   2022-04-06  \n",
       "3   2022-04-06  \n",
       "4   2022-04-06  \n",
       "5   2022-04-06  \n",
       "6   2022-04-06  \n",
       "7   2022-04-06  \n",
       "8   2022-04-06  \n",
       "9   2022-04-06  "
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Opening the created file from folder\n",
    "\n",
    "datasetTable7 = spark.read.load(\"/home/eternal/bigData/finalProject/DataSet/users_checkout_limit/part-00000-03e0c516-3c72-48d5-9955-cd9b3ffc65fa-c000.csv\", format = \"csv\",sep = \",\", inferScheme = True, header = True,minPartitions = 2 )\n",
    "datasetTable7.toPandas().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2e3aef2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f116ae3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# yay  Project Complete... :-)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62d824b5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "303c7cfb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98770176",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7711a4fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type='text/css'>\n",
       ".CodeMirror{\n",
       "    font-size: 21px;\n",
       "}\n",
       "\n",
       "div.output_area pre {\n",
       "    font-size: 21px;\n",
       "}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Change font size\n",
    "\n",
    "%%html\n",
    "<style type='text/css'>\n",
    ".CodeMirror{\n",
    "    font-size: 21px;\n",
    "}\n",
    "\n",
    "div.output_area pre {\n",
    "    font-size: 21px;\n",
    "}\n",
    "</style>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "220f4405",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}