add new column using SQLite

Question

I am trying to insert a new column in a R dataframe using sqldf, using the example 4 from sqldf

abbr <- data.frame (species = levels(iris[,"Species"]),
                    abbr = c("s","ve","vi"))
sqldf("select abbr, avg(Sepal_Length) from iris natural join abbr group by species")

sqldf("select abbr, avg(Sepal_Length) from iris join abbr using(Species) group by Species")

Both sqldf command works, but it fails when I change the column name of abbr from abbr to abbr_col, I don't know which abbr in the sql syntax should be changed.

Thanks.

update #01

> abbr <- data.frame (species = levels(iris[,"Species"]),
+                     abbr_col = c("s","ve","vi"))
> sqldf("select abbr_col, avg(Sepal_Length) from iris natural join abbr_col group by species")
Error in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: no such table: abbr_col)

I tried renaming all abbr into abbr_col, but fail.

I modified the question with additional background info, thanks — lokheart
– lokheart, Commented Aug 16, 2011 at 10:07

hamstergene · Accepted Answer · 2011-08-16 10:07:50Z

2

One “abbr” is table name, another one is column name:

abbr_table <- data.frame (species = levels(iris[,"Species"]),
                          abbr_col = c("s","ve","vi"))
sqldf("select abbr_col, avg(Sepal_Length) from iris natural join abbr_table group by species")

answered Aug 16, 2011 at 10:07

hamstergene

24.5k5 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pabdulin · Accepted Answer · 2011-08-16 10:02:43Z

0

If you want to rename column from abbr to abbr_col, then you need change all of the abbr occurrences to abbr_col.

answered Aug 16, 2011 at 10:02

pabdulin

35.6k9 gold badges66 silver badges102 bronze badges

Collectives™ on Stack Overflow

add new column using SQLite

update #01

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

update #01

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related