9923170071 / 8108094992 info@dimensionless.in

Data Cleaning, categorization and normalization is the most important step towards the data. Data that is captured is generally dirty and is unfit for statistical analysis. It has to be first cleaned, standardized, categorized and normalized, and then explored.

Definition of Clean Data

Happy families are all alike; every unhappy family is unhappy in its own way – Leo Tolstoy

Like families, clean datasets are all alike but every messy dataset is unreadable by our modeling algorithms.Clean datasets provide a standardized way to link the structure of a dataset with its semantics.

We will take the following text file with dirty data:

%% Data
Sonu ,1861, 1892, male
Arun , 1892, M
1871, Monica, 1937, Female
1880, RUCHI, F
Geetu, 1850, 1950, fem
BaLa, 1893,1863
% Names, birth dates, death dates, gender

Let us start with tidying the above data. We’ll adopt following steps for the same:

  1. Read the unclean data from the text file and analyse the structure, content and quality of data.

The following functions lets us read the data that is technically correct or close to it:

  • read.table
  • read.csv
  • read.csv2
  • read.delim
  • read.delim2

When the rows in the data file are not uniformly formatted, we can consider reading in the text line-by-line and transforming the data to rectangular text ourself.

## [1] "%% Data  "                                
## [2] "  Sonu  ,1861,  1892, male "              
## [3] "Arun  ,  1892, M"                         
## [4] "1871,  Monica, 1937, Female"              
## [5] "1880, RUCHI, F"                           
## [6] "Geetu, 1850, 1950, fem"                   
## [7] "BaLa, 1893,1863"                          
## [8] "% Names, birth dates, death dates, gender"
## [1] "character"
##  chr [1:8] "%% Data  " "  Sonu  ,1861,  1892, male " ...

The variable txt is a vercot of type “character” having 8 elements, equal to number of lines in our txt file.

  1. Delete the irrelevant / duplicate data. This improves data protection, increases the speed of processing and reduces the overall costs.

In our eg., we will delete the comments from the data. Comments are the lines followed by “%” sign using following code.

Following is the code:

i=grep("%", txt)
txt=txt[-i]
txt
## [1] "  Sonu  ,1861,  1892, male " "Arun  ,  1892, M"           
## [3] "1871,  Monica, 1937, Female" "1880, RUCHI, F"             
## [5] "Geetu, 1850, 1950, fem"      "BaLa, 1893,1863"
  1. Split lines into seperate fields. This can be done using strsplit function:
fields=strsplit(txt,",")
fields
## [[1]]
## [1] "  Sonu  " "1861"     "  1892"   " male "  
## 
## [[2]]
## [1] "Arun  " "  1892" " M"    
## 
## [[3]]
## [1] "1871"     "  Monica" " 1937"    " Female" 
## 
## [[4]]
## [1] "1880"   " RUCHI" " F"    
## 
## [[5]]
## [1] "Geetu" " 1850" " 1950" " fem" 
## 
## [[6]]
## [1] "BaLa"  " 1893" "1863"
fields[[1]][2]
## [1] "1861"
class(fields)
## [1] "list"

Here, txt was a vector of type characters, while, after splitting we have our output stored in the variable “fields” which is of “list” type.

  1. Standardize and categorize fields

It is a crucial process where the data is defined, formatted, represented and structured in all data layers. Development of schema or attributes is involved in this process.

The goal of this step is to make sure every row has same number of fields, and the fields are in the same order. In read.table command, any fields that are less than the maximum number of fields in a row get appended by NA. One advantage of do-it-yourself approach is that we don’t have to make this assumption. The easiest way to standardize rows is to write a function that takes a single character vector as input, and assigns the values in the right order.

Categorize <- function(x)
{ 
  out <- character(length(x))
  i<-grep("[[:alpha:]]",x) 
  out[1] <- x[i[1]] 
  out[4] = ifelse(length(i)==2, x[i[2]], NA)
  i <- which(as.numeric(x) < 1890) 
  out[2] <- ifelse(length(i)>0, x[i], NA) 
  i <- which(as.numeric(x) > 1890) 
  out[3] <- ifelse(length(i)>0, x[i], NA)
  return(out) 
}

The above function takes each line in the txt as the input (as x). The function returns a vector of class character in a standard order: Name, Birth Year, Death Year, Gender. Where-ever, the field will be missing, NA will be introduced. The grep statement is used to get the location of alphabetical characters in our input variable x. There are 2 kinds of alphabetical characters in our input string, one representing name and other representing sex. Both are accordingly assigned in the out vector at 1st and 4th positions. The year of birth and year of death can be recognized as “less than 1890” or “greater than 1890” respectively.

