How can I transform a specific time, to a

Question

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")

Could you please share a more reproducible example? Perhaps a sample of your data, current and expected output? Your question is a bit unclear, at least to me. — NelsonGon, Oct 25 '19 at 14:49
Hi @Sotos, indeed, a and p ar am and pm (yeah.. it is really messy) — R overflow, Oct 29 '19 at 08:41
@NelsonGon sure, I have added a sample of my data. Thanks in advance! — R overflow, Oct 29 '19 at 08:57
Can you please do `dput(vector_string[1:20])` and post for your **example data** so it's reproducible? — Sotos, Oct 29 '19 at 09:19

Grada Gukovic · Answer 1 · 2019-10-25T15:16:56.417

2

You have to separate the hours from the minutes since the input, as you give it, is ambiguous. Then add "m" at the end of non-NA entries. I think you need this:

tvec = c("850a",  "823a",  NA, "906a",  "321a",  "1154p")
notNA <- !is.na(tvec)

#separate hours from minutes with a dot and append m at the end:
tvec[notNA] <- paste0(strtrim(tvec[notNA], nchar(tvec[notNA]) - 3), ".", 
                      substr(tvec[notNA], nchar(tvec[notNA])-2, nchar(tvec[notNA]))
, "m")

as.POSIXct(tvec, format = "%I.%M%p")
[1] "2019-10-25 08:50:00 CEST" "2019-10-25 08:23:00 CEST"
[3] NA                         "2019-10-25 09:06:00 CEST"
[5] "2019-10-25 03:21:00 CEST" "2019-10-25 23:54:00 CEST"

"%I.%M%p" stands for

hour(0-12), followed by .,followed by minutes(00-59), followed by "am"(or "pm")

edited Oct 25 '19 at 15:16

answered Oct 25 '19 at 15:09

Grada Gukovic

1,228
7
13

Thanks @Grada! I used your code, but received an error: Error in strtrim(tvec[notNA], nchar(tvec[notNA]) - 3) : invalid 'width' argument – R overflow Oct 29 '19 at 08:54
I have added the data example that I used to test it... Many thanks again! – R overflow Oct 29 '19 at 08:56

Sotos · Accepted Answer · 2019-10-29T10:48:19.180

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

When you have 834a which needs to become 8:34am
When you have 1143p which needs to become 11:43pm
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")

Your solution works! However, if you use the data provided, you should add (in front of your solution): v1 <- tolower(v1) v1 <- gsub("a","am", v1) v1 <- gsub("p","pm",v1) — R overflow, Oct 29 '19 at 10:42
Oh, I forgot to add that step :P...added it now. You can use paste and avoid doing 2 `gsub` — Sotos, Oct 29 '19 at 10:47

How can I transform a specific time, to a

2 Answers2