Choose Language Hide Translation Bar
gail_massari
Community Manager Community Manager

How can I create a column with values aggregated by variable type?

 

Sometimes you get raw data you need to aggregate before you work with it. In this  example, we want to calculate the average weight by car type. There are a few methods. One is Analyze>Tabulate.

 

This, however, is a Tables method. Go to Tables>Summary, right click to get a pop-up box, right click to grab the Weight metric, and put it in Statistics box. Then, put car Type in the Group box. This will give you a table that is Average Weight by Type.

 

That creates a new table similar to an Excel Pivot Table. To add the summary information in your data table, you could join the Summary table to the data table.

 

Alternately, you can add metrics directly to the data table. For example, if you want a column that has the average weight for each car type, highlight the Type column, right click, go to New Formula Column, and then select Group By. Then, select the Weight column, because it is the metric you want to work with. Right-click, go to New Formula Column, select Aggregate, and then select Mean. Notice Grouping by Type shows up in the pop-up menu.

 

You see that the values in the new column are the mean for the group (Small, Medium, Large) for the car in that row.

 

Group ByGroup By

 

 

AggregateAggregate