Andrew Simmons
2021-Nov-20 18:01 UTC
[R] Date read correctly from CSV, then reformatted incorrectly by R
The as.Date function for a character class argument will try reading in two
formats (%Y-%m-%d and %Y/%m/%d).
This does not look like the format you have provided, which is why it
doesn't work. Try something like:
x <- c("28/10/2016", "19/11/2016",
"31/12/2016", "16/01/2016", "05/03/2017")
as.Date(x, format = "%d/%m/%Y")
which produces this output:
> x <- c("28/10/2016", "19/11/2016",
"31/12/2016", "16/01/2016",
"05/03/2017")> as.Date(x, format = "%d/%m/%Y")
[1] "2016-10-28" "2016-11-19" "2016-12-31"
"2016-01-16" "2017-03-05">
much better than before! I hope this helps
On Sat, Nov 20, 2021 at 12:49 PM Philip Monk <prmonk at gmail.com> wrote:
> Thanks Eric & Jeff.
>
> I'll certainly read up on lubridate, and the posting guide (again)
> (this should be in plain text).
>
> CSV extract below...
>
> Philip
>
> Buffer 28/10/2016 19/11/2016 31/12/2016 16/01/2017
> 05/03/2017
> 100 2.437110889 -8.69674895 3.239299816 2.443183304
> 2.346743827
> 200 2.524329899 -7.688862068 3.386811734 2.680347706
> 2.253885237
> 300 2.100784256 -8.059855835 3.143786507 2.615152896
> 2.015645973
> 400 1.985608385 -10.6707206 2.894572791 2.591925038
> 2.057913137
> 500 1.824982163 -9.122519736 2.560350727 2.372226799
> 1.995863839
>
>
> On Sat, 20 Nov 2021 at 17:08, Philip Monk <prmonk at gmail.com>
wrote:
> >
> > Hello,
> >
> > Simple but infuriating problem.
> >
> > Reading in CSV of data using :
> >
> > ```
> > # CSV file has column headers with date of scene capture in format
> dd/mm/yyyy
> > # check.names = FALSE averts R incorrectly processing dates due to
'/'
> > data <- read.csv("C:/R_data/Bungala (b2000) julian.csv",
check.names > FALSE)
> >
> > # Converts data table from wide (many columns) to long (many rows) and
> creates the new object 'data_long'
> > # Column 1 is the 'Buffer' number (100-2000), Columns 2-25
contain
> monthly data covering 2 years (the header row being the date, and rows 2-21
> being a value for each buffer).
> > # Column headers for columns 2:25 are mutated into a column called
> 'Date', values for each buffer and each date into the column
'LST'
> > data_long <- data %>% pivot_longer(cols = 2:25, names_to =
"Date",
> values_to = "LST")
> >
> > # Instructs R to treat the 'Date' column data as a date
> > data_long$Date <- as.Date(data_long$Date)
> > ```
> >
> > Using str(data), I can see that R has correctly read the dates in the
> format %d/%m/%y (e.g. 15/12/2015) though has the data type as chr.
> >
> > Once changing the type to 'Date', however, the date is
reconfigured.
> For instance, 15/01/2010 (15 January 2010), becomes 0015-01-20.
> >
> > I've tried ```data_long$Date <- as.Date(data_long$Date, format
> "%d/%m.%y")```, and also ```tryformat c("%d/%m%y")```,
but either the error
> persists or I get ```NA```.
> >
> > How do I make R change Date from 'chr' to 'date'
without it going wrong?
> >
> > Suggestions/hints/solutions would be most welcome. :)
> >
> > Thanks for your time,
> >
> > Philip
> >
> > Part-time PhD Student (Environmental Science)
> > Lancaster University, UK.
> >
> > ~~~~~
> >
> > I asked a question a few weeks ago and put together the answer I
needed
> from the responses but didn't know how to say thanks on this list. So,
> thanks Andrew Simmons, Bert Gunter, Jeff Newmiller and Daniel Nordlund!
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
[[alternative HTML version deleted]]
Jeff Newmiller
2021-Nov-20 18:40 UTC
[R] Date read correctly from CSV, then reformatted incorrectly by R
Beat me to it! But it is also worth noting that once converted to Date or
POSIXct, timestamps should be treated as data without regard to how that data is
displayed. When you choose to output that data you will have options as to the
display format associated with the function you are using for output.
My take:
dta <- read.table( text"Buffer 28/10/2016 19/11/2016 31/12/2016
16/01/2017 05/03/2017
100 2.437110889 -8.69674895 3.239299816 2.443183304 2.346743827
200 2.524329899 -7.688862068 3.386811734 2.680347706 2.253885237
300 2.100784256 -8.059855835 3.143786507 2.615152896 2.015645973
400 1.985608385 -10.6707206 2.894572791 2.591925038 2.057913137
500 1.824982163 -9.122519736 2.560350727 2.372226799 1.995863839
", header=TRUE, check.names=FALSE, as.is=TRUE)
dta
library(dplyr)
library(tidyr)
dt_fmt <- "%d/%m/%Y"
dta_long <- ( dta
%>% pivot_longer( cols = -Buffer
, names_to = "dt_chr"
, values_to = "LST"
)
%>% mutate( dt_date = as.Date( dt_chr, format = dt_fmt )
, dt_POSIXct = as.POSIXct( dt_chr, format = dt_fmt, tz =
"Etc/GMT+8" )
)
)
dta_long
On November 20, 2021 10:01:56 AM PST, Andrew Simmons <akwsimmo at
gmail.com> wrote:>The as.Date function for a character class argument will try reading in two
>formats (%Y-%m-%d and %Y/%m/%d).
>
>
>This does not look like the format you have provided, which is why it
>doesn't work. Try something like:
>
>
>x <- c("28/10/2016", "19/11/2016",
"31/12/2016", "16/01/2016", "05/03/2017")
>as.Date(x, format = "%d/%m/%Y")
>
>
>which produces this output:
>
>
>> x <- c("28/10/2016", "19/11/2016",
"31/12/2016", "16/01/2016",
>"05/03/2017")
>> as.Date(x, format = "%d/%m/%Y")
>[1] "2016-10-28" "2016-11-19" "2016-12-31"
"2016-01-16" "2017-03-05"
>>
>
>
>much better than before! I hope this helps
>
>On Sat, Nov 20, 2021 at 12:49 PM Philip Monk <prmonk at gmail.com>
wrote:
>
>> Thanks Eric & Jeff.
>>
>> I'll certainly read up on lubridate, and the posting guide (again)
>> (this should be in plain text).
>>
>> CSV extract below...
>>
>> Philip
>>
>> Buffer 28/10/2016 19/11/2016 31/12/2016 16/01/2017
>> 05/03/2017
>> 100 2.437110889 -8.69674895 3.239299816 2.443183304
>> 2.346743827
>> 200 2.524329899 -7.688862068 3.386811734 2.680347706
>> 2.253885237
>> 300 2.100784256 -8.059855835 3.143786507 2.615152896
>> 2.015645973
>> 400 1.985608385 -10.6707206 2.894572791 2.591925038
>> 2.057913137
>> 500 1.824982163 -9.122519736 2.560350727 2.372226799
>> 1.995863839
>>
>>
>> On Sat, 20 Nov 2021 at 17:08, Philip Monk <prmonk at gmail.com>
wrote:
>> >
>> > Hello,
>> >
>> > Simple but infuriating problem.
>> >
>> > Reading in CSV of data using :
>> >
>> > ```
>> > # CSV file has column headers with date of scene capture in format
>> dd/mm/yyyy
>> > # check.names = FALSE averts R incorrectly processing dates due to
'/'
>> > data <- read.csv("C:/R_data/Bungala (b2000)
julian.csv", check.names >> FALSE)
>> >
>> > # Converts data table from wide (many columns) to long (many rows)
and
>> creates the new object 'data_long'
>> > # Column 1 is the 'Buffer' number (100-2000), Columns 2-25
contain
>> monthly data covering 2 years (the header row being the date, and rows
2-21
>> being a value for each buffer).
>> > # Column headers for columns 2:25 are mutated into a column called
>> 'Date', values for each buffer and each date into the column
'LST'
>> > data_long <- data %>% pivot_longer(cols = 2:25, names_to =
"Date",
>> values_to = "LST")
>> >
>> > # Instructs R to treat the 'Date' column data as a date
>> > data_long$Date <- as.Date(data_long$Date)
>> > ```
>> >
>> > Using str(data), I can see that R has correctly read the dates in
the
>> format %d/%m/%y (e.g. 15/12/2015) though has the data type as chr.
>> >
>> > Once changing the type to 'Date', however, the date is
reconfigured.
>> For instance, 15/01/2010 (15 January 2010), becomes 0015-01-20.
>> >
>> > I've tried ```data_long$Date <- as.Date(data_long$Date,
format >> "%d/%m.%y")```, and also ```tryformat
c("%d/%m%y")```, but either the error
>> persists or I get ```NA```.
>> >
>> > How do I make R change Date from 'chr' to 'date'
without it going wrong?
>> >
>> > Suggestions/hints/solutions would be most welcome. :)
>> >
>> > Thanks for your time,
>> >
>> > Philip
>> >
>> > Part-time PhD Student (Environmental Science)
>> > Lancaster University, UK.
>> >
>> > ~~~~~
>> >
>> > I asked a question a few weeks ago and put together the answer I
needed
>> from the responses but didn't know how to say thanks on this list.
So,
>> thanks Andrew Simmons, Bert Gunter, Jeff Newmiller and Daniel Nordlund!
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
> [[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.
--
Sent from my phone. Please excuse my brevity.
Philip Monk
2021-Nov-20 20:22 UTC
[R] Date read correctly from CSV, then reformatted incorrectly by R
Thanks, Andrew. I didn't realise as.Date *only* read two formats, I think I was tripped up by using %y instead of %Y, though I also know that '/' is a special character in R (if that's the right term) and as such know there is special syntax to use (which I don't know). On Sat, 20 Nov 2021 at 18:02, Andrew Simmons <akwsimmo at gmail.com> wrote:> > The as.Date function for a character class argument will try reading in two formats (%Y-%m-%d and %Y/%m/%d). > > > This does not look like the format you have provided, which is why it doesn't work. Try something like: > > > x <- c("28/10/2016", "19/11/2016", "31/12/2016", "16/01/2016", "05/03/2017") > as.Date(x, format = "%d/%m/%Y") > > > which produces this output: > > > > x <- c("28/10/2016", "19/11/2016", "31/12/2016", "16/01/2016", "05/03/2017") > > as.Date(x, format = "%d/%m/%Y") > [1] "2016-10-28" "2016-11-19" "2016-12-31" "2016-01-16" "2017-03-05" > > > > > much better than before! I hope this helps > > On Sat, Nov 20, 2021 at 12:49 PM Philip Monk <prmonk at gmail.com> wrote: >> >> Thanks Eric & Jeff. >> >> I'll certainly read up on lubridate, and the posting guide (again) >> (this should be in plain text). >> >> CSV extract below... >> >> Philip >> >> Buffer 28/10/2016 19/11/2016 31/12/2016 16/01/2017 05/03/2017 >> 100 2.437110889 -8.69674895 3.239299816 2.443183304 2.346743827 >> 200 2.524329899 -7.688862068 3.386811734 2.680347706 2.253885237 >> 300 2.100784256 -8.059855835 3.143786507 2.615152896 2.015645973 >> 400 1.985608385 -10.6707206 2.894572791 2.591925038 2.057913137 >> 500 1.824982163 -9.122519736 2.560350727 2.372226799 1.995863839 >> >> >> On Sat, 20 Nov 2021 at 17:08, Philip Monk <prmonk at gmail.com> wrote: >> > >> > Hello, >> > >> > Simple but infuriating problem. >> > >> > Reading in CSV of data using : >> > >> > ``` >> > # CSV file has column headers with date of scene capture in format dd/mm/yyyy >> > # check.names = FALSE averts R incorrectly processing dates due to '/' >> > data <- read.csv("C:/R_data/Bungala (b2000) julian.csv", check.names = FALSE) >> > >> > # Converts data table from wide (many columns) to long (many rows) and creates the new object 'data_long' >> > # Column 1 is the 'Buffer' number (100-2000), Columns 2-25 contain monthly data covering 2 years (the header row being the date, and rows 2-21 being a value for each buffer). >> > # Column headers for columns 2:25 are mutated into a column called 'Date', values for each buffer and each date into the column 'LST' >> > data_long <- data %>% pivot_longer(cols = 2:25, names_to = "Date", values_to = "LST") >> > >> > # Instructs R to treat the 'Date' column data as a date >> > data_long$Date <- as.Date(data_long$Date) >> > ``` >> > >> > Using str(data), I can see that R has correctly read the dates in the format %d/%m/%y (e.g. 15/12/2015) though has the data type as chr. >> > >> > Once changing the type to 'Date', however, the date is reconfigured. For instance, 15/01/2010 (15 January 2010), becomes 0015-01-20. >> > >> > I've tried ```data_long$Date <- as.Date(data_long$Date, format = "%d/%m.%y")```, and also ```tryformat c("%d/%m%y")```, but either the error persists or I get ```NA```. >> > >> > How do I make R change Date from 'chr' to 'date' without it going wrong? >> > >> > Suggestions/hints/solutions would be most welcome. :) >> > >> > Thanks for your time, >> > >> > Philip >> > >> > Part-time PhD Student (Environmental Science) >> > Lancaster University, UK. >> > >> > ~~~~~ >> > >> > I asked a question a few weeks ago and put together the answer I needed from the responses but didn't know how to say thanks on this list. So, thanks Andrew Simmons, Bert Gunter, Jeff Newmiller and Daniel Nordlund! >> >> ______________________________________________ >> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code.