Applying Posixlt to column with multiple formats

I have a very large dataset that contains a list of different date formats, namely the following:

2016-12-07 06:24:49

05/12/2017 08:01

05/12/2018 08

19.02.2018 04:32:35

06-01-2018 00:32:24

I am trying to convert them all to posixlt since the entire column is of the character class. I need to keep all the information though.

I tried the following for each one:

df.all.copy$Time <- as.POSIXlt(df.all.copy$Time, tz="GMT", format = c("%d-%m-%Y %H:%M:%S"), optional = F)

This one after applying the following function:

df.all.copy$Time <- gsub("/", "-", df.all.copy$Time)

df.all.copy$Time <- gsub(".", "-", df.all.copy$Time)





df.all.copy$Time <- as.POSIXlt(df.all.copy$Time, tz="GMT", format, tryFormats = c("%Y-%m-%d %H:%M:%S",

                                                                        "%d-%m-%Y %H:%M:%S"), optional = F)

But it just doesn't work, the above only applies the function to 1/2 of them, leaving the rest as NA.

Any help would be welcome.

edited Nov 26 '18 at 22:43

r2evans

27.2k33058

asked Nov 26 '18 at 22:30

Thejohn

277

Use parse_date_time from lubridate which takes multiple formats

– akrun
Nov 26 '18 at 22:34

I'll give it a go!

– Thejohn
Nov 26 '18 at 22:53

add a comment |

I have a very large dataset that contains a list of different date formats, namely the following:

2016-12-07 06:24:49

05/12/2017 08:01

05/12/2018 08

19.02.2018 04:32:35

06-01-2018 00:32:24

I am trying to convert them all to posixlt since the entire column is of the character class. I need to keep all the information though.

I tried the following for each one:

df.all.copy$Time <- as.POSIXlt(df.all.copy$Time, tz="GMT", format = c("%d-%m-%Y %H:%M:%S"), optional = F)

This one after applying the following function:

df.all.copy$Time <- gsub("/", "-", df.all.copy$Time)

df.all.copy$Time <- gsub(".", "-", df.all.copy$Time)





df.all.copy$Time <- as.POSIXlt(df.all.copy$Time, tz="GMT", format, tryFormats = c("%Y-%m-%d %H:%M:%S",

                                                                        "%d-%m-%Y %H:%M:%S"), optional = F)

But it just doesn't work, the above only applies the function to 1/2 of them, leaving the rest as NA.

Any help would be welcome.

edited Nov 26 '18 at 22:43

r2evans

27.2k33058

asked Nov 26 '18 at 22:30

Thejohn

277

Use parse_date_time from lubridate which takes multiple formats

– akrun
Nov 26 '18 at 22:34

I'll give it a go!

– Thejohn
Nov 26 '18 at 22:53

add a comment |

I have a very large dataset that contains a list of different date formats, namely the following:

2016-12-07 06:24:49

05/12/2017 08:01

05/12/2018 08

19.02.2018 04:32:35

06-01-2018 00:32:24

I am trying to convert them all to posixlt since the entire column is of the character class. I need to keep all the information though.

I tried the following for each one:

df.all.copy$Time <- as.POSIXlt(df.all.copy$Time, tz="GMT", format = c("%d-%m-%Y %H:%M:%S"), optional = F)

This one after applying the following function:

df.all.copy$Time <- gsub("/", "-", df.all.copy$Time)

df.all.copy$Time <- gsub(".", "-", df.all.copy$Time)





df.all.copy$Time <- as.POSIXlt(df.all.copy$Time, tz="GMT", format, tryFormats = c("%Y-%m-%d %H:%M:%S",

                                                                        "%d-%m-%Y %H:%M:%S"), optional = F)

But it just doesn't work, the above only applies the function to 1/2 of them, leaving the rest as NA.

Any help would be welcome.

edited Nov 26 '18 at 22:43

r2evans

27.2k33058

asked Nov 26 '18 at 22:30

Thejohn

277

I have a very large dataset that contains a list of different date formats, namely the following:

2016-12-07 06:24:49

05/12/2017 08:01

05/12/2018 08

19.02.2018 04:32:35

06-01-2018 00:32:24

I am trying to convert them all to posixlt since the entire column is of the character class. I need to keep all the information though.

I tried the following for each one:

df.all.copy$Time <- as.POSIXlt(df.all.copy$Time, tz="GMT", format = c("%d-%m-%Y %H:%M:%S"), optional = F)

This one after applying the following function:

df.all.copy$Time <- gsub("/", "-", df.all.copy$Time)

df.all.copy$Time <- gsub(".", "-", df.all.copy$Time)





df.all.copy$Time <- as.POSIXlt(df.all.copy$Time, tz="GMT", format, tryFormats = c("%Y-%m-%d %H:%M:%S",

                                                                        "%d-%m-%Y %H:%M:%S"), optional = F)

But it just doesn't work, the above only applies the function to 1/2 of them, leaving the rest as NA.

Any help would be welcome.

edited Nov 26 '18 at 22:43

r2evans

27.2k33058

asked Nov 26 '18 at 22:30

Thejohn

277

edited Nov 26 '18 at 22:43

r2evans

27.2k33058

asked Nov 26 '18 at 22:30

Thejohn

277

edited Nov 26 '18 at 22:43

r2evans

27.2k33058

edited Nov 26 '18 at 22:43

r2evans

27.2k33058

edited Nov 26 '18 at 22:43

r2evans

27.2k33058

asked Nov 26 '18 at 22:30

Thejohn

277

asked Nov 26 '18 at 22:30

Thejohn

277

asked Nov 26 '18 at 22:30

Thejohn

277

Use parse_date_time from lubridate which takes multiple formats

