count number of variance in CSV files

hello dear mathworkrs,

I have a CSV file consisting of 41 columnes, all the values are numeric, how can i cont numbers of variance in each column? let us say column 1 consist of 1, 100, 240,0,300,etc I need to calculat or count howmany times 1 is repeated in column1 , howmany times 100 is repeated and soo on, I attached sample of my data.

thanks for help

Answer 1

[Star Strider]

Some of your columns have all the same values, so for those, just take whatever number is in the first row as the value and the row size is the the number of occurrences. You can find those columns with:

ConstCols = find(ChkCols == 0);

returning the column numbers in ‘ConstCols’.

For the others, this works:

D = xlsread('kdd.xlsx');
ChkCols = sum(diff(D));                                         % Check For Columns Having All The Same Value
ValidCols = find(ChkCols ~= 0);                                 % Columns With Different Values
for k = 1:numel(ValidCols)
    [Ucol,~,ix] = unique(D(:,ValidCols(k)));                    % Unique Values For This Column
    knts = accumarray(ix,1);                                    % Counts For This Column
    Out{k,1} = ValidCols(k);                                    % Column Number
    Out{k,2} = [Ucol, knts];                                    % Values & Counts
end

producing for example:

Result_9 = Out{9,1}
Result_9 = Out{9,2}

Result_9 =
    37

Result_9 =

            0          175
         0.01           93
         0.02           64
         0.03          157
         0.04          244
         0.05          259
         0.06           36
         0.07            5

So ‘Out{9}’ is column #37, and it has 175 repeats of 0, 93 repeats of 0.01, and so for the rest.

[mohammad Alsajri]

Dear Star Strider, thanks for your answer, I excute your code and get the result

it is not cleare enogh and i guess some thing is missing

[Star Strider]

You only appear to be looking at ‘Ucol’, the unique values in the column.

To see everything (the unique values and the counts), you need to look at the ‘Out’ cell array of matrices.

[mohammad Alsajri]

thank you very much now its claer ,

this command ConstCols = find(ChkCols == 0); dosnot retuten any value when excuted ?

[Star Strider]

It does when I run it, and you can also use it to return the constant values in those columns:

ConstCols = find(ChkCols == 0)
ConstVals = D(1,ConstCols)

produces:

ConstCols =
  Columns 1 through 19
     1     2     3     4     7     8     9    10    11    12    13    14    15    16    17    18    19    20    21

  Columns 20 through 33
    22    25    26    27    28    29    30    31    35    38    39    40    41    42

ConstVals =
  Columns 1 through 19
     0     1     1     1     0     0     0     0     0     1     0     0     0     0     0     0     0     0     0

  Columns 20 through 33
     0     0     0     0     0     1     0     0     0     0     0     0     0     1

To see the results, remove the semicolon (;) from the end of the line. With the semicolon, it just puts those values in your workspace without displaying them.

[mohammad Alsajri]

thanks alot, now its working

[Star Strider]

As always, my pleasure!