sqldf Questions
8
Solved
From a dataframe like this
test <- data.frame('id'= rep(1:5,2), 'string'= LETTERS[1:10])
test <- test[order(test$id), ]
rownames(test) <- 1:10
> test
id string
1 1 A
2 1 F
3 2 B
...
3
Solved
If I try to get an average of c(NA, NA, 3, 4, 5, 6, 7, 8, 9, 10) using AVG from SQL, I get a value of 5.2, instead of the expected 6.5.
# prepare data and write to file
write.table(data.frame(col1...
2
Solved
I hope that what I am about to write makes some sense.
If you look at
How to deal with a 50GB large csv file in r language?
it is explained how to query à la SQL, a csv file from R.
In my case, I h...
2
I am getting a sqlite3 error.
OperationalError: no such table: Bills
I first call my dataframes using pandas and then call those dataframes in my query which works fine
import pandas as pd
from pan...
4
Solved
I have a data frame that has a "DATE" field. e.g.: "24-10-2015"
The variable is in the date format.
When I use sqldf, e.g.: select min(DATE), MAX (DATE) from table ... the output is a number like...
4
Solved
I'll illustrate my question with an example.
Sample data:
df <- data.frame(ID = c(1, 1, 2, 2, 3, 5), A = c("foo", "bar", "foo", "foo", "bar", "bar"), B = c(1, 5, 7, 23, 54, 202))
df
ID A B
...
4
Solved
Consider the following data.table of events:
library(data.table)
breaks <- data.table(id = 1:8,
Channel = c("NP1", "NP1", "NP2", "NP2", "NP3", "NP3", "AT4", "AT4"),
Time = c(1000, 1100, 975, ...
Petunia asked 20/9, 2017 at 7:58
3
Solved
Below are the scripts
> library(sqldf)
> turnover = read.csv("turnover.csv")
> names(turnover)
[1] "Report.Date" "PersID" "Status" "DOB"
[5] "Age" "Tenure" "Current.Hire.Date" "Term.Date"...
2
Solved
I am working on families trees :
I have adapted Bob Horton's example based on sqldf https://www.r-bloggers.com/exploring-recursive-ctes-with-sqldf/
My data :
person father
Guillou Arthur NA
...
2
I loaded a csv file to my R, and when I Tried to use sqldf to select some column, it always went to
Error in .local(drv, ...) :
Failed to connect to database: Error: Access denied for user
'Us...
4
Error: Cannot pass NA to dbQuoteIdentifier()
In addition: Warning message:
In field_types[] <- field_types[names(data)] :
number of items to replace is not a multiple of replacement length
...
1
Solved
I've been using sqldf in my R-scripts until now when I got the following error:
library(sqldf)
data(mtcars)
out <- sqldf("SELECT * FROM mtcars")
> Error in rsqlite_send_query(conn@ptr, stat...
3
Solved
I’m trying to improve my workflow, and am hoping the community can provide insights since I am relatively new to “big data”.
I typically download several dataframes from public sources which can b...
Meliorism asked 30/3, 2017 at 9:29
0
I just updated all R packages including sqldf today. Then I kept getting warning message as follows:
Warning message:
Quoted identifiers should have class SQL, use DBI::SQL() if the caller perform...
2
Solved
I've recently upgraded R, RSQLite, and sqldf (versions below).
Normally:
sqldf('create table foo as select * from bar', db = 'test.db')
should create a table called 'foo' in an attached sqlite ...
4
I need to join 2 tables using a Date field
> class(pagos$pseudo_1mes)
[1] "Date"
> class(pseudo_meses$pseudo_1mes)
[1] "Date"
My code is:
library(sqldf)
pagos<-sqldf("select a.*, b.m...
4
Solved
I have two data-frames df1 and df2 that each have around 10 million rows and 4 columns. I read them into R using RODBC/sqlQuery with no problems, but when I try to rbind them, I get that most dread...
Kesler asked 21/1, 2011 at 16:19
1
Solved
I have a dataframe with about ~250 variables. Unfortunately, all of these variables were imported as character classes from a sql database using sqldf.
The problem: all of them should not be chara...
3
Solved
My dataframe has some variables that contain missing values as strings like "NA". What is the most efficient way to parse all columns in a dataframe that contain these and convert them into real NA...
1
arm<-as.data.frame(matrix(c(1,1,1,2,2,6,7,4,9,10),ncol=2))
colnames(arm)<-c("a","b")
This is a dataset I created in R.
Now I want to rank the column b and group by colum...
1
Solved
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 C...
5
Solved
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 b...
2
Solved
When using RPostgreSQL I find that I cannot use sqldf in the same way. For example if I load the library and read in data into a data frame using the following code:
library(RPostgreSQL)
drv <-...
Jabot asked 19/4, 2012 at 21:32
1
Solved
How do I rename a date field in SQLDF without changing the format?
See my example below where my renamed date field "dt" converts the date to a number. How do I avoid this, or convert it back to a...
2
Solved
I am using read.csv.sql from the package sqldf to try and read in a subset of rows, where the subset selects from multiple values - these values are stored in another vector.
I have hacked a way ...
1 Next >
© 2022 - 2024 — McMap. All rights reserved.