Exploratory Analysis of the Electricity Consumption and Tariffs
This article covers the Exploratory Analysis of the Electricity Consumption and Tariffs. A sample set of service numbers was downloaded and analyzed. Each service number is made up of three different parts
1) Section Code 2) Distribution Code 3) Service Number
This analysis requires the following R libraries XML and RCurl
The following are the variables that are included for the analysis. Most of the variables are self explanatory.
AsmDate : Indicates the Assessment Date AsmEntryDate : Indicates the Assessment Entry Date Reading : Indicates the Current Reading Consumed Unit : Indicates the Consumed Unit based on the last Reading CC_Charges : Indicates the Current Consumption Charges* (Rs.) Others (Rs.) AssessedAmount(Rs.) AdvanceAmount(Rs.) AdjustmentAmount(Rs.) TotBillAmount : Indicates the Total BillAmount(Rs.) Due Date For payment Amount Paid Payment Date Pending AmtTo be Paid AssessmentStatus
Due to confidentiality of Service Numbers, the R code does not display them. The following variables need to be set XX, XXX, YYY, ZZZBegin, ZZZEnd with logical values for the code to function.
library(RCurl); library(XML); sec = "XXX"; dist = "YYY"; startserno = ZZZBegin; endserno = ZZZEnd; arrCount = rep(NA,150); arrSer = rep(NA,150); rowno = 1; XX = 99; for (serno in startserno :endserno ) { fileName = paste(XX,sec,dist,as.character(serno),".html"); fileName = sub(" ","",fileName); fileName = sub(" ","",fileName); fileName = sub(" ","",fileName); fileName = sub(" ","",fileName); fileName = (sub(" 99",XX,paste(".\\ElectData\\",fileName)));
#The consumption and tariff details are found embedded in the web page. The web page is read through the readHTMLTable command and the corresponding table is referenced which is of interest area. Each of the web page is read and data is consolidated into a single dataset
tables = readHTMLTable(fileName);
# It is observed that the dataset required for analysis has > 16 columns and is a consistent observation across the files and hence looping through the tables to identify the corresponding dataset. It is observed the required dataset does not appear in a fixed position.
for(k in 2:length(tables)) { if(dim(tables[[k]])[2] >= 16) { cDetailsCopy = tables[[k]]; break; } }
arrCount[rowno] = dim(cDetailsCopy)[1]-1; arrSer[rowno] = serno; cDetailsCopy$serno = serno;
for(i in 1:dim(cDetailsCopy)[2]) { cDetailsCopy[,i] = sub("Â","",cDetailsCopy[,i]) ; cDetailsCopy[,i] = gsub("\\r","", cDetailsCopy[,i]); cDetailsCopy[,i] = gsub("\\n","", cDetailsCopy[,i]); cDetailsCopy[,i] = gsub("\\t","", cDetailsCopy[,i]); cDetailsCopy[,i] = gsub("\\s","", cDetailsCopy[,i]); } if (serno == startserno ) { cDetails = cDetailsCopy[2:dim(cDetailsCopy)[1],]; } else { cDetails = merge(cDetails,cDetailsCopy[2:dim(cDetailsCopy)[1],],all=TRUE); }
rowno = rowno +1
}
# This defines the columns of the dataset
colnames(cDetails) = c("AsmDate","AsmEntryDate","Reading", "ConsumedUnit","CC_Charges", "Other_Charges","AssessedAmount","AdvanceAmount", "AdjustmentAmount", "TotBillAmount","DueDateForPayment","AmountPaid","ReceiptNo", "PaymentDate","PendingAmtTobePaid","AssessmentStatus","serno");
# The data needs clean up in terms of removing unwanted characters
for(i in 1:dim(cDetails)[2]) { cDetails[,i] = sub("Â","",cDetails[,i]) ; }
for(i in 1:dim(cDetails)[2]) { cDetails[,i] = gsub("\\r","", cDetails[,i]); cDetails[,i] = gsub("\\n","", cDetails[,i]); cDetails[,i] = gsub("\\t","", cDetails[,i]); cDetails[,i] = gsub("\\s","", cDetails[,i]); }
# Reducing the data analysis to Assessment Date,Reading,ConsumedUnit and TotBillAmount
cDetailsRed = cDetails[cDetails$AssessmentStatus != "DEFECTIVE",] cDetailsRed = cDetailsRed[,-(5:9)] cDetailsRed = cDetailsRed[,-(6:12)] cDetailsRed = cDetailsRed[,-2]
# Previewing the reduced dataset
head(cDetailsRed)
AsmDate Reading ConsumedUnit TotBillAmount 1 02/06/2012 10 0 20 2 02/06/2012 10 0 20 3 02/06/2012 10 0 20 4 02/06/2012 10 0 20 5 02/06/2012 10 0 20 6 02/06/2012 10 0 20
# Check the data types of the dataset
sapply(cDetailsRed,class)
# Checking the dimensions of the dataset
dim(cDetailsRed)
# Normalizing the date to reflect the first of every month as the analysis is concerned with only month and year and so need a normalized julian date cDetailsRed$NAsmDate = as.Date(gsub("\\s","", paste("01/",format(cDetailsRed$AsmDate, "%m"),"/",format(cDetailsRed$AsmDate, "%Y"))) ,"%d/%m/%Y")
# Converting Consumed Unit and Reading into numeric
cDetailsRed$ConsumedUnitN = as.numeric(as.character(cDetailsRed$ConsumedUnit)) cDetailsRed$ReadingN = as.numeric(as.character(cDetailsRed$Reading))
# Assigning the Reading to the Consumed Unit wherever the Consumed Unit is 0 and Reading is non zero. This indicates the first time the Reading was done
cDetailsRed[cDetailsRed$ConsumedUnitN == 0,]$ConsumedUnitN = cDetailsRed[cDetailsRed$ConsumedUnitN == 0,]$ReadingN
# Extract the month and the year and create new columns
cDetailsRed$Year = format(cDetailsRed$NAsmDate, "%Y") cDetailsRed$Month = format(cDetailsRed$NAsmDate, "%b") cDetailsRed$juldate = julian(cDetailsRed$NAsmDate)
# Ordering the dataset by julian date
cDetailsRed = cDetailsRed[order(cDetailsRed$juldate),]
# Previewing the dataset
head(cDetailsRed)
AsmDate Reading ConsumedUnit TotBillAmount Year Month juldate NAsmDate 2709 2011-04-11 0 0 61 2011 Apr 15065 2011-04-01 2710 2011-04-11 0 0 61 2011 Apr 15065 2011-04-01 2711 2011-04-11 0 0 61 2011 Apr 15065 2011-04-01 2712 2011-04-11 0 0 61 2011 Apr 15065 2011-04-01 2713 2011-04-11 0 0 61 2011 Apr 15065 2011-04-01 2714 2011-04-11 0 0 61 2011 Apr 15065 2011-04-01
# Plotting the data distribution by Assessment Date. This is to check if the data is uniformly distributed.
plot(table(cDetailsRed$NAsmDate),ylab="Number of Observations",xlab="Assessment Dates")
# Plotting Consumption by Month. This will help to identify the months where the Consumption of Electricity is at its peak. The plot does not clearly outline the trend, its probably because of the chosen dataset.
plot(as.factor(cDetailsRed$Month),cDetailsRed$ConsumedUnitN,xlab="Month",ylab="Consumed Unit",col="blue")
# Plotting Consumption by Assessment Dates. Its interesting to observe that the Consumed Units start from 0 which indicates new move ins, there is a slow progression towards increased Consumed Unit over time. The dataset has been taken from a specific new apartment complex which explains the trend. People are moving in as the apartment gets done. Towards the left, it can be observed that the Consumed Units are very low indicating that the apartments have been handed over but they are still empty but over time the overall median for Consumed Unit is increasing.
plot(as.factor(cDetailsRed$NAsmDate),cDetailsRed$ConsumedUnitN,ylab="Consumed Unit",xaxt="n",col="blue") axis(side=1,at=1:length(unique(cDetailsRed$NAsmDate)),labels=unique(cDetailsRed$NAsmDate),las=2)
