Excel_all_in_one_Working_Steps_in_Python_Pandas.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Excel all in one Working Steps in Python Pandas.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "1yrVkmxNSOyp",
        "_TJEKuTif445",
        "ZlbTyIEgiHSH",
        "mUleh9l5RuLG"
      ],
      "mount_file_id": "1U9A0wxnowauic8ukCoW2iaCOZlDOi6Ya",
      "authorship_tag": "ABX9TyN50wk195UFERu87v8YyY7K",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/connectkishan1/Pandas-Practice/blob/master/Excel_all_in_one_Working_Steps_in_Python_Pandas.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1yrVkmxNSOyp",
        "colab_type": "text"
      },
      "source": [
        "#Project1 : Data Analytics\n",
        "-Excel all in one Working Steps in Python Pandas\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "45kaMn5KfScL",
        "colab_type": "text"
      },
      "source": [
        "**Importing libraries**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "g0EZbBNUe9Og",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import sys\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "from termcolor import colored"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_TJEKuTif445",
        "colab_type": "text"
      },
      "source": [
        "# Step1: DAQ-DataAcqisition"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ok52vob8fmOq",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df=pd.read_excel(\"/content/pythonboss data.xlsx\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-_f51244gOaB",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 483
        },
        "outputId": "2c9764e2-f0e3-44f6-a21a-9c54145bc8ef"
      },
      "source": [
        "df"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>SeqI Score With Missing</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>5.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2</td>\n",
              "      <td>5</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3</td>\n",
              "      <td>3</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4</td>\n",
              "      <td>4</td>\n",
              "      <td>4.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5</td>\n",
              "      <td>6</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6</td>\n",
              "      <td>8</td>\n",
              "      <td>5.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7</td>\n",
              "      <td>9</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8</td>\n",
              "      <td>4</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9</td>\n",
              "      <td>4</td>\n",
              "      <td>2.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>kali</td>\n",
              "      <td>AP</td>\n",
              "      <td>nellore</td>\n",
              "      <td>indus</td>\n",
              "      <td>2020-08-17</td>\n",
              "      <td>10.0</td>\n",
              "      <td>10</td>\n",
              "      <td>6</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>11</td>\n",
              "      <td>8</td>\n",
              "      <td>5.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>12</td>\n",
              "      <td>4</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>13</td>\n",
              "      <td>9</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>NaN</td>\n",
              "      <td>14</td>\n",
              "      <td>11</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       Name Circle    Cluster  ... Site Audits SQI Score  SeqI Score With Missing\n",
              "0       Ram     TN        CBE  ...           1         2                      5.0\n",
              "1    ganesh     TN        MDU  ...           2         5                      NaN\n",
              "2    shashi    KER    calicut  ...           3         3                      3.0\n",
              "3    kishan     BR       gaya  ...           4         4                      4.0\n",
              "4      shiv     BR      patna  ...           5         6                      3.0\n",
              "5   hanuman    che         sw  ...           6         8                      5.0\n",
              "6      sita    che         wt  ...           7         9                      NaN\n",
              "7   lakshmi     up   aayodhya  ...           8         4                      NaN\n",
              "8     durga     up  varanashi  ...           9         4                      2.0\n",
              "9      kali     AP    nellore  ...          10         6                      1.0\n",
              "10   kishan    Guj      surat  ...          11         8                      5.0\n",
              "11     shiv    Raj       kota  ...          12         4                      1.0\n",
              "12   shashi    KER    trissur  ...          13         9                      NaN\n",
              "13   kishan     BR       gaya  ...          14        11                      NaN\n",
              "\n",
              "[14 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EYNwb7ctf2P0",
        "colab_type": "text"
      },
      "source": [
        "\n",
        "\n",
        "```\n",
        "# This is formatted as code\n",
        "```\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZlbTyIEgiHSH",
        "colab_type": "text"
      },
      "source": [
        "# Step2: Data Exploration"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qrLYH-t_Q94T",
        "colab_type": "text"
      },
      "source": [
        "**Important attribute/method for Basic Exploration**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0QVHk4ZHjFli",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 901
        },
        "outputId": "8870fff4-c794-429e-9971-db0351d9b746"
      },
      "source": [
        "#Important attribute/method for Basic Exploration\n",
        "print(colored('Print a concise summary of a Df..', 'green', attrs=['bold']))\n",
        "print(\"Print a concise summary of a Df..\\n\",df.info())\n",
        "print(colored('print the dimensionality of the DataFrame', 'red', attrs=['bold']))\n",
        "print(df.shape) \n",
        "print(colored('Print the number of rows times number of columns', 'green', attrs=['bold']))\n",
        "print(df.size)\n",
        "print(\"print the top 5 rows of df ..\\n\",df.head())\n",
        "print(\"print the Bottom 5 rows of df ..\\n\",df.tail())\n",
        "print(\"print the data types of df ..\\n\",df.dtypes)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\u001b[1m\u001b[32mPrint a concise summary of a Df..\u001b[0m\n",
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 14 entries, 0 to 13\n",
            "Data columns (total 9 columns):\n",
            " #   Column                   Non-Null Count  Dtype         \n",
            "---  ------                   --------------  -----         \n",
            " 0   Name                     14 non-null     object        \n",
            " 1   Circle                   14 non-null     object        \n",
            " 2   Cluster                  14 non-null     object        \n",
            " 3   Company                  14 non-null     object        \n",
            " 4   Date                     14 non-null     datetime64[ns]\n",
            " 5   Site I&C                 13 non-null     float64       \n",
            " 6   Site Audits              14 non-null     int64         \n",
            " 7   SQI Score                14 non-null     int64         \n",
            " 8   SeqI Score With Missing  9 non-null      float64       \n",
            "dtypes: datetime64[ns](1), float64(2), int64(2), object(4)\n",
            "memory usage: 1.1+ KB\n",
            "Print a concise summary of a Df..\n",
            " None\n",
            "\u001b[1m\u001b[31mprint the dimensionality of the DataFrame\u001b[0m\n",
            "(14, 9)\n",
            "\u001b[1m\u001b[32mPrint the number of rows times number of columns\u001b[0m\n",
            "126\n",
            "print the top 5 rows of df ..\n",
            "      Name Circle  Cluster  ... Site Audits SQI Score  SeqI Score With Missing\n",
            "0     Ram     TN      CBE  ...           1         2                      5.0\n",
            "1  ganesh     TN      MDU  ...           2         5                      NaN\n",
            "2  shashi    KER  calicut  ...           3         3                      3.0\n",
            "3  kishan     BR     gaya  ...           4         4                      4.0\n",
            "4    shiv     BR    patna  ...           5         6                      3.0\n",
            "\n",
            "[5 rows x 9 columns]\n",
            "print the Bottom 5 rows of df ..\n",
            "       Name Circle  Cluster  ... Site Audits SQI Score  SeqI Score With Missing\n",
            "9     kali     AP  nellore  ...          10         6                      1.0\n",
            "10  kishan    Guj    surat  ...          11         8                      5.0\n",
            "11    shiv    Raj     kota  ...          12         4                      1.0\n",
            "12  shashi    KER  trissur  ...          13         9                      NaN\n",
            "13  kishan     BR     gaya  ...          14        11                      NaN\n",
            "\n",
            "[5 rows x 9 columns]\n",
            "print the data types of df ..\n",
            " Name                               object\n",
            "Circle                             object\n",
            "Cluster                            object\n",
            "Company                            object\n",
            "Date                       datetime64[ns]\n",
            "Site I&C                          float64\n",
            "Site Audits                         int64\n",
            "SQI Score                           int64\n",
            "SeqI Score With Missing           float64\n",
            "dtype: object\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2xslBe-dQo22",
        "colab_type": "text"
      },
      "source": [
        "**Detecting Missing Value**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GHT3VPX7nslR",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 187
        },
        "outputId": "4cf564ae-c677-473b-b56c-d076175f63b6"
      },
      "source": [
        "np.sum(df.isnull())#Detect no of missing values"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Name                       0\n",
              "Circle                     0\n",
              "Cluster                    0\n",
              "Company                    0\n",
              "Date                       0\n",
              "Site I&C                   1\n",
              "Site Audits                0\n",
              "SQI Score                  0\n",
              "SeqI Score With Missing    5\n",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UhwhzfzayfWB",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 187
        },
        "outputId": "ca151a6d-dd8c-4a74-c054-f5c6f4fa7f83"
      },
      "source": [
        "np.sum(df.notnull()) #Detect no of existing (non-missing) values."
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Name                       14\n",
              "Circle                     14\n",
              "Cluster                    14\n",
              "Company                    14\n",
              "Date                       14\n",
              "Site I&C                   13\n",
              "Site Audits                14\n",
              "SQI Score                  14\n",
              "SeqI Score With Missing     9\n",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nHusN4Ywnqgy",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 187
        },
        "outputId": "02ec502c-e8f2-478b-cb26-20ef707e615b"
      },
      "source": [
        "np.sum(df.isnull())/df.shape[0] #Detect % of Missing values of DF"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Name                       0.000000\n",
              "Circle                     0.000000\n",
              "Cluster                    0.000000\n",
              "Company                    0.000000\n",
              "Date                       0.000000\n",
              "Site I&C                   0.071429\n",
              "Site Audits                0.000000\n",
              "SQI Score                  0.000000\n",
              "SeqI Score With Missing    0.357143\n",
              "dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aPL_5ndnnoYX",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "8e89e303-5bd5-4214-a942-9169bb64f541"
      },
      "source": [
        "np.sum(np.sum(df.isnull())/df.shape[0]>.35) #to find the no of columns with more than 35% of data missing"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qqQLRafT0gzO",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "669c5a81-3a75-4074-876a-1e0d15bcf4e7"
      },
      "source": [
        "set(df.columns[df.isnull().mean()>.35]) #to find the name of columns with more than 35% of data missing"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'SeqI Score With Missing'}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "thJFgNblL8vC",
        "colab_type": "text"
      },
      "source": [
        "**loc-Access a group of rows and columns by label(s) or a boolean array.**\n",
        "\n",
        "df.loc[['rows name'],['column name']\n",
        "\n",
        "**Iloc-Purely integer-location based indexing for selection by position**\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_fLD-D5V1uKx",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "19799f73-e035-49e9-e3f3-89e95838b1c7"
      },
      "source": [
        "df.loc[[3],['Name','Company']]#to detect 3rd row from the column Name & Company of DF"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Company</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>elexi</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Name Company\n",
              "3  kishan   elexi"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 146
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aKg_wwEkMlNI",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        },
        "outputId": "c40fb8e6-c8f6-4cc3-c5ef-fe86e75792c7"
      },
      "source": [
        "df.iloc[[3],[0,3]]#to detect 3rd row from the column Name & Company of DF"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Company</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>elexi</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Name Company\n",
              "3  kishan   elexi"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UYetpRR9M60B",
        "colab_type": "text"
      },
      "source": [
        "**Exploring Row Values based on column from df(excel filtering work)**\n",
        "\n",
        "*Method 1* Using asign(==)\n",
        "'|' denotes OR operation to select multiple rows value in columns....\n",
        "'&' denotes AND operation to select Column\n",
        "\n",
        "*Method 2* Using asign(.isin)\n",
        "'"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ArKOuTzwRnVJ",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 111
        },
        "outputId": "9bff54b6-7d87-4b27-e8c4-2791a11a65f1"
      },
      "source": [
        "#Method1\n",
        "df[(df['Company'] == 'sky')&(df['Circle'] =='TN')&(df['Cluster'] =='CBE')|(df['Cluster'] =='MDU')]\n",
        "\n",
        "#Method2\n",
        "#df[(df['Company'].isin(['sky']))&(df['Circle'].isin(['TN']))&(df['Cluster'].isin(['CBE']))|(df['Cluster'].isin(['MDU']))]"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>SeqI Score With Missing</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>5.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2</td>\n",
              "      <td>5</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Name Circle Cluster  ... Site Audits SQI Score  SeqI Score With Missing\n",
              "0     Ram     TN     CBE  ...           1         2                      5.0\n",
              "1  ganesh     TN     MDU  ...           2         5                      NaN\n",
              "\n",
              "[2 rows x 9 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j8vqdjvccgf6",
        "colab_type": "text"
      },
      "source": [
        "**Exploring & Comairing b/w to column using conditonal operator**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "f1zn-u2Uaqya",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 442
        },
        "outputId": "c9c20511-3a14-4919-e42d-83cce510cbcb"
      },
      "source": [
        "print(\"Check condition between 2 Column\\n\",df['Site Audits']>df['Site I&C'])\n",
        "print(\"print the no of value ater conditonal\\n\", np.sum(df['Site Audits']>df['Site I&C']))\n",
        "print(\"Print the DF with applied Conditonal operation\\n\",df[df['Site Audits']>df['Site I&C']])"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Check condition between 2 Column\n",
            " 0     False\n",
            "1     False\n",
            "2     False\n",
            "3     False\n",
            "4     False\n",
            "5     False\n",
            "6     False\n",
            "7     False\n",
            "8     False\n",
            "9     False\n",
            "10     True\n",
            "11     True\n",
            "12     True\n",
            "13    False\n",
            "dtype: bool\n",
            "print the no of value ater conditonal\n",
            " 3\n",
            "Print the DF with applied Conditonal operation\n",
            "       Name Circle  Cluster  ... Site Audits SQI Score  SeqI Score With Missing\n",
            "10  kishan    Guj    surat  ...          11         8                      5.0\n",
            "11    shiv    Raj     kota  ...          12         4                      1.0\n",
            "12  shashi    KER  trissur  ...          13         9                      NaN\n",
            "\n",
            "[3 rows x 9 columns]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "36FtjOuDR536",
        "colab_type": "text"
      },
      "source": [
        "**Note**\n",
        "Write the points As per Exploration if changes is required to change or update  in dataframe.\n",
        "\n",
        "1.drop column if Missing values more than 35%\n",
        "\n",
        "2.fillna rows if less % of Missing values availble\n",
        "\n",
        "3.Replacing rows Value from original Value\n",
        "\n",
        "4.Add Column with \"% of audit\" from the column of df\n",
        "\n",
        "5.Saperate Date in Date Day Year Column\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mUleh9l5RuLG",
        "colab_type": "text"
      },
      "source": [
        "# Step3: Data Preprocessing"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "weTHJUazSACr",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 483
        },
        "outputId": "78ede00b-efa1-4251-cd62-9f621c4d896e"
      },
      "source": [
        "df_drop_missing=df.drop(df.columns[df.isnull().mean() > 0.35],axis=1)\n",
        "df_drop_missing"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5</td>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6</td>\n",
              "      <td>8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>kali</td>\n",
              "      <td>AP</td>\n",
              "      <td>nellore</td>\n",
              "      <td>indus</td>\n",
              "      <td>2020-08-17</td>\n",
              "      <td>10.0</td>\n",
              "      <td>10</td>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>11</td>\n",
              "      <td>8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>12</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>13</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>NaN</td>\n",
              "      <td>14</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       Name Circle    Cluster  ... Site I&C Site Audits  SQI Score\n",
              "0       Ram     TN        CBE  ...     10.0           1          2\n",
              "1    ganesh     TN        MDU  ...     10.0           2          5\n",
              "2    shashi    KER    calicut  ...     10.0           3          3\n",
              "3    kishan     BR       gaya  ...     10.0           4          4\n",
              "4      shiv     BR      patna  ...     10.0           5          6\n",
              "5   hanuman    che         sw  ...     10.0           6          8\n",
              "6      sita    che         wt  ...     10.0           7          9\n",
              "7   lakshmi     up   aayodhya  ...     10.0           8          4\n",
              "8     durga     up  varanashi  ...     10.0           9          4\n",
              "9      kali     AP    nellore  ...     10.0          10          6\n",
              "10   kishan    Guj      surat  ...     10.0          11          8\n",
              "11     shiv    Raj       kota  ...     10.0          12          4\n",
              "12   shashi    KER    trissur  ...     10.0          13          9\n",
              "13   kishan     BR       gaya  ...      NaN          14         11\n",
              "\n",
              "[14 rows x 8 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3wl5f6fm9nm-",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 483
        },
        "outputId": "5453808a-f6b3-4cb0-804c-c37f1fc126eb"
      },
      "source": [
        "df_replace=df_drop_missing.fillna(df_drop_missing['Site I&C'].mean())\n",
        "df_replace"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5</td>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6</td>\n",
              "      <td>8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>kali</td>\n",
              "      <td>AP</td>\n",
              "      <td>nellore</td>\n",
              "      <td>indus</td>\n",
              "      <td>2020-08-17</td>\n",
              "      <td>10.0</td>\n",
              "      <td>10</td>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>11</td>\n",
              "      <td>8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>12</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>13</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>10.0</td>\n",
              "      <td>14</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       Name Circle    Cluster  ... Site I&C Site Audits  SQI Score\n",
              "0       Ram     TN        CBE  ...     10.0           1          2\n",
              "1    ganesh     TN        MDU  ...     10.0           2          5\n",
              "2    shashi    KER    calicut  ...     10.0           3          3\n",
              "3    kishan     BR       gaya  ...     10.0           4          4\n",
              "4      shiv     BR      patna  ...     10.0           5          6\n",
              "5   hanuman    che         sw  ...     10.0           6          8\n",
              "6      sita    che         wt  ...     10.0           7          9\n",
              "7   lakshmi     up   aayodhya  ...     10.0           8          4\n",
              "8     durga     up  varanashi  ...     10.0           9          4\n",
              "9      kali     AP    nellore  ...     10.0          10          6\n",
              "10   kishan    Guj      surat  ...     10.0          11          8\n",
              "11     shiv    Raj       kota  ...     10.0          12          4\n",
              "12   shashi    KER    trissur  ...     10.0          13          9\n",
              "13   kishan     BR       gaya  ...     10.0          14         11\n",
              "\n",
              "[14 rows x 8 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YaGq4p-uPHmR",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 272
        },
        "outputId": "ede57939-795b-4cbd-ed8d-0bcbbcddc801"
      },
      "source": [
        "df_replace[\"Name\"].apply(lambda x:x.upper())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0         RAM\n",
              "1      GANESH\n",
              "2      SHASHI\n",
              "3      KISHAN\n",
              "4        SHIV\n",
              "5     HANUMAN\n",
              "6        SITA\n",
              "7     LAKSHMI\n",
              "8       DURGA\n",
              "9        KALI\n",
              "10     KISHAN\n",
              "11       SHIV\n",
              "12     SHASHI\n",
              "13     KISHAN\n",
              "Name: Name, dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zr36TA8BGAkO",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 272
        },
        "outputId": "3351a3a8-f0b9-436a-fc12-8677913283fb"
      },
      "source": [
        "df.Date=pd.to_datetime(df.Date)\n",
        "df.Date"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0    2020-07-08\n",
              "1    2020-07-09\n",
              "2    2020-07-10\n",
              "3    2020-08-11\n",
              "4    2020-08-12\n",
              "5    2020-08-13\n",
              "6    2020-08-13\n",
              "7    2020-09-15\n",
              "8    2020-09-15\n",
              "9    2020-08-17\n",
              "10   2020-08-18\n",
              "11   2020-08-19\n",
              "12   2020-08-20\n",
              "13   2020-08-21\n",
              "Name: Date, dtype: datetime64[ns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "N-bXwxJTJfIs",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 483
        },
        "outputId": "8a347767-3cd5-4110-cc87-5b6e5281776c"
      },
      "source": [
        "df_replace[['year','month','day']]=df_replace.Date.apply(lambda x: pd.Series(x.strftime(\"%y,%m,%d\").split(\",\")))\n",
        "df1=df_replace[['year','month','day']]\n",
        "df1"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>year</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   year month day\n",
              "0    20    07  08\n",
              "1    20    07  09\n",
              "2    20    07  10\n",
              "3    20    08  11\n",
              "4    20    08  12\n",
              "5    20    08  13\n",
              "6    20    08  13\n",
              "7    20    09  15\n",
              "8    20    09  15\n",
              "9    20    08  17\n",
              "10   20    08  18\n",
              "11   20    08  19\n",
              "12   20    08  20\n",
              "13   20    08  21"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h_X_qPBvGPqI",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 483
        },
        "outputId": "7c835d33-c32a-4ecc-bbfe-7f04fd974d6b"
      },
      "source": [
        "df_replace[['year','month','day']]=df1\n",
        "df_replace"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>year</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2</td>\n",
              "      <td>5</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3</td>\n",
              "      <td>3</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5</td>\n",
              "      <td>6</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>kali</td>\n",
              "      <td>AP</td>\n",
              "      <td>nellore</td>\n",
              "      <td>indus</td>\n",
              "      <td>2020-08-17</td>\n",
              "      <td>10.0</td>\n",
              "      <td>10</td>\n",
              "      <td>6</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>11</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>12</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>13</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>10.0</td>\n",
              "      <td>14</td>\n",
              "      <td>11</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       Name Circle    Cluster     Company  ... SQI Score  year  month  day\n",
              "0       Ram     TN        CBE         sky  ...         2    20     07   08\n",
              "1    ganesh     TN        MDU         sky  ...         5    20     07   09\n",
              "2    shashi    KER    calicut  rs spectra  ...         3    20     07   10\n",
              "3    kishan     BR       gaya       elexi  ...         4    20     08   11\n",
              "4      shiv     BR      patna       elexi  ...         6    20     08   12\n",
              "5   hanuman    che         sw       airon  ...         8    20     08   13\n",
              "6      sita    che         wt         tvs  ...         9    20     08   13\n",
              "7   lakshmi     up   aayodhya         ncr  ...         4    20     09   15\n",
              "8     durga     up  varanashi       wipro  ...         4    20     09   15\n",
              "9      kali     AP    nellore       indus  ...         6    20     08   17\n",
              "10   kishan    Guj      surat         iws  ...         8    20     08   18\n",
              "11     shiv    Raj       kota         sky  ...         4    20     08   19\n",
              "12   shashi    KER    trissur      vedang  ...         9    20     08   20\n",
              "13   kishan     BR       gaya  rs spectra  ...        11    20     08   21\n",
              "\n",
              "[14 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hzfOGThLmFMd",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 328
        },
        "outputId": "2733f25e-0567-4dee-aa77-e0a091760ce9"
      },
      "source": [
        "df_replacing_rows_value1=df_replace[df_replace['Site Audits']<df_replace['Site I&C']]\n",
        "df_replacing_rows_value1"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>year</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1</td>\n",
              "      <td>2</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2</td>\n",
              "      <td>5</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3</td>\n",
              "      <td>3</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5</td>\n",
              "      <td>6</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      Name Circle    Cluster     Company  ... SQI Score  year  month  day\n",
              "0      Ram     TN        CBE         sky  ...         2    20     07   08\n",
              "1   ganesh     TN        MDU         sky  ...         5    20     07   09\n",
              "2   shashi    KER    calicut  rs spectra  ...         3    20     07   10\n",
              "3   kishan     BR       gaya       elexi  ...         4    20     08   11\n",
              "4     shiv     BR      patna       elexi  ...         6    20     08   12\n",
              "5  hanuman    che         sw       airon  ...         8    20     08   13\n",
              "6     sita    che         wt         tvs  ...         9    20     08   13\n",
              "7  lakshmi     up   aayodhya         ncr  ...         4    20     09   15\n",
              "8    durga     up  varanashi       wipro  ...         4    20     09   15\n",
              "\n",
              "[9 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "X7f1ec_Nzghq",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 295
        },
        "outputId": "6b1243ef-5f47-4e44-c148-93717235f5c6"
      },
      "source": [
        "df_replacing_rows_value2=df_replace[df_replace['Site Audits']>df_replace['Site I&C']]\n",
        "df_replacing_rows_value2[\"Site Audits\"]=df_replacing_rows_value1[\"Site Audits\"].mean()\n",
        "df_replacing_rows_value2"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame.\n",
            "Try using .loc[row_indexer,col_indexer] = value instead\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
            "  \n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>year</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>11</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      Name Circle  Cluster     Company  ... SQI Score  year  month  day\n",
              "10  kishan    Guj    surat         iws  ...         8    20     08   18\n",
              "11    shiv    Raj     kota         sky  ...         4    20     08   19\n",
              "12  shashi    KER  trissur      vedang  ...         9    20     08   20\n",
              "13  kishan     BR     gaya  rs spectra  ...        11    20     08   21\n",
              "\n",
              "[4 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Sz0PS27j1x2j",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 452
        },
        "outputId": "ddfb35c0-9907-45bd-e504-ed3816e81292"
      },
      "source": [
        "df_replacing_rows_value=pd.concat([df_replacing_rows_value1,df_replacing_rows_value2])\n",
        "df_replacing_rows_value"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>year</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>2</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>5</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>3</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>6</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>11</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       Name Circle    Cluster     Company  ... SQI Score  year  month  day\n",
              "0       Ram     TN        CBE         sky  ...         2    20     07   08\n",
              "1    ganesh     TN        MDU         sky  ...         5    20     07   09\n",
              "2    shashi    KER    calicut  rs spectra  ...         3    20     07   10\n",
              "3    kishan     BR       gaya       elexi  ...         4    20     08   11\n",
              "4      shiv     BR      patna       elexi  ...         6    20     08   12\n",
              "5   hanuman    che         sw       airon  ...         8    20     08   13\n",
              "6      sita    che         wt         tvs  ...         9    20     08   13\n",
              "7   lakshmi     up   aayodhya         ncr  ...         4    20     09   15\n",
              "8     durga     up  varanashi       wipro  ...         4    20     09   15\n",
              "10   kishan    Guj      surat         iws  ...         8    20     08   18\n",
              "11     shiv    Raj       kota         sky  ...         4    20     08   19\n",
              "12   shashi    KER    trissur      vedang  ...         9    20     08   20\n",
              "13   kishan     BR       gaya  rs spectra  ...        11    20     08   21\n",
              "\n",
              "[13 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ga6GuhvzkMw3",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 452
        },
        "outputId": "d856d74b-f367-4129-9aa4-b1c96fe22dfb"
      },
      "source": [
        "df_replacing_rows_value[\"audit%\"]=df_replacing_rows_value['Site Audits']/df_replacing_rows_value['Site I&C']*100\n",
        "df_replacing_rows_value#df_with_added_column using airthmaic operation"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>year</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "      <th>audit%</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>2</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>08</td>\n",
              "      <td>10.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>5</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>09</td>\n",
              "      <td>20.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>3</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>10</td>\n",
              "      <td>30.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>11</td>\n",
              "      <td>40.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>6</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>12</td>\n",
              "      <td>50.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "      <td>60.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "      <td>70.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "      <td>80.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "      <td>90.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>18</td>\n",
              "      <td>50.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>19</td>\n",
              "      <td>50.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>20</td>\n",
              "      <td>50.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>11</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>21</td>\n",
              "      <td>50.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       Name Circle    Cluster     Company  ... year  month  day  audit%\n",
              "0       Ram     TN        CBE         sky  ...   20     07   08    10.0\n",
              "1    ganesh     TN        MDU         sky  ...   20     07   09    20.0\n",
              "2    shashi    KER    calicut  rs spectra  ...   20     07   10    30.0\n",
              "3    kishan     BR       gaya       elexi  ...   20     08   11    40.0\n",
              "4      shiv     BR      patna       elexi  ...   20     08   12    50.0\n",
              "5   hanuman    che         sw       airon  ...   20     08   13    60.0\n",
              "6      sita    che         wt         tvs  ...   20     08   13    70.0\n",
              "7   lakshmi     up   aayodhya         ncr  ...   20     09   15    80.0\n",
              "8     durga     up  varanashi       wipro  ...   20     09   15    90.0\n",
              "10   kishan    Guj      surat         iws  ...   20     08   18    50.0\n",
              "11     shiv    Raj       kota         sky  ...   20     08   19    50.0\n",
              "12   shashi    KER    trissur      vedang  ...   20     08   20    50.0\n",
              "13   kishan     BR       gaya  rs spectra  ...   20     08   21    50.0\n",
              "\n",
              "[13 rows x 12 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z73JF1asQai5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#df_replacing_rows_value.insert(8,'audit%',df_replacing_rows_value['audit%'],allow_duplicates=True) "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nQSuSSmvULuL",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 452
        },
        "outputId": "8fe98551-5b30-4c42-8395-abe1a377e9cb"
      },
      "source": [
        "order = [0,1,2,3,4,5,6,11,7,8,9,10]\n",
        "df_final = df_replacing_rows_value[[df_replacing_rows_value.columns[i] for i in order]]\n",
        "df_final"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>audit%</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>year</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>5</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>30.0</td>\n",
              "      <td>3</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>40.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>6</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>60.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>70.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>80.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>90.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>11</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       Name Circle    Cluster     Company  ... SQI Score  year  month  day\n",
              "0       Ram     TN        CBE         sky  ...         2    20     07   08\n",
              "1    ganesh     TN        MDU         sky  ...         5    20     07   09\n",
              "2    shashi    KER    calicut  rs spectra  ...         3    20     07   10\n",
              "3    kishan     BR       gaya       elexi  ...         4    20     08   11\n",
              "4      shiv     BR      patna       elexi  ...         6    20     08   12\n",
              "5   hanuman    che         sw       airon  ...         8    20     08   13\n",
              "6      sita    che         wt         tvs  ...         9    20     08   13\n",
              "7   lakshmi     up   aayodhya         ncr  ...         4    20     09   15\n",
              "8     durga     up  varanashi       wipro  ...         4    20     09   15\n",
              "10   kishan    Guj      surat         iws  ...         8    20     08   18\n",
              "11     shiv    Raj       kota         sky  ...         4    20     08   19\n",
              "12   shashi    KER    trissur      vedang  ...         9    20     08   20\n",
              "13   kishan     BR       gaya  rs spectra  ...        11    20     08   21\n",
              "\n",
              "[13 rows x 12 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lVWEjhvPTjOq",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 452
        },
        "outputId": "a94165e9-bddc-4f1f-cf3c-be0c709f48ff"
      },
      "source": [
        "df55 = df_replacing_rows_value.iloc[:, [0,1,2,3,4,5,6,11,7,8,9]]\n",
        "df55"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Name</th>\n",
              "      <th>Circle</th>\n",
              "      <th>Cluster</th>\n",
              "      <th>Company</th>\n",
              "      <th>Date</th>\n",
              "      <th>Site I&amp;C</th>\n",
              "      <th>Site Audits</th>\n",
              "      <th>audit%</th>\n",
              "      <th>SQI Score</th>\n",
              "      <th>year</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ram</td>\n",
              "      <td>TN</td>\n",
              "      <td>CBE</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-08</td>\n",
              "      <td>10.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ganesh</td>\n",
              "      <td>TN</td>\n",
              "      <td>MDU</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-07-09</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>5</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>calicut</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-07-10</td>\n",
              "      <td>10.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>30.0</td>\n",
              "      <td>3</td>\n",
              "      <td>20</td>\n",
              "      <td>07</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-11</td>\n",
              "      <td>10.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>40.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>shiv</td>\n",
              "      <td>BR</td>\n",
              "      <td>patna</td>\n",
              "      <td>elexi</td>\n",
              "      <td>2020-08-12</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>6</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>hanuman</td>\n",
              "      <td>che</td>\n",
              "      <td>sw</td>\n",
              "      <td>airon</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>60.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>sita</td>\n",
              "      <td>che</td>\n",
              "      <td>wt</td>\n",
              "      <td>tvs</td>\n",
              "      <td>2020-08-13</td>\n",
              "      <td>10.0</td>\n",
              "      <td>7.0</td>\n",
              "      <td>70.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>lakshmi</td>\n",
              "      <td>up</td>\n",
              "      <td>aayodhya</td>\n",
              "      <td>ncr</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>80.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>durga</td>\n",
              "      <td>up</td>\n",
              "      <td>varanashi</td>\n",
              "      <td>wipro</td>\n",
              "      <td>2020-09-15</td>\n",
              "      <td>10.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>90.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>09</td>\n",
              "      <td>15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>kishan</td>\n",
              "      <td>Guj</td>\n",
              "      <td>surat</td>\n",
              "      <td>iws</td>\n",
              "      <td>2020-08-18</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>8</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>shiv</td>\n",
              "      <td>Raj</td>\n",
              "      <td>kota</td>\n",
              "      <td>sky</td>\n",
              "      <td>2020-08-19</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>shashi</td>\n",
              "      <td>KER</td>\n",
              "      <td>trissur</td>\n",
              "      <td>vedang</td>\n",
              "      <td>2020-08-20</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>9</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>kishan</td>\n",
              "      <td>BR</td>\n",
              "      <td>gaya</td>\n",
              "      <td>rs spectra</td>\n",
              "      <td>2020-08-21</td>\n",
              "      <td>10.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>50.0</td>\n",
              "      <td>11</td>\n",
              "      <td>20</td>\n",
              "      <td>08</td>\n",
              "      <td>21</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       Name Circle    Cluster     Company  ... SQI Score  year  month  day\n",
              "0       Ram     TN        CBE         sky  ...         2    20     07   08\n",
              "1    ganesh     TN        MDU         sky  ...         5    20     07   09\n",
              "2    shashi    KER    calicut  rs spectra  ...         3    20     07   10\n",
              "3    kishan     BR       gaya       elexi  ...         4    20     08   11\n",
              "4      shiv     BR      patna       elexi  ...         6    20     08   12\n",
              "5   hanuman    che         sw       airon  ...         8    20     08   13\n",
              "6      sita    che         wt         tvs  ...         9    20     08   13\n",
              "7   lakshmi     up   aayodhya         ncr  ...         4    20     09   15\n",
              "8     durga     up  varanashi       wipro  ...         4    20     09   15\n",
              "10   kishan    Guj      surat         iws  ...         8    20     08   18\n",
              "11     shiv    Raj       kota         sky  ...         4    20     08   19\n",
              "12   shashi    KER    trissur      vedang  ...         9    20     08   20\n",
              "13   kishan     BR       gaya  rs spectra  ...        11    20     08   21\n",
              "\n",
              "[13 rows x 12 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SUZETh5adcJE",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df55.to_excel('df.xls', 'r',encoding='utf-8', index=False)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n8e82iTqSzeg",
        "colab_type": "text"
      },
      "source": [
        "#Step4: Data Processing> Analytics> Modeling"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_jwv7sYBS_gd",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-tuP1bva0A8Y",
        "colab_type": "text"
      },
      "source": [
        "# New Section"
      ]
    }
  ]
}