Many Data Quality Formats give problems when importing in your statistical software.A statistical software is quite unable to distingush between $1,000, 1000% and 1,000 and 1000 and will treat the former three as character variables while the third as a numeric variable by default. This issue is further compounded by the numerous ways we can represent date-time variables.
The good thing is for specific domains like finance and web analytics, even these weird data input formats are fixed, so we can fix up a list of handy data quality conversion functions in R for reference.
After much muddling about with coverting internet formats (or data used in web analytics) (mostly time formats without date like 00:35:23) into data frame numeric formats, I found that the way to handle Date-Time conversions in R is
Dataset$Var2= strptime(as.character(Dataset$Var1),”%M:%S”)
The problem with this approach is you will get the value as a Date Time format (02/31/2012 04:00:45- By default R will add today’s date to it.) while you are interested in only Time Durations (4:00:45 or actually just the equivalent in seconds).
this can be handled using the as.difftime function
dataset$Var2=as.difftime(paste(dataset$Var1))
or to get purely numeric values so we can do numeric analysis (like summary)
dataset$Var2=as.numeric(as.difftime(paste(dataset$Var1)))
(#Maybe there is a more elegant way here- but I dont know)
The kind of data is usually one we get in web analytics for average time on site , etc.
and
for factor variables
Dataset$Var2= as.numeric(as.character(Dataset$Var1))
or
Dataset$Var2= as.numeric(paste(Dataset$Var1))
Slight problem is suppose there is data like 1,504 – it will be converted to NA instead of 1504
The way to solve this is use the nice gsub function ONLy on that variable. Since the comma is also the most commonly used delimiter , you dont want to replace all the commas, just only the one in that variable.
dataset$Variable2=as.numeric(paste(gsub(“,”,””,dataset$Variable)))
Now lets assume we have data in the form of % like 0.00% , 1.23%, 3.5%
again we use the gsub function to replace the % value in the string with (nothing).
dataset$Variable2=as.numeric(paste(gsub(“%”,””,dataset$Variable)))
If you simply do the following for a factor variable, it will show you the level not the value. This can create an error when you are reading in CSV data which may be read as character or factor data type.
Dataset$Var2= as.numeric(Dataset$Var1)
An additional way is to use substr (using substr( and concatenate (using paste) for manipulating string /character variables.
iris$sp=substr(iris$Species,1,3) –will reduce the famous Iris species into three digits , without losing any analytical value.
The other issue is with missing values, and na.rm=T helps with getting summaries of numeric variables with missing values, we need to further investigate how suitable, na.omit functions are for domains which have large amounts of missing data and need to be treated.
DECISION STATS 