combine two column into one - Date and Time Column with changing their format

Question

Structure of Data

str(noaaData)

'data.frame':   92 obs. of  3 variables:
 $ Date         : int  20121029 20121029 20121029 20121029 20121029 20121029 20121029 20121029 20121029 20121029 ...
 $ Time         : int  100 200 300 400 500 600 700 800 900 1000 ...
 $ AtmosPressure: num  999 999 998 998 997 ...

I need to combine Date & Time in one column with name "DateTime" by changing them into POSIXct format "%Y-%m-%d %H:%M:%OS"

It should look like this

2012-10-29 01:00:00

minimal reproducible

dput(noaaData)

structure(list(Date = c(20121029L, 20121029L, 20121029L, 20121029L, 
20121029L, 20121029L, 20121029L, 20121029L, 20121029L, 20121029L, 
20121029L, 20121029L, 20121029L, 20121029L, 20121029L, 20121029L, 
20121029L, 20121029L, 20121029L, 20121029L, 20121029L, 20121029L, 
20121029L, 20121030L, 20121030L, 20121030L, 20121030L, 20121030L, 
20121030L, 20121030L, 20121030L, 20121030L, 20121030L, 20121030L, 
20121030L, 20121030L, 20121030L, 20121030L, 20121030L, 20121030L, 
20121030L, 20121030L, 20121030L, 20121030L, 20121031L, 20121031L, 
20121031L, 20121031L, 20121031L, 20121031L, 20121031L, 20121031L, 
20121031L, 20121031L, 20121031L, 20121031L, 20121031L, 20121031L, 
20121031L, 20121031L, 20121031L, 20121031L, 20121031L, 20121031L, 
20121031L, 20121031L, 20121031L, 20121031L, 20121101L, 20121101L, 
20121101L, 20121101L, 20121101L, 20121101L, 20121101L, 20121101L, 
20121101L, 20121101L, 20121101L, 20121101L, 20121101L, 20121101L, 
20121101L, 20121101L, 20121101L, 20121101L, 20121101L, 20121101L, 
20121101L, 20121101L, 20121101L, 20121101L), Time = structure(c(8640000, 
17280000, 25920000, 34560000, 43200000, 51840000, 60480000, 69120000, 
77760000, 86400000, 95040000, 103680000, 112320000, 120960000, 
129600000, 138240000, 146880000, 155520000, 164160000, 172800000, 
181440000, 190080000, 198720000, 25920000, 34560000, 43200000, 
51840000, 60480000, 69120000, 77760000, 86400000, 95040000, 103680000, 
112320000, 120960000, 129600000, 138240000, 146880000, 155520000, 
164160000, 172800000, 181440000, 190080000, 198720000, 0, 8640000, 
17280000, 25920000, 34560000, 43200000, 51840000, 60480000, 69120000, 
77760000, 86400000, 95040000, 103680000, 112320000, 120960000, 
129600000, 138240000, 146880000, 155520000, 164160000, 172800000, 
181440000, 190080000, 198720000, 0, 8640000, 17280000, 25920000, 
34560000, 43200000, 51840000, 60480000, 69120000, 77760000, 86400000, 
95040000, 103680000, 112320000, 120960000, 129600000, 138240000, 
146880000, 155520000, 164160000, 172800000, 181440000, 190080000, 
198720000), class = c("POSIXct", "POSIXt")), AtmosPressure = c(999.2, 
999.2, 998.3, 997.9, 996.7, 995.4, 994.4, 992, 990.2, 988.4, 
987.1, 985.9, 984, 981.5, 978.8, 975.3, 971.4, 966.3, 961.9, 
956.1, 951.4, 949.7, 946.3, 968, 972.9, 976.5, 979.4, 981.2, 
982.5, 983.7, 985, 986.2, 987.5, 988.9, 990.1, 991, 992, 992.6, 
993, 993.4, 994.1, 994.6, 995.4, 996, 996.6, 997, 997.1, 997.3, 
997.3, 997.4, 997.2, 997.2, 997.5, 997.8, 998.3, 998.6, 999, 
999.8, 1000, 1000.1, 1000.2, 999.7, 999.5, 999.1, 999.5, 1000, 
1000.4, 1001.1, 1001.3, 1001.6, 1001.7, 1001.8, 1001.7, 1001.7, 
1001.7, 1002, 1001.9, 1001.9, 1002.1, 1002, 1002.5, 1002.9, 1003.1, 
1003, 1003.1, 1002.5, 1002.1, 1001.8, 1001.9, 1001.9, 1002.2, 
1002.9)), row.names = c(NA, -92L), class = "data.frame")

