declined_deposits_task.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4dbdd1b8",
   "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>player_id</th>\n",
       "      <th>txn_timestamp</th>\n",
       "      <th>deposit_status</th>\n",
       "      <th>attempted_deposit_amt_gbp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9109073</td>\n",
       "      <td>2018-04-22 23:31:20 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>89.165977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12896373</td>\n",
       "      <td>2018-05-09 17:36:42 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>261.437054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 00:09:49 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 23:54:17 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-16 01:52:41 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141472</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 20:25:47 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141473</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:27:44 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141474</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:28:45 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141475</th>\n",
       "      <td>575327473</td>\n",
       "      <td>2018-05-15 18:45:00 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>6.170000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141476</th>\n",
       "      <td>575327773</td>\n",
       "      <td>2018-05-15 20:30:12 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>18.510000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141477 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        player_id            txn_timestamp deposit_status  \\\n",
       "0         9109073  2018-04-22 23:31:20 UTC        Success   \n",
       "1        12896373  2018-05-09 17:36:42 UTC        Success   \n",
       "2        16956073  2018-04-15 00:09:49 UTC        Success   \n",
       "3        16956073  2018-04-15 23:54:17 UTC        Success   \n",
       "4        16956073  2018-04-16 01:52:41 UTC        Success   \n",
       "...           ...                      ...            ...   \n",
       "141472  575327373  2018-05-15 20:25:47 UTC        Success   \n",
       "141473  575327373  2018-05-15 21:27:44 UTC        Success   \n",
       "141474  575327373  2018-05-15 21:28:45 UTC        Success   \n",
       "141475  575327473  2018-05-15 18:45:00 UTC        Success   \n",
       "141476  575327773  2018-05-15 20:30:12 UTC        Success   \n",
       "\n",
       "        attempted_deposit_amt_gbp  \n",
       "0                       89.165977  \n",
       "1                      261.437054  \n",
       "2                        8.968083  \n",
       "3                        8.968428  \n",
       "4                        8.968428  \n",
       "...                           ...  \n",
       "141472                  61.700000  \n",
       "141473                  61.700000  \n",
       "141474                  61.700000  \n",
       "141475                   6.170000  \n",
       "141476                  18.510000  \n",
       "\n",
       "[141477 rows x 4 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "df =pd.read_csv(r\"C:\\Users\\siutu\\Entain_interview_task_2022\\declined_deposits_test.csv\")\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "084d156c",
   "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>player_id</th>\n",
       "      <th>txn_timestamp</th>\n",
       "      <th>deposit_status</th>\n",
       "      <th>attempted_deposit_amt_gbp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9109073</td>\n",
       "      <td>2018-04-22 23:31:20 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>89.165977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12896373</td>\n",
       "      <td>2018-05-09 17:36:42 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>261.437054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 00:09:49 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 23:54:17 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-16 01:52:41 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141472</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 20:25:47 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141473</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:27:44 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141474</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:28:45 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141475</th>\n",
       "      <td>575327473</td>\n",
       "      <td>2018-05-15 18:45:00 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>6.170000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141476</th>\n",
       "      <td>575327773</td>\n",
       "      <td>2018-05-15 20:30:12 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>18.510000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141477 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        player_id            txn_timestamp deposit_status  \\\n",
       "0         9109073  2018-04-22 23:31:20 UTC        Success   \n",
       "1        12896373  2018-05-09 17:36:42 UTC        Success   \n",
       "2        16956073  2018-04-15 00:09:49 UTC        Success   \n",
       "3        16956073  2018-04-15 23:54:17 UTC        Success   \n",
       "4        16956073  2018-04-16 01:52:41 UTC        Success   \n",
       "...           ...                      ...            ...   \n",
       "141472  575327373  2018-05-15 20:25:47 UTC        Success   \n",
       "141473  575327373  2018-05-15 21:27:44 UTC        Success   \n",
       "141474  575327373  2018-05-15 21:28:45 UTC        Success   \n",
       "141475  575327473  2018-05-15 18:45:00 UTC        Success   \n",
       "141476  575327773  2018-05-15 20:30:12 UTC        Success   \n",
       "\n",
       "        attempted_deposit_amt_gbp  \n",
       "0                       89.165977  \n",
       "1                      261.437054  \n",
       "2                        8.968083  \n",
       "3                        8.968428  \n",
       "4                        8.968428  \n",
       "...                           ...  \n",
       "141472                  61.700000  \n",
       "141473                  61.700000  \n",
       "141474                  61.700000  \n",
       "141475                   6.170000  \n",
       "141476                  18.510000  \n",
       "\n",
       "[141477 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.sort_values(by=[\"player_id\",\"txn_timestamp\"],ascending=[True,True])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b5e4315d",
   "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>player_id</th>\n",
       "      <th>txn_timestamp</th>\n",
       "      <th>deposit_status</th>\n",
       "      <th>attempted_deposit_amt_gbp</th>\n",
       "      <th>consecutive_fails</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9109073</td>\n",
       "      <td>2018-04-22 23:31:20 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>89.165977</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12896373</td>\n",
       "      <td>2018-05-09 17:36:42 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>261.437054</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 00:09:49 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968083</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 23:54:17 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-16 01:52:41 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141472</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 20:25:47 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141473</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:27:44 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141474</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:28:45 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141475</th>\n",
       "      <td>575327473</td>\n",
       "      <td>2018-05-15 18:45:00 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>6.170000</td>\n",
       "      <td>0</td>\n",
       "      <td>141476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141476</th>\n",
       "      <td>575327773</td>\n",
       "      <td>2018-05-15 20:30:12 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>18.510000</td>\n",
       "      <td>0</td>\n",
       "      <td>141477</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141477 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        player_id            txn_timestamp deposit_status  \\\n",
       "0         9109073  2018-04-22 23:31:20 UTC        Success   \n",
       "1        12896373  2018-05-09 17:36:42 UTC        Success   \n",
       "2        16956073  2018-04-15 00:09:49 UTC        Success   \n",
       "3        16956073  2018-04-15 23:54:17 UTC        Success   \n",
       "4        16956073  2018-04-16 01:52:41 UTC        Success   \n",
       "...           ...                      ...            ...   \n",
       "141472  575327373  2018-05-15 20:25:47 UTC        Success   \n",
       "141473  575327373  2018-05-15 21:27:44 UTC        Success   \n",
       "141474  575327373  2018-05-15 21:28:45 UTC        Success   \n",
       "141475  575327473  2018-05-15 18:45:00 UTC        Success   \n",
       "141476  575327773  2018-05-15 20:30:12 UTC        Success   \n",
       "\n",
       "        attempted_deposit_amt_gbp  consecutive_fails      ID  \n",
       "0                       89.165977                  0       1  \n",
       "1                      261.437054                  0       2  \n",
       "2                        8.968083                  0       3  \n",
       "3                        8.968428                  0       4  \n",
       "4                        8.968428                  0       5  \n",
       "...                           ...                ...     ...  \n",
       "141472                  61.700000                  0  141473  \n",
       "141473                  61.700000                  0  141474  \n",
       "141474                  61.700000                  0  141475  \n",
       "141475                   6.170000                  0  141476  \n",
       "141476                  18.510000                  0  141477  \n",
       "\n",
       "[141477 rows x 6 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df\n",
    "df2['consecutive_fails']=0\n",
    "df2['ID'] = df2['ID'] = range(1, 1+len(df))\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "85aeb618",
   "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>player_id</th>\n",
       "      <th>txn_timestamp</th>\n",
       "      <th>deposit_status</th>\n",
       "      <th>attempted_deposit_amt_gbp</th>\n",
       "      <th>consecutive_fails</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9109073</td>\n",
       "      <td>2018-04-22 23:31:20 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>89.165977</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12896373</td>\n",
       "      <td>2018-05-09 17:36:42 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>261.437054</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 00:09:49 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968083</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 23:54:17 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-16 01:52:41 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141472</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 20:25:47 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141473</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:27:44 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141474</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:28:45 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141475</th>\n",
       "      <td>575327473</td>\n",
       "      <td>2018-05-15 18:45:00 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>6.170000</td>\n",
       "      <td>0</td>\n",
       "      <td>141476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141476</th>\n",
       "      <td>575327773</td>\n",
       "      <td>2018-05-15 20:30:12 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>18.510000</td>\n",
       "      <td>0</td>\n",
       "      <td>141477</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141477 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        player_id            txn_timestamp deposit_status  \\\n",
       "0         9109073  2018-04-22 23:31:20 UTC        Success   \n",
       "1        12896373  2018-05-09 17:36:42 UTC        Success   \n",
       "2        16956073  2018-04-15 00:09:49 UTC        Success   \n",
       "3        16956073  2018-04-15 23:54:17 UTC        Success   \n",
       "4        16956073  2018-04-16 01:52:41 UTC        Success   \n",
       "...           ...                      ...            ...   \n",
       "141472  575327373  2018-05-15 20:25:47 UTC        Success   \n",
       "141473  575327373  2018-05-15 21:27:44 UTC        Success   \n",
       "141474  575327373  2018-05-15 21:28:45 UTC        Success   \n",
       "141475  575327473  2018-05-15 18:45:00 UTC        Success   \n",
       "141476  575327773  2018-05-15 20:30:12 UTC        Success   \n",
       "\n",
       "        attempted_deposit_amt_gbp  consecutive_fails      ID  \n",
       "0                       89.165977                  0       1  \n",
       "1                      261.437054                  0       2  \n",
       "2                        8.968083                  0       3  \n",
       "3                        8.968428                  0       4  \n",
       "4                        8.968428                  0       5  \n",
       "...                           ...                ...     ...  \n",
       "141472                  61.700000                  0  141473  \n",
       "141473                  61.700000                  0  141474  \n",
       "141474                  61.700000                  0  141475  \n",
       "141475                   6.170000                  0  141476  \n",
       "141476                  18.510000                  0  141477  \n",
       "\n",
       "[141477 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a=0 #previous fail times\n",
    "    \n",
    "for i in df2[\"ID\"]:\n",
    "    if (df2.deposit_status[i-1] == \"Fail\"):\n",
    "        if (i != 1):\n",
    "            if (df2.at[i-2,\"player_id\"]==df2.at[i-1,\"player_id\"]):\n",
    "                a = a + 1\n",
    "                df2.at[i-1,\"consecutive_fails\"] = a\n",
    "                ##print (a,\"fail\")\n",
    "            elif (df2.at[i-2,\"player_id\"]!=df2.at[i-1,\"player_id\"]):\n",
    "                a = 1\n",
    "                df2.at[i-1,\"consecutive_fails\"] = a\n",
    "            else:\n",
    "                break\n",
    "        else:\n",
    "            a = 1\n",
    "            df2.at[i-1,\"consecutive_fails\"] = a\n",
    "\n",
    "    elif (df2.deposit_status[i-1] == \"Success\"):\n",
    "        #print (\"Success\")\n",
    "        a = 0\n",
    "    else:\n",
    "        break\n",
    "df2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2c61d5af",
   "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>player_id</th>\n",
       "      <th>txn_timestamp</th>\n",
       "      <th>deposit_status</th>\n",
       "      <th>attempted_deposit_amt_gbp</th>\n",
       "      <th>consecutive_fails</th>\n",
       "      <th>ID</th>\n",
       "      <th>freezed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9109073</td>\n",
       "      <td>2018-04-22 23:31:20 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>89.165977</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12896373</td>\n",
       "      <td>2018-05-09 17:36:42 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>261.437054</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 00:09:49 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968083</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 23:54:17 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-16 01:52:41 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141472</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 20:25:47 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141473</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141473</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:27:44 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141474</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141474</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:28:45 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141475</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141475</th>\n",
       "      <td>575327473</td>\n",
       "      <td>2018-05-15 18:45:00 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>6.170000</td>\n",
       "      <td>0</td>\n",
       "      <td>141476</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141476</th>\n",
       "      <td>575327773</td>\n",
       "      <td>2018-05-15 20:30:12 UTC</td>\n",
       "      <td>Success</td>\n",
       "      <td>18.510000</td>\n",
       "      <td>0</td>\n",
       "      <td>141477</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141477 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        player_id            txn_timestamp deposit_status  \\\n",
       "0         9109073  2018-04-22 23:31:20 UTC        Success   \n",
       "1        12896373  2018-05-09 17:36:42 UTC        Success   \n",
       "2        16956073  2018-04-15 00:09:49 UTC        Success   \n",
       "3        16956073  2018-04-15 23:54:17 UTC        Success   \n",
       "4        16956073  2018-04-16 01:52:41 UTC        Success   \n",
       "...           ...                      ...            ...   \n",
       "141472  575327373  2018-05-15 20:25:47 UTC        Success   \n",
       "141473  575327373  2018-05-15 21:27:44 UTC        Success   \n",
       "141474  575327373  2018-05-15 21:28:45 UTC        Success   \n",
       "141475  575327473  2018-05-15 18:45:00 UTC        Success   \n",
       "141476  575327773  2018-05-15 20:30:12 UTC        Success   \n",
       "\n",
       "        attempted_deposit_amt_gbp  consecutive_fails      ID  freezed  \n",
       "0                       89.165977                  0       1    False  \n",
       "1                      261.437054                  0       2    False  \n",
       "2                        8.968083                  0       3    False  \n",
       "3                        8.968428                  0       4    False  \n",
       "4                        8.968428                  0       5    False  \n",
       "...                           ...                ...     ...      ...  \n",
       "141472                  61.700000                  0  141473    False  \n",
       "141473                  61.700000                  0  141474    False  \n",
       "141474                  61.700000                  0  141475    False  \n",
       "141475                   6.170000                  0  141476    False  \n",
       "141476                  18.510000                  0  141477    False  \n",
       "\n",
       "[141477 rows x 7 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['freezed'] = False\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "702a5731",
   "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>player_id</th>\n",
       "      <th>txn_timestamp</th>\n",
       "      <th>deposit_status</th>\n",
       "      <th>attempted_deposit_amt_gbp</th>\n",
       "      <th>consecutive_fails</th>\n",
       "      <th>ID</th>\n",
       "      <th>freezed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9109073</td>\n",
       "      <td>2018-04-22 23:31:20</td>\n",
       "      <td>Success</td>\n",
       "      <td>89.165977</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12896373</td>\n",
       "      <td>2018-05-09 17:36:42</td>\n",
       "      <td>Success</td>\n",
       "      <td>261.437054</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 00:09:49</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968083</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 23:54:17</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-16 01:52:41</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141472</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 20:25:47</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141473</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141473</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:27:44</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141474</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141474</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:28:45</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141475</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141475</th>\n",
       "      <td>575327473</td>\n",
       "      <td>2018-05-15 18:45:00</td>\n",
       "      <td>Success</td>\n",
       "      <td>6.170000</td>\n",
       "      <td>0</td>\n",
       "      <td>141476</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141476</th>\n",
       "      <td>575327773</td>\n",
       "      <td>2018-05-15 20:30:12</td>\n",
       "      <td>Success</td>\n",
       "      <td>18.510000</td>\n",
       "      <td>0</td>\n",
       "      <td>141477</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141477 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        player_id       txn_timestamp deposit_status  \\\n",
       "0         9109073 2018-04-22 23:31:20        Success   \n",
       "1        12896373 2018-05-09 17:36:42        Success   \n",
       "2        16956073 2018-04-15 00:09:49        Success   \n",
       "3        16956073 2018-04-15 23:54:17        Success   \n",
       "4        16956073 2018-04-16 01:52:41        Success   \n",
       "...           ...                 ...            ...   \n",
       "141472  575327373 2018-05-15 20:25:47        Success   \n",
       "141473  575327373 2018-05-15 21:27:44        Success   \n",
       "141474  575327373 2018-05-15 21:28:45        Success   \n",
       "141475  575327473 2018-05-15 18:45:00        Success   \n",
       "141476  575327773 2018-05-15 20:30:12        Success   \n",
       "\n",
       "        attempted_deposit_amt_gbp  consecutive_fails      ID  freezed  \n",
       "0                       89.165977                  0       1    False  \n",
       "1                      261.437054                  0       2    False  \n",
       "2                        8.968083                  0       3    False  \n",
       "3                        8.968428                  0       4    False  \n",
       "4                        8.968428                  0       5    False  \n",
       "...                           ...                ...     ...      ...  \n",
       "141472                  61.700000                  0  141473    False  \n",
       "141473                  61.700000                  0  141474    False  \n",
       "141474                  61.700000                  0  141475    False  \n",
       "141475                   6.170000                  0  141476    False  \n",
       "141476                  18.510000                  0  141477    False  \n",
       "\n",
       "[141477 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[\"txn_timestamp\"] = df2[\"txn_timestamp\"].str.replace(\" UTC\",\"\")\n",
    "df2[\"txn_timestamp\"] = pd.to_datetime(df2[\"txn_timestamp\"])\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "id": "23131d77",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame:\n",
      "        player_id       txn_timestamp deposit_status  \\\n",
      "0         9109073 2018-04-22 23:31:20        Success   \n",
      "1        12896373 2018-05-09 17:36:42        Success   \n",
      "2        16956073 2018-04-15 00:09:49        Success   \n",
      "3        16956073 2018-04-15 23:54:17        Success   \n",
      "4        16956073 2018-04-16 01:52:41        Success   \n",
      "...           ...                 ...            ...   \n",
      "141472  575327373 2018-05-15 20:25:47        Success   \n",
      "141473  575327373 2018-05-15 21:27:44        Success   \n",
      "141474  575327373 2018-05-15 21:28:45        Success   \n",
      "141475  575327473 2018-05-15 18:45:00        Success   \n",
      "141476  575327773 2018-05-15 20:30:12        Success   \n",
      "\n",
      "        attempted_deposit_amt_gbp  consecutive_fails      ID  freezed  \n",
      "0                       89.165977                  0       1    False  \n",
      "1                      261.437054                  0       2    False  \n",
      "2                        8.968083                  0       3    False  \n",
      "3                        8.968428                  0       4    False  \n",
      "4                        8.968428                  0       5    False  \n",
      "...                           ...                ...     ...      ...  \n",
      "141472                  61.700000                  0  141473    False  \n",
      "141473                  61.700000                  0  141474    False  \n",
      "141474                  61.700000                  0  141475    False  \n",
      "141475                   6.170000                  0  141476    False  \n",
      "141476                  18.510000                  0  141477    False  \n",
      "\n",
      "[141477 rows x 7 columns]\n",
      "Output:\n",
      "player_id                             int64\n",
      "txn_timestamp                datetime64[ns]\n",
      "deposit_status                       object\n",
      "attempted_deposit_amt_gbp           float64\n",
      "consecutive_fails                     int64\n",
      "ID                                    int64\n",
      "freezed                                bool\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(\"DataFrame:\")\n",
    "print(df2)\n",
    "\n",
    "# apply the dtype attribute\n",
    "result = df2.dtypes\n",
    "\n",
    "print(\"Output:\")\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cbb2c291",
   "metadata": {
    "scrolled": false
   },
   "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>player_id</th>\n",
       "      <th>txn_timestamp</th>\n",
       "      <th>deposit_status</th>\n",
       "      <th>attempted_deposit_amt_gbp</th>\n",
       "      <th>consecutive_fails</th>\n",
       "      <th>ID</th>\n",
       "      <th>freezed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9109073</td>\n",
       "      <td>2018-04-22 23:31:20</td>\n",
       "      <td>Success</td>\n",
       "      <td>89.165977</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12896373</td>\n",
       "      <td>2018-05-09 17:36:42</td>\n",
       "      <td>Success</td>\n",
       "      <td>261.437054</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 00:09:49</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968083</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-15 23:54:17</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16956073</td>\n",
       "      <td>2018-04-16 01:52:41</td>\n",
       "      <td>Success</td>\n",
       "      <td>8.968428</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141472</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 20:25:47</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141473</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141473</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:27:44</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141474</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141474</th>\n",
       "      <td>575327373</td>\n",
       "      <td>2018-05-15 21:28:45</td>\n",
       "      <td>Success</td>\n",
       "      <td>61.700000</td>\n",
       "      <td>0</td>\n",
       "      <td>141475</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141475</th>\n",
       "      <td>575327473</td>\n",
       "      <td>2018-05-15 18:45:00</td>\n",
       "      <td>Success</td>\n",
       "      <td>6.170000</td>\n",
       "      <td>0</td>\n",
       "      <td>141476</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141476</th>\n",
       "      <td>575327773</td>\n",
       "      <td>2018-05-15 20:30:12</td>\n",
       "      <td>Success</td>\n",
       "      <td>18.510000</td>\n",
       "      <td>0</td>\n",
       "      <td>141477</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141477 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        player_id       txn_timestamp deposit_status  \\\n",
       "0         9109073 2018-04-22 23:31:20        Success   \n",
       "1        12896373 2018-05-09 17:36:42        Success   \n",
       "2        16956073 2018-04-15 00:09:49        Success   \n",
       "3        16956073 2018-04-15 23:54:17        Success   \n",
       "4        16956073 2018-04-16 01:52:41        Success   \n",
       "...           ...                 ...            ...   \n",
       "141472  575327373 2018-05-15 20:25:47        Success   \n",
       "141473  575327373 2018-05-15 21:27:44        Success   \n",
       "141474  575327373 2018-05-15 21:28:45        Success   \n",
       "141475  575327473 2018-05-15 18:45:00        Success   \n",
       "141476  575327773 2018-05-15 20:30:12        Success   \n",
       "\n",
       "        attempted_deposit_amt_gbp  consecutive_fails      ID  freezed  \n",
       "0                       89.165977                  0       1    False  \n",
       "1                      261.437054                  0       2    False  \n",
       "2                        8.968083                  0       3    False  \n",
       "3                        8.968428                  0       4    False  \n",
       "4                        8.968428                  0       5    False  \n",
       "...                           ...                ...     ...      ...  \n",
       "141472                  61.700000                  0  141473    False  \n",
       "141473                  61.700000                  0  141474    False  \n",
       "141474                  61.700000                  0  141475    False  \n",
       "141475                   6.170000                  0  141476    False  \n",
       "141476                  18.510000                  0  141477    False  \n",
       "\n",
       "[141477 rows x 7 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "freeze_period = 2\n",
    "c_fail_freeze = 5\n",
    "freeze_start_time = datetime(year = 1, month = 1, day = 1, hour = 0, minute = 0, second = 0)\n",
    "freeze_end_time = datetime(year = 1, month = 1, day = 1, hour = 0, minute = 0, second = 0)\n",
    "\n",
    "for i in df2[\"ID\"]:\n",
    "    if (i != 1): \n",
    "        if (df2.at[i-1,\"consecutive_fails\"] == c_fail_freeze):\n",
    "            freeze_start_time = df2.at[i-1,\"txn_timestamp\"]\n",
    "            freeze_end_time = df2.at[i-1,\"txn_timestamp\"] + pd.DateOffset(hours=freeze_period)\n",
    "            ##print('1')        \n",
    "        if (df2.at[i-2,\"player_id\"]==df2.at[i-1,\"player_id\"]):\n",
    "            if (df2.at[i-1,\"txn_timestamp\"] > freeze_start_time and df2.at[i-1,\"txn_timestamp\"] <= freeze_end_time): \n",
    "                df2.at[i-1,'freezed'] = True\n",
    "            else:\n",
    "                df2.at[i-1,'freezed'] = False\n",
    "                ##print(\"Freezed\")\n",
    "        if (df2.at[i-2,\"player_id\"] != df2.at[i-1,\"player_id\"]):\n",
    "            freeze_start_time = datetime(year = 1, month = 1, day = 1, hour = 0, minute = 0, second = 0)\n",
    "            freeze_end_time = datetime(year = 1, month = 1, day = 1, hour = 0, minute = 0, second = 0)\n",
    "            df2.at[i-1,'freezed'] = False\n",
    "    else:\n",
    "        if (df2.at[i-1,\"consecutive_fails\"] == c_fail_freeze):\n",
    "            freeze_start_time = df2.at[i-1,\"txn_timestamp\"]\n",
    "            freeze_end_time = df2.at[i-1,\"txn_timestamp\"] + pd.DateOffset(hours=freeze_period)\n",
    "        df2.at[i-1,'freezed'] = False\n",
    "\n",
    "df2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "id": "74b25c18",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 303,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.at[71,'freezed']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ac98acbc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "impacted_customers = []\n",
    "impacted_customers.clear()\n",
    "number_of_impacted_customers = 0\n",
    "\n",
    "for i in df2[\"ID\"]:\n",
    "    if (df2.at[i-1,'freezed'] == True and df2.deposit_status[i-1] != \"Fail\"):\n",
    "        impacted_customers.append(df2.player_id[i-1])  \n",
    "\n",
    "#print (impacted_customers)\n",
    "number_of_impacted_customers = pd.Series(impacted_customers).nunique()\n",
    "number_of_impacted_customers\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "id": "937d0dcc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "47470.14982991944"
      ]
     },
     "execution_count": 298,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "impacted_deposits = []\n",
    "impacted_deposits.clear()\n",
    "sum_of_impacted_deposits = 0\n",
    "\n",
    "for i in df2[\"ID\"]:\n",
    "    if (df2.at[i-1,'freezed'] == True and df2.deposit_status[i-1] != \"Fail\"):\n",
    "        impacted_deposits.append(df2.attempted_deposit_amt_gbp[i-1])\n",
    "\n",
    "sum_of_impacted_deposits = sum(impacted_deposits)\n",
    "sum_of_impacted_deposits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "941413d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def declined_deposits(freeze_period, c_fail_freeze):\n",
    "    result = []\n",
    "    result.clear()\n",
    "    freeze_start_time = datetime(year = 1, month = 1, day = 1, hour = 0, minute = 0, second = 0)\n",
    "    freeze_end_time = datetime(year = 1, month = 1, day = 1, hour = 0, minute = 0, second = 0)\n",
    "    for i in df2[\"ID\"]:\n",
    "        if (i != 1): \n",
    "            #determine the freeze period\n",
    "            if (df2.at[i-1,\"consecutive_fails\"] == c_fail_freeze):\n",
    "                freeze_start_time = df2.at[i-1,\"txn_timestamp\"]\n",
    "                freeze_end_time = df2.at[i-1,\"txn_timestamp\"] + pd.DateOffset(hours=freeze_period)\n",
    "            if (df2.at[i-2,\"player_id\"]==df2.at[i-1,\"player_id\"]):\n",
    "                if (df2.at[i-1,\"txn_timestamp\"] > freeze_start_time and df2.at[i-1,\"txn_timestamp\"] <= freeze_end_time): \n",
    "                    df2.at[i-1,'freezed'] = True\n",
    "                else:\n",
    "                    df2.at[i-1,'freezed'] = False\n",
    "\n",
    "            if (df2.at[i-2,\"player_id\"] != df2.at[i-1,\"player_id\"]):\n",
    "                freeze_start_time = datetime(year = 1, month = 1, day = 1, hour = 0, minute = 0, second = 0)\n",
    "                freeze_end_time = datetime(year = 1, month = 1, day = 1, hour = 0, minute = 0, second = 0)\n",
    "                df2.at[i-1,'freezed'] = False\n",
    "        else:\n",
    "            if (df2.at[i-1,\"consecutive_fails\"] == c_fail_freeze):\n",
    "                freeze_start_time = df2.at[i-1,\"txn_timestamp\"]\n",
    "                freeze_end_time = df2.at[i-1,\"txn_timestamp\"] + pd.DateOffset(hours=freeze_period)\n",
    "            df2.at[i-1,'freezed'] = False\n",
    "    #calculate total number of impacted customer\n",
    "    impacted_customers = []\n",
    "    impacted_customers.clear()\n",
    "    number_of_impacted_customers = 0\n",
    "\n",
    "    for i in df2[\"ID\"]:\n",
    "        if (df2.at[i-1,'freezed'] == True and df2.deposit_status[i-1] != \"Fail\"):\n",
    "            impacted_customers.append(df2.player_id[i-1])  \n",
    "\n",
    "    number_of_impacted_customers = pd.Series(impacted_customers).nunique()\n",
    "    #number_of_impacted_customers\n",
    "    #calculate total number of impacted deposit    \n",
    "    impacted_deposits = []\n",
    "    impacted_deposits.clear()\n",
    "    sum_of_impacted_deposits = 0\n",
    "\n",
    "    for i in df2[\"ID\"]:\n",
    "        if (df2.at[i-1,'freezed'] == True and df2.deposit_status[i-1] != \"Fail\"):\n",
    "            impacted_deposits.append(df2.attempted_deposit_amt_gbp[i-1])\n",
    "\n",
    "    sum_of_impacted_deposits = sum(impacted_deposits)\n",
    "    #sum_of_impacted_deposits\n",
    "    \n",
    "    result.append(number_of_impacted_customers)\n",
    "    result.append(sum_of_impacted_deposits)\n",
    "    \n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "929f7d22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[402, 62699.22764991902]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "declined_deposits (6,2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d9e2503",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 2 [402, 62699.22764991902]\n",
      "6 3 [169, 23279.06448000001]\n",
      "6 4 [83, 9515.176560000029]\n"
     ]
    }
   ],
   "source": [
    "declined_deposits_summary = []\n",
    "declined_deposits_summary.clear()\n",
    "for i in range(6,30,6):\n",
    "    for j in range (2,6,1):\n",
    "        declined_deposits_summary.append(declined_deposits (i,j))\n",
    "        print(i,j,declined_deposits (i,j))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7e2acf3",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}