banking_p2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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": "4d22e9cc",
   "metadata": {},
   "source": [
    "# LOAN DATASET #"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": 5,
   "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": 4,
   "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": 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",
      "|    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": "markdown",
   "id": "5fc4368c",
   "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": "ad7a41d5",
   "metadata": {},
   "source": [
    "# TRANSACTION DATASET #"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "063511d6",
   "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": 3,
   "id": "9fa12b51",
   "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": 4,
   "id": "d300ed97",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+-----+\n",
      "|   Account No|count|\n",
      "+-------------+-----+\n",
      "|409000438611'| 4588|\n",
      "|     1196711'|10536|\n",
      "|     1196428'|48779|\n",
      "|409000493210'| 6014|\n",
      "|409000611074'| 1093|\n",
      "|409000425051'|  802|\n",
      "|409000405747'|   51|\n",
      "|409000362497'|29840|\n",
      "|409000493201'| 1044|\n",
      "|409000438620'|13454|\n",
      "+-------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#COUNT OF TRANSACTION ON EVERY ACCOUNT\n",
    "txn.groupBy(\"Account No\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "ee0d20e4",
   "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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "28e855b8",
   "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": "33512ac1",
   "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": "35a6e8a8",
   "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": "9995fde7",
   "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": "ed63952b",
   "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": 33,
   "id": "3e036ee6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------------------+----------------+\n",
      "|   Account No| TRANSACTION DETAILS| WITHDRAWAL AMT |\n",
      "+-------------+--------------------+----------------+\n",
      "|409000611074'|INDO GIBL Indiafo...|        133900.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        195800.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        143800.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        331650.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        129000.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        230013.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        367900.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        108000.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        141000.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        206000.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        242300.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        113250.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        206900.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        276000.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        171000.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        189800.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        271323.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        200600.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        176900.0|\n",
      "|409000611074'|INDO GIBL Indiafo...|        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()"
   ]
  }
 ],
 "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
}