test_clean.R

#Almost identical copy of training cleaning process on test set
#As such, also divided into two sections due to time-required

###################################################################
#Part 1:
###################################################################

remoteLogin("http://",diff = TRUE,session = TRUE,commandline = TRUE)
library(data.table)
library(dplyr)
library(mrsdeploy)
test<-fread("C:/Data/microsoft-malware-prediction/test.csv",
             drop=c('Census_IsWIMBootEnabled','Census_IsFlightingInternal',
                    'Census_ThresholdOptIn','DefaultBrowsersIdentifier','SMode'))

#Imputing Mean
test[which(is.na(test$AVProductsInstalled)),'AVProductsInstalled']<-round(mean(na.exclude(test$AVProductsInstalled)),0)
test[which(is.na(test$AVProductsEnabled)),'AVProductsEnabled']<-round(mean(na.exclude(test$AVProductsEnabled)),0)
test[which(is.na(test$RtpStateBitfield)),'RtpStateBitfield']<-round(mean(na.exclude(test$RtpStateBitfield)),0)
test[which(is.na(test$Wdft_IsGamer)),'Wdft_IsGamer']<-round(mean(na.exclude(test$Wdft_IsGamer)),0)
test[which(is.na(test$Census_IsAlwaysOnAlwaysConnectedCapable)),'Census_IsAlwaysOnAlwaysConnectedCapable']<-round(mean(na.exclude(test$Census_IsAlwaysOnAlwaysConnectedCapable)),0)
test[which(is.na(test$Census_IsVirtualDevice)),'Census_IsVirtualDevice']<-round(mean(na.exclude(test$Census_IsVirtualDevice)),0)
test[which(is.na(test$Firewall)),'Firewall']<-round(mean(na.exclude(test$Firewall)),0)
test[which(is.na(test$IsProtected)),'IsProtected']<-round(mean(na.exclude(test$IsProtected)),0)
test[which(is.na(test$Census_IsFlightsDisabled)),'Census_IsFlightsDisabled']<-round(mean(na.exclude(test$Census_IsFlightsDisabled)),0)

#Imputing with Mode
test[which(is.na(test$Wdft_RegionIdentifier)),'Wdft_RegionIdentifier']<-10
test[which(is.na(test$GeoNameIdentifier)),'GeoNameIdentifier']<-277
test[which(is.na(test$UacLuaenable)),'UacLuaenable']<-1
test[which(is.na(test$AVProductStatesIdentifier)),'AVProductStatesIdentifier']<-53447
test[which(is.na(test$Census_TotalPhysicalRAM)),'Census_TotalPhysicalRAM']<-4096
test[which(is.na(test$OrganizationIdentifier)),'OrganizationIdentifier']<-27
test[which(is.na(test$Census_ProcessorCoreCount)),'Census_ProcessorCoreCount']<-4

df<-data.frame(levels(as.factor(test$Census_MDC2FormFactor)),seq(1:14),seq(1:14))
colnames(df)<-c("Census_MDC2FormFactor","Horizontal","Vertical")

for (i in 1:14){
  df$Horizontal[i]<-round(mean(na.exclude(test$Census_InternalPrimaryDisplayResolutionHorizontal[test$Census_MDC2FormFactor== df$Census_MDC2FormFactor[i]])))
  df$Vertical[i]<-round(mean(na.exclude(test$Census_InternalPrimaryDisplayResolutionVertical[test$Census_MDC2FormFactor== df$Census_MDC2FormFactor[i]])))
}
df

replace<-test[which(is.na(test$Census_InternalPrimaryDisplayResolutionVertical)),c('Census_MDC2FormFactor','Census_InternalPrimaryDisplayResolutionHorizontal','Census_InternalPrimaryDisplayResolutionVertical')]

#Originally written to be executed in one loop but that's too much for the R server to handle
for (i in 1:nrow(replace)){
  replace$Census_InternalPrimaryDisplayResolutionHorizontal[i]<-df[df$Census_MDC2FormFactor==replace$Census_MDC2FormFactor[i],'Horizontal']
}
for (i in 1:nrow(replace)){
  replace$Census_InternalPrimaryDisplayResolutionVertical[i]<-df[df$Census_MDC2FormFactor==replace$Census_MDC2FormFactor[i],'Vertical']
}

replace

replace<-replace[,-1]
test[which(is.na(test$Census_InternalPrimaryDisplayResolutionVertical)),c('Census_InternalPrimaryDisplayResolutionHorizontal','Census_InternalPrimaryDisplayResolutionVertical')]<-replace