To retrieve the fields for each row in the example, we need to apply this function to every row of fields.

stdfields=lapply(fields, Categorize)

stdfields
## [[1]]
## [1] "  Sonu  " "1861"     "  1892"   " male "  
## 
## [[2]]
## [1] "Arun  " NA       "  1892" " M"    
## 
## [[3]]
## [1] "  Monica" "1871"     " 1937"    " Female" 
## 
## [[4]]
## [1] " RUCHI" "1880"   NA       " F"    
## 
## [[5]]
## [1] "Geetu" " 1850" " 1950" " fem" 
## 
## [[6]]
## [1] "BaLa"  "1863"  " 1893" NA

Our Categorize function here is quite fragile, it crashes for eg. when our input vector contains 3 or more character fields. Only the data analyst should determine how generalized should our categorize function be.

  1. Transform to Data.Frame type

First we will copy the elements of list to a matrix which is then coerced into a data-frame.

a=unlist(stdfields)
M=matrix(a, nrow=length(stdfields), byrow=TRUE)
colnames(M)=c("Name", "Birth_Date", "Death_Date","Gender")
M
##      Name       Birth_Date Death_Date Gender   
## [1,] "  Sonu  " "1861"     "  1892"   " male " 
## [2,] "Arun  "   NA         "  1892"   " M"     
## [3,] "  Monica" "1871"     " 1937"    " Female"
## [4,] " RUCHI"   "1880"     NA         " F"     
## [5,] "Geetu"    " 1850"    " 1950"    " fem"   
## [6,] "BaLa"     "1863"     " 1893"    NA
dat=as.data.frame(M, stringsAsFactors = FALSE)
dat
##       Name Birth_Date Death_Date  Gender
## 1   Sonu         1861       1892   male 
## 2   Arun         <NA>       1892       M
## 3   Monica       1871       1937  Female
## 4    RUCHI       1880       <NA>       F
## 5    Geetu       1850       1950     fem
## 6     BaLa       1863       1893    <NA>

Here, we have made a matrix by row. The number of rows in the matrix will be same as number of elements in stdfields. There-after we have converted the matrix in data.frame formats.

  1. Data Normalization

It is the systematic process to ensure the data structure is suitable or serves the purpose. Here the undesirable characteristics of the data are eliminated or updated to improve the consistency and the quality. The goal of this process is to reduce redundancy, inaccuracy and to organize the data.

String normalization techniques are aimed at transforming a variety of strings to a smaller set of string values which are more easily processed.

  • Remove the white spaces. We can use str_trim function from stringr library.
library(stringr)
dat=sapply(dat,str_trim)
dat=as.data.frame(dat, stringsasFactors=FALSE)
dat
##     Name Birth_Date Death_Date Gender
## 1   Sonu       1861       1892   male
## 2   Arun       <NA>       1892      M
## 3 Monica       1871       1937 Female
## 4  RUCHI       1880       <NA>      F
## 5  Geetu       1850       1950    fem
## 6   BaLa       1863       1893   <NA>

sapply function will return object of type matrix. So we again recovert it into data-frame type

  • Converting all the letters in the column “Name” to upper-case for standardization
dat$Name=toupper(dat$Name)
dat
##     Name Birth_Date Death_Date Gender
## 1   SONU       1861       1892   male
## 2   ARUN       <NA>       1892      M
## 3 MONICA       1871       1937 Female
## 4  RUCHI       1880       <NA>      F
## 5  GEETU       1850       1950    fem
## 6   BALA       1863       1893   <NA>
  • Normalize the gender variable We have to normalize the gender variable. It is in 5 different formats. Following are the two ways to normalize gender variable:

One is using ^ operator. This will find the words in the Gender column which begin with m and f respectively.

Following is the code:

datf=dat #Making a copy of the dataframe
locm=grep("^m", dat$Gender, ignore.case = TRUE)
dat$Gender[locm]="M"
locf=grep("^f", dat$Gender, ignore.case = TRUE)
dat$Gender[locf]="F"
dat
##     Name Birth_Date Death_Date Gender
## 1   SONU       1861       1892      M
## 2   ARUN       <NA>       1892      M
## 3 MONICA       1871       1937      F
## 4  RUCHI       1880       <NA>      F
## 5  GEETU       1850       1950      F
## 6   BALA       1863       1893   <NA>

There is another method of approximate string matching using string distances. A string distance measures how much 2 strings differ from each other. It measures how many operations are required to turn one string to another. Eg.

adist("pqr","qpr")
##      [,1]
## [1,]    2

So, here there are 2 operations required to convert pqr to qpr

  • replace q: pqr -> ppr
  • replace p: ppr -> qpr

