Eliminate Outliers

[yxz152830]

Gurus,
I have a set of data with a portion of them with ID having more than 1 occurrences. 2nd column is value. I want to get rid of the outliers by comparing the values for IDs appearing more than 2 times. Could someone give me any suggestions? Thanks!

Criteria 1: Any values with same ID that are less than 20% different from each other(always smaller value divided by bigger value) can be grouped together to form the majority group. For example, for all values associated with C, 110/112/111 are not outliers but if there is value 88 it will be considered outlier because 88/110 = 80%, 88/112 <80%.
Criteria 2: The majority group just need to have 1 more value than minority like ID "D"
Criteria 3: if there are no majority group and the values are significantly different than others, ID will be flagged.

A	10​
B	120​	no majority group ID is flagged
B	1​	no majority group ID is flagged
C	110​
C	112​
C	111​
C	10​	outlier
C	20​	outlier
D	50​
D	51​
D	10​	outlier
E	10​	No majority group, ID is flagged
E	20​	No majority group, ID is flagged
E	30​	No majority group, ID is flagged
F	20​	No majority group, ID is flagged
F	21​	No majority group, ID is flagged
F	1​	No majority group, ID is flagged
F	1.1​	No majority group, ID is flagged
G	21​
G	20​
G	5​
G	1​

 

[awoohaw]

why aren't the first two of E considered outliers, as they are 33 and 66%, which are less than 80% of 30?

I guess I don't understand what your definition of a majority group is. please explain what a majority group is in your context?

 

[yxz152830]

why aren't the first two of E considered outliers, as they are 33 and 66%, which are less than 80% of 30?

I guess I don't understand what your definition of a majority group is. please explain what a majority group is in your context?

Hi there are 3 'E's in the data set which are significantly different in values. There's no majority group 1:1:1 hence no outlier, this ID "E" should have been flagged for further investigation.

 

[yxz152830]

why aren't the first two of E considered outliers, as they are 33 and 66%, which are less than 80% of 30?

I guess I don't understand what your definition of a majority group is. please explain what a majority group is in your context?

a majority group in my definition for now is a group of values with same ID, less than 20% different from each other within the group, by count 1+ more than any other minor group for the same ID.
For example if Z has 11 values, 6 of them being 20, 5 of them being 10, the group with 6 values would be the majority.

 

[yxz152830]

why aren't the first two of E considered outliers, as they are 33 and 66%, which are less than 80% of 30?

I guess I don't understand what your definition of a majority group is. please explain what a majority group is in your context?

The end goal is:
eliminate the outliers and flag the IDs with no majority group to apply the rule.