conflict_report_14010308-v2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "d9a727da-1528-4313-ac82-8fb6037a1ec3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "#import matplotlib.pyplot as plt\n",
    "#import seaborn as sns\n",
    "from IPython.display import display, HTML\n",
    "pd.set_option('display.max_colwidth', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b4d5f4d-6810-4525-b772-c45a9223f376",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Read data from excel files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "aaa9bc3a-0117-40c8-8da6-c7d997b99f54",
   "metadata": {},
   "outputs": [],
   "source": [
    "billDf = pd.read_excel('Bill.xlsx')\n",
    "providerDf = pd.read_excel('Provider.xlsx')\n",
    "providerDf.rename(columns ={'Ref No':'referenceID'},inplace=True)\n",
    "providerDf['referenceID'] = providerDf['referenceID'].astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "018442d6-0cd7-4571-b35a-87c1bed01cf4",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "6faf36fd-f4c9-4b6f-905a-c74ba4034064",
   "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>Amount</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>Descriptions</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>900</td>\n",
       "      <td>2721063604</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 4070910357249 و شناسه پرداخت 900733</td>\n",
       "      <td>4070910357249</td>\n",
       "      <td>900733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>32300</td>\n",
       "      <td>272109585</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 409580633423 و شناسه پرداخت 3230392</td>\n",
       "      <td>409580633423</td>\n",
       "      <td>3230392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>75400</td>\n",
       "      <td>27281098610</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 905102967222510 و شناسه پرداخت 75424950</td>\n",
       "      <td>905102967222510</td>\n",
       "      <td>75424950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>410800</td>\n",
       "      <td>2721029368</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 2233358952240 و شناسه پرداخت 41080706</td>\n",
       "      <td>2233358952240</td>\n",
       "      <td>41080706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>300</td>\n",
       "      <td>2721068678</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 421049506730 و شناسه پرداخت 325974</td>\n",
       "      <td>421049506730</td>\n",
       "      <td>325974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2914</th>\n",
       "      <td>5710300</td>\n",
       "      <td>2721057653</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 63039104204355 و شناسه پرداخت 5710305664</td>\n",
       "      <td>63039104204355</td>\n",
       "      <td>5710305664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2915</th>\n",
       "      <td>38600</td>\n",
       "      <td>2721088856</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 22510809507257 و شناسه پرداخت 3860376</td>\n",
       "      <td>22510809507257</td>\n",
       "      <td>3860376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2916</th>\n",
       "      <td>53900</td>\n",
       "      <td>27210331068</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 2228361060210 و شناسه پرداخت 53906104</td>\n",
       "      <td>2228361060210</td>\n",
       "      <td>53906104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2917</th>\n",
       "      <td>2900</td>\n",
       "      <td>27210666105</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 496930442249 و شناسه پرداخت 290742</td>\n",
       "      <td>496930442249</td>\n",
       "      <td>290742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2918</th>\n",
       "      <td>700</td>\n",
       "      <td>2721075774</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 33857242242 و شناسه پرداخت 700747</td>\n",
       "      <td>33857242242</td>\n",
       "      <td>700747</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2919 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Amount  referenceID  \\\n",
       "0         900   2721063604   \n",
       "1       32300    272109585   \n",
       "2       75400  27281098610   \n",
       "3      410800   2721029368   \n",
       "4         300   2721068678   \n",
       "...       ...          ...   \n",
       "2914  5710300   2721057653   \n",
       "2915    38600   2721088856   \n",
       "2916    53900  27210331068   \n",
       "2917     2900  27210666105   \n",
       "2918      700   2721075774   \n",
       "\n",
       "                                                        Descriptions  \\\n",
       "0          ‍‍‍پرداخت قبضبا شناسه 4070910357249 و شناسه پرداخت 900733   \n",
       "1          ‍‍‍پرداخت قبضبا شناسه 409580633423 و شناسه پرداخت 3230392   \n",
       "2      ‍‍‍پرداخت قبضبا شناسه 905102967222510 و شناسه پرداخت 75424950   \n",
       "3        ‍‍‍پرداخت قبضبا شناسه 2233358952240 و شناسه پرداخت 41080706   \n",
       "4           ‍‍‍پرداخت قبضبا شناسه 421049506730 و شناسه پرداخت 325974   \n",
       "...                                                              ...   \n",
       "2914  ‍‍‍پرداخت قبضبا شناسه 63039104204355 و شناسه پرداخت 5710305664   \n",
       "2915     ‍‍‍پرداخت قبضبا شناسه 22510809507257 و شناسه پرداخت 3860376   \n",
       "2916     ‍‍‍پرداخت قبضبا شناسه 2228361060210 و شناسه پرداخت 53906104   \n",
       "2917        ‍‍‍پرداخت قبضبا شناسه 496930442249 و شناسه پرداخت 290742   \n",
       "2918         ‍‍‍پرداخت قبضبا شناسه 33857242242 و شناسه پرداخت 700747   \n",
       "\n",
       "               billID  billPaymentID  \n",
       "0       4070910357249         900733  \n",
       "1        409580633423        3230392  \n",
       "2     905102967222510       75424950  \n",
       "3       2233358952240       41080706  \n",
       "4        421049506730         325974  \n",
       "...               ...            ...  \n",
       "2914   63039104204355     5710305664  \n",
       "2915   22510809507257        3860376  \n",
       "2916    2228361060210       53906104  \n",
       "2917     496930442249         290742  \n",
       "2918      33857242242         700747  \n",
       "\n",
       "[2919 rows x 5 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Some preprocessing and extract information from text(Descriptions column).\n",
    "providerDf['billID'] = providerDf['Descriptions'].str.replace(r'^\\D+(\\d+)\\D+(\\d+)',r'\\1',regex=True)\n",
    "providerDf['billPaymentID'] = providerDf['Descriptions'].str.replace(r'^\\D+(\\d+)\\D+(\\d+)',r'\\2',regex=True)\n",
    "providerDf['billID'] = providerDf['billID'].astype(np.int64)\n",
    "providerDf['billPaymentID'] = providerDf['billPaymentID'].astype(np.int64)\n",
    "providerDf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3df9b6a4-8e0a-482e-9251-f48118c0b109",
   "metadata": {},
   "source": [
    "# ----------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93af1090-0308-410b-bd50-6eb3babd3af9",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Some info about Bill table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "66f7042d-fa95-4b2d-854a-ac6bf9a143b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45101 entries, 0 to 45100\n",
      "Data columns (total 7 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   amount         45101 non-null  int64 \n",
      " 1   providerID     43557 non-null  object\n",
      " 2   billID         45101 non-null  int64 \n",
      " 3   billPaymentID  45101 non-null  int64 \n",
      " 4   referenceID    43557 non-null  object\n",
      " 5   status         45101 non-null  object\n",
      " 6   transactionID  45101 non-null  object\n",
      "dtypes: int64(3), object(4)\n",
      "memory usage: 2.4+ MB\n"
     ]
    }
   ],
   "source": [
    "billDf.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b63049f4-fb6d-451d-9614-b2f7b533993b",
   "metadata": {},
   "source": [
    "### Providers and their transactions status in Bill table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "4ae2e0c2-e2a1-4561-9091-e6d8f507de79",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "providerID  status   \n",
       "P1          COMPLETED    40647\n",
       "P2          COMPLETED     2900\n",
       "NaN         REQUESTED     1544\n",
       "P2          REQUESTED       10\n",
       "dtype: int64"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billDf[['providerID','status']].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e97b4157-fb15-4067-8630-9bae7843140e",
   "metadata": {},
   "source": [
    "### NaN value in ProviderID and referenceID in Bill table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "d2c8d47a-4270-4485-b506-e16114c42d06",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1544 -> Number of NaN value in ProviderID column.\n",
      "1544 -> Number of NaN value in referenceID column.\n",
      "-------------------------------------------\n",
      "This info show that when providerID is NaN, refrenceID is NaN too. \n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1544 entries, 3 to 45073\n",
      "Data columns (total 7 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   amount         1544 non-null   int64 \n",
      " 1   providerID     0 non-null      object\n",
      " 2   billID         1544 non-null   int64 \n",
      " 3   billPaymentID  1544 non-null   int64 \n",
      " 4   referenceID    0 non-null      object\n",
      " 5   status         1544 non-null   object\n",
      " 6   transactionID  1544 non-null   object\n",
      "dtypes: int64(3), object(4)\n",
      "memory usage: 96.5+ KB\n",
      "None\n",
      "-------------------------------------------\n",
      "And of course all status these transactions are REQUESTED.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "REQUESTED    1544\n",
       "Name: status, dtype: int64"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(f\"{billDf['providerID'].isna().sum()} -> Number of NaN value in ProviderID column.\")\n",
    "print(f\"{billDf['referenceID'].isna().sum()} -> Number of NaN value in referenceID column.\")\n",
    "print(\"-------------------------------------------\")\n",
    "print('This info show that when providerID is NaN, refrenceID is NaN too.','\\n')\n",
    "print(billDf[billDf['providerID'].isna()].info())\n",
    "print(\"-------------------------------------------\")\n",
    "print(\"And of course all status these transactions are REQUESTED.\")\n",
    "billDf[billDf['providerID'].isna()]['status'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b3ef744-07a1-4ea9-8efb-a43d92815c0a",
   "metadata": {},
   "source": [
    "# ----------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8da87e6-fe07-4dd9-b955-890c205c39ec",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Some info about provider report table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "5ec2175f-ff2b-43b0-9a27-930cd8f3acd7",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The provider report list hasn't any NaN(null) cells. \n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2919 entries, 0 to 2918\n",
      "Data columns (total 5 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   Amount         2919 non-null   int64 \n",
      " 1   referenceID    2919 non-null   object\n",
      " 2   Descriptions   2919 non-null   object\n",
      " 3   billID         2919 non-null   int64 \n",
      " 4   billPaymentID  2919 non-null   int64 \n",
      "dtypes: int64(3), object(2)\n",
      "memory usage: 114.1+ KB\n"
     ]
    }
   ],
   "source": [
    "print('The provider report list hasn\\'t any NaN(null) cells.','\\n')\n",
    "providerDf.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1de2c261-712d-4309-8958-84bf1d047112",
   "metadata": {},
   "source": [
    "# ----------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6329cd59-d946-4c3b-8520-2775a4aae1aa",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Which columns suitable for merging?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57f26512-a8e8-4575-a1c4-972ed995c602",
   "metadata": {},
   "source": [
    "I want use some columns to merging and use it to recognizing conflictions.But which columns and how many of them?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5b617c7-faa4-4bba-bee8-8d47ae60af41",
   "metadata": {},
   "source": [
    "I prefered use referenceID or BillID or BillIDPaymentID.But because that the columns have duplicated rows(Apparently common)\n",
    "and have NaN value i prefer use all of them together."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4dff7c9-75f0-4028-952e-8d27e7477717",
   "metadata": {
    "tags": []
   },
   "source": [
    "### checking referenceID or BillID or BillIDPaymentID for dublication in bill table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "41cd528c-f42a-46fa-8184-029e35d68c86",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of all NaN rows for refrenceID column is 43557 and Number of unique elements in that column is 43553\n",
      "Number of all NaN rows for BillID column is 45101 and Number of unique elements in that column is 43094\n",
      "Number of all NaN rows for BillIDPaymentID column is 45101 and Number of unique elements in that column is 23674\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print(f\"Number of all NaN rows for refrenceID column is {billDf['referenceID'].count()} \\\n",
    "and Number of unique elements in that column is {billDf['referenceID'].nunique()}\")\n",
    "\n",
    "print(f\"Number of all NaN rows for BillID column is {billDf['billID'].count()} \\\n",
    "and Number of unique elements in that column is {billDf['billID'].nunique()}\")\n",
    "\n",
    "print(f\"Number of all NaN rows for BillIDPaymentID column is {billDf['billPaymentID'].count()} \\\n",
    "and Number of unique elements in that column is {billDf['billPaymentID'].nunique()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af04e5f8-0947-4cc4-a737-adf685433d65",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Merging two tables together for using find conflictions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "c54de91e-b628-472f-9991-165244b02d90",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Result of merging for p2 provider. \n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2919 entries, 0 to 2918\n",
      "Data columns (total 9 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   Amount         2919 non-null   int64 \n",
      " 1   referenceID    2919 non-null   object\n",
      " 2   Descriptions   2919 non-null   object\n",
      " 3   billID         2919 non-null   int64 \n",
      " 4   billPaymentID  2919 non-null   int64 \n",
      " 5   amount         2911 non-null   Int64 \n",
      " 6   providerID     2911 non-null   object\n",
      " 7   status         2911 non-null   object\n",
      " 8   transactionID  2911 non-null   object\n",
      "dtypes: Int64(1), int64(3), object(5)\n",
      "memory usage: 230.9+ KB\n",
      "None \n",
      "\n",
      "providerID  status   \n",
      "P2          COMPLETED    2898\n",
      "            REQUESTED      10\n",
      "NaN         NaN             8\n",
      "P1          COMPLETED       3\n",
      "dtype: int64 \n",
      "\n",
      "Number of rows in the provider report table is 2919 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "mergeDf = pd.merge(providerDf,billDf,how='left',on=['billID','billPaymentID','referenceID'])\n",
    "mergeDf['amount'] = mergeDf['amount'].astype(pd.Int64Dtype())\n",
    "print('Result of merging for p2 provider.','\\n')\n",
    "print(mergeDf.info(),'\\n')\n",
    "print(mergeDf[['providerID','status']].value_counts(dropna=False),'\\n')\n",
    "print(f\"Number of rows in the provider report table is {providerDf['Amount'].count()}\",'\\n')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7cd139a-a13d-423c-ba01-0da8552eaef7",
   "metadata": {},
   "source": [
    "# ----------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce7d9290-4d80-4587-b855-1242c0b05600",
   "metadata": {},
   "source": [
    "# checking p1 p2 providers confiliction."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6da7998-b7a9-4628-9cf8-cff33e904dce",
   "metadata": {},
   "source": [
    "There is p1 provider transactions(recorded in bill table) in p2 provider report.why?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "7d96225d-10f8-4b19-899f-5330c3095683",
   "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>Amount</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>Descriptions</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "      <th>amount</th>\n",
       "      <th>providerID</th>\n",
       "      <th>status</th>\n",
       "      <th>transactionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>9000</td>\n",
       "      <td>275064210</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 7269409324550 و شناسه پرداخت 90030</td>\n",
       "      <td>7269409324550</td>\n",
       "      <td>90030</td>\n",
       "      <td>90000</td>\n",
       "      <td>P1</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>ffa10b587-1054-4da7-1063f-b2e98afdb875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>800</td>\n",
       "      <td>2721099876</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 2259948625256 و شناسه پرداخت 800327</td>\n",
       "      <td>2259948625256</td>\n",
       "      <td>800327</td>\n",
       "      <td>800</td>\n",
       "      <td>P1</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>01044eff0-05ef-4105e-8cc6-d25568ed97a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1616</th>\n",
       "      <td>49600</td>\n",
       "      <td>2721032735</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 2292293472240 و شناسه پرداخت 4960706</td>\n",
       "      <td>2292293472240</td>\n",
       "      <td>4960706</td>\n",
       "      <td>49600</td>\n",
       "      <td>P1</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>02290f49-f2f10-466e-1010710-097972d090a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Amount referenceID  \\\n",
       "89      9000   275064210   \n",
       "145      800  2721099876   \n",
       "1616   49600  2721032735   \n",
       "\n",
       "                                                    Descriptions  \\\n",
       "89      ‍‍‍پرداخت قبضبا شناسه 7269409324550 و شناسه پرداخت 90030   \n",
       "145    ‍‍‍پرداخت قبضبا شناسه 2259948625256 و شناسه پرداخت 800327   \n",
       "1616  ‍‍‍پرداخت قبضبا شناسه 2292293472240 و شناسه پرداخت 4960706   \n",
       "\n",
       "             billID  billPaymentID  amount providerID     status  \\\n",
       "89    7269409324550          90030   90000         P1  COMPLETED   \n",
       "145   2259948625256         800327     800         P1  COMPLETED   \n",
       "1616  2292293472240        4960706   49600         P1  COMPLETED   \n",
       "\n",
       "                                transactionID  \n",
       "89     ffa10b587-1054-4da7-1063f-b2e98afdb875  \n",
       "145    01044eff0-05ef-4105e-8cc6-d25568ed97a2  \n",
       "1616  02290f49-f2f10-466e-1010710-097972d090a  "
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This list is a confilict in bill and provider tables. They are releted to p1 provider in bill table but p2 provider reported it.\n",
    "p1p2conf = mergeDf[mergeDf['providerID']=='P1']\n",
    "p1p2conf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c2d6816-dfae-4421-a7f1-18ddf7e0c8a4",
   "metadata": {},
   "source": [
    "### checking the confliction in bill table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "c789af7c-8720-4b58-923b-8b292608f7f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "providerID  status   \n",
       "P1          COMPLETED    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "billIDprovider = providerDf['billID']\n",
    "billPayIDprovider = providerDf['billPaymentID']\n",
    "cond = ( billDf['billID'].isin(billIDprovider) ) & ( billDf['billPaymentID'].isin(billPayIDprovider) ) & ( billDf['providerID']=='P1' )\n",
    "confilictP1_P2=billDf[ cond ]\n",
    "confilictP1_P2[['providerID','status']].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "3f15b185-2350-4015-97f9-894d7b494cb7",
   "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>amount</th>\n",
       "      <th>providerID</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>status</th>\n",
       "      <th>transactionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5510</th>\n",
       "      <td>49600</td>\n",
       "      <td>P1</td>\n",
       "      <td>2292293472240</td>\n",
       "      <td>4960706</td>\n",
       "      <td>2721032735</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>02290f49-f2f10-466e-1010710-097972d090a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18193</th>\n",
       "      <td>800</td>\n",
       "      <td>P1</td>\n",
       "      <td>2259948625256</td>\n",
       "      <td>800327</td>\n",
       "      <td>2721099876</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>01044eff0-05ef-4105e-8cc6-d25568ed97a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23436</th>\n",
       "      <td>90000</td>\n",
       "      <td>P1</td>\n",
       "      <td>7269409324550</td>\n",
       "      <td>90030</td>\n",
       "      <td>275064210</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>ffa10b587-1054-4da7-1063f-b2e98afdb875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       amount providerID         billID  billPaymentID referenceID     status  \\\n",
       "5510    49600         P1  2292293472240        4960706  2721032735  COMPLETED   \n",
       "18193     800         P1  2259948625256         800327  2721099876  COMPLETED   \n",
       "23436   90000         P1  7269409324550          90030   275064210  COMPLETED   \n",
       "\n",
       "                                 transactionID  \n",
       "5510   02290f49-f2f10-466e-1010710-097972d090a  \n",
       "18193   01044eff0-05ef-4105e-8cc6-d25568ed97a2  \n",
       "23436   ffa10b587-1054-4da7-1063f-b2e98afdb875  "
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "confilictP1_P2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaed0698-cee2-483f-99a8-8475e27d0a72",
   "metadata": {},
   "source": [
    "### checking the confliction in the provider table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "a80eb4cc-b409-450f-be5e-d4a78d788427",
   "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>Amount</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>Descriptions</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>9000</td>\n",
       "      <td>275064210</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 7269409324550 و شناسه پرداخت 90030</td>\n",
       "      <td>7269409324550</td>\n",
       "      <td>90030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>800</td>\n",
       "      <td>2721099876</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 2259948625256 و شناسه پرداخت 800327</td>\n",
       "      <td>2259948625256</td>\n",
       "      <td>800327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1616</th>\n",
       "      <td>49600</td>\n",
       "      <td>2721032735</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 2292293472240 و شناسه پرداخت 4960706</td>\n",
       "      <td>2292293472240</td>\n",
       "      <td>4960706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Amount referenceID  \\\n",
       "89      9000   275064210   \n",
       "145      800  2721099876   \n",
       "1616   49600  2721032735   \n",
       "\n",
       "                                                    Descriptions  \\\n",
       "89      ‍‍‍پرداخت قبضبا شناسه 7269409324550 و شناسه پرداخت 90030   \n",
       "145    ‍‍‍پرداخت قبضبا شناسه 2259948625256 و شناسه پرداخت 800327   \n",
       "1616  ‍‍‍پرداخت قبضبا شناسه 2292293472240 و شناسه پرداخت 4960706   \n",
       "\n",
       "             billID  billPaymentID  \n",
       "89    7269409324550          90030  \n",
       "145   2259948625256         800327  \n",
       "1616  2292293472240        4960706  "
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond = (providerDf['billID'].isin(confilictP1_P2['billID'])) & (providerDf['billPaymentID'].isin(confilictP1_P2['billPaymentID']))\n",
    "providerDf[ cond]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3106ae5c-cebe-409d-b283-d886bc741bf3",
   "metadata": {},
   "source": [
    "### checking the price confliction for the p1 p2 confliction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "3c91ee5b-315c-4325-8db4-2031b71886bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bill list reported 90000 but p2 provider reported 9000 for the price.\n"
     ]
    },
    {
     "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>Amount</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>Descriptions</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "      <th>amount</th>\n",
       "      <th>providerID</th>\n",
       "      <th>status</th>\n",
       "      <th>transactionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>9000</td>\n",
       "      <td>275064210</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 7269409324550 و شناسه پرداخت 90030</td>\n",
       "      <td>7269409324550</td>\n",
       "      <td>90030</td>\n",
       "      <td>90000</td>\n",
       "      <td>P1</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>ffa10b587-1054-4da7-1063f-b2e98afdb875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Amount referenceID  \\\n",
       "89    9000   275064210   \n",
       "\n",
       "                                                Descriptions         billID  \\\n",
       "89  ‍‍‍پرداخت قبضبا شناسه 7269409324550 و شناسه پرداخت 90030  7269409324550   \n",
       "\n",
       "    billPaymentID  amount providerID     status  \\\n",
       "89          90030   90000         P1  COMPLETED   \n",
       "\n",
       "                             transactionID  \n",
       "89  ffa10b587-1054-4da7-1063f-b2e98afdb875  "
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# conflict in price of above table.\n",
    "cond = (p1p2conf['Amount']!= p1p2conf['amount'])\n",
    "priceConflict1 = p1p2conf[cond]\n",
    "for i,row in priceConflict1.iterrows():\n",
    "    print(f\"Bill list reported {row['amount']} but p2 provider reported {row['Amount']} for the price.\")\n",
    "priceConflict1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9eebcc61-7e94-41b8-b93d-3e3e2e1d8163",
   "metadata": {},
   "source": [
    "# ----------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d51505e-2ca3-4a15-8911-9931225681d2",
   "metadata": {
    "tags": []
   },
   "source": [
    "# NaN providers in merge table checking"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d2ee760-8de7-4df5-b2b6-c84cca5d7d2f",
   "metadata": {},
   "source": [
    "There are some rows in merge table that reported by p2 provider but in bill table, they haven't provider or refrenceID or even didn't exist in bill table."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "938b8dd9-b5af-4619-93fe-a12cf5a2b6fc",
   "metadata": {},
   "source": [
    "Why p2 provider reports this transaction?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "e561d5c4-e806-4ef8-8a0d-55e9acd9ce4e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique nanstatus list\n",
      "Amount           8\n",
      "referenceID      8\n",
      "Descriptions     8\n",
      "billID           8\n",
      "billPaymentID    8\n",
      "amount           0\n",
      "providerID       0\n",
      "status           0\n",
      "transactionID    0\n",
      "dtype: int64 \n",
      "\n",
      "In merge table:\n"
     ]
    },
    {
     "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>Amount</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>Descriptions</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "      <th>amount</th>\n",
       "      <th>providerID</th>\n",
       "      <th>status</th>\n",
       "      <th>transactionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>670</th>\n",
       "      <td>576200</td>\n",
       "      <td>27210610727</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 227733307250 و شناسه پرداخت 57620356</td>\n",
       "      <td>227733307250</td>\n",
       "      <td>57620356</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1785</th>\n",
       "      <td>2700</td>\n",
       "      <td>2728621078</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 262433252240 و شناسه پرداخت 270730</td>\n",
       "      <td>262433252240</td>\n",
       "      <td>270730</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>634</th>\n",
       "      <td>20400</td>\n",
       "      <td>272862823</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 686866022240 و شناسه پرداخت 2040790</td>\n",
       "      <td>686866022240</td>\n",
       "      <td>2040790</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367</th>\n",
       "      <td>64400</td>\n",
       "      <td>272862990</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 1024422052240 و شناسه پرداخت 6440739</td>\n",
       "      <td>1024422052240</td>\n",
       "      <td>6440739</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2218</th>\n",
       "      <td>39600</td>\n",
       "      <td>27286210910</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 2063385552249 و شناسه پرداخت 3960796</td>\n",
       "      <td>2063385552249</td>\n",
       "      <td>3960796</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1632</th>\n",
       "      <td>22800</td>\n",
       "      <td>272862805</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 4105084844244 و شناسه پرداخت 2280735</td>\n",
       "      <td>4105084844244</td>\n",
       "      <td>2280735</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>30500</td>\n",
       "      <td>27286210106</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 6606632504355 و شناسه پرداخت 30505403</td>\n",
       "      <td>6606632504355</td>\n",
       "      <td>30505403</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1693</th>\n",
       "      <td>5400</td>\n",
       "      <td>272862880</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 7107537552248 و شناسه پرداخت 540753</td>\n",
       "      <td>7107537552248</td>\n",
       "      <td>540753</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Amount  referenceID  \\\n",
       "670   576200  27210610727   \n",
       "1785    2700   2728621078   \n",
       "634    20400    272862823   \n",
       "367    64400    272862990   \n",
       "2218   39600  27286210910   \n",
       "1632   22800    272862805   \n",
       "75     30500  27286210106   \n",
       "1693    5400    272862880   \n",
       "\n",
       "                                                     Descriptions  \\\n",
       "670    ‍‍‍پرداخت قبضبا شناسه 227733307250 و شناسه پرداخت 57620356   \n",
       "1785     ‍‍‍پرداخت قبضبا شناسه 262433252240 و شناسه پرداخت 270730   \n",
       "634     ‍‍‍پرداخت قبضبا شناسه 686866022240 و شناسه پرداخت 2040790   \n",
       "367    ‍‍‍پرداخت قبضبا شناسه 1024422052240 و شناسه پرداخت 6440739   \n",
       "2218   ‍‍‍پرداخت قبضبا شناسه 2063385552249 و شناسه پرداخت 3960796   \n",
       "1632   ‍‍‍پرداخت قبضبا شناسه 4105084844244 و شناسه پرداخت 2280735   \n",
       "75    ‍‍‍پرداخت قبضبا شناسه 6606632504355 و شناسه پرداخت 30505403   \n",
       "1693    ‍‍‍پرداخت قبضبا شناسه 7107537552248 و شناسه پرداخت 540753   \n",
       "\n",
       "             billID  billPaymentID  amount providerID status transactionID  \n",
       "670    227733307250       57620356    <NA>        NaN    NaN           NaN  \n",
       "1785   262433252240         270730    <NA>        NaN    NaN           NaN  \n",
       "634    686866022240        2040790    <NA>        NaN    NaN           NaN  \n",
       "367   1024422052240        6440739    <NA>        NaN    NaN           NaN  \n",
       "2218  2063385552249        3960796    <NA>        NaN    NaN           NaN  \n",
       "1632  4105084844244        2280735    <NA>        NaN    NaN           NaN  \n",
       "75    6606632504355       30505403    <NA>        NaN    NaN           NaN  \n",
       "1693  7107537552248         540753    <NA>        NaN    NaN           NaN  "
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This list is a confilict in bill and provider tables. They are reported by p2 but they are haven't providerID or compelete status in bill list.\n",
    "#Releted two refrenceID,BillID and BillPaymentID.\n",
    "nanstatus = mergeDf[mergeDf['status'].isna()]\n",
    "print(f\"Number of unique nanstatus list\\n{nanstatus.nunique()}\",'\\n')\n",
    "print('In merge table:')\n",
    "nanstatus.sort_values(by=['billID'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "888206f9-51b3-42fd-ab58-68455b8be193",
   "metadata": {},
   "outputs": [],
   "source": [
    "nanstatus.to_excel('nanstatus.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "221a8999-bc36-440b-8469-dfb4d128a65c",
   "metadata": {},
   "source": [
    "### checking above list in bill table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "f1cbaa79-b6d4-4dec-b79f-e370b28a5572",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique list\n",
      "amount            7\n",
      "providerID        0\n",
      "billID            7\n",
      "billPaymentID     7\n",
      "referenceID       0\n",
      "status            1\n",
      "transactionID    10\n",
      "dtype: int64 \n",
      "\n",
      "there is a report in p2 provider but didn't exist in bill table.  \n",
      "\n"
     ]
    },
    {
     "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>amount</th>\n",
       "      <th>providerID</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>status</th>\n",
       "      <th>transactionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25384</th>\n",
       "      <td>2700</td>\n",
       "      <td>NaN</td>\n",
       "      <td>262433252240</td>\n",
       "      <td>270730</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>ebf94296-e637-4a10b-b24b-c4805cd75b42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23927</th>\n",
       "      <td>20400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>686866022240</td>\n",
       "      <td>2040790</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>aeeea2d8-5bac-4bf8-804b-2b40c04d5229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2067</th>\n",
       "      <td>64400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1024422052240</td>\n",
       "      <td>6440739</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>4bf10333e-ed108-47f4-108108-a878625fa10e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16661</th>\n",
       "      <td>64400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1024422052240</td>\n",
       "      <td>6440739</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>883fe622-e58c-446d-a776-494da1089a408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2223</th>\n",
       "      <td>39600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2063385552249</td>\n",
       "      <td>3960796</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>88f0660f-aeb6-405-ae28-cafe5f9ae4dc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25773</th>\n",
       "      <td>39600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2063385552249</td>\n",
       "      <td>3960796</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>0d49c7710-ddaf-4cf7-ae1010-dd8d83d851010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38769</th>\n",
       "      <td>39600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2063385552249</td>\n",
       "      <td>3960796</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>b5e74c90-fcef-4b6f-10582-4463ba57e1054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19044</th>\n",
       "      <td>22800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4105084844244</td>\n",
       "      <td>2280735</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>da646d810-9260-4fbe-ad49-2863ec440fd2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44790</th>\n",
       "      <td>30500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6606632504355</td>\n",
       "      <td>30505403</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>0f7709f8-0424-4f84-10654-e226fa9e2103e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14900</th>\n",
       "      <td>5400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7107537552248</td>\n",
       "      <td>540753</td>\n",
       "      <td>NaN</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>645f71048-e7103-4fd10-a0103-85974548d0c4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       amount providerID         billID  billPaymentID referenceID     status  \\\n",
       "25384    2700        NaN   262433252240         270730         NaN  REQUESTED   \n",
       "23927   20400        NaN   686866022240        2040790         NaN  REQUESTED   \n",
       "2067    64400        NaN  1024422052240        6440739         NaN  REQUESTED   \n",
       "16661   64400        NaN  1024422052240        6440739         NaN  REQUESTED   \n",
       "2223    39600        NaN  2063385552249        3960796         NaN  REQUESTED   \n",
       "25773   39600        NaN  2063385552249        3960796         NaN  REQUESTED   \n",
       "38769   39600        NaN  2063385552249        3960796         NaN  REQUESTED   \n",
       "19044   22800        NaN  4105084844244        2280735         NaN  REQUESTED   \n",
       "44790   30500        NaN  6606632504355       30505403         NaN  REQUESTED   \n",
       "14900    5400        NaN  7107537552248         540753         NaN  REQUESTED   \n",
       "\n",
       "                                  transactionID  \n",
       "25384     ebf94296-e637-4a10b-b24b-c4805cd75b42  \n",
       "23927      aeeea2d8-5bac-4bf8-804b-2b40c04d5229  \n",
       "2067   4bf10333e-ed108-47f4-108108-a878625fa10e  \n",
       "16661     883fe622-e58c-446d-a776-494da1089a408  \n",
       "2223        88f0660f-aeb6-405-ae28-cafe5f9ae4dc  \n",
       "25773  0d49c7710-ddaf-4cf7-ae1010-dd8d83d851010  \n",
       "38769    b5e74c90-fcef-4b6f-10582-4463ba57e1054  \n",
       "19044     da646d810-9260-4fbe-ad49-2863ec440fd2  \n",
       "44790    0f7709f8-0424-4f84-10654-e226fa9e2103e  \n",
       "14900  645f71048-e7103-4fd10-a0103-85974548d0c4  "
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A list of nan provider and refrenceID related to above in bill table.\n",
    "cond = (billDf['billID'].isin(nanstatus['billID'])) & (billDf['billPaymentID'].isin(nanstatus['billPaymentID'])) \n",
    "nanInBill= billDf[cond].sort_values(by=['billID'])\n",
    "print(f\"Number of unique list\\n{nanInBill.nunique()}\",'\\n')\n",
    "print('there is a report in p2 provider but didn\\'t exist in bill table. ','\\n')\n",
    "nanInBill"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ab894b7-ec3d-44d1-a2b9-ba9ad7d00cc3",
   "metadata": {},
   "source": [
    "#### The elements that reported by p2 provider but don't exist in bill table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "6074cd08-5083-45da-bd12-041239672f6d",
   "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>Amount</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>Descriptions</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "      <th>amount</th>\n",
       "      <th>providerID</th>\n",
       "      <th>status</th>\n",
       "      <th>transactionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>670</th>\n",
       "      <td>576200</td>\n",
       "      <td>27210610727</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 227733307250 و شناسه پرداخت 57620356</td>\n",
       "      <td>227733307250</td>\n",
       "      <td>57620356</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Amount  referenceID  \\\n",
       "670  576200  27210610727   \n",
       "\n",
       "                                                   Descriptions        billID  \\\n",
       "670  ‍‍‍پرداخت قبضبا شناسه 227733307250 و شناسه پرداخت 57620356  227733307250   \n",
       "\n",
       "     billPaymentID  amount providerID status transactionID  \n",
       "670       57620356    <NA>        NaN    NaN           NaN  "
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The difference between the two lists nanInBill and nanstatus. This list show rows reported in p2 provider but ins't exist in bill list.\n",
    "cond = ~(nanstatus['billID'].isin(nanInBill['billID'])) & ~(nanstatus['billPaymentID'].isin(nanInBill['billPaymentID'])) \n",
    "nanstatus[cond]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac70af87-e5c3-493c-b927-9aaa0cc7bdc9",
   "metadata": {},
   "source": [
    "# ----------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff488303-7902-4e54-8846-708bfd01ad34",
   "metadata": {},
   "source": [
    "# The elemets that exist in bill table and related to p2 provider but not reported by p2 provider."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "edf8449a-baa3-4460-99f3-df8c5c018517",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "providerID  status   \n",
       "P2          COMPLETED    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# two complete transaction that is't exist in p2 reported but they assigned to p2 provider in bill table.\n",
    "billIDprovider = providerDf['billID']\n",
    "billPayIDprovider = providerDf['billPaymentID']\n",
    "cond = ~( billDf['billID'].isin(billIDprovider) ) & ~( billDf['billPaymentID'].isin(billPayIDprovider) )  & ( billDf['providerID']=='P2' )\n",
    "confilictP1_P2_2=billDf[ cond ]\n",
    "confilictP1_P2_2[['providerID','status']].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "8c3677f7-fe01-4038-97ae-0473f91cf801",
   "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>amount</th>\n",
       "      <th>providerID</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>status</th>\n",
       "      <th>transactionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29737</th>\n",
       "      <td>59100</td>\n",
       "      <td>P2</td>\n",
       "      <td>25210389972244</td>\n",
       "      <td>59100706</td>\n",
       "      <td>27210210735</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>ffcfa7e5-076e-494a-8468-b923d24d94b7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34035</th>\n",
       "      <td>24600</td>\n",
       "      <td>P2</td>\n",
       "      <td>592560952249</td>\n",
       "      <td>2460765</td>\n",
       "      <td>2721080547</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>02365802-8410a-466d-8023-7fb7c964b95b</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       amount providerID          billID  billPaymentID  referenceID  \\\n",
       "29737   59100         P2  25210389972244       59100706  27210210735   \n",
       "34035   24600         P2    592560952249        2460765   2721080547   \n",
       "\n",
       "          status                          transactionID  \n",
       "29737  COMPLETED   ffcfa7e5-076e-494a-8468-b923d24d94b7  \n",
       "34035  COMPLETED  02365802-8410a-466d-8023-7fb7c964b95b  "
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "confilictP1_P2_2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94ae00cc-2ecf-403d-8a1d-245115bf700d",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### checking the elements in the provider table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "6d977e77-c9ec-460d-836d-e7fea2ddfc92",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [Amount, referenceID, Descriptions, billID, billPaymentID]\n",
      "Index: []\n",
      "Empty DataFrame\n",
      "Columns: [Amount, referenceID, Descriptions, billID, billPaymentID]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "#nothing in p2 provider list with confilictP1_P2_2 refrenceIDs.\n",
    "print(providerDf[providerDf['referenceID'].isin(confilictP1_P2_2['referenceID'])])\n",
    "cond = ( providerDf['billID'].isin(confilictP1_P2_2['billID']) ) & ( providerDf['billPaymentID'].isin(confilictP1_P2_2['billPaymentID']) )\n",
    "print(providerDf[ cond ])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2790920-0200-43c3-98e1-450f9c72cdfb",
   "metadata": {},
   "source": [
    "# ----------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eba5bff2-03d0-4058-ab57-9ea390a4f6e4",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Checking REQUESTED transactions related p2 provider and reported by p2."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d355508-fd82-47b2-a98b-bc60f691414e",
   "metadata": {},
   "source": [
    "why p2 provider reported these?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "4c54b8b2-1a2c-4af8-88d4-755f4d91bddc",
   "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>Amount</th>\n",
       "      <th>referenceID</th>\n",
       "      <th>Descriptions</th>\n",
       "      <th>billID</th>\n",
       "      <th>billPaymentID</th>\n",
       "      <th>amount</th>\n",
       "      <th>providerID</th>\n",
       "      <th>status</th>\n",
       "      <th>transactionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>202900</td>\n",
       "      <td>2721035940</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 10101020204255 و شناسه پرداخت 20290757</td>\n",
       "      <td>10101020204255</td>\n",
       "      <td>20290757</td>\n",
       "      <td>202900</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>f825c7a7-daee-4210c-a77b-23a44c210e58e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>300</td>\n",
       "      <td>27210410246</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 84010806032 و شناسه پرداخت 304063</td>\n",
       "      <td>84010806032</td>\n",
       "      <td>304063</td>\n",
       "      <td>300</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>dfe79e8b-2fd0-46e6-10d8-2a8f3e4e7e73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>928</th>\n",
       "      <td>6800</td>\n",
       "      <td>27288023</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 8410409652247 و شناسه پرداخت 680753</td>\n",
       "      <td>8410409652247</td>\n",
       "      <td>680753</td>\n",
       "      <td>6800</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>f10dd802c-810de-4605-be6a-a43eb075affc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930</th>\n",
       "      <td>500</td>\n",
       "      <td>2721039609</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 290873806933 و شناسه پرداخت 5020</td>\n",
       "      <td>290873806933</td>\n",
       "      <td>5020</td>\n",
       "      <td>500</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>e310a0636-09f4-4c06-a589-b2e25380524f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1491</th>\n",
       "      <td>232100</td>\n",
       "      <td>275043730</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 2348348707550 و شناسه پرداخت 232100387</td>\n",
       "      <td>2348348707550</td>\n",
       "      <td>232100387</td>\n",
       "      <td>232100</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>fe1052608-5104f-46102-b39e-7f756526924f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1711</th>\n",
       "      <td>4200</td>\n",
       "      <td>27210527106</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 7010105652246 و شناسه پرداخت 420779</td>\n",
       "      <td>7010105652246</td>\n",
       "      <td>420779</td>\n",
       "      <td>4200</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>fb101010a0e-ef510-4f68-a5a7-bc5ee10d9c244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1987</th>\n",
       "      <td>10500</td>\n",
       "      <td>27210910237</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 604291026 و شناسه پرداخت 10520686</td>\n",
       "      <td>604291026</td>\n",
       "      <td>10520686</td>\n",
       "      <td>10500</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>e659ca103-610e-466d-10540-cce2577fe71010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2097</th>\n",
       "      <td>55500</td>\n",
       "      <td>275022692</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 10810678752246 و شناسه پرداخت 55507109</td>\n",
       "      <td>10810678752246</td>\n",
       "      <td>55507109</td>\n",
       "      <td>55500</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>74e97720-764b-41052-10879-6e847bf10b107f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2538</th>\n",
       "      <td>56300</td>\n",
       "      <td>275040537</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 272920337240 و شناسه پرداخت 5630750</td>\n",
       "      <td>272920337240</td>\n",
       "      <td>5630750</td>\n",
       "      <td>56300</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>784b1097a-48ce-474b-80bf-2f608a35effd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2575</th>\n",
       "      <td>2500</td>\n",
       "      <td>2750371024</td>\n",
       "      <td>‍‍‍پرداخت قبضبا شناسه 5797806234 و شناسه پرداخت 00025027304</td>\n",
       "      <td>5797806234</td>\n",
       "      <td>25027304</td>\n",
       "      <td>2500</td>\n",
       "      <td>P2</td>\n",
       "      <td>REQUESTED</td>\n",
       "      <td>f3d34cd3-a992-47cf-891010-42c97e4a9e4f</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Amount  referenceID  \\\n",
       "252   202900   2721035940   \n",
       "567      300  27210410246   \n",
       "928     6800     27288023   \n",
       "930      500   2721039609   \n",
       "1491  232100    275043730   \n",
       "1711    4200  27210527106   \n",
       "1987   10500  27210910237   \n",
       "2097   55500    275022692   \n",
       "2538   56300    275040537   \n",
       "2575    2500   2750371024   \n",
       "\n",
       "                                                      Descriptions  \\\n",
       "252   ‍‍‍پرداخت قبضبا شناسه 10101020204255 و شناسه پرداخت 20290757   \n",
       "567        ‍‍‍پرداخت قبضبا شناسه 84010806032 و شناسه پرداخت 304063   \n",
       "928      ‍‍‍پرداخت قبضبا شناسه 8410409652247 و شناسه پرداخت 680753   \n",
       "930         ‍‍‍پرداخت قبضبا شناسه 290873806933 و شناسه پرداخت 5020   \n",
       "1491  ‍‍‍پرداخت قبضبا شناسه 2348348707550 و شناسه پرداخت 232100387   \n",
       "1711     ‍‍‍پرداخت قبضبا شناسه 7010105652246 و شناسه پرداخت 420779   \n",
       "1987       ‍‍‍پرداخت قبضبا شناسه 604291026 و شناسه پرداخت 10520686   \n",
       "2097  ‍‍‍پرداخت قبضبا شناسه 10810678752246 و شناسه پرداخت 55507109   \n",
       "2538     ‍‍‍پرداخت قبضبا شناسه 272920337240 و شناسه پرداخت 5630750   \n",
       "2575   ‍‍‍پرداخت قبضبا شناسه 5797806234 و شناسه پرداخت 00025027304   \n",
       "\n",
       "              billID  billPaymentID  amount providerID     status  \\\n",
       "252   10101020204255       20290757  202900         P2  REQUESTED   \n",
       "567      84010806032         304063     300         P2  REQUESTED   \n",
       "928    8410409652247         680753    6800         P2  REQUESTED   \n",
       "930     290873806933           5020     500         P2  REQUESTED   \n",
       "1491   2348348707550      232100387  232100         P2  REQUESTED   \n",
       "1711   7010105652246         420779    4200         P2  REQUESTED   \n",
       "1987       604291026       10520686   10500         P2  REQUESTED   \n",
       "2097  10810678752246       55507109   55500         P2  REQUESTED   \n",
       "2538    272920337240        5630750   56300         P2  REQUESTED   \n",
       "2575      5797806234       25027304    2500         P2  REQUESTED   \n",
       "\n",
       "                                  transactionID  \n",
       "252      f825c7a7-daee-4210c-a77b-23a44c210e58e  \n",
       "567        dfe79e8b-2fd0-46e6-10d8-2a8f3e4e7e73  \n",
       "928      f10dd802c-810de-4605-be6a-a43eb075affc  \n",
       "930       e310a0636-09f4-4c06-a589-b2e25380524f  \n",
       "1491    fe1052608-5104f-46102-b39e-7f756526924f  \n",
       "1711  fb101010a0e-ef510-4f68-a5a7-bc5ee10d9c244  \n",
       "1987   e659ca103-610e-466d-10540-cce2577fe71010  \n",
       "2097   74e97720-764b-41052-10879-6e847bf10b107f  \n",
       "2538      784b1097a-48ce-474b-80bf-2f608a35effd  \n",
       "2575     f3d34cd3-a992-47cf-891010-42c97e4a9e4f  "
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking REQUESTED transactions related p2 provider and reported by p2.\n",
    "# why p2 reported these?.\n",
    "cond = (mergeDf['status']=='REQUESTED')\n",
    "requestedReport = mergeDf[cond]\n",
    "requestedReport"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "a3367f68-5666-421f-9eb0-2cc8f2afb5df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there aren't confliction in price of above table!\n"
     ]
    }
   ],
   "source": [
    "# conflict in price of above table.\n",
    "cond = (requestedReport['Amount']!= requestedReport['amount'])\n",
    "priceConflict2 = requestedReport[cond]\n",
    "for i,row in priceConflict2.iterrows():\n",
    "    print(f\"Bill list reported {row['amount']} but p2 provider reported {row['Amount']} for the price.\")\n",
    "if len(priceConflict2)==0:\n",
    "    print('there aren\\'t confliction in price of above table!')\n",
    "else:\n",
    "    display(priceConflict2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccfbb46d-3da8-4814-8a84-12975ceb4468",
   "metadata": {},
   "source": [
    "# ----------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f85734b-f4f8-4e56-a2e3-8814c2b324bb",
   "metadata": {},
   "source": [
    "# Checking difference pricing in p2 reported list and bill list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "e903e767-fb5b-410c-9681-c19e5a4b705e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there are not confliction in price of bill table and p2 providers when status is COMPLETED!\n"
     ]
    }
   ],
   "source": [
    "# checking difference pricing in p2 reported list and bill list.\n",
    "cond = (requestedReport['Amount']!= requestedReport['amount']) & (mergeDf['providerID']=='P2') & (mergeDf['status']=='COMPLETED')\n",
    "priceConflict3 = mergeDf[cond]\n",
    "\n",
    "if len(priceConflict3)==0:\n",
    "    print('there are not confliction in price of bill table and p2 providers when status is COMPLETED!')\n",
    "else:\n",
    "    display(priceConflict3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "820b5655-8a66-4947-8f12-3e6566a18238",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}