I request someone to please help me in this.

hi Shashank Shekhar. Please add a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610). Also show what you have already tried. That way you can help others to help you! — dario, Feb 17 '20 at 15:15
@dario I have added the put here, can you figure it now how to proceed further? — Shashank Shekhar, Feb 17 '20 at 20:47
In the `str` outpu of `noaaData` the variable `time` is of type `int` but in the dput ouptu it's `POSIXct`? Maybe you posted the wrong dput? — dario, Feb 17 '20 at 21:50

dario · Answer 1 · 2020-02-17T22:55:47.287

There are probably cleaner solutions but this should work and be flexible...

1.Minimal reproducible example:

df <- data.frame(Date = c(20121029,20121029, 20121029, 20121029, 20121029, 20121029, 20121029),
                 Time = c(400, 500, 600, 700, 800, 900, 1000),
                 stringsAsFactors = FALSE)

'data.frame':   7 obs. of  2 variables:
$ Date         : int  20121029 20121029 20121029 20121029 20121029 20121029 20121029
$ Time         : int  400 500 600 700 800 900 1000

2.Solution with helper function and strptime:

leftPad <- function(x) {
  len <- 4
  s <- sapply(sapply(x, function(y) paste0("0000", y)),
    function(z) substr(z, nchar(z) - len + 1, nchar(z)))

  return(as.character(s))
}

leftPad can then be used to format Time for strptime(We can change the timezone with the tz argument

strptime(paste0(df$Date, " ", leftPad(df$Time)), format = "%Y%m%d %H%M", tz="UTC")

This returns:

[1] "2012-10-29 04:00:00 UTC" "2012-10-29 05:00:00 UTC"
[3] "2012-10-29 06:00:00 UTC" "2012-10-29 07:00:00 UTC"
[5] "2012-10-29 08:00:00 UTC" "2012-10-29 09:00:00 UTC"
[7] "2012-10-29 10:00:00 UTC"

thanks for help. You code works, glad for your help. – Shashank Shekhar Feb 18 '20 at 22:36 — Shashank Shekhar, Feb 18 '20 at 22:36

Clemsang · Accepted Answer · 2020-02-18T07:56:50.177

0

Here is one way:

noaaData <- data.frame(
  Date = c(20121029,20121029, 20121029, 20121029, 20121029, 20121029, 20121029),
  Time = c(400, 500, 600, 700, 800, 900, 1000),
  stringsAsFactors = FALSE
)
strptime(
  paste(noaaData$Date, formatC(noaaData$Time, width = 4, format = "d", flag = "0")), 
  format = "%Y%m%d %H%M"
)
[1] "2012-10-29 04:00:00 CET" "2012-10-29 05:00:00 CET" "2012-10-29 06:00:00 CET" "2012-10-29 07:00:00 CET" "2012-10-29 08:00:00 CET"
[6] "2012-10-29 09:00:00 CET" "2012-10-29 10:00:00 CET"

You might want to change timezone tz in strptime

edited Feb 18 '20 at 07:56

answered Feb 17 '20 at 15:22

Clemsang

5,053
3
23
41

thanks for help me but I see a problem here, In Time = 100 that means 1:00:00, so does 200 means 2:00:00 and the solution came with 00:01:40 Can you please check with that? – Shashank Shekhar Feb 17 '20 at 16:36
Improved. Please check the best answer – Clemsang Feb 18 '20 at 07:57
you code is more relevant to my work. Thanks for helping me in this problem. – Shashank Shekhar Feb 18 '20 at 22:36

combine two column into one - Date and Time Column with changing their format

2 Answers2