We’ll use adist function in the gender column in the following manner:

codes=c("male", "female")
D=adist(datf$Gender, codes)
D
##      [,1] [,2]
## [1,]    0    2
## [2,]    4    6
## [3,]    2    1
## [4,]    4    6
## [5,]    4    3
## [6,]   NA   NA

First is male – 0 replacement away from “male” and 2 replacements away from “female”.
Second is M is 4 replacements away from “male” and 6 replacements away from “female”.
Third is Female 2 replacements away from “male” and 1 replacement away from “female”.

We can use which.min() function on each row to get minimum distance from the codes.

i=apply(D,1,which.min)
i
## [[1]]
## [1] 1
## 
## [[2]]
## [1] 1
## 
## [[3]]
## [1] 2
## 
## [[4]]
## [1] 1
## 
## [[5]]
## [1] 2
## 
## [[6]]
## integer(0)

i contains the column number where the distance is minimum from our codes. If the distance from column number 1 is minimum, then we will substitute gender with code[1], and if the distance from column number 2 is minimum, then we will substitute the gender with code[2]. Following is the code:

i[[6]]=0
datf$Gender=as.character(datf$Gender)
for(j in 1:nrow(datf))
{
  datf$Gender[j]=ifelse(i[[j]]==1, codes[1],(ifelse(i[[j]]==2, codes[2], NA)))
}
datf
##     Name Birth_Date Death_Date Gender
## 1   SONU       1861       1892   male
## 2   ARUN       <NA>       1892   male
## 3 MONICA       1871       1937 female
## 4  RUCHI       1880       <NA>   male
## 5  GEETU       1850       1950 female
## 6   BALA       1863       1893   <NA>
  • Normalize the data-types

Let’s retreive the classes of each column in data-frame dat:

sapply(dat, class)
##        Name  Birth_Date  Death_Date      Gender 
## "character"    "factor"    "factor"    "factor"

We will convert Birth and Death columns to Numeric data-types using transform function.

dat=transform(datf, Birth_Date=as.numeric(as.character(Birth_Date)), Death_Date=as.numeric(as.character(Death_Date)))
dat
##     Name Birth_Date Death_Date Gender
## 1   SONU       1861       1892   male
## 2   ARUN         NA       1892   male
## 3 MONICA       1871       1937 female
## 4  RUCHI       1880         NA   male
## 5  GEETU       1850       1950 female
## 6   BALA       1863       1893   <NA>

The data as represented in dat dataframe is clean, standardized, categorized and normalized.

Cleaning the Date Variable

When we talk to data cleaning, we can’t miss cleaning and normalizing the date variable. Date variable mostly is generally keyed in by different people in different formats. So, we always face the problem in standardizing it.

In the following example, we’ll standardize and normalize the date variable:

date=c("140283", "14081977", "1963", "19-09-1984", "19 1 85", "15-Jan-80", "19-2-90", "20-02-83", "19-Dec-1990","2 Jan 1995", "15/Jul/1982", "90-Dec-15", "1985December24","1983Jan1")

We notice the date variable is in mixed format. We will first substitute “/” and ” ” in the date variable with “-”, in our attempt to standardize dates.

In the below code, we will make the assumption that month can’t be in the first-2 or last-2 characters. The month in all cases comes in the middle of date and year; or year and date.

date=gsub("/","-", date)
date=gsub(" ","-", date)
date
##  [1] "140283"         "14081977"       "1963"           "19-09-1984"    
##  [5] "19-1-85"        "15-Jan-80"      "19-2-90"        "20-02-83"      
##  [9] "19-Dec-1990"    "2-Jan-1995"     "15-Jul-1982"    "90-Dec-15"     
## [13] "1985December24" "1983Jan1"

Next, we will write a function to find the first-two digits of each date:

  • Split the date string and store the output in dt. Eg. “110798” to dt = ‘1’ ‘1’ ‘0’ ‘7’ ‘9’ ‘8’
  • If dt[2]=“-”, then first_2 numbers will be 0 and dt[1]
  • We will convert the first_2 characters to numeric type.
find_first_2=function(date)
{
  dt=unlist(strsplit(date,""))
  if(dt[2]=="-")
  {
    first_2=c(0,dt[1])
    date=paste("0", date)
    date=gsub(" ","", date)
    dt=unlist(strsplit(date,""))
  } else 
    first_2=dt[1:2]
  
  first_2=as.numeric(first_2)
  first_2=first_2[1]*10+first_2[2]
  return(first_2)
}

