SQLDF How to append the new vetors?

Question

see I have a data.frame like this:

str(veh)
$VEHAGE  int  3 6 35 35 5 3 7 4 5 1 ...
$VEHTYPE int  2 1 3 1 2 3 1 4 2 3 ...

then I run

a<-sqldf("SELECT VEHAGE, COUNT(VEHAGE) as count1
                             FROM veh 
                             WHERE VEHTYPE==1
                             GROUP BY VEHAGE")


a<-sqldf("SELECT VEHAGE, COUNT(VEHAGE) as count234
                             FROM veh 
                             WHERE VEHTYPE==2|3|4
                             GROUP BY VEHAGE")

I get a result like this

head(a)
  VEHAGE count234
1     -9     92
2      1     79
3      2    164
4      3    212
5      4    260
6      5    199

I lost count1 in this case. But I want a head(a) like this

  VEHAGE count1   count234
1     -9     92    510
2      1     79    844
3      2    164    123
4      3    212    123
5      4    260    100
6      5    199    100

Any Idea? Thanks in advance!

You overwrite `a` when you call `sqldf` the second time. That's why you lose `count1`. — rrs, Feb 14 '14 at 19:29

Sven Hohenstein · Accepted Answer · 2014-02-14T19:30:30.947

2

I suppose you're looking for this:

# first query
a <- sqldf("SELECT VEHAGE, COUNT(VEHAGE) as count1
                             FROM veh 
                             WHERE VEHTYPE==1
                             GROUP BY VEHAGE")

# second query
b <- sqldf("SELECT VEHAGE, COUNT(VEHAGE) as count234
                             FROM veh 
                             WHERE VEHTYPE IN (2, 3, 4)
                             GROUP BY VEHAGE")

# combine
a <- sqldf("SELECT * 
            FROM a JOIN b
            USING (VEHAGE)")

edited Feb 14 '14 at 19:30

answered Feb 14 '14 at 19:21

Sven Hohenstein

80,497
17
145
168

1

I think you have to `merge` in case the two groups have different number of rows (e.g. some VEHTYPE==1 may be of ages unique to that group). – BrodieG Feb 14 '14 at 19:25
@BrodieG Thanks for pointing this out. I changed my approach. – Sven Hohenstein Feb 14 '14 at 19:31
Thank you both. It works! Yes, I should to merge since some group has no age -9, the combine function works perfect. – user3311486 Feb 14 '14 at 19:33

score 0 · Answer 2 · answered Feb 14 '14 at 19:23

0

Try something like:

a<-sqldf(
 "SELECT VEHAGE, 
    SUM(if(VEHTYPE==1, 1, 0)) as count1,
    SUM(if(VEHTYPE==2|3|4, 1, 0)) as count234
  FROM veh 
  GROUP BY VEHAGE")

Though disclaimer, I'm not familiar with sqldf syntax, but something like this would work in MySQL (though in MySQL ==2|3|4 would have to be in(2, 3, 4)).

Also, you may want to add something like HAVING count1 | count234 to get rid of the VEHAGEs with no vehicles in those categories.

answered Feb 14 '14 at 19:23

BrodieG

51,669
9
93
146

The SQL syntax used by sqldf depends on the back end you are using. By default it uses SQLite but can also use H2, MySQL and PostgreSQL. – G. Grothendieck Feb 14 '14 at 19:28

SQLDF How to append the new vetors?

2 Answers2