R equivalent for count(case when) group by in sql

Question

I am trying to find a R function which can implement the functionality of "count" and "case when" in sql. Simple example could be as follows:

I have a data frame

df <- data.frame(flag=c("pos","neg","pos"),rule=c("Strict","Lenient","Strict"))

> df
  flag    rule
1  pos  Strict
2  neg Lenient
3  pos  Strict

I want the output to be

rule    Positive_flag     Negative_flag
Strict   2                  0
Lenient  0                  1

I am basically creating two new variables based on the count of positive and negative flag.

I can do this in sql using

select sum(case when flag = "pos" then 1 else 0 end) as Positive_flag, sum(case when flag = "neg" then 1 else 0 end) as Negative_flag group by rule;

What will be the R equivalent function for this

David · Accepted Answer · 2017-11-21T09:07:59.130

What you are looking for is a table of, therefore, you can use the table() function

df <- data.frame(flag = c("pos", "neg", "pos"),
                 rule = c("Strict", "Lenient", "Strict"))

table_df <- table(df)
table_df
#>      rule
#> flag  Lenient Strict
#>   neg       1      0
#>   pos       0      2

To get the desired output:

t(table_df)
#>          flag
#> rule      neg pos
#>   Lenient   1   0
#>   Strict    0   2

Using reshape2 that's mostly used to convert data from wide to long and vice versa.

reshape2::dcast(df, rule~flag, value.var = "rule", fun.aggregate = length)
#>      rule neg pos
#> 1 Lenient   1   0
#> 2  Strict   0   2

R equivalent for count(case when) group by in sql

1 Answers1