Philip Monk
2021-Nov-20 17:48 UTC
[R] Date read correctly from CSV, then reformatted incorrectly by R
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!
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]]