Geek Answers Handbook

SQL `case when ...` code conversion using data.table to R package

I am trying to convert SQL code to R code. However, the data is about 35 million records with 200 columns each. So the best choice I could find is the data.table package.

Here is the problem. In SQL code, I can perform such an operation,

select order_date,sum(case when item in ("D","C","B") then col4 end)as col1 sum(case when item not in ("Z","X","Y") then col4 end) as col2 from datatable where col3 <25 group by order_date;

That the above query allows me to group by each date. I can not duplicate it in data.table. My attempts are as follows.

 grp1<- c("D","C","B") grp2<- c("Z","X","Y") d1 <- dat[item %in% grp1,.(col1 = sum(col4,na.rm = TRUE),by = Order_Date] d2 <- dat[item %in% grp2,.(col2 = sum(col4,na.rm = TRUE),by = Order_Date] d3 <- data.table(d1,d2)

Now, since it is subsets initially, my grouping is different in both d1 and d2

+5
source share
2 answers

You can try the following:

 DT[col3 < 25, .(col1 = sum(col4[item %in% c("D","C","B")]), col2 = sum(col4[!item %in% c("Z","X","Y")])), by = .(order_date)]
+6
source 
 > d <- " + order_date,item,col4,col3 + 2000-01-01,D,1,10 + 2000-01-01,C,1,10 + 2000-01-01,M,1,10 + 2000-01-01,N,1,50 + 2000-01-01,Z,1,10 + 2000-01-01,X,1,10 + 2001-01-02,Z,1,0 + 2001-01-02,X,1,50" > > df = read.csv(textConnection(d)) > > # data.frame + plyr approach > > require(plyr) Loading required package: plyr > ddply( + df[df$col3<25,], + .(order_date), + summarize, + col1=sum(item %in% c("D","C","B") & col4), + col2=sum(!item %in% c("Z","X","Y") & col4) + ) order_date col1 col2 1 2000-01-01 2 3 2 2001-01-02 0 0 > > # data.table approach, thanks to jangorecki > > require(data.table) Loading required package: data.table > dt = data.table(df) > > dt[col3 < 25, + .(col1 = sum(col4[item %in% c("D","C","B")]), + col2 = sum(col4[!item %in% c("Z","X","Y")])), + by = .(order_date)] order_date col1 col2 1: 2000-01-01 2 3 2: 2001-01-02 0 0 >
0
source

More articles:


All Articles