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

  • 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?

    Tuesday, December 6, 2016 11:02 PM

Answers

  • 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

    • Proposed as answer by jopela-msft Friday, December 9, 2016 4:39 PM
    • Marked as answer by jopela-msft Monday, December 12, 2016 1:08 AM
    Friday, December 9, 2016 4:36 PM

All replies

  • 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 !

    Thursday, December 8, 2016 3:49 PM
  • 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?

    Thursday, December 8, 2016 5:41 PM
  • Hi Priya,

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

    jmp

    Thursday, December 8, 2016 6:33 PM
  • 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.

    Thursday, December 8, 2016 7:46 PM
  • 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

    • Proposed as answer by jopela-msft Friday, December 9, 2016 4:39 PM
    • Marked as answer by jopela-msft Monday, December 12, 2016 1:08 AM
    Friday, December 9, 2016 4:36 PM