source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
#SETTING UP DEPENDENCIES
library(readxl)
library(dplyr)
library(RMySQL)
source("Configurations.R")
source("Utilities.R")
Sys.setlocale("LC_ALL", "en_US.UTF8")
propertyFileVector <- listFiles(propertyInfoInputFolder)
taxFileVector <- listFiles(taxInfoInputFolder)
propertyFileVector
propertyInfoInputFolder
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
propertyFileVector <- listFiles(propertyInfoInputFolder)
taxFileVector <- listFiles(taxInfoInputFolder)
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
propertyFileVector
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
propertyFileVector
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
propertyFileVector
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
for(file in taxFileVector){
taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames,
startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}
#RETAINING ONLY THE REQUIRED COLUMNS
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets,
columnsToRetain = propertyInfoColumnsToRetain)
taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
columnsToRetain = taxInfoColumnsToRetain)
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)
View(propertyInfoTable)
View(taxInfoTable)
testDF <- data.frame(col1 = c(1,2,3,4,4), col2 = c(1,3,5,7, 7))
View(testDF)
distinct(testDF, col1, col2)
testDF %>% distinct(c("col1", "col2"))
testDF %>% distinct("col1", "col2")
testDF %>% distinct(col1, col2)
propertyInfoTable <- distinct(propertyInfoTable, PROPERTYID, OWNERNAMES, STREETADDRESS1, WARDNAME)
taxInfoTable <- distinct(taxInfoTable, PROP_ID, CHALLAN_ID, CHALLAN_DATE, CHALLAN_AMOUNT)
View(propertyInfoTable)
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[1]
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[1]]
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[1]][1]
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[1]][2]
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[1]][3]
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[1]][4]
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[2]][4]
length(gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[1]])
gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[1]][length(gregexpr("-", propertyInfoTable[1, "PROPERTYID"])[[1]])]
pidStartPosition <- gregexpr("-", propertyInfoTable[, "PROPERTYID"])
pidStartPosition
pidStartPosition[[1]]
pidStartPosition[1]
len(pidStartPosition)
length(pidStartPosition)
pidStartPosition[[1]][1]
pidStartPosition[[1]][2]
View(taxInfoTable)
View(propertyInfoTable)
pidStartPosition[[1]][3]
?strsplit
pidComponentColumns <- strsplit(propertyInfoTable[, "PROPERTYID"], "-")
pidComponentColumns <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentColumns <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentColumns[[1]]
pidComponentColumns[1]
pidComponentColumns[2][1]
pidComponentColumns[3][1]
data.frame(pidComponentColumns)
testDF <- data.frame(pidComponentColumns)
View(testDF)
rm(testDF)
pidComponentColumns[1]
pidComponentColumns[1][1]
pidComponentColumns[[1]][1]
pidComponentColumns
class(pidComponentColumns)
length(pidComponentColumns)
?strsplit
testDF <- data.frame(pidComponentColumns)
View(testDF)
library(reshape)
library(tidyverse)
?t
testDF <- t(testDF)
?cbind
pidComponentList <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentsDF <- t(data.frame(pidComponentList))
propertyInfoTableCopy <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]))
View(propertyInfoTableCopy)
row.names(propertyInfoTableCopy) <- NULL
View(propertyInfoTableCopy)
rm(propertyInfoTableCopy)
propertyInfoTable <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]))
row.names(propertyInfoTable) <- NULL
View(propertyInfoTable)
colnames(propertyInfoTable)[5] <- "mapping_column"
View(propertyInfoTable)
?left_join
View(taxInfoTable)
View(propertyInfoTable)
totalInfoTable <- left_join(taxInfoTable, propertyInfoTable, by = c("PROP_ID" = "mapping_column"))
str(propertyInfoTable)
?data.frame
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
for(file in taxFileVector){
taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames,
startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets,
columnsToRetain = propertyInfoColumnsToRetain)
taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
columnsToRetain = taxInfoColumnsToRetain)
#COLLATING THE PROPERTY AND TAX INFO TABLES
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)
#REMOVING ANY DUPLICATIONS, IF THEY EXIST
propertyInfoTable <- distinct(propertyInfoTable, PROPERTYID, OWNERNAMES, STREETADDRESS1, WARDNAME)
taxInfoTable <- distinct(taxInfoTable, PROP_ID, CHALLAN_ID, CHALLAN_DATE, CHALLAN_AMOUNT)
pidComponentList <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentsDF <- t(data.frame(pidComponentList, stringsAsFactors = FALSE))
str(pidComponentsDF)
View(pidComponentsDF)
rm(pidComponentList)
propertyInfoTable <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]))
row.names(propertyInfoTable) <- NULL
colnames(propertyInfoTable)[5] <- "mapping_column"
str(propertyInfoTable)
View(pidComponentsDF)
str(pidComponentsDF)
?as.data.frame
propertyInfoTable <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]), stringsAsFactors = FALSE)
View(propertyInfoTable)
str(propertyInfoTable)
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
for(file in taxFileVector){
taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames,
startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}
#RETAINING ONLY THE REQUIRED COLUMNS
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets,
columnsToRetain = propertyInfoColumnsToRetain)
taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
columnsToRetain = taxInfoColumnsToRetain)
#COLLATING THE PROPERTY AND TAX INFO TABLES
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)
#REMOVING ANY DUPLICATIONS, IF THEY EXIST
propertyInfoTable <- distinct(propertyInfoTable, PROPERTYID, OWNERNAMES, STREETADDRESS1, WARDNAME)
taxInfoTable <- distinct(taxInfoTable, PROP_ID, CHALLAN_ID, CHALLAN_DATE, CHALLAN_AMOUNT)
pidComponentList <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentsDF <- t(data.frame(pidComponentList, stringsAsFactors = FALSE))
rm(pidComponentList)
propertyInfoTable <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]), stringsAsFactors = FALSE)
row.names(propertyInfoTable) <- NULL
colnames(propertyInfoTable)[5] <- "mapping_column"
propertyInfoTable[, "mapping_columns"] <- as.numeric(as.character(propertyInfoTable[, "mapping_columns"]))
propertyInfoTable[, "mapping_columns"] <- as.numeric(as.character(propertyInfoTable[, "mapping_column"]))
View(taxInfoTable)
View(propertyInfoTable)
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
for(file in taxFileVector){
taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames,
startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}
#RETAINING ONLY THE REQUIRED COLUMNS
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets,
columnsToRetain = propertyInfoColumnsToRetain)
taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
columnsToRetain = taxInfoColumnsToRetain)
#COLLATING THE PROPERTY AND TAX INFO TABLES
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)
#REMOVING ANY DUPLICATIONS, IF THEY EXIST
propertyInfoTable <- distinct(propertyInfoTable, PROPERTYID, OWNERNAMES, STREETADDRESS1, WARDNAME)
taxInfoTable <- distinct(taxInfoTable, PROP_ID, CHALLAN_ID, CHALLAN_DATE, CHALLAN_AMOUNT)
pidComponentList <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentsDF <- t(data.frame(pidComponentList, stringsAsFactors = FALSE))
rm(pidComponentList)
propertyInfoTable <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]), stringsAsFactors = FALSE)
row.names(propertyInfoTable) <- NULL
colnames(propertyInfoTable)[5] <- "mapping_column"
propertyInfoTable[, "mapping_column"] <- as.character(propertyInfoTable[, "mapping_column"])
View(taxInfoTable)
View(propertyInfoTable)
totalInfoTable <- left_join(taxInfoTable, propertyInfoTable, by = c("PROP_ID" = "mapping_column"))
taxInfoTable[, "PROP_ID"] <- as.character(taxInfoTable[, "PROP_ID"])
totalInfoTable <- left_join(taxInfoTable, propertyInfoTable, by = c("PROP_ID" = "mapping_column"))
View(totalInfoTable)
View(totalInfoTable)
View(totalInfoTable)
View(taxInfoTable)
View(propertyInfoTable)
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Utilities.R')
source('C:/Users/Admin/Desktop/ICST Property Tax/06. New ULBs/02. Kolar/02. Parser/KLRParser/Configurations.R')
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
for(file in taxFileVector){
taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames,
startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}
#RETAINING ONLY THE REQUIRED COLUMNS
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets,
columnsToRetain = propertyInfoColumnsToRetain)
taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
columnsToRetain = taxInfoColumnsToRetain)
#COLLATING THE PROPERTY AND TAX INFO TABLES
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)
#REMOVING ANY DUPLICATIONS, IF THEY EXIST
propertyInfoTable <- distinct(propertyInfoTable, PROPERTYID, OWNERNAMES, STREETADDRESS1, WARDNAME)
taxInfoTable <- distinct(taxInfoTable, PROP_ID, CHALLAN_ID, CHALLAN_DATE, CHALLAN_AMOUNT)
View(taxInfoTable)
pidComponentList <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentsDF <- t(data.frame(pidComponentList, stringsAsFactors = FALSE))
rm(pidComponentList)
propertyInfoTable <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]), stringsAsFactors = FALSE)
row.names(propertyInfoTable) <- NULL
colnames(propertyInfoTable)[5] <- "mapping_column"
propertyInfoTable[, "mapping_column"] <- as.character(propertyInfoTable[, "mapping_column"])
taxInfoTable[, "PROP_ID"] = as.character(taxInfoTable[, "PROP_ID"])
View(taxInfoTable)
library(readxl)
library(dplyr)
library(RMySQL)
source("Configurations.R")
source("Utilities.R")
Sys.setlocale("LC_ALL", "en_US.UTF8")
#READING FILES IN THE INPUT DIRECTORY
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
for(file in taxFileVector){
taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames,
startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}
#RETAINING ONLY THE REQUIRED COLUMNS
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets,
columnsToRetain = propertyInfoColumnsToRetain)
taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
columnsToRetain = taxInfoColumnsToRetain)
#COLLATING THE PROPERTY AND TAX INFO TABLES
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)
#REMOVING ANY DUPLICATIONS, IF THEY EXIST
propertyInfoTable <- distinct(propertyInfoTable, PROPERTYID, OWNERNAMES, STREETADDRESS1, WARDNAME)
taxInfoTable <- distinct(taxInfoTable, PROP_ID, CHALLAN_ID, CHALLAN_DATE, CHALLAN_AMOUNT)
View(taxInfoTable)
pidComponentList <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentsDF <- t(data.frame(pidComponentList, stringsAsFactors = FALSE))
rm(pidComponentList)
propertyInfoTable <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]), stringsAsFactors = FALSE)
row.names(propertyInfoTable) <- NULL
colnames(propertyInfoTable)[5] <- "mapping_column"
propertyInfoTable[, "mapping_column"] <- as.character(propertyInfoTable[, "mapping_column"])
View(propertyInfoTable)
View(taxInfoTable)
taxInfoTableCopy <- taxInfoTable
taxInfoTableCopy[, "PROP_ID"] = as.character(taxInfoTableCopy$PROP_ID)
View(taxInfoTableCopy)
str(taxInfoTableCopy)
rm(taxInfoTableCopy)
taxInfoTable[, "PROP_ID"] = as.character(taxInfoTable$PROP_ID)
View(taxInfoTable)
totalInfoTable <- left_join(taxInfoTable, propertyInfoTable, by = c("PROP_ID" = "mapping_column"))
View(totalInfoTable)
library(readxl)
library(dplyr)
library(RMySQL)
source("Configurations.R")
source("Utilities.R")
Sys.setlocale("LC_ALL", "en_US.UTF8")
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
propertyFileVector
taxFileVector
source("Utilities.R")
source("Utilities.R")
source("Utilities.R")
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
propertyFileVector
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
propertyFileVector
for(file in propertyFileVector){
print(file)
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
?print
utf8sampleFile.csv
sampleTable <- totalInfoTable[1:5, ]
#SETTING UP DEPENDENCIES
library(readxl)
library(dplyr)
library(RMySQL)
source("Configurations.R")
source("Utilities.R")
Sys.setlocale("LC_ALL", "en_US.UTF8")
#READING FILES IN THE INPUT DIRECTORY
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
for(file in taxFileVector){
taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames,
startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}
#RETAINING ONLY THE REQUIRED COLUMNS
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets,
columnsToRetain = propertyInfoColumnsToRetain)
taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
columnsToRetain = taxInfoColumnsToRetain)
#COLLATING THE PROPERTY AND TAX INFO TABLES
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)
#REMOVING ANY DUPLICATIONS, IF THEY EXIST
propertyInfoTable <- distinct(propertyInfoTable, PROPERTYID, OWNERNAMES, STREETADDRESS1, WARDNAME)
taxInfoTable <- distinct(taxInfoTable, PROP_ID, CHALLAN_ID, CHALLAN_DATE, CHALLAN_AMOUNT)
#CREATING MAPPING COLUMN TO MAP THE PROPERTY AND TAX INFO SHEET
pidComponentList <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentsDF <- t(data.frame(pidComponentList, stringsAsFactors = FALSE))
rm(pidComponentList)
propertyInfoTable <- as.data.frame(cbind(propertyInfoTable, pidComponentsDF[, 4]), stringsAsFactors = FALSE)
row.names(propertyInfoTable) <- NULL
colnames(propertyInfoTable)[5] <- "mapping_column"
propertyInfoTable[, "mapping_column"] <- as.character(propertyInfoTable[, "mapping_column"])
taxInfoTable[, "PROP_ID"] = as.character(taxInfoTable$PROP_ID)
#OBTAINING THE COMPLETE INFORMATION BY JOINING THE TABLES
totalInfoTable <- left_join(taxInfoTable, propertyInfoTable, by = c("PROP_ID" = "mapping_column"))
#CHECKING UNMATCHED VALUES AND WRITING THEM TO A LOG FILE
unmatchedRows <- filter(totalInfoTable, is.na(Owner_F_Name), is.na(Owner_M_Name),
is.na(Owner_L_Name), is.na(Address))
write.csv(unmatchedRows, "UnmatchedValuesLog.csv")
#CONVERTING TABLE TO REQUIRED FORM
totalInfoTable <- totalInfoTable %>%
mutate(sl_no = 1:nrow(totalInfoTable),
app_no = paste(PID, Rvn_reference_no, AssessmentYear, sep = "_"),
owner_name = paste(Owner_F_Name, Owner_M_Name, Owner_L_Name, sep = " ")) %>%
select(sl_no, PID, owner_name, Address, AssessmentYear, app_no,
Tax, Cess, Penalty, SolidCess,TotalAmount) %>%
rename(year = AssessmentYear, amount = TotalAmount)
rm(propertyInfoTable)
rm(taxInfoTable)
#CONVERTING TO UTF 8
totalInfoTable[, "owner_name"] = iconv(totalInfoTable[, "owner_name"], to = "UTF-8")
totalInfoTable[, "Address"] = iconv(totalInfoTable[, "Address"], to = "UTF-8")
#WRITING THE TABLE
write.csv(totalInfoTable, "TMK_NewData.csv", row.names = FALSE)
write.csv(totalInfoTable[1:15, ], "TMK_NewData_Sample.csv", row.names = FALSE)
#VALIDATING IF NON ENGLISH CHARACTERS ARE PROPERLY READ
print(totalInfoTable$owner_name[1])
#WRITING SAMPLE CSV
sampleTable <- totalInfoTable[1:5, ]
sampleTable[, "owner_name"] = iconv(sampleTable[, "owner_name"], to = "UTF-8")
write.csv(sampleTable, "utf8sampleFile.csv", row.names = FALSE)
#CONNECTING TO THE MYSQL DB
sqlDB = dbConnect(MySQL(), user = "root", password = "gC6icstvvzzvk", dbname = "test_evs",
host = "14.139.134.252", port = 22)
#, port = 22
dbListTables(sqlDB)
#SETTING UP DEPENDENCIES
library(readxl)
library(dplyr)
library(RMySQL)
source("Configurations.R")
source("Utilities.R")
Sys.setlocale("LC_ALL", "en_US.UTF8")
#READING FILES IN THE INPUT DIRECTORY
propertyFileVector <- listFiles(propertyInfoInputFolder, fileExtension)
taxFileVector <- listFiles(taxInfoInputFolder, fileExtension)
#ITERATING THROUGH THE SHEETS AND READING PROPERTY AND TAX INFO
for(file in propertyFileVector){
propertyInfoSheets <- readTables(filePath = file, sheetNames = propertyInfoSheetNames,
startRow = propertyInfoStartRow,
headerPresent = propertyInfoHeaderPresent)
}
for(file in taxFileVector){
taxInfoSheets <- readTables(filePath = file, sheetNames = taxInfoSheetNames,
startRow = taxInfoStartRow, headerPresent = taxInfoHeaderPresent)
}
#RETAINING ONLY THE REQUIRED COLUMNS
propertyInfoSheets <- dropColumns(tableList = propertyInfoSheets,
columnsToRetain = propertyInfoColumnsToRetain)
taxInfoSheets <- dropColumns(tableList = taxInfoSheets,
columnsToRetain = taxInfoColumnsToRetain)
#COLLATING THE PROPERTY AND TAX INFO TABLES
propertyInfoTable <- collateTables(tableList = propertyInfoSheets)
rm(propertyInfoSheets)
taxInfoTable <- collateTables(tableList = taxInfoSheets)
rm(taxInfoSheets)
#REMOVING ANY DUPLICATIONS, IF THEY EXIST
propertyInfoTable <- distinct(propertyInfoTable, PROPERTYID, OWNERNAMES, STREETADDRESS1, WARDNAME)
taxInfoTable <- distinct(taxInfoTable, PROP_ID, CHALLAN_ID, CHALLAN_DATE, CHALLAN_AMOUNT)
#CREATING MAPPING COLUMN TO MAP THE PROPERTY AND TAX INFO SHEET
pidComponentList <- strsplit(propertyInfoTable$PROPERTYID, "-")
pidComponentsDF <- t(data.frame(pidComponentList, stringsAsFactors = FALSE))
library(readxl)
library(dplyr)
library(RMySQL)
source("Configurations.R")
source("Utilities.R")
Sys.setlocale("LC_ALL", "en_US.UTF8")
