Sum of Rows

I have the below sample of txt file:

Stock Date Time Price Volume Stock Category Trade

ETE 04/01/2010 10145959 18.31 500 Big Cap 1

ETE 04/01/2010 10150000 18.01 70 Big Cap 0

ABC 04/01/2010 10190000 18.34 200 Big Cap 1

YYY 04/01/2010 10200000 18.34 100 Big Cap 1

ETE 04/01/2010 10170000 18.54 430 Big Cap 1

How can I calculate the sum of the seventh column (trade) only for rows with value 'ETE' in column one?

Many thanks in advance

Panos

Answer 1

[Matt Fig]

I simply copied and pasted your data into a textfile I named stock_prices.txt, then:

% Retrieve the data...
fid = fopen('stock_prices.txt');
T = textscan(fid,'%s %10s %n %n %n %s %s %n','headerlines',1);
fclose(fid);

% Now that we have the data, do our math...
idx = cellfun(@(x) isequal(x,'ETE'),T{1});
S = sum(T{end}(idx)) % Sum last column, only rows in idx...
S = 
    2

[Pap]

Thanks Matt,

Iget the below error:

S = sum(data{end}(idx));

??? Undefined function or method 'sum' for input arguments of type 'cell'.

I can only input the file with as strings (%s) because this is compatible wit some other codes.

What could be the problem?

[Matt Fig]

I can only input the file with as strings (%s)

[Matt Fig]

I see the quoting problem is still here with us on Answers. What I actually said in the previous comment was, please explain what you mean by that line (shown above in my previous comment). How, exactly, are you reading in the data? Instead of doing what I do to create T, what are you doing there?

[Pap]

%I read the data as strings:

textscan(fid,'%s%s%s%s%s%[^\n]','headerlines',1);

% then I run a code to create the last column (trade) but I cannot run the above due to the above error message

[Matt Fig]

Why are you reading the file this way? This gives you 6 columns, whereas your header line indicates that there are 7 columns...

[Pap]

Actually I create the last column so now I want to apply the sum of the last column but only of the elements with specific values on column 1 ( ETE )

[Matt Fig]

Here is how to do it, given how you are reading the data:

fid = fopen('stock_prices.txt');

% T = textscan(fid,'%s %10s %n %n %n %s %s %n','headerlines',1);

T = textscan(fid,'%s%s%s%s%s%[^\n]','headerlines',1);

fclose(fid);

% Now that we have the data, do our math...

idx = cellfun(@(x) isequal(x,'ETE'),T{1});

valx = str2num(cellfun(@(x) x(end),T{end}));

S = sum(valx(idx)) % Sum last column, only rows in idx...