Case statements with Aggregate

User 1703 | 5/3/2015, 8:57:41 AM

I am wondering if it is possible to choose case statements in the aggreagate function like SQL.

For instance:

AVG(case when date1 < date2 then value else 0 end) as avglastx_months

Is this possible? I have had to export the SFrame to csv then to postgres to manipulate like this.

Comments

User 91 | 5/4/2015, 5:12:17 PM

You can do this as follows:

index = sf['date1'] < sf['date2'] sa = sf['value'][index] sa.sum()


User 398 | 5/4/2015, 5:42:59 PM

Hi mk00,

This can definitely be accomplished. Your AVG aggregator is possible with gl.aggregate.AVG. The transformation achieved by your case statement would have to be taken care of first and you could do that using the SFrame apply method. So for example, consider an SFrame (sf) with a column called "date1", "date2" and "value" (to reflect your initial naming). You could do the following:

sf["last_x_months"] = sf.apply(lambda row: row["value"] if row["date1"] < row["date2"] else 0)
sf.groupby("last_x_months", {"avg_last_x_months": gl.aggregate.AVG("last_x_months")}

I hope that helps.

Robert