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.
Solved! Go to Solution.
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
Thanks, Jason! A very intuitive solution that worked like a peach!