Convert to time series

Hello everybody,

i am a student from Hamburg, Germany and i want modify some electricity-data(Regelzonensaldo) to a time series (XTS), but i don´t know how to merge the two columns(Date and Time) into one.
I tried:

as.Date(Gesamt_Regelzonensaldo_csv$Date, format = "%d/%m/%Y")

as.POSIXct(paste(Gesamt_Regelzonensaldo_csv$Date, Gesamt_Regelzonensaldo_csv$Time), format="%Y-%m-%d %H:%M:%S")

with(Gesamt_Regelzonensaldo_csv, ymd(Date) + hms(Time))

but it didn´t work.

Regelz|307x480

Maybe the problem is the periods in the dataset? I attached a picture. I hope somebody is able to help me.

Kindly regards

FelixRegelz

Hi!

To help us help you, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

Hi FlexR,
and welcome to the RStudio Community!

Dig into the FAQ about how to REPREX.

Here is an example of a REPREX to help you get started, using some lubridate functions.

library(tidyverse)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following object is masked from 'package:base':
#> 
#>     date
df <- data.frame(
  date = c('01.01.12', '01.04.12', '01.15.12'),
  time = c('01.12', '04.16', '08.44')
)

df %>% 
  mutate(dttm = mdy_hm(paste(date, time)),
         date = mdy(date),
         time = hm(time))
#>         date      time                dttm
#> 1 2012-01-01 1H 12M 0S 2012-01-01 01:12:00
#> 2 2012-01-04 4H 16M 0S 2012-01-04 04:16:00
#> 3 2012-01-15 8H 44M 0S 2012-01-15 08:44:00

Created on 2019-11-07 by the reprex package (v0.3.0)

hello, thank you so much!

Hey again,
thank you one more time for your advice. I tried my best to convert with your given code and it worked for many rows but i have now the the message:

Warning messages:
1: 142443 failed to parse.
2: 139483 failed to parse.
3: In .parse_hms(..., order = "HM", quiet = quiet) :
Some strings failed to parse, or all strings are NAs

I can´t figure ou why some rows work and some don´t. I converted the excel in a csv data and imported it. Maybe this is the problem. In excel everything has the same format.

Maybe somebody has an idea.

Kindly regards

Felixhelp!

It would help if you could make a reprex, so we could experiment on your code and data. But it does look like some of your NAs occur when the hours and/or minutes have been stripped off. Excel is notorious for doing strange things to data. Some pre-formatting in Excel to date and time formats could possibly help. You may need to filter these problem rows and re-assign them new times or dates.