Create training and test data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d2bf5f4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Here are the code used to create training and test data for the experiments. \n",
    "#Training and test data should be created or recreated before running any of the experiments.\n",
    "#Script assumes that the database name is IJCAI2022 and that the source table is called BayesianDynamicChecklistLocalDb\n",
    "#Please also make sure that the table is assigned to the schema called dbo\n",
    "\n",
    "import pyodbc\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "205a251b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def deleteTrainTestTables():\n",
    "    cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=localhost;DATABASE=IJCAI2022;Trusted_Connection=yes;')\n",
    "    cursor = cnxn.cursor()\n",
    "    cursor.execute(\"Drop Table BayesianDynamicChecklistLocalDbRandomized\"\n",
    "                   )\n",
    "    cnxn.commit()\n",
    "    for ik in range(0,8):\n",
    "        cursor.execute(\"Drop Table BayesianDynamicChecklistLocalDbRandomizedTest\"+str(ik)\n",
    "                   )\n",
    "        cnxn.commit()\n",
    "        cursor.execute(\"Drop Table BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)\n",
    "                   )\n",
    "        cnxn.commit()\n",
    "def createTrainTestTables():\n",
    "    cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=localhost;DATABASE=IJCAI2022;Trusted_Connection=yes;')\n",
    "    cursor = cnxn.cursor()\n",
    "    cursor.execute(\"Select [RowId],[MuncipalityIndex],[IndustrySubgroupCodeIndex],[InspectionId],[InspectionDateId],[ControlPointText],[IndustryMainAreaCode],[IndustryGroupCode],[IndustryAreaCode],[IndustryCode],[IndustrySubgroupCode],[Fylke],[Municipality],[MunicipalityNumber],[NonCompliance],new into BayesianDynamicChecklistLocalDbRandomized FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDb]\"+\"  \"\n",
    "                      +\"left join (select NewID() as new, InspectionId as inspID from [IJCAI2022].[dbo].BayesianDynamicChecklistLocalDb\"+\"  group by InspectionId) as a on a.inspId=InspectionId\"+\n",
    "                      \" where InspectionDateId<20190601 ORDER BY new\"\n",
    "                   )\n",
    "\n",
    "        \n",
    "    #commit the transaction\n",
    "    \n",
    "    cnxn.commit()\n",
    "    length=len(pd.read_sql(\"SELECT  * \"+\"FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomized\"+\"]\"+\" \"+\n",
    "                      \" where InspectionDateId<20190601\",cnxn))\n",
    "    for ik in range(0,8):\n",
    "\n",
    "        cursor.execute(\"Select [RowId],[MuncipalityIndex],[IndustrySubgroupCodeIndex],[InspectionId],[InspectionDateId],[ControlPointText],[IndustryMainAreaCode],[IndustryGroupCode],[IndustryAreaCode],[IndustryCode],[IndustrySubgroupCode],[Fylke],[Municipality],[MunicipalityNumber],[NonCompliance],new into BayesianDynamicChecklistLocalDbRandomizedTest\"+str(ik) \n",
    "                           +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomized] \"\n",
    "                          +\" where InspectionDateId<20190601 ORDER BY new OFFSET (\"+str(round((length/8)*(ik)))+\") ROWS FETCH NEXT (\"+str(round((length/8))-1)+\") ROWS ONLY\"\n",
    "                       )\n",
    "        cnxn.commit()\n",
    "        if ik>0 and ik <7:   \n",
    "            cursor.execute(\"Select [RowId],[MuncipalityIndex],[IndustrySubgroupCodeIndex],[InspectionId],[InspectionDateId],[ControlPointText],[IndustryMainAreaCode],[IndustryGroupCode],[IndustryAreaCode],[IndustryCode],[IndustrySubgroupCode],[Fylke],[Municipality],[MunicipalityNumber],[NonCompliance],new into BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik) \n",
    "                           +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomized] \"\n",
    "                          +\" where InspectionDateId<20190601 ORDER BY new OFFSET (\"+str(0)+\") ROWS FETCH NEXT (\"+str(round((length/8)*ik)-1)+\") ROWS ONLY\"\n",
    "                       )\n",
    "            cnxn.commit()\n",
    "            cursor.execute(\"Insert into BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\" Select [RowId],[MuncipalityIndex],[IndustrySubgroupCodeIndex],[InspectionId],[InspectionDateId],[ControlPointText],[IndustryMainAreaCode],[IndustryGroupCode],[IndustryAreaCode],[IndustryCode],[IndustrySubgroupCode],[Fylke],[Municipality],[MunicipalityNumber],[NonCompliance],new \" \n",
    "                          + \" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomized] \"\n",
    "                          +\" where InspectionDateId<20190601 ORDER BY new OFFSET (\"+str(round((length/8)*(ik+1)))+\") ROWS FETCH NEXT (\"+str(round((length/8)*(7-ik))-1)+\") ROWS ONLY\"\n",
    "                       )\n",
    "            cnxn.commit()\n",
    "            \n",
    "        elif ik<1:\n",
    "            cursor.execute(\"Select [RowId],[MuncipalityIndex],[IndustrySubgroupCodeIndex],[InspectionId],[InspectionDateId],[ControlPointText],[IndustryMainAreaCode],[IndustryGroupCode],[IndustryAreaCode],[IndustryCode],[IndustrySubgroupCode],[Fylke],[Municipality],[MunicipalityNumber],[NonCompliance],new into BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik) \n",
    "                           +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomized] \"\n",
    "                          +\" where InspectionDateId<20190601 ORDER BY new OFFSET (\"+str(round((length/8)*(ik+1)))+\") ROWS FETCH NEXT (\"+str(length-round((length/8)*(ik+1))-1)+\") ROWS ONLY\"\n",
    "                       )\n",
    "            cnxn.commit()\n",
    "        else:\n",
    "            cursor.execute(\"Select [RowId],[MuncipalityIndex],[IndustrySubgroupCodeIndex],[InspectionId],[InspectionDateId],[ControlPointText],[IndustryMainAreaCode],[IndustryGroupCode],[IndustryAreaCode],[IndustryCode],[IndustrySubgroupCode],[Fylke],[Municipality],[MunicipalityNumber],[NonCompliance],new into BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik) \n",
    "                           +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomized] \"\n",
    "                          +\" where InspectionDateId<20190601 ORDER BY new OFFSET (\"+str(0)+\") ROWS FETCH NEXT (\"+str(length-round((length/8))-1)+\") ROWS ONLY\"\n",
    "                       )\n",
    "            cnxn.commit()\n",
    "            \n",
    "\n",
    "def deleteTrainTestTablesParameters():\n",
    "    cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=localhost;DATABASE=IJCAI2022;Trusted_Connection=yes;')\n",
    "    cursor = cnxn.cursor()\n",
    "    for ik in range(0,8):\n",
    "        cursor.execute(\"Drop Table BayesianDynamicChecklistLocalDbRandomizedTestParameters\"+str(ik)\n",
    "                   )\n",
    "        cnxn.commit()    \n",
    "def createTrainTestTablesParameters():\n",
    "    cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=localhost;DATABASE=IJCAI2022;Trusted_Connection=yes;')\n",
    "    cursor = cnxn.cursor()\n",
    "\n",
    "    for ik in range(0,8):\n",
    "        traindateid=\"\"\n",
    "        testdateid=\"\"\n",
    "        cursor.execute(\"SELECT  [MuncipalityIndex] as MuncipalityIndex ,[IndustrySubgroupCodeIndex],[IndustryGroupCode],[IndustryAreaCode],[IndustryCode],[Fylke],[MunicipalityNumber],  [InspectionId] as 'InspectionId' ,[IndustryMainAreaCode],ControlPointText,[IndustrySubgroupCode],[Municipality],[NonCompliance]\"\n",
    "    +\",round(isNull(SannsynlighetForBruddGittKPogKommune,(1.0/(6.0+isNull(tttg2,0))))*100,0) as ProbabilityForNonComplianceGivenControlPointandMunicipality\"\n",
    "    +\",round(isNull(SannsynlighetForBruddGittKPogNaring,(1.0/(6.0+isNull(tttg,0))))*100,0) as ProbabilityForNonComplianceGivenControlPointandIndustrySubgroup\"\n",
    "    +\",round(isNull(AntBruddGittKPogNaring,0),0) as NumberOfNonComplianceGivenControlPointandIndustrySubgroup\"\n",
    "    +\",round(isNull(AntBruddGittKPogKommune,0),0) as NumberOfNonComplianceGivenControlPointandMunicipality\" \n",
    "    +\",isNull(MunicipalityPar01,1) as MunicipalityPar1\"\n",
    "    +\",IIF(SannsynlighetForBruddGittKPogKommune is null,(6.0+isNull(tttg2,0)),isNull(MunicipalityParTot1,6)) as MunicipalityParTot\"\n",
    "    +\",isNull(NaringPar1,1) as IndustryPar1\"\n",
    "    +\",IIF(SannsynlighetForBruddGittKPogNaring is null,(6+isNull(tttg,0)),isNull(NaringParTot,6))  as IndustryParTot\" \n",
    "    +\" into BayesianDynamicChecklistLocalDbRandomizedTestParameters\"+str(ik)\n",
    "                          +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTest\"+str(ik)+\"]\"\n",
    "    +\" Left join(Select ( (Convert(real,isNull(Count(IndustrySubgroupCode),0))+1.0)/(AVG(Convert(real, Kommune3))+6.0))  as SannsynlighetForBruddGittKPogNaring,Count(IndustrySubgroupCode) as AntBruddGittKPogNaring,ControlPointText as KPT2, IndustrySubgroupCode as Nugk\"\n",
    "    +\", (Convert(real,isNull(Count(IndustrySubgroupCode),0))+1.0) as NaringPar1,(AVG(Convert(real, Kommune3))+6.0) as NaringParTot\"\n",
    "                       +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\"]\"+\" \"  \n",
    "    +\" join (Select Count(IndustrySubgroupCode) as Kommune3 , ControlPointText as KT2, IndustrySubgroupCode as Kom\"\n",
    "    +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\"]\"+\" \"+traindateid+\"  group by ControlPointText, IndustrySubgroupCode) as a on a.Kom=IndustrySubgroupCode and ControlPointText=KT2\" \n",
    "    +\" join (Select Count(Municipality) as Kommune5 ,  max(NonCompliance) as AntB2\"\n",
    "    +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\"]\"+\" \" +traindateid+\") as c on c.AntB2=NonCompliance\"\n",
    "                        +traindateid\n",
    "    +\" group by ControlPointText, NonCompliance, IndustrySubgroupCode) as b on b.KPT2=ControlPointText and b.Nugk=IndustrySubgroupCode\"\n",
    "    +\" Left join(Select ( (Convert(real,isNull(Count(Municipality),0))+1.0)/(AVG(Convert(real, Kommune3))+6.0))  as SannsynlighetForBruddGittKPogKommune,Count(Municipality) as AntBruddGittKPogKommune, ControlPointText as KPT, Municipality as KOMM\"\n",
    "    +\",(Convert(real,isNull(Count(Municipality),0))+1.0) as MunicipalityPar01,(AVG(Convert(real, Kommune3))+6.0) as MunicipalityParTot1\"\n",
    "    +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\"]\"+\"  join (Select Count(Municipality) as Kommune3 , ControlPointText as KT2, Municipality as Kom\"\n",
    "    +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\"]\"+\" \"+traindateid+\" group by ControlPointText, Municipality) as a on a.Kom=Municipality and ControlPointText=KT2\" \n",
    "    +\" join (Select Count(Municipality) as Kommune5 ,  max(NonCompliance) as AntB2\"\n",
    "    +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\"]\"+\" \" +traindateid+\") as c on c.AntB2=NonCompliance\"\n",
    "                        +traindateid+\"  group by ControlPointText, NonCompliance, Municipality) as a on a.KOMM=Municipality and a.KPT=ControlPointText\"\n",
    "    +\" left join(Select Count(IndustrySubgroupCode) as tttg , ControlPointText as KT10, IndustrySubgroupCode as Kom10\"\n",
    "    +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\"]\"+\" \"+traindateid+\" group by ControlPointText, IndustrySubgroupCode) as z on z.Kom10=IndustrySubgroupCode and ControlPointText=z.KT10\" \n",
    "    +\" left join(Select Count(Municipality) as tttg2 , ControlPointText as KT10, Municipality as NUG10\"\n",
    "    +\" FROM [IJCAI2022].[dbo].[BayesianDynamicChecklistLocalDbRandomizedTrain\"+str(ik)+\"]\"+\" \"+traindateid+\" group by ControlPointText, Municipality) as zz on ControlPointText=zz.KT10 and zz.NUG10=Municipality\" \n",
    "    +\" where InspectionDateId<20190601\"\n",
    "                          +\" Order by InspectionDateId \" + testdateid)\n",
    "\n",
    "        cnxn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "09d0fc72",
   "metadata": {},
   "outputs": [],
   "source": [
    "#deleteTrainTestTables() #Uncomment whenever random resampling is needed\n",
    "createTrainTestTables() \n",
    "#deleteTrainTestTablesParameters() #Uncomment whenever random resampling is needed\n",
    "createTrainTestTablesParameters()#Filling test sets with precalculated parameters for CBCBR, calculated from the corresponding training set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "991d4da2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}