FAQ Database Discussion Community


Count of data by Sqldf

r,sqldf
My data looks like: ID category 101 A 101 B 101 C 102 A 103 B 103 C I would like the result like: ID category Count 101 A 3 101 B 3 101 C 3 102 A 1 103 B 2 103 C 2 I have tried something like:...

R command to check if table exists in an sql database

r,sqldf
I'm using the sqldf R library to write to an sql database using the command: dbWriteTable(db, table_id, as.data.frame(table_df)) where db is the sql database file, table_id is the name of the table to be saved in the sql database, and the data.frame corresponding to the table_id is table_df. My question...

ERROR use datename/datepart in R sqldf package

sql,r,sqldf
I have an error message when I running sqldf package for datename or datepart. Here is the table I used. Height Date 163 12/01/90 182 11/13/88 167 5/14/97 172 3/18/94 170 10/11/92 185 7/15/90 expected_table <-sqldf("select [Height], (datename(weekday,[Date])) AS [Day of Week] from table1 ") Error in sqliteSendQuery(con, statement, bind.data)...

Trouble Finding ID's with Duplicate Fields

sql,r,sqldf
My data looks like this: ID Email 1 [email protected] 2 [email protected] 3 [email protected] 4 [email protected] 5 [email protected] 6 [email protected] There should be exactly 1 email per ID, but there's not. > dim(data) [1] 5071 2 > length(unique(data$Person_Onyx_Id)) [1] 5071 > length((data$Email)) [1] 5071 > length(unique(data$Email)) [1] 4481 So, I need...

Skip metadata when Importing dataset in R

r,sqldf
My question involves how to skip metadata in the beginning of a file when importing data into R. My data is in .txt format where the first lines are metadata describing the data and these need to be filtered out. Below is a minimal example of the data frame in...

Apply CASE WHEN in sqldf statement for manipulating multiple columns

r,data.frame,apply,sqldf
I have a dataframe datwe with 37 columns. I am interested in converting the integer values(1,2,99) in columns 23 to 35 to character values('Yes','No','NA'). datwe$COL23 <- sqldf("SELECT CASE COL23 WHEN 1 THEN 'Yes' WHEN 2 THEN 'No' WHEN 99 THEN 'NA' ELSE 'Name ittt' END as newCol FROM datwe")$newCol I...

Select specified rows when importing CSV

r,csv,sqldf
I have a large CSV file and I only want to import select certain rows if it. First I create the indices of the rows that will be imported then I wish to pass the names of these rows to sqldf and return the full records for specified rows. #create...

sqldf: How to query based on a date condition

r,date,sqldf
I have spent a couple of hours researching this but I am getting nowhere unfortunately. I am trying to get a subset of data by using sqldf to query a data frame, result. This is the structure of result: > str(result) 'data.frame': 316125 obs. of 6 variables: $ ID :...

R- SQLDF - SELECT … CASE… END

mysql,r,sqldf
Hello, I am trying to run a query that use a CASE statement from R. I am using the SQLDF package. The same query works fine in Mysql. The query gives the values of 0 or 1 to "express" according to the following situations: Select "express" = CASE when E_MAIL...

R sqldf - match.fun(asfn) 'c(“as.labelled”, “as.integer”)' is not a function, character or symbol

r,sqldf
Total newbie to R, have just spent a couple of hours playing and thought I'd have a play with some of the NHANES datasets e.g. ftp://ftp.cdc.gov/pub/Health_Statistics/NCHS/nhanes/2003-2004/ So grabbed a couple and after a play with merge(bmx_c, demo_c) and a quick Google I thought the sqldf library would be a more...

Inserting values of a column from one dataframe to another while respecting a given condition

sql,r,sqldf
I have two data frames d1 and d2. d2 has a column which contains data that I would prefer to be added to d1. Each of the data frames have equal number of rows and columns. > d1 t1 t2 numVehicles avgByRunRep 1 0.2 0.3 10 225.5000 2 0.2 0.4...