Convert to time series

[FlexR]

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

Felix

[andresrcs]

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:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

[phiggins]

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

[FlexR]

hello, thank you so much!

[FlexR]

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

Felix

[phiggins]

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.