Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
It reads other formats _if you specify them_. After all, no computer (or human) can tell whether 11/03/1959 is November 3 or March 11 without further hinting. So it tries the two ISO-like formats and leaves other cases for the user. -pd > On 20 Nov 2021, at 21:22 , Philip Monk wrote: > > Thanks, Andrew. I didn't realise as.Date *only* read two formats, I > think I was tripped up by using %y instead of %Y, -- Peter Dalgaard, Professor, Center for Statistics, Copenhagen Business School Solbjerg Plads 3, 2000 Frederiksberg, Denmark Phone: (+45)38153501 Office: A 4.23 Email: pd@cbs.dk Priv: pda...@gmail.com __ R-help@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.
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
I've written Ecfun::asNumericDF to overcome some of the common problems with read.data, read.csv, etc.: https://www.rdocumentation.org/packages/Ecfun/versions/0.2-5/topics/asNumericDF I use it routinely to help parse numbers, dates, etc., that are read as character. I'm sure it can be improved. It's on GitHub in case anyone would like to take the time to suggest improvements: https://github.com/sbgraves237/Ecfun Hope this helps. Spencer Graves On 11/20/21 4:13 PM, Avi Gross via R-help wrote: This seems to be a topic that comes up periodically. The various ways in R and other packages for reading in data often come with methods that simply guess wrong or encounter one or more data items in a column that really do not fit so fields may just by default become a more common denominator of character or perhaps floating point. There are ways that some such programs can be given a hint of what you expect or even be supplied with a way to coerce them into what you want while being read in. But realistically, often a more practical method might be to take the data.frame variety you read in and before using it for other purposes, check it for validity and make any needed changes. Simplistic ones might be to see how many columns were read in to see if it matches expectations or generate an error. Or you may trim columns (or rows) that are not wanted. In that vein, are there existing functions available that will accept what types you want one or more columns to be in and that validate if the current type is something else and then convert if needed? I mean we have functions like as.integer(df$x ) or more flexibly as(df$x, "integer") and you may simply build on a set of those and create others to suit any special needs. Of course a good method carefully checks the results before over-writing as sometimes the result may not be the same length (as shown below) or may violate some other ideas or rules: as(c(NULL, NA, 3, 3.1, "3.1", list(1,2,"a")), "character") [1] "NA" "3" "3.1" "3.1" "1" "2" "a" So if you have dates in some format, or sometimes an unknown format, there are ways, including some others have shown, to make them into some other date format or even make multiple columns that together embody the format. What people sometimes do is assume software is perfect and should do anything they want. It is the other way around and the programmer or data creator has some responsibilities to use the right software on the right data and that may also mean sanity checks along the way to see if the data is what you expect or alter it to be what you need. -Original Message- From: R-help On Behalf Of Philip Monk Sent: Saturday, November 20, 2021 3:28 PM To: Jeff Newmiller Cc: R-help Mailing List Subject: Re: [R] Date read correctly from CSV, then reformatted incorrectly by R Thanks, Jeff. I follow what you're doing below, but know I need to read up on Date / POSIXct. Helpful direction! :) On Sat, 20 Nov 2021 at 18:41, Jeff Newmiller wrote: 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= "Buffer28/10/201619/11/2016 31/12/201616/01/2017 05/03/2017 1002.437110889-8.696748953.2392998162.443183304 2.346743827 2002.524329899-7.6888620683.3868117342.680347706 2.253885237 3002.100784256-8.0598558353.1437865072.615152896 2.015645973 4001.985608385-10.67072062.8945727912.591925038 2.057913137 5001.824982163-9.1225197362.5603507272.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 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 = "%
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
Richard, This response was awe-inspiring. Thank you. -Original Message- From: R-help On Behalf Of Richard O'Keefe Sent: Sunday, November 21, 2021 8:55 PM To: Philip Monk Cc: R Project Help Subject: Re: [R] Date read correctly from CSV, then reformatted incorrectly by R CSV data is very often strangely laid out. For analysis, Buffer Date Reading 100... ... 100... ... and so on is more like what a data frame should be. I get quite annoyed when I finally manage to extract data from a government agency only to find that my tax money has been spent on making it harder to access than it needed to be. (1) You do NOT need any additional library to convert dates. ?strptime is quite capable. (2) Just because reshaping CAN be done in R doesn't mean it SHOULD be. Instead of reading data in as the wrong format and then hacking it into shape every time, it makes sense to convert the data once and only once, then load the converted data. It took just a couple of minutes to write (CSVDecoder read: 'transpose-in.csv') bindOwn: [:source | (CSVEncoder write: 'transpose-out.csv') bindOwn: [:target | source next bind: [:header | "Label date-1 ... date-n" target nextPut: {header first. 'Date'. 'Reading'}. [source atEnd] whileFalse: [ source next bind: [:group | group with: header keysAndValuesDo: [:index :reading :date | 1 < index ifTrue: [ (date subStrings: '/') bind: [:dmy | (dmy third,'-',dmy second,'-',dmy first) bind: [:iso | target nextPut: {group first. iso. reading}]]]. in another programming language, run it, and turn your example into Buffer,Date,Reading 100,2016-10-28,2.437110889 100,2016-11-19,-8.69674895 100,2016-12-31,3.239299816 100,2017-01-16,2.443183304 100,2017-03-05,2.346743827 200,2016-10-28,2.524329899 200,2016-11-19,-7.688862068 ... You could do the same kind of thing easily in Perl, Python, F#, ... Then just read the table in using read.csv("transpose-out.csv", colClasses = c("integer","Date","numeric")) and you're away laughing. (3) Of course you can do the whole thing in base R. h <- read.csv("transpose-in.csv", header=FALSE, nrows=1, stringsAsFactors=FALSE) d <- strptime(h[1,-1], format="%d/%m/%Y") b <- read.csv("transpose-in.csv", header=FALSE, skip=1) r <- expand.grid(Date=d, Buffer=b[,1]) r$Result <- as.vector(t(as.matrix(b[,-1]))) Lessons: (A) You don't have to read a CSV file (or any other) all in one piece. This pays off when the structure is irregular. (B) You don't HAVE to accept or convert column names. (C) strptime is your friend. (D) expand.grid is particularly handy for "matrix form" CSV data. (E) Someone who suggests doing something in another language because it is easier can end up with egg on his face when doing the whole thing in R turns out to be easier, simpler, and far more obvious. (A) really is an important lesson. (F) It's *amazing* what you can do in base R. It is useful to familiarise yourself with its capabilities before considering other packages. Compositional data? Not in base R. Correspondence analysis? Not in base R. Data reshaping? Very much there. On Sun, 21 Nov 2021 at 06:09, Philip Monk 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/ # 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_
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
CSV data is very often strangely laid out. For analysis, Buffer Date Reading 100... ... 100... ... and so on is more like what a data frame should be. I get quite annoyed when I finally manage to extract data from a government agency only to find that my tax money has been spent on making it harder to access than it needed to be. (1) You do NOT need any additional library to convert dates. ?strptime is quite capable. (2) Just because reshaping CAN be done in R doesn't mean it SHOULD be. Instead of reading data in as the wrong format and then hacking it into shape every time, it makes sense to convert the data once and only once, then load the converted data. It took just a couple of minutes to write (CSVDecoder read: 'transpose-in.csv') bindOwn: [:source | (CSVEncoder write: 'transpose-out.csv') bindOwn: [:target | source next bind: [:header | "Label date-1 ... date-n" target nextPut: {header first. 'Date'. 'Reading'}. [source atEnd] whileFalse: [ source next bind: [:group | group with: header keysAndValuesDo: [:index :reading :date | 1 < index ifTrue: [ (date subStrings: '/') bind: [:dmy | (dmy third,'-',dmy second,'-',dmy first) bind: [:iso | target nextPut: {group first. iso. reading}]]]. in another programming language, run it, and turn your example into Buffer,Date,Reading 100,2016-10-28,2.437110889 100,2016-11-19,-8.69674895 100,2016-12-31,3.239299816 100,2017-01-16,2.443183304 100,2017-03-05,2.346743827 200,2016-10-28,2.524329899 200,2016-11-19,-7.688862068 ... You could do the same kind of thing easily in Perl, Python, F#, ... Then just read the table in using read.csv("transpose-out.csv", colClasses = c("integer","Date","numeric")) and you're away laughing. (3) Of course you can do the whole thing in base R. h <- read.csv("transpose-in.csv", header=FALSE, nrows=1, stringsAsFactors=FALSE) d <- strptime(h[1,-1], format="%d/%m/%Y") b <- read.csv("transpose-in.csv", header=FALSE, skip=1) r <- expand.grid(Date=d, Buffer=b[,1]) r$Result <- as.vector(t(as.matrix(b[,-1]))) Lessons: (A) You don't have to read a CSV file (or any other) all in one piece. This pays off when the structure is irregular. (B) You don't HAVE to accept or convert column names. (C) strptime is your friend. (D) expand.grid is particularly handy for "matrix form" CSV data. (E) Someone who suggests doing something in another language because it is easier can end up with egg on his face when doing the whole thing in R turns out to be easier, simpler, and far more obvious. (A) really is an important lesson. (F) It's *amazing* what you can do in base R. It is useful to familiarise yourself with its capabilities before considering other packages. Compositional data? Not in base R. Correspondence analysis? Not in base R. Data reshaping? Very much there. On Sun, 21 Nov 2021 at 06:09, Philip Monk 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/ > # 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
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
This seems to be a topic that comes up periodically. The various ways in R and other packages for reading in data often come with methods that simply guess wrong or encounter one or more data items in a column that really do not fit so fields may just by default become a more common denominator of character or perhaps floating point. There are ways that some such programs can be given a hint of what you expect or even be supplied with a way to coerce them into what you want while being read in. But realistically, often a more practical method might be to take the data.frame variety you read in and before using it for other purposes, check it for validity and make any needed changes. Simplistic ones might be to see how many columns were read in to see if it matches expectations or generate an error. Or you may trim columns (or rows) that are not wanted. In that vein, are there existing functions available that will accept what types you want one or more columns to be in and that validate if the current type is something else and then convert if needed? I mean we have functions like as.integer(df$x ) or more flexibly as(df$x, "integer") and you may simply build on a set of those and create others to suit any special needs. Of course a good method carefully checks the results before over-writing as sometimes the result may not be the same length (as shown below) or may violate some other ideas or rules: > as(c(NULL, NA, 3, 3.1, "3.1", list(1,2,"a")), "character") [1] "NA" "3" "3.1" "3.1" "1" "2" "a" So if you have dates in some format, or sometimes an unknown format, there are ways, including some others have shown, to make them into some other date format or even make multiple columns that together embody the format. What people sometimes do is assume software is perfect and should do anything they want. It is the other way around and the programmer or data creator has some responsibilities to use the right software on the right data and that may also mean sanity checks along the way to see if the data is what you expect or alter it to be what you need. -Original Message- From: R-help On Behalf Of Philip Monk Sent: Saturday, November 20, 2021 3:28 PM To: Jeff Newmiller Cc: R-help Mailing List Subject: Re: [R] Date read correctly from CSV, then reformatted incorrectly by R Thanks, Jeff. I follow what you're doing below, but know I need to read up on Date / POSIXct. Helpful direction! :) On Sat, 20 Nov 2021 at 18:41, Jeff Newmiller wrote: > > 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= > "Buffer28/10/201619/11/2016 31/12/201616/01/2017 05/03/2017 > 1002.437110889-8.696748953.2392998162.443183304 2.346743827 > 2002.524329899-7.6888620683.3868117342.680347706 2.253885237 > 3002.100784256-8.0598558353.1437865072.615152896 2.015645973 > 4001.985608385-10.67072062.8945727912.591925038 2.057913137 > 5001.824982163-9.1225197362.5603507272.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 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") >
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
I am. Long day, poorly small children! P On Sat, 20 Nov 2021, 21:08 Bert Gunter, wrote: > "I also know that '/' is a special character in R (if that's the right > term) " > > That is false. I think you are confusing "/" with "\", which is R's > *escape* character. > > > cat("a/nb") > a/nb > > cat("a\nb") > a > b > > It gets confusing especially in regex's, because "\" is used in regex > syntax also. > > Bert Gunter > > "The trouble with having an open mind is that people keep coming along > and sticking things into it." > -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) > > On Sat, Nov 20, 2021 at 12:23 PM Philip Monk wrote: > > > > 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 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 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 > > >> > > >> Buffer28/10/201619/11/201631/12/201616/01/2017 > 05/03/2017 > > >> 1002.437110889-8.696748953.2392998162.443183304 > 2.346743827 > > >> 2002.524329899-7.6888620683.3868117342.680347706 > 2.253885237 > > >> 3002.100784256-8.0598558353.1437865072.615152896 > 2.015645973 > > >> 4001.985608385-10.67072062.8945727912.591925038 > 2.057913137 > > >> 5001.824982163-9.1225197362.5603507272.372226799 > 1.995863839 > > >> > > >> > > >> On Sat, 20 Nov 2021 at 17:08, Philip Monk 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/ > > >> > # 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@r-project.org mailing list -- To UNSUBSCRIBE and more, see > > >> https://stat.ethz.ch/mailman/listinfo/r-help > > >> PLEASE do read the
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
"I also know that '/' is a special character in R (if that's the right term) " That is false. I think you are confusing "/" with "\", which is R's *escape* character. > cat("a/nb") a/nb > cat("a\nb") a b It gets confusing especially in regex's, because "\" is used in regex syntax also. Bert Gunter "The trouble with having an open mind is that people keep coming along and sticking things into it." -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) On Sat, Nov 20, 2021 at 12:23 PM Philip Monk wrote: > > 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 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 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 > >> > >> Buffer28/10/201619/11/201631/12/201616/01/2017 > >> 05/03/2017 > >> 1002.437110889-8.696748953.2392998162.443183304 > >> 2.346743827 > >> 2002.524329899-7.6888620683.3868117342.680347706 > >> 2.253885237 > >> 3002.100784256-8.0598558353.1437865072.615152896 > >> 2.015645973 > >> 4001.985608385-10.67072062.8945727912.591925038 > >> 2.057913137 > >> 5001.824982163-9.1225197362.5603507272.372226799 > >> 1.995863839 > >> > >> > >> On Sat, 20 Nov 2021 at 17:08, Philip Monk 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/ > >> > # 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@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. > > __ >
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
Thanks, Jeff. I follow what you're doing below, but know I need to read up on Date / POSIXct. Helpful direction! :) On Sat, 20 Nov 2021 at 18:41, Jeff Newmiller wrote: > > 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= > "Buffer28/10/201619/11/2016 31/12/201616/01/201705/03/2017 > 1002.437110889-8.696748953.2392998162.4431833042.346743827 > 2002.524329899-7.6888620683.3868117342.680347706 > 2.253885237 > 3002.100784256-8.0598558353.1437865072.615152896 > 2.015645973 > 4001.985608385-10.67072062.8945727912.5919250382.057913137 > 5001.824982163-9.1225197362.5603507272.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 > 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 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 > >> > >> Buffer28/10/201619/11/201631/12/201616/01/2017 > >> 05/03/2017 > >> 1002.437110889-8.696748953.2392998162.443183304 > >> 2.346743827 > >> 2002.524329899-7.6888620683.3868117342.680347706 > >> 2.253885237 > >> 3002.100784256-8.0598558353.1437865072.615152896 > >> 2.015645973 > >> 4001.985608385-10.67072062.8945727912.591925038 > >> 2.057913137 > >> 5001.824982163-9.1225197362.5603507272.372226799 > >> 1.995863839 > >> > >> > >> On Sat, 20 Nov 2021 at 17:08, Philip Monk 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/ > >> > # 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) > >> >
Re: [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 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 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 >> >> Buffer28/10/201619/11/201631/12/201616/01/201705/03/2017 >> 1002.437110889-8.696748953.2392998162.443183304 >> 2.346743827 >> 2002.524329899-7.6888620683.3868117342.680347706 >> 2.253885237 >> 3002.100784256-8.0598558353.1437865072.615152896 >> 2.015645973 >> 4001.985608385-10.67072062.8945727912.591925038 >> 2.057913137 >> 5001.824982163-9.1225197362.5603507272.372226799 >> 1.995863839 >> >> >> On Sat, 20 Nov 2021 at 17:08, Philip Monk 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/ >> > # 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@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. __ R-help@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.
Re: [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= "Buffer28/10/201619/11/2016 31/12/201616/01/201705/03/2017 1002.437110889-8.696748953.2392998162.4431833042.346743827 2002.524329899-7.6888620683.3868117342.6803477062.253885237 3002.100784256-8.0598558353.1437865072.6151528962.015645973 4001.985608385-10.67072062.8945727912.5919250382.057913137 5001.824982163-9.1225197362.5603507272.3722267991.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 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 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 >> >> Buffer28/10/201619/11/201631/12/201616/01/2017 >> 05/03/2017 >> 1002.437110889-8.696748953.2392998162.443183304 >> 2.346743827 >> 2002.524329899-7.6888620683.3868117342.680347706 >> 2.253885237 >> 3002.100784256-8.0598558353.1437865072.615152896 >> 2.015645973 >> 4001.985608385-10.67072062.8945727912.591925038 >> 2.057913137 >> 5001.824982163-9.1225197362.5603507272.372226799 >> 1.995863839 >> >> >> On Sat, 20 Nov 2021 at 17:08, Philip Monk 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/ >> > # 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@r-project.org mailing list -- To UNSUBSCRIBE and more, see >> https://stat.ethz.ch/mailman/listinfo/r-help >>
Re: [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 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 > > Buffer28/10/201619/11/201631/12/201616/01/2017 > 05/03/2017 > 1002.437110889-8.696748953.2392998162.443183304 > 2.346743827 > 2002.524329899-7.6888620683.3868117342.680347706 > 2.253885237 > 3002.100784256-8.0598558353.1437865072.615152896 > 2.015645973 > 4001.985608385-10.67072062.8945727912.591925038 > 2.057913137 > 5001.824982163-9.1225197362.5603507272.372226799 > 1.995863839 > > > On Sat, 20 Nov 2021 at 17:08, Philip Monk 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/ > > # 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@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@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.
Re: [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 Buffer28/10/201619/11/201631/12/201616/01/201705/03/2017 1002.437110889-8.696748953.2392998162.4431833042.346743827 2002.524329899-7.6888620683.3868117342.6803477062.253885237 3002.100784256-8.0598558353.1437865072.6151528962.015645973 4001.985608385-10.67072062.8945727912.5919250382.057913137 5001.824982163-9.1225197362.5603507272.3722267991.995863839 On Sat, 20 Nov 2021 at 17:08, Philip Monk 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/ > # 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@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.
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
a) R data frames are column oriented. Do not fight this. b) Data frame header names are character type. Period. Do not fight this. It sounds like you need to reshape your data after you read it in. Provide the first five lines of your CSV file (or a reasonable facsimile if your data are confidential) and someone (me if I get to it first) can help make it more adapted to R. Also, make sure you configure your email software to send plain text... formatted email gets damaged to varying degrees by the automatic stripping of formatting performed by the mailing list. And periodically read the Posting Guide for other advice. On November 20, 2021 9:08:39 AM PST, Philip Monk 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/ ># 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! > > [[alternative HTML version deleted]] > >__ >R-help@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. __ R-help@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.
Re: [R] Date read correctly from CSV, then reformatted incorrectly by R
Hi Philip, This is a recurring question and there are many ways to do this. My preference is to use the lubridate package. library(lubridate) a <- "15/01/2010" b <- dmy(a) b # "2010-01-15" class(b) # [1] "Date" HTH, Eric On Sat, Nov 20, 2021 at 7:09 PM Philip Monk 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/ > # 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! > > [[alternative HTML version deleted]] > > __ > R-help@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@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.
[R] Date read correctly from CSV, then reformatted incorrectly by R
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/ # 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! [[alternative HTML version deleted]] __ R-help@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.