How to use the Percentile Function for Ranking purpose?

Goal:

For every date calculate lower and upper 20% percentiles and, based on the outcome, create 2 new variables, e.g. 'L' for "lower" and 'U' for "upper", which contain the ticker names as seen in the header of the table.

_ _ _ _ _

Table T (606 x 274):

_ _ _ _ _

Step by step:

% Replace NaNs with 'empty' for the percentile calculation (error: input to be cell array)
     T(cellfun(@isnan,T)) = {[]}
% Change date format
     T.Date=[datetime(T.Date, 'InputFormat', 'eee dd-MMM-yyyy')];
% Take row by row 
     for row=1:606
% If Value is in upper 20% percentile create new variable 'U' that contains the according ticker names.
% If Value is in lower 20% percentile create new variable 'L' that contains the according ticker names.
     end;

So far, experimenting with 'prctile' only yielded a numeric outcome, for a single column. Example:

Y = prctile(T.A2AIM,20,2);

Thanks for your help and ideas!

[Oleg Komarov]

Use rowfun()

[Kirby Fears]

Using the prctile function will only get you the value of a given percentile. What you're after is the column # of values above the 80th percentile and below the 20th percentile for each row. You'll want to perform a sort on each row to obtain the sorting index.

If you have 100 columns, the first 20 columns and last 20 columns after sorting are what you're interested in. To determine the first and last n columns you need, just divide n by 5.

Answer 1

[Kawee Numpacharoen]

In addition to solutions provided in the comment, you may also use rmmissing to remove missing data from each column. For example:

rmmissing(T.A2AIM)