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]]