Group By and aggregate function

[MdC]

Hello everyone,

I am new to the CODAP platform which seems very promising.

I discovered that it was possible to group cases (rows) by value for one of the fields with the Transformer plugin. What I want to do is make a calculation (max, average…) on the values of each group (in SQL we say “apply an aggregate function on each group”)

For example, let’s consider the Mammals dataset provided with CODAP. What if I want to group the cases (rows) by Diet (hence creating three categories: plants, meat, both) and then, compute the average Mass of each group (average mass for those who eat plants, average mass of those who eat meat, average mass for those who eat both)?

In SQL, you could say SELECT Diet, AVG(Mass) FROM Mammals GROUP BY Diet

I hope my explainations are clear enough, English not being my mother tongue, do not hesitate is something isn’t clear enough.

Thanks to everyone who has read my message and is willing to help.

MdC

• This topic was modified 1 year, 11 months ago by MdC.
• This topic was modified 1 year, 11 months ago by MdC.
• This topic was modified 1 year, 11 months ago by MdC.

[Bill Finzer]

Hello MdC and welcome to the CODAP community,

While it is possible to use the Transformers plugin to make calculations on groups, if you’re willing to restructure the data a bit (a strength of CODAP), there is an elegant, builtin method.

1. Drag the grouping attribute (Diet) all the way to the left in the case table. This will produce a new “parent” table, name Diets, with three rows, one for each value of Diet.
2. Create a new attribute for the Diets table by clicking on the “plus” icon. Give it a meaningful name, perhaps AvgMass.
3. Click on the name of the new attribute and choose Edit Formula.
4. A reasonable formula in this situation might be
   mean(Mass).

Please see this CODAP help page for more details.

Bill

[MdC]

Hello Bill Finzer,

Thank you very much for your explaination. It works perfectly.

I’m sorry I didn’t dig deep enough into the documentation.

MdC

 

[Author]

Posts