Passing in two dates to a sql statement (sqldf). Is returning a factor. Tried setting back to a Date via as.Date, but get an error the error: character string is not in a standard unambiguous format. Any thoughts appreciated. Code/Results listed below:> summary(df.possible.combos)Date Hour Min. :2011-03-01 Min. : 0.00 1st Qu.:2011-03-23 1st Qu.: 5.75 Median :2011-04-14 Median :11.50 Mean :2011-04-14 Mean :11.50 3rd Qu.:2011-05-06 3rd Qu.:17.25 Max. :2011-05-31 Max. :23.00> summary(df.aggregate)Date Hour x Min. :2011-03-01 16 : 82 Min. : 1.000 1st Qu.:2011-03-22 17 : 82 1st Qu.: 1.000 Median :2011-04-13 18 : 82 Median : 2.000 Mean :2011-04-14 19 : 79 Mean : 4.195 3rd Qu.:2011-05-07 20 : 76 3rd Qu.: 7.000 Max. :2011-05-31 7 : 75 Max. :20.000 (Other):377> #merge raw data and all possible combinations > df.final <- sqldf('select Date, Hour, x as RoomsInUse from > "df.aggregate"+ left join "df.possible.combos" using (Hour, Date)')> summary(df.final)Date Hour RoomsInUse 15069.0: 16 16 : 82 Min. : 1.000 15114.0: 16 17 : 82 1st Qu.: 1.000 15063.0: 15 18 : 82 Median : 2.000 15082.0: 15 19 : 79 Mean : 4.195 15125.0: 15 20 : 76 3rd Qu.: 7.000 15044.0: 14 7 : 75 Max. :20.000 (Other):762 (Other):377> thedate <- as.Date(df.final$Date)Error in charToDate(x) : character string is not in a standard unambiguous format>-- View this message in context: http://r.789695.n4.nabble.com/SQL-Changing-Data-Type-tp3623508p3623508.html Sent from the R help mailing list archive at Nabble.com.
Hi: str() probably would have been more informative than summary(), but if the date is a factor, perhaps this will help: as.Date(as.character(Date), format = '%Y-%m-%d') Including the format argument is usually safer than letting the function try to figure it out on its own. Neglecting to include it may well be the source of the error message. You might also look into the POSIX classes if you want to combine date and time. Start with ?DateTimeClasses to learn more. HTH, Dennis On Fri, Jun 24, 2011 at 1:12 PM, GL <pflugg at shands.ufl.edu> wrote:> Passing in two dates to a sql statement (sqldf). Is returning a factor. Tried > setting back to a Date via as.Date, but get an error the error: character > string is not in a standard unambiguous format. Any thoughts appreciated. > > Code/Results listed below: > >> summary(df.possible.combos) > ? ? ?Date ? ? ? ? ? ? ? ? Hour > ?Min. ? :2011-03-01 ? Min. ? : 0.00 > ?1st Qu.:2011-03-23 ? 1st Qu.: 5.75 > ?Median :2011-04-14 ? Median :11.50 > ?Mean ? :2011-04-14 ? Mean ? :11.50 > ?3rd Qu.:2011-05-06 ? 3rd Qu.:17.25 > ?Max. ? :2011-05-31 ? Max. ? :23.00 >> summary(df.aggregate) > ? ? ?Date ? ? ? ? ? ? ? ? Hour ? ? ? ? ? x > ?Min. ? :2011-03-01 ? 16 ? ? : 82 ? Min. ? : 1.000 > ?1st Qu.:2011-03-22 ? 17 ? ? : 82 ? 1st Qu.: 1.000 > ?Median :2011-04-13 ? 18 ? ? : 82 ? Median : 2.000 > ?Mean ? :2011-04-14 ? 19 ? ? : 79 ? Mean ? : 4.195 > ?3rd Qu.:2011-05-07 ? 20 ? ? : 76 ? 3rd Qu.: 7.000 > ?Max. ? :2011-05-31 ? 7 ? ? ?: 75 ? Max. ? :20.000 > ? ? ? ? ? ? ? ? ? ? ?(Other):377 >> #merge raw data and all possible combinations >> ? df.final <- sqldf('select Date, Hour, x as RoomsInUse from >> "df.aggregate" > + ? ? left join "df.possible.combos" using (Hour, Date)') >> summary(df.final) > ? ? ?Date ? ? ? ? ?Hour ? ? ? RoomsInUse > ?15069.0: 16 ? 16 ? ? : 82 ? Min. ? : 1.000 > ?15114.0: 16 ? 17 ? ? : 82 ? 1st Qu.: 1.000 > ?15063.0: 15 ? 18 ? ? : 82 ? Median : 2.000 > ?15082.0: 15 ? 19 ? ? : 79 ? Mean ? : 4.195 > ?15125.0: 15 ? 20 ? ? : 76 ? 3rd Qu.: 7.000 > ?15044.0: 14 ? 7 ? ? ?: 75 ? Max. ? :20.000 > ?(Other):762 ? (Other):377 >> thedate <- as.Date(df.final$Date) > Error in charToDate(x) : > ?character string is not in a standard unambiguous format >> > > > -- > View this message in context: http://r.789695.n4.nabble.com/SQL-Changing-Data-Type-tp3623508p3623508.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at r-project.org mailing list > 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. >
On Fri, Jun 24, 2011 at 4:12 PM, GL <pflugg at shands.ufl.edu> wrote:> Passing in two dates to a sql statement (sqldf). Is returning a factor. Tried > setting back to a Date via as.Date, but get an error the error: character > string is not in a standard unambiguous format. Any thoughts appreciated. > > Code/Results listed below: > >> summary(df.possible.combos) > ? ? ?Date ? ? ? ? ? ? ? ? Hour > ?Min. ? :2011-03-01 ? Min. ? : 0.00 > ?1st Qu.:2011-03-23 ? 1st Qu.: 5.75 > ?Median :2011-04-14 ? Median :11.50 > ?Mean ? :2011-04-14 ? Mean ? :11.50 > ?3rd Qu.:2011-05-06 ? 3rd Qu.:17.25 > ?Max. ? :2011-05-31 ? Max. ? :23.00 >> summary(df.aggregate) > ? ? ?Date ? ? ? ? ? ? ? ? Hour ? ? ? ? ? x > ?Min. ? :2011-03-01 ? 16 ? ? : 82 ? Min. ? : 1.000 > ?1st Qu.:2011-03-22 ? 17 ? ? : 82 ? 1st Qu.: 1.000 > ?Median :2011-04-13 ? 18 ? ? : 82 ? Median : 2.000 > ?Mean ? :2011-04-14 ? 19 ? ? : 79 ? Mean ? : 4.195 > ?3rd Qu.:2011-05-07 ? 20 ? ? : 76 ? 3rd Qu.: 7.000 > ?Max. ? :2011-05-31 ? 7 ? ? ?: 75 ? Max. ? :20.000 > ? ? ? ? ? ? ? ? ? ? ?(Other):377 >> #merge raw data and all possible combinations >> ? df.final <- sqldf('select Date, Hour, x as RoomsInUse from >> "df.aggregate" > + ? ? left join "df.possible.combos" using (Hour, Date)') >> summary(df.final) > ? ? ?Date ? ? ? ? ?Hour ? ? ? RoomsInUse > ?15069.0: 16 ? 16 ? ? : 82 ? Min. ? : 1.000 > ?15114.0: 16 ? 17 ? ? : 82 ? 1st Qu.: 1.000 > ?15063.0: 15 ? 18 ? ? : 82 ? Median : 2.000 > ?15082.0: 15 ? 19 ? ? : 79 ? Mean ? : 4.195 > ?15125.0: 15 ? 20 ? ? : 76 ? 3rd Qu.: 7.000 > ?15044.0: 14 ? 7 ? ? ?: 75 ? Max. ? :20.000 > ?(Other):762 ? (Other):377 >> thedate <- as.Date(df.final$Date) > Error in charToDate(x) : > ?character string is not in a standard unambiguous format >>Please read the last line to every message to r-help and provide this in reproducible form as requested. Use dput to display all inputs and showing all code. Also ensure you are using the latest version of sqldf which is 0.4-1. -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com