Search code examples
rtimedplyrchron

How can I transform a specific time, to a

I want to transform a string column, into a proper format.

Normally I would do something like: 

print(df$Time) 
> "00:00:01"
as.POSIXct(df$Time,format="%H:%M:%S")

However, my data is strange. It looks like this: 

print(df$Time)
850a"  "823a"  NA      "906a"  "321a"  "1154p"

My solution is not working. Since I firstly stripped the characters (in this case "a" and "p"). But after doing that, my times are missing an important part (if it is morning or afternoon).

Therefore my question: how can I transform this data into a proper format?

Expected output: 

df$Time_Old
850a"  "823a"  NA      "906a"  "321a"  "1154p"

df$Time_New
08.50   08.23    NA    09.06   03.21   23.54

Some example data: 

vector_string <- as.vector(tv_Adds[["Time"]])
vector_string = vector_string[1:20]
> vector_string 


[1] "850a"  "823a"  NA      "906a"  "321a"  "1154p" "608p"  "1012a" "354a"  "1121p" "414p"  "1241p" "721p"  "223p"  "316p" 
[16] "345p"  "1145a" "3p"    "937a"  "138p"

> dput(vector_string[1:20])
c("850a", "823a", NA, "906a", "321a", "1154p", "608p", "1012a", 
"354a", "1121p", "414p", "1241p", "721p", "223p", "316p", "345p", 
"1145a", "3p", "937a", "138p")
Solution

  • Based on your shared example, it seems that you have 3 different cases we need to handle.

    1. When you have 834a which needs to become 8:34am
    2. When you have 1143p which needs to become 11:43pm
    3. When you have 3a which needs to become 3:00am

    Once those are handled, in this case with a simple ifelse statement counting number of characters and modifying accordingly, then we can convert to datetime object simly by calling strptime with the correct format, i.e.

    v1[!is.na(v1)] <- paste0(v1[!is.na(v1)], 'm')
v2 <- ifelse(nchar(v1) == 5, gsub('(^[0-9]{1})(.*$)', '\\1:\\2', v1), 
           ifelse(nchar(v1) == 3, gsub('(^[0-9]{1})(.*$)', '\\1:00\\2', v1), 
                                                  gsub('(^[0-9]{2})(.*$)', '\\1:\\2', v1)))

v2
#[1] "8:50am"  "8:23am"  NA        "9:06am"  "3:21am"  "11:54pm" "6:08pm"  "10:12am" "3:54am"  "11:21pm" "4:14pm"  "12:41pm" "7:21pm"  "2:23pm"  "3:16pm"  "3:45pm"  "11:45am" "3:00pm"  "9:37am"  "1:38pm" 

strptime(v2, format = '%I:%M%p')
#[1] "2019-10-29 08:50:00 +03" "2019-10-29 08:23:00 +03" NA                        "2019-10-29 09:06:00 +03" "2019-10-29 03:21:00 +03" "2019-10-29 23:54:00 +03" "2019-10-29 18:08:00 +03" "2019-10-29 10:12:00 +03" "2019-10-29 03:54:00 +03" "2019-10-29 23:21:00 +03"
#[11] "2019-10-29 16:14:00 +03" "2019-10-29 12:41:00 +03" "2019-10-29 19:21:00 +03" "2019-10-29 14:23:00 +03" "2019-10-29 15:16:00 +03" "2019-10-29 15:45:00 +03" "2019-10-29 11:45:00 +03" "2019-10-29 15:00:00 +03" "2019-10-29 09:37:00 +03" "2019-10-29 13:38:00 +03"

    DATA USED

    dput(v1)
c("850am", "823am", NA, "906am", "321am", "1154pm", "608pm", 
"1012am", "354am", "1121pm", "414pm", "1241pm", "721pm", "223pm", 
"316pm", "345pm", "1145am", "3pm", "937am", "138pm")