Hi All, I'm trying to replace the NULL values in one of my variables by the Median of that variable when grouped by another variable. For example, imagine I have the following dataset - I want to replace the missing age values by median of all ages with the same title. TitleAgeMaster10Master14Master3MasterNULLMrs26Mrs52Mrs45MrsNULLMrs76 I can accomplish this using a series of filters connected to imputes then reconnected via a union but was hoping there was a more elegant solution.

Replace NULL by with the Median Grouped by another Variable

Hi All,

I'm trying to replace the NULL values in one of my variables by the Median of that variable when grouped by another variable.

For example, imagine I have the following dataset - I want to replace the missing age values by median of all ages with the same title.

Title	Age
Master	10
Master	14
Master	3
Master	NULL
Mrs	26
Mrs	52
Mrs	45
Mrs	NULL
Mrs	76

I can accomplish this using a series of filters connected to imputes then reconnected via a union but was hoping there was a more elegant solution.

Common Use Cases

Accepted answers

MarqueeCrew

How about summarizing the data with appropriate Group by titles. You can then join the summary median to the original data by title. Then a formula can be used.

IIF(isnull(value),median_slug,value)

That's what I'd do.

All comments

MarqueeCrew

phil_budden

Great, thanks Smiley Happy

rb1

Is there a way to do this with multiple columns simultaneously? I have around a hundred columns for which I'd like to replace null values with the grouped-by median values, and I'd like to not have to create 100 formulas.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703