Data Transformation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing basic packages\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ComputerId</th>\n",
       "      <th>ItemName</th>\n",
       "      <th>Comment</th>\n",
       "      <th>Unit</th>\n",
       "      <th>C/S/ID</th>\n",
       "      <th>DataType</th>\n",
       "      <th>BitPos</th>\n",
       "      <th>Address</th>\n",
       "      <th>MAX</th>\n",
       "      <th>MIN</th>\n",
       "      <th>ScaleMax</th>\n",
       "      <th>ScaleMin</th>\n",
       "      <th>Decimal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35827</td>\n",
       "      <td>10</td>\n",
       "      <td>L_R1SDW_SPDREF</td>\n",
       "      <td>R1 Screw Down WS Speed Reference (mps)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>REAL : Float</td>\n",
       "      <td>0</td>\n",
       "      <td>0x16464</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35828</td>\n",
       "      <td>10</td>\n",
       "      <td>L_R1SDD_SPDREF</td>\n",
       "      <td>R1 Screw Down DS Speed Reference (mps)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>REAL : Float</td>\n",
       "      <td>0</td>\n",
       "      <td>0x16468</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35829</td>\n",
       "      <td>10</td>\n",
       "      <td>L_R1SDL_SPDREF</td>\n",
       "      <td>R1 Screw Down LS Speed Reference (mps)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>REAL : Float</td>\n",
       "      <td>0</td>\n",
       "      <td>0x1646c</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35830</td>\n",
       "      <td>10</td>\n",
       "      <td>L_R1SDW_SPDFBK</td>\n",
       "      <td>R1 Screw Down WS Speed Feedback (mps)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>REAL : Float</td>\n",
       "      <td>0</td>\n",
       "      <td>0x16470</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35831</td>\n",
       "      <td>10</td>\n",
       "      <td>L_R1SDD_SPDFBK</td>\n",
       "      <td>R1 Screw Down DS Speed Feedback (mps)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>REAL : Float</td>\n",
       "      <td>0</td>\n",
       "      <td>0x16474</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>-10000.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ItemId  ComputerId        ItemName                                 Comment  \\\n",
       "0   35827          10  L_R1SDW_SPDREF  R1 Screw Down WS Speed Reference (mps)   \n",
       "1   35828          10  L_R1SDD_SPDREF  R1 Screw Down DS Speed Reference (mps)   \n",
       "2   35829          10  L_R1SDL_SPDREF  R1 Screw Down LS Speed Reference (mps)   \n",
       "3   35830          10  L_R1SDW_SPDFBK   R1 Screw Down WS Speed Feedback (mps)   \n",
       "4   35831          10  L_R1SDD_SPDFBK   R1 Screw Down DS Speed Feedback (mps)   \n",
       "\n",
       "   Unit  C/S/ID      DataType  BitPos  Address      MAX      MIN  ScaleMax  \\\n",
       "0   NaN       0  REAL : Float       0  0x16464  10000.0 -10000.0   10000.0   \n",
       "1   NaN       0  REAL : Float       0  0x16468  10000.0 -10000.0   10000.0   \n",
       "2   NaN       0  REAL : Float       0  0x1646c  10000.0 -10000.0   10000.0   \n",
       "3   NaN       0  REAL : Float       0  0x16470  10000.0 -10000.0   10000.0   \n",
       "4   NaN       0  REAL : Float       0  0x16474  10000.0 -10000.0   10000.0   \n",
       "\n",
       "   ScaleMin  Decimal  \n",
       "0  -10000.0      0.0  \n",
       "1  -10000.0      0.0  \n",
       "2  -10000.0      0.0  \n",
       "3  -10000.0      0.0  \n",
       "4  -10000.0      0.0  "
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Loading the data\n",
    "df1 = pd.read_csv('Input1.csv')\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 19 entries, 0 to 18\n",
      "Data columns (total 14 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   ItemId      19 non-null     int64  \n",
      " 1   ComputerId  19 non-null     int64  \n",
      " 2   ItemName    19 non-null     object \n",
      " 3   Comment     19 non-null     object \n",
      " 4   Unit        0 non-null      float64\n",
      " 5   C/S/ID      19 non-null     int64  \n",
      " 6   DataType    19 non-null     object \n",
      " 7   BitPos      19 non-null     int64  \n",
      " 8   Address     19 non-null     object \n",
      " 9   MAX         18 non-null     float64\n",
      " 10  MIN         18 non-null     float64\n",
      " 11  ScaleMax    18 non-null     float64\n",
      " 12  ScaleMin    18 non-null     float64\n",
      " 13  Decimal     18 non-null     float64\n",
      "dtypes: float64(6), int64(4), object(4)\n",
      "memory usage: 2.2+ KB\n"
     ]
    }
   ],
   "source": [
    "# Getting information about data\n",
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Time</th>\n",
       "      <th>Milli_Sec</th>\n",
       "      <th>L_R1SDW_SPDREF</th>\n",
       "      <th>L_R1SDD_SPDREF</th>\n",
       "      <th>L_R1SDL_SPDREF</th>\n",
       "      <th>L_R1SDW_SPDFBK</th>\n",
       "      <th>L_R1SDD_SPDFBK</th>\n",
       "      <th>L_R1SDL_SPDFBK</th>\n",
       "      <th>L_R1_ODG_AUX_B07</th>\n",
       "      <th>...</th>\n",
       "      <th>L_R1SD_DS_GAP</th>\n",
       "      <th>L_R1SD_WS_SPDREF</th>\n",
       "      <th>L_R1SD_DS_SPDREF</th>\n",
       "      <th>L_R1SDLS_SPDREF</th>\n",
       "      <th>L_R1SD_WS_SPDFBK</th>\n",
       "      <th>L_R1SD_DS_SPDFBK</th>\n",
       "      <th>L_R1SDLS_SPDFBK</th>\n",
       "      <th>L_R1SD_WS_CUR</th>\n",
       "      <th>L_R1SD_DS_CUR</th>\n",
       "      <th>L_R1_CNT_PASS0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>140</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>145</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>150</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>155</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>160</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date      Time  Milli_Sec  L_R1SDW_SPDREF  L_R1SDD_SPDREF  \\\n",
       "0  06-05-2022  21:58:33        140             0.0             0.0   \n",
       "1  06-05-2022  21:58:33        145             0.0             0.0   \n",
       "2  06-05-2022  21:58:33        150             0.0             0.0   \n",
       "3  06-05-2022  21:58:33        155             0.0             0.0   \n",
       "4  06-05-2022  21:58:33        160             0.0             0.0   \n",
       "\n",
       "   L_R1SDL_SPDREF  L_R1SDW_SPDFBK  L_R1SDD_SPDFBK  L_R1SDL_SPDFBK  \\\n",
       "0               0             0.0             0.0               0   \n",
       "1               0             0.0             0.0               0   \n",
       "2               0             0.0             0.0               0   \n",
       "3               0             0.0             0.0               0   \n",
       "4               0             0.0             0.0               0   \n",
       "\n",
       "   L_R1_ODG_AUX_B07  ...  L_R1SD_DS_GAP  L_R1SD_WS_SPDREF  L_R1SD_DS_SPDREF  \\\n",
       "0                 0  ...     194.347992               0.0               0.0   \n",
       "1                 0  ...     194.347992               0.0               0.0   \n",
       "2                 0  ...     194.347992               0.0               0.0   \n",
       "3                 0  ...     194.347992               0.0               0.0   \n",
       "4                 0  ...     194.347992               0.0               0.0   \n",
       "\n",
       "   L_R1SDLS_SPDREF  L_R1SD_WS_SPDFBK  L_R1SD_DS_SPDFBK  L_R1SDLS_SPDFBK  \\\n",
       "0                0               0.0               0.0                0   \n",
       "1                0               0.0               0.0                0   \n",
       "2                0               0.0               0.0                0   \n",
       "3                0               0.0               0.0                0   \n",
       "4                0               0.0               0.0                0   \n",
       "\n",
       "   L_R1SD_WS_CUR  L_R1SD_DS_CUR  L_R1_CNT_PASS0  \n",
       "0              0              0               1  \n",
       "1              0              0               1  \n",
       "2              0              0               1  \n",
       "3              0              0               1  \n",
       "4              0              0               1  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Loading the data\n",
    "df2 = pd.read_csv('Input2.csv')\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 12751 entries, 0 to 12750\n",
      "Data columns (total 22 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   Date               12751 non-null  object \n",
      " 1   Time               12751 non-null  object \n",
      " 2   Milli_Sec          12751 non-null  int64  \n",
      " 3   L_R1SDW_SPDREF     12751 non-null  float64\n",
      " 4   L_R1SDD_SPDREF     12751 non-null  float64\n",
      " 5   L_R1SDL_SPDREF     12751 non-null  int64  \n",
      " 6   L_R1SDW_SPDFBK     12751 non-null  float64\n",
      " 7   L_R1SDD_SPDFBK     12751 non-null  float64\n",
      " 8   L_R1SDL_SPDFBK     12751 non-null  int64  \n",
      " 9   L_R1_ODG_AUX_B07   12751 non-null  int64  \n",
      " 10  RM1_RUNDOWN_ID_HS  12751 non-null  int64  \n",
      " 11  L_R1SD_WS_GAP      12751 non-null  float64\n",
      " 12  L_R1SD_DS_GAP      12751 non-null  float64\n",
      " 13  L_R1SD_WS_SPDREF   12751 non-null  float64\n",
      " 14  L_R1SD_DS_SPDREF   12751 non-null  float64\n",
      " 15  L_R1SDLS_SPDREF    12751 non-null  int64  \n",
      " 16  L_R1SD_WS_SPDFBK   12751 non-null  float64\n",
      " 17  L_R1SD_DS_SPDFBK   12751 non-null  float64\n",
      " 18  L_R1SDLS_SPDFBK    12751 non-null  int64  \n",
      " 19  L_R1SD_WS_CUR      12751 non-null  int64  \n",
      " 20  L_R1SD_DS_CUR      12751 non-null  int64  \n",
      " 21  L_R1_CNT_PASS0     12751 non-null  int64  \n",
      "dtypes: float64(10), int64(10), object(2)\n",
      "memory usage: 2.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# Getting information about data\n",
    "df2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ItemName</th>\n",
       "      <th>Comment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35827</td>\n",
       "      <td>L_R1SDW_SPDREF</td>\n",
       "      <td>R1 Screw Down WS Speed Reference (mps)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35828</td>\n",
       "      <td>L_R1SDD_SPDREF</td>\n",
       "      <td>R1 Screw Down DS Speed Reference (mps)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35829</td>\n",
       "      <td>L_R1SDL_SPDREF</td>\n",
       "      <td>R1 Screw Down LS Speed Reference (mps)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35830</td>\n",
       "      <td>L_R1SDW_SPDFBK</td>\n",
       "      <td>R1 Screw Down WS Speed Feedback (mps)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35831</td>\n",
       "      <td>L_R1SDD_SPDFBK</td>\n",
       "      <td>R1 Screw Down DS Speed Feedback (mps)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ItemId        ItemName                                 Comment\n",
       "0   35827  L_R1SDW_SPDREF  R1 Screw Down WS Speed Reference (mps)\n",
       "1   35828  L_R1SDD_SPDREF  R1 Screw Down DS Speed Reference (mps)\n",
       "2   35829  L_R1SDL_SPDREF  R1 Screw Down LS Speed Reference (mps)\n",
       "3   35830  L_R1SDW_SPDFBK   R1 Screw Down WS Speed Feedback (mps)\n",
       "4   35831  L_R1SDD_SPDFBK   R1 Screw Down DS Speed Feedback (mps)"
      ]
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df1 = df1.iloc[: , [0, 2,3]].copy()\n",
    "new_df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Time</th>\n",
       "      <th>L_R1SDW_SPDREF</th>\n",
       "      <th>L_R1SDD_SPDREF</th>\n",
       "      <th>L_R1SDL_SPDREF</th>\n",
       "      <th>L_R1SDW_SPDFBK</th>\n",
       "      <th>L_R1SDD_SPDFBK</th>\n",
       "      <th>L_R1SDL_SPDFBK</th>\n",
       "      <th>L_R1_ODG_AUX_B07</th>\n",
       "      <th>RM1_RUNDOWN_ID_HS</th>\n",
       "      <th>...</th>\n",
       "      <th>L_R1SD_DS_GAP</th>\n",
       "      <th>L_R1SD_WS_SPDREF</th>\n",
       "      <th>L_R1SD_DS_SPDREF</th>\n",
       "      <th>L_R1SDLS_SPDREF</th>\n",
       "      <th>L_R1SD_WS_SPDFBK</th>\n",
       "      <th>L_R1SD_DS_SPDFBK</th>\n",
       "      <th>L_R1SDLS_SPDFBK</th>\n",
       "      <th>L_R1SD_WS_CUR</th>\n",
       "      <th>L_R1SD_DS_CUR</th>\n",
       "      <th>L_R1_CNT_PASS0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06-05-2022</td>\n",
       "      <td>21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>...</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date      Time  L_R1SDW_SPDREF  L_R1SDD_SPDREF  L_R1SDL_SPDREF  \\\n",
       "0  06-05-2022  21:58:33             0.0             0.0               0   \n",
       "1  06-05-2022  21:58:33             0.0             0.0               0   \n",
       "2  06-05-2022  21:58:33             0.0             0.0               0   \n",
       "3  06-05-2022  21:58:33             0.0             0.0               0   \n",
       "4  06-05-2022  21:58:33             0.0             0.0               0   \n",
       "\n",
       "   L_R1SDW_SPDFBK  L_R1SDD_SPDFBK  L_R1SDL_SPDFBK  L_R1_ODG_AUX_B07  \\\n",
       "0             0.0             0.0               0                 0   \n",
       "1             0.0             0.0               0                 0   \n",
       "2             0.0             0.0               0                 0   \n",
       "3             0.0             0.0               0                 0   \n",
       "4             0.0             0.0               0                 0   \n",
       "\n",
       "   RM1_RUNDOWN_ID_HS  ...  L_R1SD_DS_GAP  L_R1SD_WS_SPDREF  L_R1SD_DS_SPDREF  \\\n",
       "0          225726050  ...     194.347992               0.0               0.0   \n",
       "1          225726050  ...     194.347992               0.0               0.0   \n",
       "2          225726050  ...     194.347992               0.0               0.0   \n",
       "3          225726050  ...     194.347992               0.0               0.0   \n",
       "4          225726050  ...     194.347992               0.0               0.0   \n",
       "\n",
       "   L_R1SDLS_SPDREF  L_R1SD_WS_SPDFBK  L_R1SD_DS_SPDFBK  L_R1SDLS_SPDFBK  \\\n",
       "0                0               0.0               0.0                0   \n",
       "1                0               0.0               0.0                0   \n",
       "2                0               0.0               0.0                0   \n",
       "3                0               0.0               0.0                0   \n",
       "4                0               0.0               0.0                0   \n",
       "\n",
       "   L_R1SD_WS_CUR  L_R1SD_DS_CUR  L_R1_CNT_PASS0  \n",
       "0              0              0               1  \n",
       "1              0              0               1  \n",
       "2              0              0               1  \n",
       "3              0              0               1  \n",
       "4              0              0               1  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df2 = df2.drop([\"Milli_Sec\"], axis = 1)\n",
    "new_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\SAUHAR~1\\AppData\\Local\\Temp/ipykernel_24536/1105144319.py:2: DeprecationWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.\n",
      "  comment=pd.Series(index=new_df1['Comment'])\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Comment\n",
       "R1 Screw Down WS Speed Reference (mps)   NaN\n",
       "R1 Screw Down DS Speed Reference (mps)   NaN\n",
       "R1 Screw Down LS Speed Reference (mps)   NaN\n",
       "R1 Screw Down WS Speed Feedback (mps)    NaN\n",
       "R1 Screw Down DS Speed Feedback (mps)    NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#defining the function to remove punctuation\n",
    "comment=pd.Series(index=new_df1['Comment'])\n",
    "comment.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df1['Unit']=comment.index.str.extract(r'\\((\\w+)\\)', expand=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ItemName</th>\n",
       "      <th>Comment</th>\n",
       "      <th>Unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35827</td>\n",
       "      <td>L_R1SDW_SPDREF</td>\n",
       "      <td>R1 Screw Down WS Speed Reference (mps)</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35828</td>\n",
       "      <td>L_R1SDD_SPDREF</td>\n",
       "      <td>R1 Screw Down DS Speed Reference (mps)</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>35829</td>\n",
       "      <td>L_R1SDL_SPDREF</td>\n",
       "      <td>R1 Screw Down LS Speed Reference (mps)</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35830</td>\n",
       "      <td>L_R1SDW_SPDFBK</td>\n",
       "      <td>R1 Screw Down WS Speed Feedback (mps)</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>35831</td>\n",
       "      <td>L_R1SDD_SPDFBK</td>\n",
       "      <td>R1 Screw Down DS Speed Feedback (mps)</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ItemId        ItemName                                 Comment Unit\n",
       "0   35827  L_R1SDW_SPDREF  R1 Screw Down WS Speed Reference (mps)  mps\n",
       "1   35828  L_R1SDD_SPDREF  R1 Screw Down DS Speed Reference (mps)  mps\n",
       "2   35829  L_R1SDL_SPDREF  R1 Screw Down LS Speed Reference (mps)  mps\n",
       "3   35830  L_R1SDW_SPDFBK   R1 Screw Down WS Speed Feedback (mps)  mps\n",
       "4   35831  L_R1SDD_SPDFBK   R1 Screw Down DS Speed Feedback (mps)  mps"
      ]
     },
     "execution_count": 257,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(19, 4)"
      ]
     },
     "execution_count": 258,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df2.insert(2, \"Time_Stamp\", new_df2['Date'].astype(str) +\" \"+ new_df2[\"Time\"], True)\n",
    "new_df2=new_df2.drop(columns=['Date','Time'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time_Stamp</th>\n",
       "      <th>L_R1SDW_SPDREF</th>\n",
       "      <th>L_R1SDD_SPDREF</th>\n",
       "      <th>L_R1SDL_SPDREF</th>\n",
       "      <th>L_R1SDW_SPDFBK</th>\n",
       "      <th>L_R1SDD_SPDFBK</th>\n",
       "      <th>L_R1SDL_SPDFBK</th>\n",
       "      <th>L_R1_ODG_AUX_B07</th>\n",
       "      <th>RM1_RUNDOWN_ID_HS</th>\n",
       "      <th>L_R1SD_WS_GAP</th>\n",
       "      <th>L_R1SD_DS_GAP</th>\n",
       "      <th>L_R1SD_WS_SPDREF</th>\n",
       "      <th>L_R1SD_DS_SPDREF</th>\n",
       "      <th>L_R1SDLS_SPDREF</th>\n",
       "      <th>L_R1SD_WS_SPDFBK</th>\n",
       "      <th>L_R1SD_DS_SPDFBK</th>\n",
       "      <th>L_R1SDLS_SPDFBK</th>\n",
       "      <th>L_R1SD_WS_CUR</th>\n",
       "      <th>L_R1SD_DS_CUR</th>\n",
       "      <th>L_R1_CNT_PASS0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06-05-2022 21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.430008</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06-05-2022 21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.430008</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06-05-2022 21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.430008</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06-05-2022 21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.430008</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06-05-2022 21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.430008</td>\n",
       "      <td>194.347992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Time_Stamp  L_R1SDW_SPDREF  L_R1SDD_SPDREF  L_R1SDL_SPDREF  \\\n",
       "0  06-05-2022 21:58:33             0.0             0.0               0   \n",
       "1  06-05-2022 21:58:33             0.0             0.0               0   \n",
       "2  06-05-2022 21:58:33             0.0             0.0               0   \n",
       "3  06-05-2022 21:58:33             0.0             0.0               0   \n",
       "4  06-05-2022 21:58:33             0.0             0.0               0   \n",
       "\n",
       "   L_R1SDW_SPDFBK  L_R1SDD_SPDFBK  L_R1SDL_SPDFBK  L_R1_ODG_AUX_B07  \\\n",
       "0             0.0             0.0               0                 0   \n",
       "1             0.0             0.0               0                 0   \n",
       "2             0.0             0.0               0                 0   \n",
       "3             0.0             0.0               0                 0   \n",
       "4             0.0             0.0               0                 0   \n",
       "\n",
       "   RM1_RUNDOWN_ID_HS  L_R1SD_WS_GAP  L_R1SD_DS_GAP  L_R1SD_WS_SPDREF  \\\n",
       "0          225726050     194.430008     194.347992               0.0   \n",
       "1          225726050     194.430008     194.347992               0.0   \n",
       "2          225726050     194.430008     194.347992               0.0   \n",
       "3          225726050     194.430008     194.347992               0.0   \n",
       "4          225726050     194.430008     194.347992               0.0   \n",
       "\n",
       "   L_R1SD_DS_SPDREF  L_R1SDLS_SPDREF  L_R1SD_WS_SPDFBK  L_R1SD_DS_SPDFBK  \\\n",
       "0               0.0                0               0.0               0.0   \n",
       "1               0.0                0               0.0               0.0   \n",
       "2               0.0                0               0.0               0.0   \n",
       "3               0.0                0               0.0               0.0   \n",
       "4               0.0                0               0.0               0.0   \n",
       "\n",
       "   L_R1SDLS_SPDFBK  L_R1SD_WS_CUR  L_R1SD_DS_CUR  L_R1_CNT_PASS0  \n",
       "0                0              0              0               1  \n",
       "1                0              0              0               1  \n",
       "2                0              0              0               1  \n",
       "3                0              0              0               1  \n",
       "4                0              0              0               1  "
      ]
     },
     "execution_count": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(12751, 20)"
      ]
     },
     "execution_count": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time_Stamp</th>\n",
       "      <th>L_R1SDW_SPDREF</th>\n",
       "      <th>L_R1SDD_SPDREF</th>\n",
       "      <th>L_R1SDL_SPDREF</th>\n",
       "      <th>L_R1SDW_SPDFBK</th>\n",
       "      <th>L_R1SDD_SPDFBK</th>\n",
       "      <th>L_R1SDL_SPDFBK</th>\n",
       "      <th>L_R1_ODG_AUX_B07</th>\n",
       "      <th>RM1_RUNDOWN_ID_HS</th>\n",
       "      <th>L_R1SD_WS_GAP</th>\n",
       "      <th>L_R1SD_DS_GAP</th>\n",
       "      <th>L_R1SD_WS_SPDREF</th>\n",
       "      <th>L_R1SD_DS_SPDREF</th>\n",
       "      <th>L_R1SDLS_SPDREF</th>\n",
       "      <th>L_R1SD_WS_SPDFBK</th>\n",
       "      <th>L_R1SD_DS_SPDFBK</th>\n",
       "      <th>L_R1SDLS_SPDFBK</th>\n",
       "      <th>L_R1SD_WS_CUR</th>\n",
       "      <th>L_R1SD_DS_CUR</th>\n",
       "      <th>L_R1_CNT_PASS0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06-05-2022 21:58:33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.434006</td>\n",
       "      <td>194.356003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06-05-2022 21:58:34</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.432007</td>\n",
       "      <td>194.358002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06-05-2022 21:58:35</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.668015</td>\n",
       "      <td>194.593994</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06-05-2022 21:58:36</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.672012</td>\n",
       "      <td>194.599991</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06-05-2022 21:58:37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.670013</td>\n",
       "      <td>194.601990</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>06-05-2022 21:58:38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.674011</td>\n",
       "      <td>194.608002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>06-05-2022 21:58:39</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.680008</td>\n",
       "      <td>194.619995</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>06-05-2022 21:58:40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.678009</td>\n",
       "      <td>194.612000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>06-05-2022 21:58:41</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.676010</td>\n",
       "      <td>194.608002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>06-05-2022 21:58:42</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>225726050</td>\n",
       "      <td>194.668015</td>\n",
       "      <td>194.601990</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Time_Stamp  L_R1SDW_SPDREF  L_R1SDD_SPDREF  L_R1SDL_SPDREF  \\\n",
       "0  06-05-2022 21:58:33             0.0             0.0               0   \n",
       "1  06-05-2022 21:58:34             0.0             0.0               0   \n",
       "2  06-05-2022 21:58:35             0.0             0.0               0   \n",
       "3  06-05-2022 21:58:36             0.0             0.0               0   \n",
       "4  06-05-2022 21:58:37             0.0             0.0               0   \n",
       "5  06-05-2022 21:58:38             0.0             0.0               0   \n",
       "6  06-05-2022 21:58:39             0.0             0.0               0   \n",
       "7  06-05-2022 21:58:40             0.0             0.0               0   \n",
       "8  06-05-2022 21:58:41             0.0             0.0               0   \n",
       "9  06-05-2022 21:58:42             0.0             0.0               0   \n",
       "\n",
       "   L_R1SDW_SPDFBK  L_R1SDD_SPDFBK  L_R1SDL_SPDFBK  L_R1_ODG_AUX_B07  \\\n",
       "0             0.0             0.0               0                 0   \n",
       "1             0.0             0.0               0                 0   \n",
       "2             0.0             0.0               0                 1   \n",
       "3             0.0             0.0               0                 1   \n",
       "4             0.0             0.0               0                 1   \n",
       "5             0.0             0.0               0                 1   \n",
       "6             0.0             0.0               0                 1   \n",
       "7             0.0             0.0               0                 1   \n",
       "8             0.0             0.0               0                 1   \n",
       "9             0.0             0.0               0                 1   \n",
       "\n",
       "   RM1_RUNDOWN_ID_HS  L_R1SD_WS_GAP  L_R1SD_DS_GAP  L_R1SD_WS_SPDREF  \\\n",
       "0          225726050     194.434006     194.356003               0.0   \n",
       "1          225726050     194.432007     194.358002               0.0   \n",
       "2          225726050     194.668015     194.593994               0.0   \n",
       "3          225726050     194.672012     194.599991               0.0   \n",
       "4          225726050     194.670013     194.601990               0.0   \n",
       "5          225726050     194.674011     194.608002               0.0   \n",
       "6          225726050     194.680008     194.619995               0.0   \n",
       "7          225726050     194.678009     194.612000               0.0   \n",
       "8          225726050     194.676010     194.608002               0.0   \n",
       "9          225726050     194.668015     194.601990               0.0   \n",
       "\n",
       "   L_R1SD_DS_SPDREF  L_R1SDLS_SPDREF  L_R1SD_WS_SPDFBK  L_R1SD_DS_SPDFBK  \\\n",
       "0               0.0                0               0.0               0.0   \n",
       "1               0.0                0               0.0               0.0   \n",
       "2               0.0                0               0.0               0.0   \n",
       "3               0.0                0               0.0               0.0   \n",
       "4               0.0                0               0.0               0.0   \n",
       "5               0.0                0               0.0               0.0   \n",
       "6               0.0                0               0.0               0.0   \n",
       "7               0.0                0               0.0               0.0   \n",
       "8               0.0                0               0.0               0.0   \n",
       "9               0.0                0               0.0               0.0   \n",
       "\n",
       "   L_R1SDLS_SPDFBK  L_R1SD_WS_CUR  L_R1SD_DS_CUR  L_R1_CNT_PASS0  \n",
       "0                0              0              0               1  \n",
       "1                0              0              0               1  \n",
       "2                0              0              0               1  \n",
       "3                0              0              0               0  \n",
       "4                0              0              0               0  \n",
       "5                0              0              0               0  \n",
       "6                0              0              0               0  \n",
       "7                0              0              0               0  \n",
       "8                0              0              0               0  \n",
       "9                0              0              0               0  "
      ]
     },
     "execution_count": 262,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df2 =new_df2.groupby('Time_Stamp', as_index=False).max()\n",
    "new_df2.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>event_timestamp</th>\n",
       "      <th>tag__id</th>\n",
       "      <th>tag__name</th>\n",
       "      <th>tag__desc</th>\n",
       "      <th>tag__value</th>\n",
       "      <th>tag__unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>06-05-2022 21:58:33</td>\n",
       "      <td>35827</td>\n",
       "      <td>L_R1SDW_SPDREF</td>\n",
       "      <td>R1 Screw Down WS Speed Reference (mps)</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>06-05-2022 21:58:43</td>\n",
       "      <td>35828</td>\n",
       "      <td>L_R1SDD_SPDREF</td>\n",
       "      <td>R1 Screw Down DS Speed Reference (mps)</td>\n",
       "      <td>29.866667</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06-05-2022 21:58:53</td>\n",
       "      <td>35829</td>\n",
       "      <td>L_R1SDL_SPDREF</td>\n",
       "      <td>R1 Screw Down LS Speed Reference (mps)</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06-05-2022 21:59:03</td>\n",
       "      <td>35830</td>\n",
       "      <td>L_R1SDW_SPDFBK</td>\n",
       "      <td>R1 Screw Down WS Speed Feedback (mps)</td>\n",
       "      <td>10.550400</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06-05-2022 21:59:13</td>\n",
       "      <td>35831</td>\n",
       "      <td>L_R1SDD_SPDFBK</td>\n",
       "      <td>R1 Screw Down DS Speed Feedback (mps)</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>06-05-2022 21:59:23</td>\n",
       "      <td>35832</td>\n",
       "      <td>L_R1SDL_SPDFBK</td>\n",
       "      <td>R1 Screw Down LS Speed Feedback (mps)</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>mps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>06-05-2022 21:59:33</td>\n",
       "      <td>35974</td>\n",
       "      <td>L_R1_ODG_AUX_B07</td>\n",
       "      <td>R1 ODG AUX output B07</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       event_timestamp  tag__id         tag__name  \\\n",
       "0  06-05-2022 21:58:33    35827    L_R1SDW_SPDREF   \n",
       "1  06-05-2022 21:58:43    35828    L_R1SDD_SPDREF   \n",
       "2  06-05-2022 21:58:53    35829    L_R1SDL_SPDREF   \n",
       "3  06-05-2022 21:59:03    35830    L_R1SDW_SPDFBK   \n",
       "4  06-05-2022 21:59:13    35831    L_R1SDD_SPDFBK   \n",
       "5  06-05-2022 21:59:23    35832    L_R1SDL_SPDFBK   \n",
       "6  06-05-2022 21:59:33    35974  L_R1_ODG_AUX_B07   \n",
       "\n",
       "                                tag__desc  tag__value tag__unit  \n",
       "0  R1 Screw Down WS Speed Reference (mps)    0.000000       mps  \n",
       "1  R1 Screw Down DS Speed Reference (mps)   29.866667       mps  \n",
       "2  R1 Screw Down LS Speed Reference (mps)    0.000000       mps  \n",
       "3   R1 Screw Down WS Speed Feedback (mps)   10.550400       mps  \n",
       "4   R1 Screw Down DS Speed Feedback (mps)    0.000000       mps  \n",
       "5   R1 Screw Down LS Speed Feedback (mps)    0.000000       mps  \n",
       "6                   R1 ODG AUX output B07    1.000000       NaN  "
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Output = pd.DataFrame()\n",
    "d = dict()\n",
    "\n",
    "d['event_timestamp'] = []\n",
    "d[\"tag__id\"] = []\n",
    "d[\"tag__name\"] = []\n",
    "d[\"tag__desc\"] = []\n",
    "d[\"tag__value\"] = []\n",
    "d[\"tag__unit\"] = []\n",
    "\n",
    "a2 = []\n",
    "for k in d:\n",
    "    a2.append(k)\n",
    "\n",
    "alias = [\"ItemId\", 'ItemId', 'ItemName', 'Comment', 'ItemId','Unit']\n",
    "for i in range(7):\n",
    "    for j in range(6):\n",
    "        d[a2[j]].append(new_df1[alias[j]][i])\n",
    "    d[a2[0]][i] = new_df2[\"Time_Stamp\"][i*10]\n",
    "    d[a2[4]][i] = -212340981234091283470112342\n",
    "    for j in range(i*10, min(i*10+10, 64)):\n",
    "        d['tag__value'][i] = max(new_df2[new_df1[alias[2]][i]][j], d['tag__value'][i])\n",
    "\n",
    "Output = pd.DataFrame(d)\n",
    "Output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {},
   "outputs": [],
   "source": [
    "Output.to_csv('Output.csv', index=False, na_rep='')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.9 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.9"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}