#Similar plan of action for total capacity

test[test$Census_MDC2FormFactor=="Other",'Census_MDC2FormFactor']<-"IoTOther"

df<-data.frame(levels(as.factor(test$Census_MDC2FormFactor)),seq(1:13),seq(1:13))
colnames(df)<-c("Census_MDC2FormFactor","Census_PrimaryDiskTotalCapacity","Census_SystemVolumeTotalCapacity")

for (i in 1:13){
  df$Census_PrimaryDiskTotalCapacity[i]<-round(mean(na.exclude(test$Census_PrimaryDiskTotalCapacity[test$Census_MDC2FormFactor== df$Census_MDC2FormFactor[i]])))
  df$Census_SystemVolumeTotalCapacity[i]<-round(mean(na.exclude(test$Census_SystemVolumeTotalCapacity[test$Census_MDC2FormFactor== df$Census_MDC2FormFactor[i]])))
}
df

df$Census_MDC2FormFactor<-as.character(df$Census_MDC2FormFactor)
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="AllInOne"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[1,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="Convertible"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[2,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="Desktop"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[3,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="Detachable"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[4,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="IoTOther"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[5,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="LargeServer"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[6,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="LargeTablet"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[7,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="MediumServer"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[8,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="Notebook"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[9,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="PCOther"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[10,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="ServerOther"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[11,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="SmallServer"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[12,2:3]
test[which(is.na(test$Census_SystemVolumeTotalCapacity)&test$Census_MDC2FormFactor=="SmallTablet"),c('Census_PrimaryDiskTotalCapacity','Census_SystemVolumeTotalCapacity')]<-df[13,2:3]

test[which(is.na(test$Census_PrimaryDiskTotalCapacity)),'Census_MDC2FormFactor']
test[which(is.na(test$Census_PrimaryDiskTotalCapacity)&test$Census_MDC2FormFactor=="Desktop"),'Census_PrimaryDiskTotalCapacity']<-df[3,2]
test[which(is.na(test$Census_PrimaryDiskTotalCapacity)&test$Census_MDC2FormFactor=="Notebook"),'Census_PrimaryDiskTotalCapacity']<-df[9,2]
test[which(is.na(test$Census_PrimaryDiskTotalCapacity)&test$Census_MDC2FormFactor=="Convertible"),'Census_PrimaryDiskTotalCapacity']<-df[2,2]
test[which(is.na(test$Census_PrimaryDiskTotalCapacity)&test$Census_MDC2FormFactor=="AllInOne"),'Census_PrimaryDiskTotalCapacity']<-df[1,2]

#The same exact process for 2 other real value features
df<-data.frame(levels(as.factor(test$Census_MDC2FormFactor)),seq(1:13),seq(1:13))
colnames(df)<-c("Census_MDC2FormFactor","Census_InternalBatteryNumberOfCharges","Census_InternalPrimaryDiagonalDisplaySizeInInches")

for (i in 1:13){
  df$Census_InternalBatteryNumberOfCharges[i]<-round(mean(na.exclude(test$Census_InternalBatteryNumberOfCharges[test$Census_MDC2FormFactor== df$Census_MDC2FormFactor[i]])))
  df$Census_InternalPrimaryDiagonalDisplaySizeInInches[i]<-round(mean(na.exclude(test$Census_InternalPrimaryDiagonalDisplaySizeInInches[test$Census_MDC2FormFactor== df$Census_MDC2FormFactor[i]])))
}
df

df$Census_MDC2FormFactor<-as.character(df$Census_MDC2FormFactor)
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="AllInOne"),'Census_InternalBatteryNumberOfCharges']<-df[1,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="Convertible"),'Census_InternalBatteryNumberOfCharges']<-df[2,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="Desktop"),'Census_InternalBatteryNumberOfCharges']<-df[3,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="Detachable"),'Census_InternalBatteryNumberOfCharges']<-df[4,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="IoTOther"),'Census_InternalBatteryNumberOfCharges']<-df[5,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="LargeServer"),'Census_InternalBatteryNumberOfCharges']<-df[6,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="LargeTablet"),'Census_InternalBatteryNumberOfCharges']<-df[7,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="MediumServer"),'Census_InternalBatteryNumberOfCharges']<-df[8,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="Notebook"),'Census_InternalBatteryNumberOfCharges']<-df[9,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="PCOther"),'Census_InternalBatteryNumberOfCharges']<-df[10,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="ServerOther"),'Census_InternalBatteryNumberOfCharges']<-df[11,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="SmallServer"),'Census_InternalBatteryNumberOfCharges']<-df[12,2]
test[which(is.na(test$Census_InternalBatteryNumberOfCharges)&test$Census_MDC2FormFactor=="SmallTablet"),'Census_InternalBatteryNumberOfCharges']<-df[13,2]

