How can I drop out the outliers at 1% and 99% of each ordered (or sorted) sequence of observations ???

Hi everyone!

I have two excel files: criteria.xlsx and input.xlsx.

In criteria.xlsx, I have 3 column: year (2006 -> 2010, 5 values) ; sector_id (1 -> 89, 89 values) and province_id (1 -> 64, 64 values). As aresult I have 5*89*64 = 28.480 groups of observations.

In input.xlsx, I have 4 column: year, sector_id, province_id, revenues, exist (818.616 observations in total).

I would like to separate all observations into 28.480 groups, then I want to drop outliers: 1% and and 99% percentiles in revenues for each group (the identifier of each group is described as mentioned above).

Consequently, any observation satisfies dropping _outlier criterion is assigned value 1 in the 5th column: exist.

To realize my goal, I wrote code as below:

clear all

clc

A = xlsread('criteria2.xlsx');

B = xlsread('input.xlsx');

for i=1:5

      for j=1:89

          for k=1:64

              % create filter for each group                     
              mask = B(:,1) == A(i,1) & B(:,2) == A(j,2) & B(:,3) == A(k,3);

              % sort observations in each group                     
              C = sortrows(B(mask,4));

              % figure out the values at 1% [Y(1,1)] and 99% [Y(2,1)]in each group
              Y = prctile(C,[1 99],1);

              % create filter for observations in each group satisfying dropping criterion 
              mask1 = B(mask,4) <= Y(1,1) | B(mask,4) <= Y(2,1);

              % observations satisfying dropping criterion are assigned value 1 in column "exist"
              B(mask1,5) = 1;

          end

       end

end

xlswrite('result.xlsx',B)

The problem is that my code does not run and does not export the true result. If anyone can solve my problem, fixing my code. Please help me. Thank you so much! :)

I also attached the two excel files as well as their images, so that you can feel more easily to understand what I express.

[jgg]

I can't see your actual code, but this should work:

 A = data; %load your input data into this so that your grouping variables are the first three columns
 [~,~,G] = unique(A(:,1:3),'rows'); %now, G contains an identifier for each category
 stats = grpstats(A(:,4),G,@(y) prctile(y,[1 99]')); %now this contains

 drop = zero(n,1);%size of your dataset n 

 for i=1:max(G)
   ind = (G == i);
   drop(ind) = (A(ind,4) < stats(i,1)) | (A(ind,4) > stats(i,2))
  end

Basically, group your data using the categories, the calculate the 1st and 99th percentiles for each group. Then, loop over each group and calculate whether the value exceeds these tolerances.

[Dung Le]

But I would like to drop a ORDERED (SORTED) sequence because i want to drop the 1% minimum and 1% maximum of sequence.

But in your code, I don't see this characteristic. Your code just solved 1% and 99% but have not resolved the maximum and minimum problem!

[jgg]

Yes, it has. You don't need to sort the sequence, because prctile takes care of that for you. This code finds the 99th and 1st percentiles, then indicates them with the drop variable. These are the top 1% and bottom 1% of the data by group. Just exclude these from your dataset.

[Dung Le]

Thank you for your enthusiastic support so much ^^