– akrun
Nov 26 '18 at 22:34

I'll give it a go!

– Thejohn
Nov 26 '18 at 22:53

add a comment |

Use parse_date_time from lubridate which takes multiple formats

– akrun
Nov 26 '18 at 22:34

I'll give it a go!

– Thejohn
Nov 26 '18 at 22:53

Use parse_date_time from lubridate which takes multiple formats

– akrun
Nov 26 '18 at 22:34

I'll give it a go!

– Thejohn
Nov 26 '18 at 22:53

add a comment |

1 Answer
1

active

oldest

votes

Assuming that the ambiguous day/month for the 2nd and 3rd case is day followed by month, we can use anytime to automatically pick up the format and convert to DateTime format

library(anytime)

anytime(v1)

#[1] "2016-12-07 06:24:49 EST" "2017-05-12 08:01:00 EDT" "2018-05-12 08:00:00 EDT" NA                       

#[5] "2018-06-01 00:00:00 EDT"

or specify the format in parse_date_time

library(lubridate)

parse_date_time(v1,   c("%Y-%m-%d %H:%M:%S", "%d/%m/%Y %H:%M",

    "%d/%m/%Y %H", "%d.%m.%Y %H:%M:%S",  "%d-%m-%Y %H:%M:%S"))

data

v1 <- c('2016-12-07 06:24:49', '05/12/2017 08:01', '05/12/2018 08', 

      '19.02.2018 04:32:35', '06-01-2018 00:32:24 ')

answered Nov 26 '18 at 22:51

akrun

409k13198273

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53490095%2fapplying-posixlt-to-column-with-multiple-formats%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Assuming that the ambiguous day/month for the 2nd and 3rd case is day followed by month, we can use anytime to automatically pick up the format and convert to DateTime format

library(anytime)

anytime(v1)

#[1] "2016-12-07 06:24:49 EST" "2017-05-12 08:01:00 EDT" "2018-05-12 08:00:00 EDT" NA                       

#[5] "2018-06-01 00:00:00 EDT"

or specify the format in parse_date_time

library(lubridate)

parse_date_time(v1,   c("%Y-%m-%d %H:%M:%S", "%d/%m/%Y %H:%M",

    "%d/%m/%Y %H", "%d.%m.%Y %H:%M:%S",  "%d-%m-%Y %H:%M:%S"))

data

v1 <- c('2016-12-07 06:24:49', '05/12/2017 08:01', '05/12/2018 08', 

      '19.02.2018 04:32:35', '06-01-2018 00:32:24 ')

answered Nov 26 '18 at 22:51

akrun

409k13198273

add a comment |

Assuming that the ambiguous day/month for the 2nd and 3rd case is day followed by month, we can use anytime to automatically pick up the format and convert to DateTime format

library(anytime)

anytime(v1)

#[1] "2016-12-07 06:24:49 EST" "2017-05-12 08:01:00 EDT" "2018-05-12 08:00:00 EDT" NA                       

#[5] "2018-06-01 00:00:00 EDT"

or specify the format in parse_date_time

library(lubridate)

parse_date_time(v1,   c("%Y-%m-%d %H:%M:%S", "%d/%m/%Y %H:%M",

    "%d/%m/%Y %H", "%d.%m.%Y %H:%M:%S",  "%d-%m-%Y %H:%M:%S"))

data

v1 <- c('2016-12-07 06:24:49', '05/12/2017 08:01', '05/12/2018 08', 

      '19.02.2018 04:32:35', '06-01-2018 00:32:24 ')

answered Nov 26 '18 at 22:51

akrun

409k13198273

add a comment |

Assuming that the ambiguous day/month for the 2nd and 3rd case is day followed by month, we can use anytime to automatically pick up the format and convert to DateTime format

library(anytime)

anytime(v1)

#[1] "2016-12-07 06:24:49 EST" "2017-05-12 08:01:00 EDT" "2018-05-12 08:00:00 EDT" NA                       

#[5] "2018-06-01 00:00:00 EDT"

or specify the format in parse_date_time

library(lubridate)

parse_date_time(v1,   c("%Y-%m-%d %H:%M:%S", "%d/%m/%Y %H:%M",

    "%d/%m/%Y %H", "%d.%m.%Y %H:%M:%S",  "%d-%m-%Y %H:%M:%S"))

data

v1 <- c('2016-12-07 06:24:49', '05/12/2017 08:01', '05/12/2018 08', 

      '19.02.2018 04:32:35', '06-01-2018 00:32:24 ')

answered Nov 26 '18 at 22:51

akrun

409k13198273

Assuming that the ambiguous day/month for the 2nd and 3rd case is day followed by month, we can use anytime to automatically pick up the format and convert to DateTime format

library(anytime)

anytime(v1)

#[1] "2016-12-07 06:24:49 EST" "2017-05-12 08:01:00 EDT" "2018-05-12 08:00:00 EDT" NA                       

#[5] "2018-06-01 00:00:00 EDT"

or specify the format in parse_date_time

library(lubridate)

parse_date_time(v1,   c("%Y-%m-%d %H:%M:%S", "%d/%m/%Y %H:%M",

    "%d/%m/%Y %H", "%d.%m.%Y %H:%M:%S",  "%d-%m-%Y %H:%M:%S"))

data

v1 <- c('2016-12-07 06:24:49', '05/12/2017 08:01', '05/12/2018 08', 

      '19.02.2018 04:32:35', '06-01-2018 00:32:24 ')

answered Nov 26 '18 at 22:51

akrun

409k13198273

answered Nov 26 '18 at 22:51

akrun

409k13198273

answered Nov 26 '18 at 22:51

akrun

409k13198273

answered Nov 26 '18 at 22:51

akrun

409k13198273

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl