Final_model_for_SFS_Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b1ab4b17",
   "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>Question</th>\n",
       "      <th>weightage</th>\n",
       "      <th>Zero percentage</th>\n",
       "      <th>Multiplier</th>\n",
       "      <th>IMP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Security Check (NCF)</td>\n",
       "      <td>Scored    8.0</td>\n",
       "      <td>81.949807</td>\n",
       "      <td>8</td>\n",
       "      <td>655.598456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Do IT Digitally , explaining benefits of APP i...</td>\n",
       "      <td>Scored    10.0</td>\n",
       "      <td>65.057915</td>\n",
       "      <td>10</td>\n",
       "      <td>650.579151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CRM Tagging (NCF)</td>\n",
       "      <td>Scored    10.0</td>\n",
       "      <td>63.030888</td>\n",
       "      <td>10</td>\n",
       "      <td>630.308880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Sir / Madam, have you installed APP ? Explaine...</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>64.864865</td>\n",
       "      <td>5</td>\n",
       "      <td>324.324324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Complete &amp; Correct information (NCF)</td>\n",
       "      <td>Scored    10.0</td>\n",
       "      <td>30.694981</td>\n",
       "      <td>10</td>\n",
       "      <td>306.949807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hand Sanitizer</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>97.586873</td>\n",
       "      <td>2</td>\n",
       "      <td>195.173745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Verbal Greeting</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>42.664093</td>\n",
       "      <td>2</td>\n",
       "      <td>85.328185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Probing as per Scenario</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>13.803089</td>\n",
       "      <td>5</td>\n",
       "      <td>69.015444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grooming of CCE( CCE in proper dress code)+ Gr...</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>12.837838</td>\n",
       "      <td>5</td>\n",
       "      <td>64.189189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CCE Wear name badge</td>\n",
       "      <td>Scored    3.0</td>\n",
       "      <td>17.567568</td>\n",
       "      <td>3</td>\n",
       "      <td>52.702703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tent Card on CCE desk</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>23.552124</td>\n",
       "      <td>2</td>\n",
       "      <td>47.104247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Interior Flooring</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>15.926641</td>\n",
       "      <td>2</td>\n",
       "      <td>31.853282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Entrance mat</td>\n",
       "      <td>Scored    3.0</td>\n",
       "      <td>7.335907</td>\n",
       "      <td>3</td>\n",
       "      <td>22.007722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>No Cross talking / or, Talking over the phone ...</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>3.571429</td>\n",
       "      <td>5</td>\n",
       "      <td>17.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ceiling &amp; walls</td>\n",
       "      <td>Scored    1.0</td>\n",
       "      <td>13.803089</td>\n",
       "      <td>1</td>\n",
       "      <td>13.803089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Professional Behavior (Polite/ courteous)</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>1.930502</td>\n",
       "      <td>5</td>\n",
       "      <td>9.652510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Main signage</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>4.633205</td>\n",
       "      <td>2</td>\n",
       "      <td>9.266409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Not interrupting and rushing through the inter...</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>1.254826</td>\n",
       "      <td>5</td>\n",
       "      <td>6.274131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Overall Scored</td>\n",
       "      <td>int64</td>\n",
       "      <td>0.096525</td>\n",
       "      <td>64</td>\n",
       "      <td>6.177606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Entrance Door</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>1.930502</td>\n",
       "      <td>2</td>\n",
       "      <td>3.861004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Lights</td>\n",
       "      <td>Scored    3.0</td>\n",
       "      <td>0.965251</td>\n",
       "      <td>3</td>\n",
       "      <td>2.895753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Computers Cleanliness</td>\n",
       "      <td>Scored    3.0</td>\n",
       "      <td>0.868726</td>\n",
       "      <td>3</td>\n",
       "      <td>2.606178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Fixed Glass facade</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>0.579151</td>\n",
       "      <td>2</td>\n",
       "      <td>1.158301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Brand Exclusivity (NCF)</td>\n",
       "      <td>Scored    0.0</td>\n",
       "      <td>98.455598</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Dummy Question</td>\n",
       "      <td>Scored    0.0</td>\n",
       "      <td>98.455598</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Face Mask of CCE</td>\n",
       "      <td>Scored    0.0</td>\n",
       "      <td>98.455598</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Question       weightage  \\\n",
       "19                              Security Check (NCF)    Scored    8.0   \n",
       "23  Do IT Digitally , explaining benefits of APP i...  Scored    10.0   \n",
       "21                                 CRM Tagging (NCF)   Scored    10.0   \n",
       "22  Sir / Madam, have you installed APP ? Explaine...   Scored    5.0   \n",
       "20              Complete & Correct information (NCF)   Scored    10.0   \n",
       "4                                     Hand Sanitizer    Scored    2.0   \n",
       "17                                   Verbal Greeting    Scored    2.0   \n",
       "18                           Probing as per Scenario    Scored    5.0   \n",
       "11  Grooming of CCE( CCE in proper dress code)+ Gr...   Scored    5.0   \n",
       "12                               CCE Wear name badge    Scored    3.0   \n",
       "9                              Tent Card on CCE desk    Scored    2.0   \n",
       "1                                  Interior Flooring    Scored    2.0   \n",
       "7                                       Entrance mat    Scored    3.0   \n",
       "16  No Cross talking / or, Talking over the phone ...   Scored    5.0   \n",
       "2                                    Ceiling & walls    Scored    1.0   \n",
       "14         Professional Behavior (Polite/ courteous)    Scored    5.0   \n",
       "8                                       Main signage    Scored    2.0   \n",
       "15  Not interrupting and rushing through the inter...   Scored    5.0   \n",
       "25                                     Overall Scored           int64   \n",
       "0                                      Entrance Door    Scored    2.0   \n",
       "6                                             Lights    Scored    3.0   \n",
       "3                              Computers Cleanliness    Scored    3.0   \n",
       "5                                 Fixed Glass facade    Scored    2.0   \n",
       "10                           Brand Exclusivity (NCF)    Scored    0.0   \n",
       "24                                    Dummy Question    Scored    0.0   \n",
       "13                                  Face Mask of CCE    Scored    0.0   \n",
       "\n",
       "    Zero percentage  Multiplier         IMP  \n",
       "19        81.949807           8  655.598456  \n",
       "23        65.057915          10  650.579151  \n",
       "21        63.030888          10  630.308880  \n",
       "22        64.864865           5  324.324324  \n",
       "20        30.694981          10  306.949807  \n",
       "4         97.586873           2  195.173745  \n",
       "17        42.664093           2   85.328185  \n",
       "18        13.803089           5   69.015444  \n",
       "11        12.837838           5   64.189189  \n",
       "12        17.567568           3   52.702703  \n",
       "9         23.552124           2   47.104247  \n",
       "1         15.926641           2   31.853282  \n",
       "7          7.335907           3   22.007722  \n",
       "16         3.571429           5   17.857143  \n",
       "2         13.803089           1   13.803089  \n",
       "14         1.930502           5    9.652510  \n",
       "8          4.633205           2    9.266409  \n",
       "15         1.254826           5    6.274131  \n",
       "25         0.096525          64    6.177606  \n",
       "0          1.930502           2    3.861004  \n",
       "6          0.965251           3    2.895753  \n",
       "3          0.868726           3    2.606178  \n",
       "5          0.579151           2    1.158301  \n",
       "10        98.455598           0    0.000000  \n",
       "24        98.455598           0    0.000000  \n",
       "13        98.455598           0    0.000000  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import string\n",
    "\n",
    "df_sfs = pd.read_excel(r'/home/saurabhtiwari/retail_data/VI/new_data/August_sfs.xlsx')\n",
    "features = df_sfs.columns.to_list()\n",
    "attributes = df_sfs.columns.to_list()\n",
    "attributes_1 = attributes[:31]\n",
    "attributes_2 = attributes[31:]\n",
    "for i in attributes_2:\n",
    "    if  i.find('Scored')== -1:\n",
    "        pass\n",
    "    else:\n",
    "        attributes_1.append(i)\n",
    "for i in attributes_2:\n",
    "    if  i.find('Scorable')== -1:\n",
    "        pass\n",
    "    else:\n",
    "        attributes_1.append(i)\n",
    "        \n",
    "df_sfs_new = df_sfs[attributes_1]\n",
    "list_df =[]\n",
    "for i in df_sfs_new.columns:\n",
    "    if i.find('Scored')!=-1:\n",
    "        list_df.append(pd.DataFrame(df_sfs_new[i]))\n",
    "    else:\n",
    "        pass\n",
    "Question = []\n",
    "weightage = []\n",
    "no_count =[]\n",
    "for i in list_df:\n",
    "    x = i.columns.to_list()[0]\n",
    "    Question.append(i.columns.to_list()[0].split(':')[0])\n",
    "    t = str(i.max())\n",
    "    \n",
    "    u = t.split(':')[1].split('\\n')[0]\n",
    "    weightage.append(u)\n",
    "    a = len(i[i[x]==0])\n",
    "    b = len(i[x])\n",
    "    no_count.append((a/b)*100)\n",
    "df__ = pd.DataFrame({'Question':Question,'weightage':weightage,'Zero percentage':no_count})\n",
    "df__['Multiplier'] = df__['weightage'].apply(lambda x:int(re.findall(r'\\d+',x)[0]))\n",
    "\n",
    "df__['IMP'] = df__['Zero percentage']*df__['Multiplier']\n",
    "df__.sort_values('IMP',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4c9d03af",
   "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>Question</th>\n",
       "      <th>weightage</th>\n",
       "      <th>Zero percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Entrance Door</td>\n",
       "      <td>Entrance Door :Scored    2.0\n",
       "dtype: float64</td>\n",
       "      <td>1.930502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Interior Flooring</td>\n",
       "      <td>Interior Flooring :Scored    2.0\n",
       "dtype: float64</td>\n",
       "      <td>15.926641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ceiling &amp; walls</td>\n",
       "      <td>Ceiling &amp; walls :Scored    1.0\n",
       "dtype: float64</td>\n",
       "      <td>13.803089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Computers Cleanliness</td>\n",
       "      <td>Computers Cleanliness :Scored    3.0\n",
       "dtype: fl...</td>\n",
       "      <td>0.868726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hand Sanitizer</td>\n",
       "      <td>Hand Sanitizer :Scored    2.0\n",
       "dtype: float64</td>\n",
       "      <td>97.586873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Fixed Glass facade</td>\n",
       "      <td>Fixed Glass facade :Scored    2.0\n",
       "dtype: float64</td>\n",
       "      <td>0.579151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Lights</td>\n",
       "      <td>Lights :Scored    3.0\n",
       "dtype: float64</td>\n",
       "      <td>0.965251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Entrance mat</td>\n",
       "      <td>Entrance mat :Scored    3.0\n",
       "dtype: float64</td>\n",
       "      <td>7.335907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Main signage</td>\n",
       "      <td>Main signage :Scored    2.0\n",
       "dtype: float64</td>\n",
       "      <td>4.633205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tent Card on CCE desk</td>\n",
       "      <td>Tent Card on CCE desk :Scored    2.0\n",
       "dtype: fl...</td>\n",
       "      <td>23.552124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Brand Exclusivity (NCF)</td>\n",
       "      <td>Brand Exclusivity (NCF) :Scored    0.0\n",
       "dtype: ...</td>\n",
       "      <td>98.455598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grooming of CCE( CCE in proper dress code)+ Gr...</td>\n",
       "      <td>Grooming of CCE( CCE in proper dress code)+ Gr...</td>\n",
       "      <td>12.837838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CCE Wear name badge</td>\n",
       "      <td>CCE Wear name badge :Scored    3.0\n",
       "dtype: float64</td>\n",
       "      <td>17.567568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Face Mask of CCE</td>\n",
       "      <td>Face Mask of CCE :Scored    0.0\n",
       "dtype: float64</td>\n",
       "      <td>98.455598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Professional Behavior (Polite/ courteous)</td>\n",
       "      <td>Professional Behavior (Polite/ courteous) :Sco...</td>\n",
       "      <td>1.930502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Not interrupting and rushing through the inter...</td>\n",
       "      <td>Not interrupting and rushing through the inter...</td>\n",
       "      <td>1.254826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>No Cross talking / or, Talking over the phone ...</td>\n",
       "      <td>No Cross talking / or, Talking over the phone ...</td>\n",
       "      <td>3.571429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Verbal Greeting</td>\n",
       "      <td>Verbal Greeting :Scored    2.0\n",
       "dtype: float64</td>\n",
       "      <td>42.664093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Probing as per Scenario</td>\n",
       "      <td>Probing as per Scenario :Scored    5.0\n",
       "dtype: ...</td>\n",
       "      <td>13.803089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Security Check (NCF)</td>\n",
       "      <td>Security Check (NCF) :Scored    8.0\n",
       "dtype: flo...</td>\n",
       "      <td>81.949807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Complete &amp; Correct information (NCF)</td>\n",
       "      <td>Complete &amp; Correct information (NCF) :Scored  ...</td>\n",
       "      <td>30.694981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CRM Tagging (NCF)</td>\n",
       "      <td>CRM Tagging (NCF) :Scored    10.0\n",
       "dtype: float64</td>\n",
       "      <td>63.030888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Sir / Madam, have you installed APP ? Explaine...</td>\n",
       "      <td>Sir / Madam, have you installed APP ? Explaine...</td>\n",
       "      <td>64.864865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Do IT Digitally , explaining benefits of APP i...</td>\n",
       "      <td>Do IT Digitally , explaining benefits of APP i...</td>\n",
       "      <td>65.057915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Dummy Question</td>\n",
       "      <td>Dummy Question :Scored    0.0\n",
       "dtype: float64</td>\n",
       "      <td>98.455598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Overall Scored</td>\n",
       "      <td>Overall Scored    95\n",
       "dtype: int64</td>\n",
       "      <td>0.096525</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Question  \\\n",
       "0                                      Entrance Door    \n",
       "1                                  Interior Flooring    \n",
       "2                                    Ceiling & walls    \n",
       "3                              Computers Cleanliness    \n",
       "4                                     Hand Sanitizer    \n",
       "5                                 Fixed Glass facade    \n",
       "6                                             Lights    \n",
       "7                                       Entrance mat    \n",
       "8                                       Main signage    \n",
       "9                              Tent Card on CCE desk    \n",
       "10                           Brand Exclusivity (NCF)    \n",
       "11  Grooming of CCE( CCE in proper dress code)+ Gr...   \n",
       "12                               CCE Wear name badge    \n",
       "13                                  Face Mask of CCE    \n",
       "14         Professional Behavior (Polite/ courteous)    \n",
       "15  Not interrupting and rushing through the inter...   \n",
       "16  No Cross talking / or, Talking over the phone ...   \n",
       "17                                   Verbal Greeting    \n",
       "18                           Probing as per Scenario    \n",
       "19                              Security Check (NCF)    \n",
       "20              Complete & Correct information (NCF)    \n",
       "21                                 CRM Tagging (NCF)    \n",
       "22  Sir / Madam, have you installed APP ? Explaine...   \n",
       "23  Do IT Digitally , explaining benefits of APP i...   \n",
       "24                                    Dummy Question    \n",
       "25                                     Overall Scored   \n",
       "\n",
       "                                            weightage  Zero percentage  \n",
       "0         Entrance Door :Scored    2.0\n",
       "dtype: float64         1.930502  \n",
       "1     Interior Flooring :Scored    2.0\n",
       "dtype: float64        15.926641  \n",
       "2       Ceiling & walls :Scored    1.0\n",
       "dtype: float64        13.803089  \n",
       "3   Computers Cleanliness :Scored    3.0\n",
       "dtype: fl...         0.868726  \n",
       "4        Hand Sanitizer :Scored    2.0\n",
       "dtype: float64        97.586873  \n",
       "5    Fixed Glass facade :Scored    2.0\n",
       "dtype: float64         0.579151  \n",
       "6                Lights :Scored    3.0\n",
       "dtype: float64         0.965251  \n",
       "7          Entrance mat :Scored    3.0\n",
       "dtype: float64         7.335907  \n",
       "8          Main signage :Scored    2.0\n",
       "dtype: float64         4.633205  \n",
       "9   Tent Card on CCE desk :Scored    2.0\n",
       "dtype: fl...        23.552124  \n",
       "10  Brand Exclusivity (NCF) :Scored    0.0\n",
       "dtype: ...        98.455598  \n",
       "11  Grooming of CCE( CCE in proper dress code)+ Gr...        12.837838  \n",
       "12  CCE Wear name badge :Scored    3.0\n",
       "dtype: float64        17.567568  \n",
       "13     Face Mask of CCE :Scored    0.0\n",
       "dtype: float64        98.455598  \n",
       "14  Professional Behavior (Polite/ courteous) :Sco...         1.930502  \n",
       "15  Not interrupting and rushing through the inter...         1.254826  \n",
       "16  No Cross talking / or, Talking over the phone ...         3.571429  \n",
       "17      Verbal Greeting :Scored    2.0\n",
       "dtype: float64        42.664093  \n",
       "18  Probing as per Scenario :Scored    5.0\n",
       "dtype: ...        13.803089  \n",
       "19  Security Check (NCF) :Scored    8.0\n",
       "dtype: flo...        81.949807  \n",
       "20  Complete & Correct information (NCF) :Scored  ...        30.694981  \n",
       "21   CRM Tagging (NCF) :Scored    10.0\n",
       "dtype: float64        63.030888  \n",
       "22  Sir / Madam, have you installed APP ? Explaine...        64.864865  \n",
       "23  Do IT Digitally , explaining benefits of APP i...        65.057915  \n",
       "24       Dummy Question :Scored    0.0\n",
       "dtype: float64        98.455598  \n",
       "25                  Overall Scored    95\n",
       "dtype: int64         0.096525  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b387219c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1036, 131)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sfs = pd.read_excel(r'/home/saurabhtiwari/retail_data/VI/new_data/August_sfs.xlsx')\n",
    "df_sfs.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9eb66e2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sfs = pd.read_excel(r'/home/saurabhtiwari/retail_data/VI/new_data/August_sfs.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "749caba2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sfs= df_sfs[df_sfs['Store Status']=='OPEN']\n",
    "df_sfs.reset_index(drop ='True', inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1ac06b7a",
   "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>Question</th>\n",
       "      <th>weightage</th>\n",
       "      <th>Zero percentage</th>\n",
       "      <th>Multiplier</th>\n",
       "      <th>IMP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Security Check (NCF)</td>\n",
       "      <td>Scored    8.0</td>\n",
       "      <td>81.949807</td>\n",
       "      <td>8</td>\n",
       "      <td>655.598456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Do IT Digitally , explaining benefits of APP i...</td>\n",
       "      <td>Scored    10.0</td>\n",
       "      <td>65.057915</td>\n",
       "      <td>10</td>\n",
       "      <td>650.579151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CRM Tagging (NCF)</td>\n",
       "      <td>Scored    10.0</td>\n",
       "      <td>63.030888</td>\n",
       "      <td>10</td>\n",
       "      <td>630.308880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Sir / Madam, have you installed APP ? Explaine...</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>64.864865</td>\n",
       "      <td>5</td>\n",
       "      <td>324.324324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Complete &amp; Correct information (NCF)</td>\n",
       "      <td>Scored    10.0</td>\n",
       "      <td>30.694981</td>\n",
       "      <td>10</td>\n",
       "      <td>306.949807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hand Sanitizer</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>97.586873</td>\n",
       "      <td>2</td>\n",
       "      <td>195.173745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Verbal Greeting</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>42.664093</td>\n",
       "      <td>2</td>\n",
       "      <td>85.328185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Probing as per Scenario</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>13.803089</td>\n",
       "      <td>5</td>\n",
       "      <td>69.015444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grooming of CCE( CCE in proper dress code)+ Gr...</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>12.837838</td>\n",
       "      <td>5</td>\n",
       "      <td>64.189189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CCE Wear name badge</td>\n",
       "      <td>Scored    3.0</td>\n",
       "      <td>17.567568</td>\n",
       "      <td>3</td>\n",
       "      <td>52.702703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tent Card on CCE desk</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>23.552124</td>\n",
       "      <td>2</td>\n",
       "      <td>47.104247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Interior Flooring</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>15.926641</td>\n",
       "      <td>2</td>\n",
       "      <td>31.853282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Entrance mat</td>\n",
       "      <td>Scored    3.0</td>\n",
       "      <td>7.335907</td>\n",
       "      <td>3</td>\n",
       "      <td>22.007722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>No Cross talking / or, Talking over the phone ...</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>3.571429</td>\n",
       "      <td>5</td>\n",
       "      <td>17.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ceiling &amp; walls</td>\n",
       "      <td>Scored    1.0</td>\n",
       "      <td>13.803089</td>\n",
       "      <td>1</td>\n",
       "      <td>13.803089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Professional Behavior (Polite/ courteous)</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>1.930502</td>\n",
       "      <td>5</td>\n",
       "      <td>9.652510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Main signage</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>4.633205</td>\n",
       "      <td>2</td>\n",
       "      <td>9.266409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Not interrupting and rushing through the inter...</td>\n",
       "      <td>Scored    5.0</td>\n",
       "      <td>1.254826</td>\n",
       "      <td>5</td>\n",
       "      <td>6.274131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Overall Scored</td>\n",
       "      <td>int64</td>\n",
       "      <td>0.096525</td>\n",
       "      <td>64</td>\n",
       "      <td>6.177606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Entrance Door</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>1.930502</td>\n",
       "      <td>2</td>\n",
       "      <td>3.861004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Lights</td>\n",
       "      <td>Scored    3.0</td>\n",
       "      <td>0.965251</td>\n",
       "      <td>3</td>\n",
       "      <td>2.895753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Computers Cleanliness</td>\n",
       "      <td>Scored    3.0</td>\n",
       "      <td>0.868726</td>\n",
       "      <td>3</td>\n",
       "      <td>2.606178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Fixed Glass facade</td>\n",
       "      <td>Scored    2.0</td>\n",
       "      <td>0.579151</td>\n",
       "      <td>2</td>\n",
       "      <td>1.158301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Brand Exclusivity (NCF)</td>\n",
       "      <td>Scored    0.0</td>\n",
       "      <td>98.455598</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Dummy Question</td>\n",
       "      <td>Scored    0.0</td>\n",
       "      <td>98.455598</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Face Mask of CCE</td>\n",
       "      <td>Scored    0.0</td>\n",
       "      <td>98.455598</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Question       weightage  \\\n",
       "19                              Security Check (NCF)    Scored    8.0   \n",
       "23  Do IT Digitally , explaining benefits of APP i...  Scored    10.0   \n",
       "21                                 CRM Tagging (NCF)   Scored    10.0   \n",
       "22  Sir / Madam, have you installed APP ? Explaine...   Scored    5.0   \n",
       "20              Complete & Correct information (NCF)   Scored    10.0   \n",
       "4                                     Hand Sanitizer    Scored    2.0   \n",
       "17                                   Verbal Greeting    Scored    2.0   \n",
       "18                           Probing as per Scenario    Scored    5.0   \n",
       "11  Grooming of CCE( CCE in proper dress code)+ Gr...   Scored    5.0   \n",
       "12                               CCE Wear name badge    Scored    3.0   \n",
       "9                              Tent Card on CCE desk    Scored    2.0   \n",
       "1                                  Interior Flooring    Scored    2.0   \n",
       "7                                       Entrance mat    Scored    3.0   \n",
       "16  No Cross talking / or, Talking over the phone ...   Scored    5.0   \n",
       "2                                    Ceiling & walls    Scored    1.0   \n",
       "14         Professional Behavior (Polite/ courteous)    Scored    5.0   \n",
       "8                                       Main signage    Scored    2.0   \n",
       "15  Not interrupting and rushing through the inter...   Scored    5.0   \n",
       "25                                     Overall Scored           int64   \n",
       "0                                      Entrance Door    Scored    2.0   \n",
       "6                                             Lights    Scored    3.0   \n",
       "3                              Computers Cleanliness    Scored    3.0   \n",
       "5                                 Fixed Glass facade    Scored    2.0   \n",
       "10                           Brand Exclusivity (NCF)    Scored    0.0   \n",
       "24                                    Dummy Question    Scored    0.0   \n",
       "13                                  Face Mask of CCE    Scored    0.0   \n",
       "\n",
       "    Zero percentage  Multiplier         IMP  \n",
       "19        81.949807           8  655.598456  \n",
       "23        65.057915          10  650.579151  \n",
       "21        63.030888          10  630.308880  \n",
       "22        64.864865           5  324.324324  \n",
       "20        30.694981          10  306.949807  \n",
       "4         97.586873           2  195.173745  \n",
       "17        42.664093           2   85.328185  \n",
       "18        13.803089           5   69.015444  \n",
       "11        12.837838           5   64.189189  \n",
       "12        17.567568           3   52.702703  \n",
       "9         23.552124           2   47.104247  \n",
       "1         15.926641           2   31.853282  \n",
       "7          7.335907           3   22.007722  \n",
       "16         3.571429           5   17.857143  \n",
       "2         13.803089           1   13.803089  \n",
       "14         1.930502           5    9.652510  \n",
       "8          4.633205           2    9.266409  \n",
       "15         1.254826           5    6.274131  \n",
       "25         0.096525          64    6.177606  \n",
       "0          1.930502           2    3.861004  \n",
       "6          0.965251           3    2.895753  \n",
       "3          0.868726           3    2.606178  \n",
       "5          0.579151           2    1.158301  \n",
       "10        98.455598           0    0.000000  \n",
       "24        98.455598           0    0.000000  \n",
       "13        98.455598           0    0.000000  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import string\n",
    "\n",
    "df_sfs = pd.read_excel(r'/home/saurabhtiwari/retail_data/VI/new_data/August_sfs.xlsx')\n",
    "features = df_sfs.columns.to_list()\n",
    "attributes = df_sfs.columns.to_list()\n",
    "attributes_1 = attributes[:31]\n",
    "attributes_2 = attributes[31:]\n",
    "for i in attributes_2:\n",
    "    if  i.find('Scored')== -1:\n",
    "        pass\n",
    "    else:\n",
    "        attributes_1.append(i)\n",
    "for i in attributes_2:\n",
    "    if  i.find('Scorable')== -1:\n",
    "        pass\n",
    "    else:\n",
    "        attributes_1.append(i)\n",
    "        \n",
    "df_sfs_new = df_sfs[attributes_1]\n",
    "list_df =[]\n",
    "for i in df_sfs_new.columns:\n",
    "    if i.find('Scored')!=-1:\n",
    "        list_df.append(pd.DataFrame(df_sfs_new[i]))\n",
    "    else:\n",
    "        pass\n",
    "Question = []\n",
    "weightage = []\n",
    "no_count =[]\n",
    "for i in list_df:\n",
    "    x = i.columns.to_list()[0]\n",
    "    Question.append(i.columns.to_list()[0].split(':')[0])\n",
    "    t = str(i.max())\n",
    "    \n",
    "    u = t.split(':')[1].split('\\n')[0]\n",
    "    weightage.append(u)\n",
    "    a = len(i[i[x]==0])\n",
    "    b = len(i[x])\n",
    "    no_count.append((a/b)*100)\n",
    "df__ = pd.DataFrame({'Question':Question,'weightage':weightage,'Zero percentage':no_count})\n",
    "df__['Multiplier'] = df__['weightage'].apply(lambda x:int(re.findall(r'\\d+',x)[0]))\n",
    "\n",
    "df__['IMP'] = df__['Zero percentage']*df__['Multiplier']\n",
    "df__.sort_values('IMP',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17cba6d9",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}