Next, we will write a function to find the last-2 digits of each date:

  • Split the date string and store the output in dt. Eg. “110798” to dt = ‘1’ ‘1’ ‘0’ ‘7’ ‘9’ ‘8’
  • If dt[2]=“-”, then first_2 numbers will be 0 and dt[1]
  • We will convert the first_2 characters to numeric type.
find_last_2=function(date)
{
  dt=unlist(strsplit(date,""))
  last_2=dt[(length(dt)-1):length(dt)]
  #if 2nd last character is alphabetical, then it's a single digit date
  loc=grep("[[:alpha:]]", last_2)
  #if 2nd last character is "-", then it is a single digit date
  l=grep("-", last_2) 
  if((length(loc)==1) | (length(l)==1))
  #This indicates that there is a single digit date in the end
  {
    last_2=c("0", dt[length(dt)])
    dd[i]=dt[length(dt)]
  }
  last_2=as.numeric(last_2)
  last_2=last_2[1]*10+last_2[2]
  return(last_2)
}

Middle 2 digits can be alphabetic or numeric. We will write the function to find the middle 2 characters. We will adopt following steps to reach the middle characters:

  • Split the date string and store the output in dt. Eg. “110798” to dt = ‘1’ ‘1’ ‘0’ ‘7’ ‘9’ ‘8’
  • If dt[3]=“-”, then, dt[4:5] are the middle characters. But, if dt[5] is a “-”, then, dt[4] (single digit) is the middle character.
  • If dt[3]!=“-”, then, dt[3:4] are the middle characters. But, if dt[4] is a “-”, then, dt[3] (single digit)is the middle character.
  • If the middle character is a single digit, then we need to append 0 to the single digit, to return a 2 digit middle character.
  • Later in the code, we will evaluate if the middle character is an alphabetic or numeric, and process accordingly.
find_middle=function(date)
{
  dt=unlist(strsplit(date,""))
  #if 3rd character is -, then middle numbers start from 4th else from 5th
  if(dt[3]=="-")
  {   
    middle=dt[4:5]
    #if dt[5]="-", then month is a single digit number - so we'll paste 0 to it
    if(dt[5]=="-") 
      middle=c("0",dt[4])
  } else
  { 
    middle=dt[3:4]
    if(dt[4]=="-")#if month is a single digit number
      middle=c("0", dt[3])
  }
  return(middle)
}

Next we will initialize variables before applying a for-loop to process and segregate the date, month and year for each date field.

  • df is a dummy data.frame object.
  • datf will be the final data frame object which will comprise of fields dd, mm, year, month format (i.e numeric or alphabetical) and concatenated date in the standard format.
  • snum refers to serial number of each date in the vector “date”.
  • We will keep the default date to be 01, default month to be 01 and default year to be 1990 incase of missing fields in these variables.
  • Mon_Format refers to whether month is in numeric or alphabetical format.
  • cat_date field contains date, month and year concatenated.
  • format_date will contain the cat_date converted to “date” format.
snum=c(1:length(date))
year=rep("90",length(date)) 
mm=rep("01", length(date))
dd=rep("01", length(date))
mon_format=rep("num", length(date))
cat_date=rep("00", length(date))
formatted_date=rep(as.Date("01-01-91","%d-%m-%y"),length(date))
df=data.frame()
datf=data.frame()

Now, for each date field, we will find the first-two numbers, the last-two numbers and the middle characters. Then we’ll apply following rules:

  • If number of characters in our date is 4, then this represents the year.
  • If first_2>31, then the first-two numbers represent the year.
  • If first_2<31, then, first_2 represents the date.
  • If 2nd number is a “-”, then 1st number represents the single digit date.
  • If last_2>31, then the last-two numbers represent the year.
  • If last_2<31, then the last-two numbers represent the date.
  • If the 2nd last number is a “-”, then the last number represents the single digit date.
  • If middle characters are non-alphabetical (i.e if middle characters are numeric), then mid-num represents the numeric middle characters. Mon_Format will be labelled numeric in such case.
  • If mid-num>31, then mid-num represents the year, else if mid-num<12, then mid-num represents the month.
  • If the date has an alphabetical character, then the first 3 alphabets represent the month. The Mon_Format will be alphabetical in this case.
  • Next we will concatenate the date in following format: dd-mm-yy, where mm can be numeric (with mon_format as “num”) or alphabetic (with mon_format as “alpha”) and store into variable “cat_date”.
  • We will change the format of cat_date to “date” data-type and store in the variable “formatted_date”.

Following is the code:

