Inv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from fuzzywuzzy import fuzz\n",
    "from pandas import ExcelWriter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>SpecID</th>\n",
       "      <th>QuestionText</th>\n",
       "      <th>ResponseBaseFldName</th>\n",
       "      <th>CreateDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1991865953</td>\n",
       "      <td>Provide additional comments or clarifications ...</td>\n",
       "      <td>InAccCore9</td>\n",
       "      <td>2018-06-25 20:12:46.717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1955103041</td>\n",
       "      <td>If applicable, provide the life coverage as a ...</td>\n",
       "      <td>InfLifeCoreAddl4</td>\n",
       "      <td>2018-06-21 17:53:29.397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1972152103</td>\n",
       "      <td>If applicable, provide the life coverge as orp...</td>\n",
       "      <td>InfLifeCoreAddl7</td>\n",
       "      <td>2018-06-21 17:57:28.277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1991000950</td>\n",
       "      <td>Does your organization offer accident benefit ...</td>\n",
       "      <td>InvAccCore1</td>\n",
       "      <td>2018-06-25 17:36:15.167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1951233399</td>\n",
       "      <td>Indicate the type(s) of accident benefits prov...</td>\n",
       "      <td>InvAccCore1a</td>\n",
       "      <td>2018-06-25 18:57:26.423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1963767465</td>\n",
       "      <td>Indicate the type(s) of accident benefits cove...</td>\n",
       "      <td>InvAccCore2</td>\n",
       "      <td>2018-06-25 19:32:31.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1968191132</td>\n",
       "      <td>If applicable, provide the accident coverage a...</td>\n",
       "      <td>InvAccCore3</td>\n",
       "      <td>2018-06-25 19:42:32.507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1980394980</td>\n",
       "      <td>If lump sum - variable amount, please describe.</td>\n",
       "      <td>InvAccCore4</td>\n",
       "      <td>2018-07-25 17:54:18.477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1970968411</td>\n",
       "      <td>If applicable, provide the accident coverage a...</td>\n",
       "      <td>InvAccCore5</td>\n",
       "      <td>2018-06-25 19:52:27.400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1969774414</td>\n",
       "      <td>Indicate what elements of pay are used to dete...</td>\n",
       "      <td>InvAccCore6</td>\n",
       "      <td>2018-06-25 19:53:59.470</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       SpecID                                       QuestionText  \\\n",
       "0  1991865953  Provide additional comments or clarifications ...   \n",
       "1  1955103041  If applicable, provide the life coverage as a ...   \n",
       "2  1972152103  If applicable, provide the life coverge as orp...   \n",
       "3  1991000950  Does your organization offer accident benefit ...   \n",
       "4  1951233399  Indicate the type(s) of accident benefits prov...   \n",
       "5  1963767465  Indicate the type(s) of accident benefits cove...   \n",
       "6  1968191132  If applicable, provide the accident coverage a...   \n",
       "7  1980394980    If lump sum - variable amount, please describe.   \n",
       "8  1970968411  If applicable, provide the accident coverage a...   \n",
       "9  1969774414  Indicate what elements of pay are used to dete...   \n",
       "\n",
       "  ResponseBaseFldName              CreateDate  \n",
       "0          InAccCore9 2018-06-25 20:12:46.717  \n",
       "1    InfLifeCoreAddl4 2018-06-21 17:53:29.397  \n",
       "2    InfLifeCoreAddl7 2018-06-21 17:57:28.277  \n",
       "3         InvAccCore1 2018-06-25 17:36:15.167  \n",
       "4        InvAccCore1a 2018-06-25 18:57:26.423  \n",
       "5         InvAccCore2 2018-06-25 19:32:31.100  \n",
       "6         InvAccCore3 2018-06-25 19:42:32.507  \n",
       "7         InvAccCore4 2018-07-25 17:54:18.477  \n",
       "8         InvAccCore5 2018-06-25 19:52:27.400  \n",
       "9         InvAccCore6 2018-06-25 19:53:59.470  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"I.xlsx\")\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"IsDeleted\"] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "l=df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydict={}\n",
    "for index, row in df.iterrows():\n",
    "    if index < l-1 and row.IsDeleted == 0:\n",
    "        for i in range(index+1,l):\n",
    "            if df.iloc[i,4] == 0:\n",
    "                ratio = fuzz.token_set_ratio(row.QuestionText, df.iloc[i,1])\n",
    "                if ratio > 70:\n",
    "                    df.at[i,'IsDeleted'] = 1\n",
    "                    df.at[index,'IsDeleted'] = 1\n",
    "                    if df.iloc[index].SpecID in mydict:\n",
    "                        mylist = mydict[df.iloc[index,0]]\n",
    "                    else:\n",
    "                        mylist = []\n",
    "                    mylist.append(df.iloc[i,0])\n",
    "                    mydict[row.SpecID] = mylist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_dfs = []\n",
    "for i, j in mydict.items():\n",
    "    itemsToSelect=[]\n",
    "    itemsToSelect.append(i)\n",
    "    itemsToSelect = itemsToSelect+j\n",
    "    df_subset = df.loc[df['SpecID'].isin(itemsToSelect)]\n",
    "    list_dfs.append(df_subset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_xls(list_dfs, xls_path):\n",
    "    with ExcelWriter(xls_path) as writer:\n",
    "        for n, df in enumerate(list_dfs):\n",
    "            df.to_excel(writer,'sheet%s' % n)\n",
    "        writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_xls(list_dfs,\"Inv70.xls\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}