banking_p2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "90939705",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()\n",
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.getOrCreate()\n",
    "from pyspark.sql.functions import *"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e6eb510",
   "metadata": {},
   "source": [
    "# LOAN DATASET #"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "61ff6326",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.csv(\"C:/Users/sidse/Downloads/big data course/ASSIGNMENTS/projects/Banking P2/loan.csv\", inferSchema = True, header = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "87e4d89a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Customer_ID: string (nullable = true)\n",
      " |-- Age: integer (nullable = true)\n",
      " |-- Gender: string (nullable = true)\n",
      " |-- Occupation: string (nullable = true)\n",
      " |-- Marital Status: string (nullable = true)\n",
      " |-- Family Size: integer (nullable = true)\n",
      " |-- Income: integer (nullable = true)\n",
      " |-- Expenditure: integer (nullable = true)\n",
      " |-- Use Frequency: integer (nullable = true)\n",
      " |-- Loan Category: string (nullable = true)\n",
      " |-- Loan Amount: string (nullable = true)\n",
      " |-- Overdue: integer (nullable = true)\n",
      " |--  Debt Record: integer (nullable = true)\n",
      " |--  Returned Cheque: integer (nullable = true)\n",
      " |--  Dishonour of Bill: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8bd5b2ce",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---+------+------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "|Customer_ID|Age|Gender|  Occupation|Marital Status|Family Size|Income|Expenditure|Use Frequency|Loan Category|Loan Amount|Overdue| Debt Record| Returned Cheque| Dishonour of Bill|\n",
      "+-----------+---+------+------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "|    IB14001| 30|  MALE|BANK MANAGER|        SINGLE|          4| 50000|      22199|            6|      HOUSING| 10,00,000 |      5|       42898|               6|                 9|\n",
      "|    IB14008| 44|  MALE|   PROFESSOR|       MARRIED|          6| 51000|      19999|            4|     SHOPPING|      50000|      3|       33999|               1|                 5|\n",
      "|    IB14012| 30|FEMALE|     DENTIST|        SINGLE|          3| 58450|      27675|            5|   TRAVELLING|      75000|      6|       20876|               3|                 1|\n",
      "|    IB14018| 29|  MALE|     TEACHER|       MARRIED|          5| 45767|      12787|            3|    GOLD LOAN|  6,00,000 |      7|       11000|               0|                 4|\n",
      "|    IB14022| 34|  MALE|      POLICE|        SINGLE|          4| 43521|      11999|            3|   AUTOMOBILE|  2,00,000 |      2|       43898|               1|                 2|\n",
      "+-----------+---+------+------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e10fa58a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "83383d08",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "500"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1256f51f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "500"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b04f601a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+-----+\n",
      "|     Loan Category|count|\n",
      "+------------------+-----+\n",
      "|         GOLD LOAN|   77|\n",
      "|           HOUSING|   67|\n",
      "|        AUTOMOBILE|   60|\n",
      "|        TRAVELLING|   53|\n",
      "|       RESTAURANTS|   41|\n",
      "|          SHOPPING|   35|\n",
      "|COMPUTER SOFTWARES|   35|\n",
      "|          BUSINESS|   24|\n",
      "|  EDUCATIONAL LOAN|   20|\n",
      "|        RESTAURANT|   20|\n",
      "|       ELECTRONICS|   14|\n",
      "|   HOME APPLIANCES|   14|\n",
      "|           DINNING|   14|\n",
      "|       AGRICULTURE|   12|\n",
      "|       BOOK STORES|    7|\n",
      "|          BUILDING|    7|\n",
      "+------------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#number of loans in each category\n",
    "df.groupBy(\"Loan Category\").count().orderBy(\"count\", ascending = False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "43e8f7fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "379"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number of people who have taken more than 1 lack loan\n",
    "df.filter(df[\"Loan Amount\"]>\"1,00,000\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "957fafb0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "198"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number of people with income greater than 60000 rupees\n",
    "df.filter(df[\"Income\"]>\"60000\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "5c03f421",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "137"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number of people with 2 or more returned cheques and income less than 50000\n",
    "df.filter((df[\" Returned Cheque\"]>\"1\") & (df[\"Income\"]<\"50000\")).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "df3cb381",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "283"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number of people with 2 or more returned cheques and are single\n",
    "df.filter((df[\" Returned Cheque\"]>\"1\") & (df[\"Marital Status\"]<\"SINGLE\")).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9366c41a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---+------+---------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "|Customer_ID|Age|Gender|     Occupation|Marital Status|Family Size|Income|Expenditure|Use Frequency|Loan Category|Loan Amount|Overdue| Debt Record| Returned Cheque| Dishonour of Bill|\n",
      "+-----------+---+------+---------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "|    IB14158| 54|  MALE|AIRPORT OFFICER|       MARRIED|          6| 80000|      62541|            2|   AUTOMOBILE| 20,45,789 |      1|       16599|               2|                 3|\n",
      "|    IB14176| 54|  MALE|AIRPORT OFFICER|       MARRIED|          6| 80000|      62541|            2|      HOUSING| 20,45,789 |      1|       16599|               2|                 3|\n",
      "|    IB14204| 54|  MALE|AIRPORT OFFICER|       MARRIED|          6| 81000|      62541|            2|      DINNING| 20,45,789 |      1|       16599|               2|                 3|\n",
      "|    IB14227| 54|  MALE|AIRPORT OFFICER|       MARRIED|          6| 80000|      62541|            2|      HOUSING| 20,45,789 |      1|       16599|               2|                 3|\n",
      "|    IB14278| 41|  MALE|   BANK MANAGER|       MARRIED|          6| 64125|      51246|            6|   TRAVELLING|  6,52,147 |      5|       16524|               3|                 3|\n",
      "|    IB15024| 26|  MALE|      DIETICIAN|        SINGLE|          3| 95425|      53086|            2|      HOUSING|     488076|      4|       61227|               5|                 2|\n",
      "+-----------+---+------+---------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#number of people with expenditure over 50000 a month \n",
    "df.filter((df[\"Expenditure\"]>\"50000\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "8adeb4a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---+--------------------+--------------+------+\n",
      "|Customer_ID|Age|          Occupation|Marital Status|Income|\n",
      "+-----------+---+--------------------+--------------+------+\n",
      "|    IB14001| 30|        BANK MANAGER|        SINGLE| 50000|\n",
      "|    IB14012| 30|             DENTIST|        SINGLE| 58450|\n",
      "|    IB14085| 30|         ELECTRICIAN|       MARRIED| 30000|\n",
      "|    IB14187| 30|             DENTIST|        SINGLE| 58450|\n",
      "|    IB14220| 30|             DENTIST|        SINGLE| 58450|\n",
      "|    IB14304| 30|         ELECTRICIAN|       MARRIED| 30000|\n",
      "|    IB14497| 30|     ACCOUNT MANAGER|       MARRIED| 52568|\n",
      "|    IB14566| 30| CHARTERED APPRAISER|       MARRIED| 81225|\n",
      "|    IB14633| 30|   SOFTWARE ENGINEER|       MARRIED| 62522|\n",
      "|    IB14667| 30|AGRICULTURAL ENGI...|        SINGLE| 85289|\n",
      "|    IB14706| 30|             TEACHER|        SINGLE| 51564|\n",
      "|    IB14721| 30|               PILOT|        SINGLE| 76333|\n",
      "|    IB14800| 30|             TEACHER|        SINGLE| 49370|\n",
      "|    IB14804| 30|            BUSINESS|        SINGLE| 53957|\n",
      "|    IB14831| 30|                NAVY|        SINGLE| 51862|\n",
      "|    IB14976| 30| CHARTERED APPRAISER|       MARRIED| 85225|\n",
      "|    IB15011| 30|             TEACHER|        SINGLE| 55564|\n",
      "|    IB15031| 30|               PILOT|        SINGLE| 86333|\n",
      "+-----------+---+--------------------+--------------+------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"Customer_ID\", \"Age\", \"Occupation\", \"Marital Status\", \"Income\").filter(df.Age==30).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3d50898b",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---+------+-------------------+--------------+-----------+------+-----------+-------------+------------------+-----------+-------+------------+----------------+------------------+\n",
      "|Customer_ID|Age|Gender|         Occupation|Marital Status|Family Size|Income|Expenditure|Use Frequency|     Loan Category|Loan Amount|Overdue| Debt Record| Returned Cheque| Dishonour of Bill|\n",
      "+-----------+---+------+-------------------+--------------+-----------+------+-----------+-------------+------------------+-----------+-------+------------+----------------+------------------+\n",
      "|    IB14001| 40|  MALE|       BANK MANAGER|        SINGLE|          4| 50000|      22199|            6|           HOUSING| 10,00,000 |      5|       42898|               6|                 9|\n",
      "|    IB14008| 44|  MALE|          PROFESSOR|       MARRIED|          6| 51000|      19999|            4|          SHOPPING|      50000|      3|       33999|               1|                 5|\n",
      "|    IB14012| 40|FEMALE|            DENTIST|        SINGLE|          3| 58450|      27675|            5|        TRAVELLING|      75000|      6|       20876|               3|                 1|\n",
      "|    IB14018| 29|  MALE|            TEACHER|       MARRIED|          5| 45767|      12787|            3|         GOLD LOAN|  6,00,000 |      7|       11000|               0|                 4|\n",
      "|    IB14022| 34|  MALE|             POLICE|        SINGLE|          4| 43521|      11999|            3|        AUTOMOBILE|  2,00,000 |      2|       43898|               1|                 2|\n",
      "|    IB14024| 55|FEMALE|              NURSE|       MARRIED|          6| 34999|      19888|            4|        AUTOMOBILE|      47787|      1|       50000|               0|                 3|\n",
      "|    IB14025| 39|FEMALE|            TEACHER|       MARRIED|          6| 46619|      18675|            4|           HOUSING| 12,09,867 |      8|       29999|               6|                 8|\n",
      "|    IB14027| 51|  MALE|     SYSTEM MANAGER|       MARRIED|          3| 49999|      19111|            5|       RESTAURANTS|      60676|      8|       13000|               2|                 5|\n",
      "|    IB14029| 24|FEMALE|            TEACHER|        SINGLE|          3| 45008|      17454|            4|        AUTOMOBILE|  3,99,435 |      9|       51987|               4|                 7|\n",
      "|    IB14031| 37|FEMALE|  SOFTWARE ENGINEER|       MARRIED|          5| 55999|      23999|            5|        AUTOMOBILE|      60999|      2|           0|               5|                 3|\n",
      "|    IB14032| 24|  MALE|       DATA ANALYST|        SINGLE|          4| 60111|      28999|            6|        AUTOMOBILE|      35232|      5|       33333|               1|                 2|\n",
      "|    IB14034| 32|  MALE|   PRODUCT ENGINEER|       MARRIED|          6|  null|      29000|            7|COMPUTER SOFTWARES|      80660|      6|        4500|               5|                 4|\n",
      "|    IB14037| 54|FEMALE|            TEACHER|       MARRIED|          5| 48099|      19999|            4|       RESTAURANTS|      30999|      1|       12000|               7|                 5|\n",
      "|    IB14039| 45|  MALE|    ACCOUNT MANAGER|       MARRIED|          7| 45777|      18452|            4|         GOLD LOAN|  9,87,611 |      7|       39999|               8|                 1|\n",
      "|    IB14041| 59|FEMALE|ASSISTANT PROFESSOR|       MARRIED|          4| 50999|      22999|            5|  EDUCATIONAL LOAN|  5,99,934 |      3|        9000|               9|                 9|\n",
      "|    IB14042| 25|FEMALE|             DOCTOR|        SINGLE|          4| 60111|      27111|            5|        TRAVELLING| 12,90,929 |      4|       18000|               1|                 0|\n",
      "|    IB14045| 31|  MALE|       STORE KEEPER|        SINGLE|          5| 40999|      11999|            3|       BOOK STORES|  1,67,654 |      1|        4500|               0|                 1|\n",
      "|    IB14049| 49|  MALE|       BANK MANAGER|       MARRIED|          4| 45999|      14500|            4|        TRAVELLING|      79999|      4|        6700|               7|                 3|\n",
      "|    IB14050| 56|  MALE|     CIVIL ENGINEER|       MARRIED|          4|  null|      13999|            3|           HOUSING| 10,65,577 |      6|       19999|               4|                 2|\n",
      "|    IB14054| 58|FEMALE|             DOCTOR|       MARRIED|          5| 60000|      25000|            5|           HOUSING|  9,00,000 |      5|       21000|               9|                 0|\n",
      "+-----------+---+------+-------------------+--------------+-----------+------+-----------+-------------+------------------+-----------+-------+------------+----------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "n = df.withColumn(\"Age\", when(df.Age==30,40).otherwise(df.Age))\n",
    "n.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "5914e0aa",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+---+------+------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "|Customer_ID|Age|Gender|  Occupation|Marital Status|Family Size|Income|Expenditure|Use Frequency|Loan Category|Loan Amount|Overdue| Debt Record| Returned Cheque| Dishonour of Bill|\n",
      "+-----------+---+------+------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "|    IB14001| 40|  MALE|BANK MANAGER|        SINGLE|          4| 50000|      22199|            6|      HOUSING| 10,00,000 |      5|       42898|               6|                 9|\n",
      "+-----------+---+------+------------+--------------+-----------+------+-----------+-------------+-------------+-----------+-------+------------+----------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "n.filter(df.Customer_ID==\"IB14001\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "27d17d90",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+\n",
      "|Marital Status|\n",
      "+--------------+\n",
      "|        SINGLE|\n",
      "|       MARRIED|\n",
      "+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"Marital Status\").distinct().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fbd0c89",
   "metadata": {},
   "source": [
    "# CREDIT CARD DATASET #"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "48fe73aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfc = spark.read.csv(\"C:/Users/sidse/Downloads/big data course/ASSIGNMENTS/projects/Banking P2/credit card.csv\", inferSchema = True, header = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9be751d9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- RowNumber: integer (nullable = true)\n",
      " |-- CustomerId: integer (nullable = true)\n",
      " |-- Surname: string (nullable = true)\n",
      " |-- CreditScore: integer (nullable = true)\n",
      " |-- Geography: string (nullable = true)\n",
      " |-- Gender: string (nullable = true)\n",
      " |-- Age: integer (nullable = true)\n",
      " |-- Tenure: integer (nullable = true)\n",
      " |-- Balance: double (nullable = true)\n",
      " |-- NumOfProducts: integer (nullable = true)\n",
      " |-- IsActiveMember: integer (nullable = true)\n",
      " |-- EstimatedSalary: double (nullable = true)\n",
      " |-- Exited: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfc.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "9c6b2b32",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dfc.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "439cad86",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10000"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfc.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "273e6dbf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfc.distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "13526ac8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+----------+--------+-----------+---------+------+---+------+---------+-------------+--------------+---------------+------+\n",
      "|RowNumber|CustomerId| Surname|CreditScore|Geography|Gender|Age|Tenure|  Balance|NumOfProducts|IsActiveMember|EstimatedSalary|Exited|\n",
      "+---------+----------+--------+-----------+---------+------+---+------+---------+-------------+--------------+---------------+------+\n",
      "|        1|  15634602|Hargrave|        619|   France|Female| 42|     2|      0.0|            1|             1|      101348.88|     1|\n",
      "|        2|  15647311|    Hill|        608|    Spain|Female| 41|     1| 83807.86|            1|             1|      112542.58|     0|\n",
      "|        3|  15619304|    Onio|        502|   France|Female| 42|     8| 159660.8|            3|             0|      113931.57|     1|\n",
      "|        4|  15701354|    Boni|        699|   France|Female| 39|     1|      0.0|            2|             0|       93826.63|     0|\n",
      "|        5|  15737888|Mitchell|        850|    Spain|Female| 43|     2|125510.82|            1|             1|        79084.1|     0|\n",
      "+---------+----------+--------+-----------+---------+------+---+------+---------+-------------+--------------+---------------+------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfc.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a4a66fb6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3116"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number of members who are elgible for credit card\n",
    "dfc.filter(dfc[\"CreditScore\"]>700).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "7ad5aeea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1637"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number of members who are  elgible and active in the bank\n",
    "dfc.filter((dfc[\"IsActiveMember\"]==1) & (dfc[\"CreditScore\"]>700)).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fb95f7f6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+----------+---------+-----------+---------+------+---+------+---------+-------------+--------------+---------------+------+\n",
      "|RowNumber|CustomerId|  Surname|CreditScore|Geography|Gender|Age|Tenure|  Balance|NumOfProducts|IsActiveMember|EstimatedSalary|Exited|\n",
      "+---------+----------+---------+-----------+---------+------+---+------+---------+-------------+--------------+---------------+------+\n",
      "|        2|  15647311|     Hill|        608|    Spain|Female| 41|     1| 83807.86|            1|             1|      112542.58|     0|\n",
      "|        5|  15737888| Mitchell|        850|    Spain|Female| 43|     2|125510.82|            1|             1|        79084.1|     0|\n",
      "|        6|  15574012|      Chu|        645|    Spain|  Male| 44|     8|113755.78|            2|             0|      149756.71|     1|\n",
      "|       12|  15737173|  Andrews|        497|    Spain|  Male| 24|     3|      0.0|            2|             0|       76390.01|     0|\n",
      "|       15|  15600882|    Scott|        635|    Spain|Female| 35|     7|      0.0|            2|             1|       65951.65|     0|\n",
      "|       18|  15788218|Henderson|        549|    Spain|Female| 24|     9|      0.0|            2|             1|       14406.41|     0|\n",
      "|       19|  15661507|  Muldrow|        587|    Spain|  Male| 45|     6|      0.0|            1|             0|      158684.81|     0|\n",
      "|       22|  15597945| Dellucci|        636|    Spain|Female| 32|     8|      0.0|            2|             0|      138555.46|     0|\n",
      "|       23|  15699309|Gerasimov|        510|    Spain|Female| 38|     4|      0.0|            1|             0|      118913.53|     1|\n",
      "|       31|  15589475|  Azikiwe|        591|    Spain|Female| 39|     3|      0.0|            3|             0|      140469.38|     1|\n",
      "|       34|  15659428|  Maggard|        520|    Spain|Female| 42|     6|      0.0|            2|             1|       34410.55|     0|\n",
      "|       35|  15732963| Clements|        722|    Spain|Female| 29|     9|      0.0|            2|             1|      142033.07|     0|\n",
      "|       37|  15788448|   Watson|        490|    Spain|  Male| 31|     3|145260.23|            1|             1|      114066.77|     0|\n",
      "|       38|  15729599|  Lorenzo|        804|    Spain|  Male| 33|     7|  76548.6|            1|             1|       98453.45|     0|\n",
      "|       41|  15619360|    Hsiao|        472|    Spain|  Male| 40|     4|      0.0|            1|             0|       70154.22|     0|\n",
      "|       45|  15684171|  Bianchi|        660|    Spain|Female| 61|     5|155931.11|            1|             1|      158338.39|     0|\n",
      "|       59|  15623944|    T'ien|        511|    Spain|Female| 66|     4|      0.0|            1|             0|        1643.11|     1|\n",
      "|       63|  15702014|  Jeffrey|        555|    Spain|  Male| 33|     1| 56084.69|            2|             0|      178798.13|     0|\n",
      "|       64|  15751208|  Pirozzi|        684|    Spain|  Male| 56|     8| 78707.16|            1|             1|       99398.36|     0|\n",
      "|       73|  15812518|  Palermo|        657|    Spain|Female| 37|     0|163607.18|            1|             1|       44203.55|     0|\n",
      "+---------+----------+---------+-----------+---------+------+---+------+---------+-------------+--------------+---------------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#credit card users in Spain \n",
    "dfc.filter(dfc[\"Geography\"]==\"Spain\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6ee4661a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1044"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfc.filter((dfc[\"EstimatedSalary\"]>100000) & (dfc[\"Exited\"]==1)).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "41df0d5e",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2432"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfc.filter((dfc[\"EstimatedSalary\"]<100000) & (dfc[\"NumOfProducts\"]>1)).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "179d7bbd",
   "metadata": {},
   "source": [
    "# TRANSACTION DATASET #"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8bb28abc",
   "metadata": {},
   "outputs": [],
   "source": [
    "txn = spark.read.csv(\"C:/Users/sidse/Downloads/big data course/ASSIGNMENTS/projects/Banking P2/txn.csv\", inferSchema=True, header =True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9959b748",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Account No: string (nullable = true)\n",
      " |-- TRANSACTION DETAILS: string (nullable = true)\n",
      " |-- VALUE DATE: string (nullable = true)\n",
      " |--  WITHDRAWAL AMT : double (nullable = true)\n",
      " |--  DEPOSIT AMT : double (nullable = true)\n",
      " |-- BALANCE AMT: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "txn.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a34784bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+-----+\n",
      "|Account No   |count|\n",
      "+-------------+-----+\n",
      "|1196428'     |48779|\n",
      "|409000362497'|29840|\n",
      "|409000438620'|13454|\n",
      "|1196711'     |10536|\n",
      "|409000493210'|6014 |\n",
      "|409000438611'|4588 |\n",
      "|409000611074'|1093 |\n",
      "|409000493201'|1044 |\n",
      "|409000425051'|802  |\n",
      "|409000405747'|51   |\n",
      "+-------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#COUNT OF TRANSACTION ON EVERY ACCOUNT\n",
    "txn.groupBy(\"Account No\").count().orderBy(\"count\", ascending = False).show(20, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "66b11b89",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+---------------------+\n",
      "|Account No   |max( WITHDRAWAL AMT )|\n",
      "+-------------+---------------------+\n",
      "|1196711'     |4.594475464E8        |\n",
      "|409000438620'|4.0E8                |\n",
      "|409000425051'|3.54E8               |\n",
      "|409000438611'|2.4E8                |\n",
      "|409000405747'|1.7E8                |\n",
      "|1196428'     |1.5E8                |\n",
      "|409000362497'|1.413662392E8        |\n",
      "|409000493210'|1.5E7                |\n",
      "|409000493201'|2500000.0            |\n",
      "|409000611074'|912000.0             |\n",
      "+-------------+---------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Maximum withdrawal amount\n",
    "txn.groupBy(\"Account No\").max(\" WITHDRAWAL AMT \").orderBy(\"max( WITHDRAWAL AMT )\", ascending = False).show(20, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f2825d44",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+---------------------+\n",
      "|   Account No|min( WITHDRAWAL AMT )|\n",
      "+-------------+---------------------+\n",
      "|409000493210'|                 0.01|\n",
      "|409000438611'|                  0.2|\n",
      "|     1196711'|                 0.25|\n",
      "|     1196428'|                 0.25|\n",
      "|409000438620'|                 0.34|\n",
      "|409000362497'|                 0.97|\n",
      "|409000425051'|                 1.25|\n",
      "|409000493201'|                  2.1|\n",
      "|409000405747'|                 21.0|\n",
      "|409000611074'|                120.0|\n",
      "+-------------+---------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#MINIMUM WITHDRAWAL AMOUNT OF AN ACCOUNT\n",
    "txn.groupBy(\"Account No\").min(\" WITHDRAWAL AMT \").orderBy(\"min( WITHDRAWAL AMT )\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "236db723",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+------------------+\n",
      "|   Account No|max( DEPOSIT AMT )|\n",
      "+-------------+------------------+\n",
      "|409000438620'|           5.448E8|\n",
      "|     1196711'|             5.0E8|\n",
      "|     1196428'|     2.119594422E8|\n",
      "|409000405747'|           2.021E8|\n",
      "|409000362497'|             2.0E8|\n",
      "|409000438611'|          1.7025E8|\n",
      "|409000425051'|             1.5E7|\n",
      "|409000493210'|             1.5E7|\n",
      "|409000611074'|         3000000.0|\n",
      "|409000493201'|         1000000.0|\n",
      "+-------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#MAXIMUM DEPOSIT AMOUNT OF AN ACCOUNT\n",
    "txn.groupBy(\"Account No\").max(\" DEPOSIT AMT \").orderBy(\"max( DEPOSIT AMT )\", ascending = False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "e99a49f6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+------------------+\n",
      "|   Account No|min( DEPOSIT AMT )|\n",
      "+-------------+------------------+\n",
      "|409000493210'|              0.01|\n",
      "|409000438611'|              0.03|\n",
      "|409000362497'|              0.03|\n",
      "|409000438620'|              0.07|\n",
      "|409000493201'|               0.9|\n",
      "|     1196428'|               1.0|\n",
      "|409000425051'|               1.0|\n",
      "|     1196711'|              1.01|\n",
      "|409000405747'|             500.0|\n",
      "|409000611074'|            1320.0|\n",
      "+-------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#MINIMUM DEPOSIT AMOUNT OF AN ACCOUNT\n",
    "txn.groupBy(\"Account No\").min(\" DEPOSIT AMT \").orderBy(\"min( DEPOSIT AMT )\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "12adea4a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------------------+\n",
      "|   Account No|    sum(BALANCE AMT)|\n",
      "+-------------+--------------------+\n",
      "|409000438611'|-2.49486577068339...|\n",
      "|     1196711'|-1.60476498101275E13|\n",
      "|     1196428'| -8.1418498130721E13|\n",
      "|409000493210'|-3.27584952132095...|\n",
      "|409000611074'|       1.615533622E9|\n",
      "|409000425051'|-3.77211841164998...|\n",
      "|409000405747'|-2.43108047067000...|\n",
      "|409000362497'| -5.2860004792808E13|\n",
      "|409000493201'|1.0420831829499985E9|\n",
      "|409000438620'|-7.12291867951358...|\n",
      "+-------------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#sum of balance in every bank account\n",
    "txn.groupBy(\"Account No\").sum(\"BALANCE AMT\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "198dbdc1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-----+\n",
      "|VALUE DATE|count|\n",
      "+----------+-----+\n",
      "| 27-Jul-17|  567|\n",
      "| 13-Aug-18|  463|\n",
      "|  8-Nov-17|  402|\n",
      "|  7-Oct-17|  382|\n",
      "| 10-Jul-18|  374|\n",
      "| 12-Dec-17|  367|\n",
      "| 12-Sep-18|  365|\n",
      "|  9-Aug-18|  360|\n",
      "| 19-Sep-17|  358|\n",
      "| 16-Mar-17|  353|\n",
      "| 10-Sep-18|  344|\n",
      "| 14-Jul-17|  333|\n",
      "|  7-Mar-18|  319|\n",
      "| 11-Oct-18|  303|\n",
      "| 22-Aug-17|  301|\n",
      "|  9-Jan-18|  299|\n",
      "|  9-Oct-18|  297|\n",
      "| 20-Apr-18|  296|\n",
      "|  9-Jul-18|  292|\n",
      "|  7-Apr-18|  291|\n",
      "+----------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Number of transaction on each date\n",
    "txn.groupBy(\"VALUE DATE\").count().orderBy(\"count\", ascending = False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5b649253",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------------------------------+----------------+\n",
      "|Account No   |TRANSACTION DETAILS             | WITHDRAWAL AMT |\n",
      "+-------------+--------------------------------+----------------+\n",
      "|409000611074'|INDO GIBL Indiaforensic STL01071|133900.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL04071|195800.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL10071|143800.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL11071|331650.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL12071|129000.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL13071|230013.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL14071|367900.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL15071|108000.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL17071|141000.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL22071|206000.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL02081|242300.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL04081|113250.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL07081|206900.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL08081|276000.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL09081|171000.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL11081|189800.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL14081|271323.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL16081|200600.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL17081|176900.0        |\n",
      "|409000611074'|INDO GIBL Indiaforensic STL18081|150050.0        |\n",
      "+-------------+--------------------------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#List of customers with withdrawal amount more than 1 lakh\n",
    "txn.select(\"Account No\",\"TRANSACTION DETAILS\",\" WITHDRAWAL AMT \").filter(txn[\" WITHDRAWAL AMT \"]>100000).show(20, False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}