for(i in 1:length(date))
{
  dt=date[i]
  dt=unlist(strsplit(dt, ""))
  if(length(dt)==4)
  #if length is 4, then this number represents an year
  {  
    year[i]=substr(date[i], (length(dt)-1), length(dt))
    cat_date[i]=gsub(" ","",paste(dd[i],"-",mm[i],"-",year[i]))
    
    df=cbind(i, dd[i], mm[i], year[i], mon_format[i], cat_date[i])
    datf=rbind(datf,df)
    next()
  }
  first_2=find_first_2(date[i])  #finding the first 2 numbers
  last_2=find_last_2(date[i])  #finding the last 2 numbers
  middle=find_middle(date[i])  #finding the middle numbers
  isalpha=grep("[[:alpha:]]", middle )
  if(length(isalpha)==0) 
  #if middle numbers are not alphabetical characters - then we will process them as numeric fields
  {  
    middle=as.numeric(middle)
    mid_num=middle[1]*10+middle[2]
    if(mid_num>31) 
    #if middle numbers are >31 then middle numbers represent the year field
    {
      year[i]=mid_num
      dd[i]=last_2
    }  else if(mid_num<12) 
    #if middle numbers are < 12, then they represent the month
    {
      mm[i]=paste(as.character(middle[1]), as.character(middle[2]))
      mm[i]=gsub(" ","", mm[i])
      mid_num=as.character(middle)
      mon_format[i]="num"
    }
  } 
  if(last_2>31) 
  #if the last 2 numbers are>31 => last 2 numbers represent the year field
  {
    year[i]=last_2
    dd[i]=first_2 #note date is either first or last 2 digits
  }else if(first_2>31)
  #if first 2 numbers are >31 => first 2 numbers represent the year field
    {
      year[i]=first_2
      dd[i]=last_2
    } 
  #if the date has an alphabetical month
  loc=grep("[[:alpha:]]", dt)
  if(length(loc)!=0)
  {
    mm[i]=substr(date[i], loc[1], loc[3])
    mon_format[i]="alpha"
  }
    #if Date is a single digit number, then we'll append a 0 to it
  if(nchar(dd[i])==1)
  {
      dd[i]=paste("0", dd[i])
      dd[i]=gsub(" ", "", dd[i])
  }
  #Get the final date by pasting the date months and year columns
  cat_date[i]=paste(dd[i], "-", mm[i],"-", year[i])
  cat_date[i]=gsub(" ","", cat_date[i])
  #storing the date, month, year, concatenated date data in a data-frame
  df=cbind(i, dd[i], mm[i], year[i], mon_format[i], cat_date[i])
  datf=rbind(datf,df)
  #Converting the date to the date data-type. Vector formatted_date
  if(mon_format[i]=="alpha")
  {
    formatted_date[i]=as.Date(cat_date[i],"%d-%b-%y")
  } else if(mon_format[i]=="num")
  {
    formatted_date[i]=as.Date(cat_date[i],"%d-%m-%y")
  }
}

The vector formatted_date contains the date in the correct format.

The datal.frame datf comprises of all the entries segregated as date, month and year.

names(datf)=c("S.Num", "DD", "Month", "Year", "Month Type", "Date")
date=formatted_date
datf
##    S.Num DD Month Year Month Type      Date
## 1      1 14    02   83        num  14-02-83
## 2      2 14    08   77        num  14-08-77
## 3      3 01    01   63        num  01-01-63
## 4      4 19    09   84        num  19-09-84
## 5      5 19    01   85        num  19-01-85
## 6      6 15   Jan   80      alpha 15-Jan-80
## 7      7 19    02   90        num  19-02-90
## 8      8 20    02   83        num  20-02-83
## 9      9 19   Dec   90      alpha 19-Dec-90
## 10    10 02   Jan   95      alpha 02-Jan-95
## 11    11 15   Jul   82      alpha 15-Jul-82
## 12    12 15   Dec   90      alpha 15-Dec-90
## 13    13 24   Dec   85      alpha 24-Dec-85
## 14    14 01   Jan   83      alpha 01-Jan-83
date 
##  [1] "1983-02-14" "1977-08-14" "1991-01-01" "1984-09-19" "1985-01-19"
##  [6] "1980-01-15" "1990-02-19" "1983-02-20" "1990-12-19" "1995-01-02"
## [11] "1982-07-15" "1990-12-15" "1985-12-24" "1983-01-01"
class(date)
## [1] "Date"

We note, that date vector now contains all the dates in the standard format. The class of variable date is “date”.

I would like to conclude this article by:

“Bad Data leads to Bad Decisions”

“Good Data improves Efficient Decisions”

Also read:
Data Exploration and Uni-Variate Analysis
Bi-Variate Analysis

Share
Share
Social Media Auto Publish Powered By : XYZScripts.com