Taking an Average of Multiple Conditions

Question

The following fields are relevant to my question: Age, Sex, and Title

The Age field has Null values and non-Null values. For any Null value I want to populate that field with a non-Null value by taking the average on the Age field by specifying a certain Sex and Title.

For instance, if the 4th record in the data set has the following attributes: Age = Null, Sex = "Male" and Title = "Mr", I want to change the Null value with the average of the population that is Sex = Male and Title = Mr.

Any help appreciated.

dataMack · Accepted Answer

1. Use a summary tool to calculate the average of age with a group by on Sex and Title.

2. Join the output of #1 above to your main dataset (node before the summary tool) specifying the join on both Sex and Title - this will add the average age to all records as a new column

3. Use a formula tool to interrogate your Age field and if it is null, return the value of the Average Age field, otherwise return the existing Age value

4. Use a select tool to drop the extra average Age field