Looking for R code that is the equivelant of an Excel formula

Question

Column A in my Excel sheet has time in text format - in hours minutes and seconds (<n>h <n>m <n>s format). I have an Excel formula that converts these values with the numeric equivalent. For eg,

• the value 1h 16m is converted to 1.27
• the value 0h 4m is converted to 0.07
• the value 24s  is converted to 0

Excel formula: =(IFERROR(LEFT(A1,SEARCH("h",A1)-1)*60,0)+IFERROR(MID(A1,SEARCH("m",A1)-2,2),0))/60

What is the R code to do the same calculation?

Answer 1

Hello,

You can try with this one :

minPerGame <- c("1:16","0:4","0:0:24")

sapply(strsplit(minPerGame,":"),
       function(x) {
         x <- as.numeric(x)
         x[1]+x[2]/60
       }
)

Best regard !

Answer 2

Thanks! To use this code, I will need to strip out  h, m, and s along with the spaces in the data and replace it with :

Any tips on how to do that?

Answer 3

Hi Priya,

Did you attempt and find/replace within Excel?  That should help, I think.

jmp

Answer 4

I can make it work in Excel, but I am trying to do all the data prep steps in Azure ML/R. That way I don't have to do any manual data cleanup each time the dataset changes. In my case, I will have a new dataset monthly. I want to overwrite the csv file that the Azure ML experiment is using and simply re-run the experiment.

Answer 5

Hi Priya,

R provides the gsub() function to do search/replace type operations.  The stackoverflow posting here should hopefully help you here:

http://stackoverflow.com/questions/11936339/in-r-how-do-i-replace-text-within-a-string

jmp