On 2020-07-18 18:09 +0100, Rui Barradas wrote: | Às 17:59 de 18/07/2020, H escreveu: | | On Fri, Jul 17, 2020 at 6:28 PM H <age...@meddatainc.com> wrote: | | | | | | The problem I am having is that | | | the csv files have header rows | | | with column names that are | | | slightly different from the column | | | names I have assigned in the | | | dataframe and it seems that when I | | | read the csv data into the | | | dataframe, the column names from | | | the csv file replace the column | | | names I chose when creating the | | | dataframe. | | | | | | A secondary issue is that the csv | | | files have a column with a date in | | | mm/dd/yyyy format that I would | | | like to make into a Date type | | | column in my dataframe. Again, I | | | have been unable to find a way - | | | if at all possible - to force a | | | conversion into a Date format when | | | importing into the dataframe. The | | | best I have so far is to import is | | | a character column and then use | | | as.Date() to later force the | | | conversion of the dataframe | | | column. | | | | The documentation for read.csv.sql() | | suggests that colClasses() and/or | | field.types() should work but I may | | well have misunderstood the | | documentation, hence my question in | | this group. | | As for colClasses, those are R class | names.
Ok Mister H, I might have hit the nail on the head this time with this badass example for your usecase: # Make a csv with %d/%m/%Y dates in it ... Lines <- "STM05-1 2005/02/28 17:35 Good -35.562 177.158 STM05-1 2005/02/28 19:44 Good -35.487 177.129 STM05-1 2005/02/28 23:01 Unknown -35.399 177.064 STM05-1 2005/03/01 07:28 Unknown -34.978 177.268 STM05-1 2005/03/01 18:06 Poor -34.799 177.027 STM05-1 2005/03/01 18:47 Poor -34.85 177.059 STM05-2 2005/02/28 12:49 Good -35.928 177.328 STM05-2 2005/02/28 21:23 Poor -35.926 177.314 " DF <- read.table(textConnection(Lines), as.is = TRUE, col.names = c("Id", "Date", "Time", "Quality", "Lat", "Long")) DF$Date <- format(as.Date(DF$Date, "%Y/%m/%d"), "%d/%m/%Y") write.csv(DF, file="df.csv", row.names=FALSE) colClasses <- c("character", "Date", "character", "character", "numeric", "numeric") sql <- paste0( "select ", "date(", # [2] "substr(Date, 8, 4) || '-' || ", # [1] "substr(Date, 5, 2) || '-' || ", "substr(Date, 2, 2)), Long, Lat, Quality ", "from ff where Quality like '%oo%' and Long>177.129") ff <- file(description="df.csv", open="r") dat <- sqldf::read.csv.sql( sql=sql, colClasses=colClasses) close(ff) str(dat) as.Date(dat[,1]) dat[,3] Both sqlite and Postgres has a function substr you can call on strings like this.[5] I have a hunch this has always been possible in sql from way back ... The warning from sqldf about unused connections, might suggest file descriptor handling to be a bit crusty ... [3] The thing is, defining the second column as of type Date in colClasses happens to work, but it's still character when you check with str(dat) ... perhaps it has something to do with this info from [4]: as_tibble_row() converts a vector to a tibble with one row. The input must be a bare vector, e.g. vectors of dates are not supported yet. If the input is a list, all elements must have length one. [1] https://stackoverflow.com/questions/15563656/convert-string-to-date-in-sqlite [2] https://www.sqlite.org/lang_datefunc.html [3] https://groups.google.com/forum/#!topic/sqldf/mcQ_K_E--q8 [4] https://tibble.tidyverse.org/reference/as_tibble.html [5] https://www.sqlite.org/lang_corefunc.html#substr, https://www.postgresql.org/docs/9.1/functions-string.html, http://www.h2database.com/html/functions.html#substring
signature.asc
Description: PGP signature
______________________________________________ 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.