test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="AllInOne"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[1,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="Convertible"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[2,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="Desktop"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[3,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="Detachable"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[4,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="IoTOther"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[5,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="LargeServer"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[6,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="LargeTablet"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[7,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="MediumServer"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[8,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="Notebook"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[9,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="PCOther"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[10,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="ServerOther"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[11,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="SmallServer"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[12,3]
test[which(is.na(test$Census_InternalPrimaryDiagonalDisplaySizeInInches)&test$Census_MDC2FormFactor=="SmallTablet"),'Census_InternalPrimaryDiagonalDisplaySizeInInches']<-df[13,3]

df<-data.frame(levels(as.factor(test$CountryIdentifier)),seq(1:222))
colnames(df)<-c("CountryIdentifier","MostPopularCity")
df$CountryIdentifier<-as.numeric(df$CountryIdentifier)

getmode <- function(x) {
  uniqx <- unique(x)
  uniqx[which.max(tabulate(match(x, uniqx)))]
}

for (i in 1:222){
  df$MostPopularCity[i]<-getmode(na.exclude(test$CityIdentifier[test$CountryIdentifier== df$CountryIdentifier[i]]))
}
df<-distinct(df,CountryIdentifier,.keep_all=T)
df<-df[order(df$CountryIdentifier),]

#Have to put in different loops because combining them crashes the server
for (i in 1:50){
  test[which(is.na(test$CityIdentifier)&test$CountryIdentifier==df$CountryIdentifier[i]),'CityIdentifier']<-df$MostPopularCity[i]
  print(i)
}
for (i in 51:100){
  test[which(is.na(test$CityIdentifier)&test$CountryIdentifier==df$CountryIdentifier[i]),'CityIdentifier']<-df$MostPopularCity[i]
  print(i)
}
for (i in 101:150){
  test[which(is.na(test$CityIdentifier)&test$CountryIdentifier==df$CountryIdentifier[i]),'CityIdentifier']<-df$MostPopularCity[i]
  print(i)
}
for (i in 151:200){
  test[which(is.na(test$CityIdentifier)&test$CountryIdentifier==df$CountryIdentifier[i]),'CityIdentifier']<-df$MostPopularCity[i]
  print(i)
}
for (i in 201:222){
  test[which(is.na(test$CityIdentifier)&test$CountryIdentifier==df$CountryIdentifier[i]),'CityIdentifier']<-df$MostPopularCity[i]
  print(i)
}

#Recode with mode
getmode(na.exclude(test$Census_ProcessorModelIdentifier))
sum(na.exclude(test$Census_ProcessorModelIdentifier==2697))
test[which(is.na(test$Census_ProcessorModelIdentifier)),'Census_ProcessorModelIdentifier']<-2697

getmode(na.exclude(test$Census_ProcessorManufacturerIdentifier))
sum(na.exclude(test$Census_ProcessorManufacturerIdentifier==5))
test[which(is.na(test$Census_ProcessorManufacturerIdentifier)),'Census_ProcessorManufacturerIdentifier']<-5

getmode(na.exclude(test$IeVerIdentifier))
sum(na.exclude(test$IeVerIdentifier==137))
test[which(is.na(test$IeVerIdentifier)),'IeVerIdentifier']<-137

getmode(na.exclude(test$Census_OSInstallLanguageIdentifier))
sum(na.exclude(test$Census_OSInstallLanguageIdentifier==8))
test[which(is.na(test$Census_OSInstallLanguageIdentifier)),'Census_OSInstallLanguageIdentifier']<-8

getmode(na.exclude(test$Census_OEMNameIdentifier))
sum(na.exclude(test$Census_OEMNameIdentifier==2668))
test[which(is.na(test$Census_OEMNameIdentifier)),'Census_OEMNameIdentifier']<-2668

getmode(na.exclude(test$Census_OEMModelIdentifier))
sum(na.exclude(test$Census_OEMModelIdentifier==313586))
test[which(is.na(test$Census_OEMModelIdentifier)),'Census_OEMModelIdentifier']<-313586

getmode(na.exclude(test$Census_FirmwareVersionIdentifier))
sum(na.exclude(test$Census_FirmwareVersionIdentifier==33105))
test[which(is.na(test$Census_FirmwareVersionIdentifier)),'Census_FirmwareVersionIdentifier']<-33105

getmode(na.exclude(test$Census_FirmwareManufacturerIdentifier))
sum(na.exclude(test$Census_FirmwareManufacturerIdentifier==142))
test[which(is.na(test$Census_FirmwareManufacturerIdentifier)),'Census_FirmwareManufacturerIdentifier']<-142

na_count<-data.frame(colnames(test),as.numeric(colSums(is.na(test)))) 
colnames(na_count)<-c("Feature","NAs")
na_count<-na_count[order(na_count$NAs,decreasing = T),]
na_count<-na_count%>%filter(NAs>0)
na_count

write.csv(test,"C:/Data/microsoft-malware-prediction/cleaned_train.csv",row.names = F)


###################################################################
#Part 2:
###################################################################

remoteLogin("http://",diff = TRUE,session = TRUE,commandline = TRUE)
library(data.table)
library(dplyr)
library(mrsdeploy)
test<-fread("C:/Data/microsoft-malware-prediction/cleaned_train.csv",drop=c('OsBuildLab','Census_PowerPlatformRoleName','OsPlatformSubRelease',
                    'Census_InternalBatteryType','SkuEdition','Census_OSArchitecture',
                    'Census_ChassisTypeName','Census_OSSkuName','Census_DeviceFamily',
                    'Census_ProcessorClass')) 


#Get rid of OsBuildLab as everything is in the other columns
#Get rid of internal battery type as the variables make no sense
#Get rid of Census_PowerPlatformRoleName as all information is preserved in my fav field
#Get rid of OsPlatformSubRelease as we're just using main versioning info
#Get rid of SkuEdition as osskuname has all the same info
#get rid of Census_OSArchitecture as all the same info is in Processor
#get rid of Census_ChassisTypeName as all the same info is in Census_MDC2FormFactor
#removing Census_OSSkuName because it is also redundant to other fields
#removing Census_DeviceFamily because all of them are Windows and granularity preserved 
#in other fields

#Now that NAs are removed, character vectors need to be dealt with
levels(as.factor(test$PuaMode))
test[test$PuaMode=="",'PuaMode']<-"0"
test[test$PuaMode=="audit",'PuaMode']<-"0.5"
test[test$PuaMode=="on",'PuaMode']<-"1"

#levels(as.factor(test$Census_OSVersion))
test$Census_OSVersion<-gsub("[[:punct:]]0[[:punct:]]","",test$Census_OSVersion)
test$Census_OSVersion<-gsub("[[:punct:]]","",test$Census_OSVersion)
df<-as.vector(test[which(startsWith(test$Census_OSVersion,"6")),'Census_OSVersion'])
df$Census_OSVersion<-substring(df$Census_OSVersion,1,4)
test[which(startsWith(test$Census_OSVersion,"6")),'Census_OSVersion']<- sprintf("%05s",df$Census_OSVersion)
test$Census_OSVersion<-substring(test$Census_OSVersion,1,5)
test[test$Census_OSVersion==" 6176",'Census_OSVersion']<-"06176"
test[test$Census_OSVersion==" 6396",'Census_OSVersion']<-"06396"

#Make a new feature for if SmartScreen= ExistsNotSet 1 y 0 n 
#has an 80% detection rate
test$ExistsNotSet<-NA
test[test$SmartScreen=="ExistsNotSet",'ExistsNotSet']<-1
test[test$SmartScreen!="ExistsNotSet",'ExistsNotSet']<-0

#levels(as.factor(test$Platform))
test[which(test$Platform=="windows10"),'Platform']<-10
test[which(test$Platform=="windows7"),'Platform']<-7
test[which(test$Platform=="windows8"),'Platform']<-8
test[which(test$Platform=="windows2016"),'Platform']<-16

levels(as.factor(test$Processor))
test[which(test$Processor=="x64"|test$Processor=="arm64"),'Processor']<-64
test[which(test$Processor=="x86"),'Processor']<-86

#levels(as.factor(test$AvSigVersion))
test$AvSigVersion<-gsub("1[[:punct:]]","",test$AvSigVersion)
test$AvSigVersion<-gsub("[[:punct:]][[:digit:]]","",test$AvSigVersion)
test[which(test$AvSigVersion=="0"),'AvSigVersion']<-"200"
test$AvSigVersion<-substring(test$AvSigVersion,1,3)

#levels(as.factor(test$OsVer))
test$OsVer<-gsub("[[:punct:]]","",test$OsVer)
test$OsVer<-substring(test$OsVer,1,2)
test$OsVer<-as.numeric(test$OsVer)
test[test$OsVer>50,'OsVer']<-6

#levels(as.factor(test$EngineVersion))
test$EngineVersion<-gsub("1[[:punct:]]","",test$EngineVersion)
test$EngineVersion<-gsub("[[:punct:]]","",test$EngineVersion)
test$EngineVersion<-substring(test$EngineVersion,1,3)

#levels(as.factor(test$AppVersion))
test$AppVersion<-gsub("[[:punct:]]","",test$AppVersion)
test$AppVersion<-substring(test$AppVersion,1,3)

#levels(as.factor(test$SmartScreen))
test[test$SmartScreen=="",'SmartScreen']<-"RequireAdmin"
test[test$SmartScreen %in% c("off","OFF","of"),'SmartScreen']<-"Off"
test[test$SmartScreen %in% c("Enabled","on","ON"),'SmartScreen']<-"On"
test[test$SmartScreen=="warn",'SmartScreen']<-"Warn"
test[test$SmartScreen=="BLOCK",'SmartScreen']<-"Block"
test[test$SmartScreen %in% c("prompt","Promt","Promprt"),'SmartScreen']<-"Prompt"
test[test$SmartScreen %in% c("requireadmin","requireAdmin","RequiredAdmin"),'SmartScreen']<-"RequireAdmin"
#Setting all as ExistsNotSet as these are all replacement smart screens
test[test$SmartScreen %in% c("00000000","&#x01;","&#x02;","&#x03;","0"),'SmartScreen']<-"ExistsNotSet"


#df<-data.frame(levels(as.factor(test$SmartScreen)),seq(1:7))
#colnames(df)<-c("SmartScreen","DetectionRate")
#df$SmartScreen<-as.character(df$SmartScreen)

#for (i in 1:8){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$SmartScreen==df$SmartScreen[i]))/sum(test$SmartScreen==df$SmartScreen[i])*100),3)
#}
#df

#Highest detection Rate = 1, going down to n
test[test$SmartScreen=="ExistsNotSet",'SmartScreen']<-"1"
test[test$SmartScreen=="Warn",'SmartScreen']<-"2"
test[test$SmartScreen=="On",'SmartScreen']<-"3"
test[test$SmartScreen=="Block",'SmartScreen']<-"4"
test[test$SmartScreen=="Off",'SmartScreen']<-"5"
test[test$SmartScreen=="Prompt",'SmartScreen']<-"6"
test[test$SmartScreen=="RequireAdmin",'SmartScreen']<-"7"
test[test$SmartScreen=="Deny",'SmartScreen']<-"8"


#table(test$Census_MDC2FormFactor)
'%ni%' <- Negate('%in%')

#df<-data.frame(levels(as.factor(test$Census_MDC2FormFactor)),seq(1:13))
#colnames(df)<-c("Census_MDC2FormFactor","DetectionRate")
#df$Census_MDC2FormFactor<-as.character(df$Census_MDC2FormFactor)

#for (i in 1:13){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_MDC2FormFactor==df$Census_MDC2FormFactor[i]))/sum(test$Census_MDC2FormFactor==df$Census_MDC2FormFactor[i])*100),3)
#}
#df

test$IOT<-NA
#Notebook/IOTOther, LargeTablet, Notebook, SmallTablet
test[test$Census_MDC2FormFactor %in% c("Notebook","IOTOther"),'IOT']<-3
test[test$Census_MDC2FormFactor=="LargeTablet",'IOT']<-2
test[test$Census_MDC2FormFactor=="SmallTablet",'IOT']<-1
test[test$Census_MDC2FormFactor %ni% c("Notebook","LargeTablet","SmallTablet","IOTOther"),'IOT']<-0

test$PC<-NA
#Desktop/PCOther,AllInOne,Convertible,Detachable
test[test$Census_MDC2FormFactor %in% c("Desktop","PCOther"),'PC']<-4
test[test$Census_MDC2FormFactor=="AllInOne",'PC']<-3
test[test$Census_MDC2FormFactor=="Convertible",'PC']<-2
test[test$Census_MDC2FormFactor=="Detachable",'PC']<-1
test[test$Census_MDC2FormFactor %ni% c("Desktop","AllInOne","Convertible","Detachable","PCOther"),'PC']<-0

test$Server<-NA
#LargeServer,MediumServer,SmallServer,ServerOther
test[test$Census_MDC2FormFactor %in% c("SmallServer","ServerOther"),'Server']<-3
test[test$Census_MDC2FormFactor=="MediumServer",'Server']<-2
test[test$Census_MDC2FormFactor=="LargeServer",'Server']<-1
test[test$Census_MDC2FormFactor %ni% c("LargeServer","MediumServer","SmallServer","ServerOther"),'Server']<-0

test<-test[,-30]

#levels(as.factor(test$Census_OSBranch))
test[which(startsWith(test$Census_OSBranch,"rs")),'Census_OSBranch']<-"rs"
test[which(startsWith(test$Census_OSBranch,"th")),'Census_OSBranch']<-"th"
test[which(startsWith(test$Census_OSBranch,"win")),'Census_OSBranch']<-"win"
test[test$Census_OSBranch=="Khmer OS",'Census_OSBranch']<-"rs"

#df<-data.frame(levels(as.factor(test$Census_OSBranch)),seq(1:3))
#colnames(df)<-c("Census_OSBranch","DetectionRate")
#df$Census_OSBranch<-as.character(df$Census_OSBranch)

#for (i in 1:3){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_OSBranch==df$Census_OSBranch[i]))/sum(test$Census_OSBranch==df$Census_OSBranch[i])*100),3)
#}
#df

test[test$Census_OSBranch=="rs",'Census_OSBranch']<-1
test[test$Census_OSBranch=="th",'Census_OSBranch']<-2
test[test$Census_OSBranch=="win",'Census_OSBranch']<-3

#levels(as.factor(test$Census_OSEdition))
test[which(startsWith(test$Census_OSEdition,"Clo")),'Census_OSEdition']<-"Cloud"
test[which(startsWith(test$Census_OSEdition,"Core")),'Census_OSEdition']<-"Core"
test[which(startsWith(test$Census_OSEdition,"Education")),'Census_OSEdition']<-"Education"
test[which(startsWith(test$Census_OSEdition,"Home")),'Census_OSEdition']<-"Home"
test[which(startsWith(test$Census_OSEdition,"Pro")),'Census_OSEdition']<-"Professional"
test[which(startsWith(test$Census_OSEdition,"pro")),'Census_OSEdition']<-"Professional"
test[which(startsWith(test$Census_OSEdition,"Ent")),'Census_OSEdition']<-"Enterprise"
test[which(startsWith(test$Census_OSEdition,"Ser")),'Census_OSEdition']<-"Server"
test[test$Census_OSEdition %in% c("Ultimate","Window 10 Enterprise"),'Census_OSEdition']<-"Enterprise"
test[test$Census_OSEdition =="PRO",'Census_OSEdition']<-"Professional"
test[test$Census_OSEdition %in% c("","#","00426-OEM-8992662-00006"),'Census_OSEdition']<-"Core"


#df<-data.frame(levels(as.factor(test$Census_OSEdition)),seq(1:7))
#colnames(df)<-c("Census_OSEdition","DetectionRate")
#df$Census_OSEdition<-as.character(df$Census_OSEdition)

#for (i in 1:7){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_OSEdition==df$Census_OSEdition[i]))/sum(test$Census_OSEdition==df$Census_OSEdition[i])*100),3)
#}
#df

test[test$Census_OSEdition=="Education",'Census_OSEdition']<-1
test[test$Census_OSEdition=="Enterprise",'Census_OSEdition']<-2
test[test$Census_OSEdition=="Professional",'Census_OSEdition']<-3
test[test$Census_OSEdition=="Home",'Census_OSEdition']<-4
test[test$Census_OSEdition=="Core",'Census_OSEdition']<-5
test[test$Census_OSEdition=="Cloud",'Census_OSEdition']<-6
test[test$Census_OSEdition=="Server",'Census_OSEdition']<-7

levels(as.factor(test$Census_ActivationChannel))
test[which(startsWith(test$Census_ActivationChannel,"OEM")),'Census_ActivationChannel']<-"OEM"
test[which(startsWith(test$Census_ActivationChannel,"Vol")),'Census_ActivationChannel']<-"Volume"
test[which(startsWith(test$Census_ActivationChannel,"Ret")),'Census_ActivationChannel']<-"Retail"

#df<-data.frame(levels(as.factor(test$Census_ActivationChannel)),seq(1:3))
#colnames(df)<-c("Census_ActivationChannel","DetectionRate")
#df$Census_ActivationChannel<-as.character(df$Census_ActivationChannel)

#for (i in 1:3){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_ActivationChannel==df$Census_ActivationChannel[i]))/sum(test$Census_ActivationChannel==df$Census_ActivationChannel[i])*100),3)
#}
#df

test[test$Census_ActivationChannel=="Volume",'Census_ActivationChannel']<-1
test[test$Census_ActivationChannel=="OEM",'Census_ActivationChannel']<-2
test[test$Census_ActivationChannel=="Retail",'Census_ActivationChannel']<-3

levels(as.factor(test$Census_FlightRing))
test[which(test$Census_FlightRing %in% c("Canary","Invalid","OSG","CBCanary")),'Census_FlightRing']<-"Unknown"
test[which(test$Census_FlightRing %in% c("Disabled","NOT_SET")),'Census_FlightRing']<-"No"
test[which(test$Census_FlightRing %in% c("Retail","RP")),'Census_FlightRing']<-"Retail"
test[which(test$Census_FlightRing %in% c("WIF","WIS")),'Census_FlightRing']<-"W"
#df<-data.frame(levels(as.factor(test$Census_FlightRing)),seq(1:4))
#colnames(df)<-c("Census_FlightRing","DetectionRate")
#df$Census_FlightRing<-as.character(df$Census_FlightRing)

#for (i in 1:4){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_FlightRing==df$Census_FlightRing[i]))/sum(test$Census_FlightRing==df$Census_FlightRing[i])*100),3)
#}
#df

test[test$Census_FlightRing=="Retail",'Census_FlightRing']<-1
test[test$Census_FlightRing=="No",'Census_FlightRing']<-2
test[test$Census_FlightRing=="Unknown",'Census_FlightRing']<-3
test[test$Census_FlightRing=="W",'Census_FlightRing']<-4

levels(as.factor(test$Census_GenuineStateName))
#table(test$Census_GenuineStateName)
test[test$Census_GenuineStateName=="TAMPERED",'Census_GenuineStateName']<-"INVALID_LICENSE"
test[test$Census_GenuineStateName=="",'Census_GenuineStateName']<-"IS_GENUINE"


#df<-data.frame(levels(as.factor(test$Census_GenuineStateName)),seq(1:4))
#colnames(df)<-c("Census_GenuineStateName","DetectionRate")
#df$Census_GenuineStateName<-as.character(df$Census_GenuineStateName)

#for (i in 1:4){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_GenuineStateName==df$Census_GenuineStateName[i]))/sum(test$Census_GenuineStateName==df$Census_GenuineStateName[i])*100),3)
#}
#df

test[test$Census_GenuineStateName=="OFFLINE",'Census_GenuineStateName']<-1
test[test$Census_GenuineStateName=="IS_GENUINE",'Census_GenuineStateName']<-2
test[test$Census_GenuineStateName=="INVALID_LICENSE",'Census_GenuineStateName']<-3
test[test$Census_GenuineStateName=="UNKNOWN",'Census_GenuineStateName']<-4

levels(as.factor(test$Census_OSWUAutoUpdateOptionsName))

#df<-data.frame(levels(as.factor(test$Census_OSWUAutoUpdateOptionsName)),seq(1:6))
#colnames(df)<-c("Census_OSWUAutoUpdateOptionsName","DetectionRate")
#df$Census_OSWUAutoUpdateOptionsName<-as.character(df$Census_OSWUAutoUpdateOptionsName)

#for (i in 1:6){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_OSWUAutoUpdateOptionsName==df$Census_OSWUAutoUpdateOptionsName[i]))/sum(test$Census_OSWUAutoUpdateOptionsName==df$Census_OSWUAutoUpdateOptionsName[i])*100),3)
#}
#df

test[test$Census_OSWUAutoUpdateOptionsName=="FullAuto",'Census_OSWUAutoUpdateOptionsName']<-1
test[test$Census_OSWUAutoUpdateOptionsName=="UNKNOWN",'Census_OSWUAutoUpdateOptionsName']<-2
test[test$Census_OSWUAutoUpdateOptionsName=="AutoInstallAndRebootAtMaintenanceTime",'Census_OSWUAutoUpdateOptionsName']<-3
test[test$Census_OSWUAutoUpdateOptionsName=="Off",'Census_OSWUAutoUpdateOptionsName']<-4
test[test$Census_OSWUAutoUpdateOptionsName=="Notify",'Census_OSWUAutoUpdateOptionsName']<-5
test[test$Census_OSWUAutoUpdateOptionsName=="DownloadNotify",'Census_OSWUAutoUpdateOptionsName']<-6

levels(as.factor(test$Census_PrimaryDiskTypeName))
test[test$Census_PrimaryDiskTypeName=="",'Census_PrimaryDiskTypeName']<-"HDD"

#df<-data.frame(levels(as.factor(test$Census_PrimaryDiskTypeName)),seq(1:4))
#colnames(df)<-c("Census_PrimaryDiskTypeName","DetectionRate")
#df$Census_PrimaryDiskTypeName<-as.character(df$Census_PrimaryDiskTypeName)

#for (i in 1:4){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_PrimaryDiskTypeName==df$Census_PrimaryDiskTypeName[i]))/sum(test$Census_PrimaryDiskTypeName==df$Census_PrimaryDiskTypeName[i])*100),3)
#}
#df

test[test$Census_PrimaryDiskTypeName=="HDD",'Census_PrimaryDiskTypeName']<-1
test[test$Census_PrimaryDiskTypeName=="SSD",'Census_PrimaryDiskTypeName']<-2
test[test$Census_PrimaryDiskTypeName=="UNKNOWN",'Census_PrimaryDiskTypeName']<-3
test[test$Census_PrimaryDiskTypeName=="Unspecified",'Census_PrimaryDiskTypeName']<-4

#ProductName
levels(as.factor(test$ProductName))
test[which(startsWith(test$ProductName,"ms")),'ProductName']<-"mse"
test[which(startsWith(test$ProductName,"win")),'ProductName']<-"windows"

#df<-data.frame(levels(as.factor(test$ProductName)),seq(1:4))
#colnames(df)<-c("ProductName","DetectionRate")
#df$ProductName<-as.character(df$ProductName)

#for (i in 1:4){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$ProductName==df$ProductName[i]))/sum(test$ProductName==df$ProductName[i])*100),3)
#}
#df

test[test$ProductName=="windows",'ProductName']<-1
test[test$ProductName=="mse",'ProductName']<-2
test[test$ProductName=="scep",'ProductName']<-3
test[test$ProductName=="fep",'ProductName']<-4

#Census_OSInstallTypeName
#levels(as.factor(test$Census_OSInstallTypeName))
test[which(startsWith(test$Census_OSInstallTypeName,"Cle")),'Census_OSInstallTypeName']<-"Clean"
test[which(test$Census_OSInstallTypeName=="IBSClean"),'Census_OSInstallTypeName']<-"Clean"
test[which(endsWith(test$Census_OSInstallTypeName,"rade")),'Census_OSInstallTypeName']<-"Upgrade"

#df<-data.frame(levels(as.factor(test$Census_OSInstallTypeName)),seq(1:6))
#colnames(df)<-c("Census_OSInstallTypeName","DetectionRate")
#df$Census_OSInstallTypeName<-as.character(df$Census_OSInstallTypeName)

#for (i in 1:6){
#df$DetectionRate[i]<-round(((sum(test$HasDetections==1&test$Census_OSInstallTypeName==df$Census_OSInstallTypeName[i]))/sum(test$Census_OSInstallTypeName==df$Census_OSInstallTypeName[i])*100),3)
#}
#df

test[test$Census_OSInstallTypeName=="Clean",'Census_OSInstallTypeName']<-1
test[test$Census_OSInstallTypeName=="Upgrade",'Census_OSInstallTypeName']<-2
test[test$Census_OSInstallTypeName=="Other",'Census_OSInstallTypeName']<-3
test[test$Census_OSInstallTypeName=="Reset",'Census_OSInstallTypeName']<-4
test[test$Census_OSInstallTypeName=="Update",'Census_OSInstallTypeName']<-5
test[test$Census_OSInstallTypeName=="Refresh",'Census_OSInstallTypeName']<-6


str(test)
test$ExistsNotSet<-as.numeric(test$ExistsNotSet)
test$IOT<-as.numeric(test$IOT)
test$PC<-as.numeric(test$PC)
test$Server<-as.numeric(test$Server)

na_count<-data.frame(colnames(test),as.numeric(colSums(is.na(test)))) 
colnames(na_count)<-c("Feature","NAs")
na_count<-na_count[order(na_count$NAs,decreasing = T),]
na_count<-na_count%>%filter(NAs>0)

write.csv(test,"C:/Data/microsoft-malware-prediction/cleaned_test_FINAL.csv",row.names = F)
exit