Analyzing the occurrences of strings within cells using loops

Good afternoon Matlab forums!

I've been working on a code to sort an array of data that includes both numbers and string values. Basically, within column 1 I have identification #'s, in column 2, I have strings, and in column 3 I have charges to be summed.

I am attempting to extract a histogram which shows the occurrences of the strings in column 2 based on particular conditions of the set. Basically, for each unique column 1 ID#(There are multiple occurences of the same here), I wish to check if Column 3 adds up to a particular threshold, in this case being 10,000. Here is a sample of my current code.

Q = cellfun(@str2num,SUB_ID,'UniformOutput',false);
D = cell2mat(Q);
concentated = [D C];
unique_ids = unique(concentated(:,1));
n_ids = length(unique_ids);
for i = 1:n_ids;
  A_id = concentated(concentated(:,1) == unique_ids(i),:);
  sum_id = sum(A_id(:,2));
  if sum_id >= 10000
    disp(unique_ids(i))
    disp(sum_id)
  end
end

The beginning of this code is me creating a 2 column matrix with the data in the array, excluding the previous "Column 2" data, as I can't seem to figure out how to make these loops work within in array as opposed to a matrix. Any help would be appreciated!!!

Thanks, J

[dpb]

How about a sample (short) data file illustrating precisely what you have and how it's stored and then the desired output for that dataset...I'm having trouble parsing exactly what you're looking for.

[Julian Cruz]

SUB_ID      SERV_PROV   CHRG_AMT  
002482720  203725622  450.00
032342720  203725622  450.00
002482720  352308681  215.00

Ok, so I want the code to parse CHRG_AMT through each SUB_ID, checking to see if the total charges are above 10,000 for each SUB_ID. If so, then I would like to then create a histogram of SERV_PROV occurrence over the entire file considering all ID's that have CHRG_AMT over 10k. My current code basically outputs the SUB_ID and total CHRG_AMT if it is over 10k. My issue lies in that the SERV_PROV field contains both numbers and letters, preventing me from using matrix operations on the entire data set.

[dpb]

OK, that helps and thanks for the short data set...but, it would be good to have some that represent the general case and how they're stored. How about attaching a set of 100 or so so that the condition would be true and would be able to have a set of providers over which could try the histogram idea.

I think I'd approach the first part a little differently, though--for your set in an array x

>> [u,~,iu]=unique(x(:,1)); % unique IDs, index
>> s=accumarray(iu,x(:,3))  % compute sum by ID
s =
 665
 450
>> ix=s>500;  % check against threshold--logical address
>> [u(ix) s(ix)]  % result for those above threshold
ans =
   2482720         665
>>

Note I used $500 as the threshold to have a positive result; you want $10k but it's trivial to set at any level this way, even after the fact it's just screening the result already obtained for each ID. I'm pretty sure there's a way to handle the other--I could make up some data but it'd be better to have the real mccoy...

[Julian Cruz]

Here I attached a sample of the data, 300 lines worth. I'm relatively new to this forum so I don't know the etiquette when it comes to attaching files, but this is as close to the real mccoy as I can get! It never ceases to amaze me what one can do with logical indexing. Thanks, Julian

[dpb]

Well, bummer! There's a but in Firefox that it renders the ASCII file content as text for download links from the Forum. I had not uprgraded and had a workaround w/ the earlier release but last night I accidentally missed the 'cancel' on the nag screen and the upgrade happened. I can't get to the .xlsx file from here (not your fault; it's the browser here and I don't have another installed). How about attaching a text copy of the data within the spreadsheet? I can get to that, I think...or, use my contact info at the Contributors page and just e-mail me the file itself and I'll try to get a chance to look later on...

[Walter Roberson]

Right click and Save Link As, and then open the downloaded file.

[dpb]

As noted in the other thread, that worked w/ the earlier release; it's not working this AM after the upgrade--tried several incantations/variations with no success, unfortunately.

Answer 1

[Guillaume]

I'm using a table here instead of cell arrays but the principle is the same. It's not clear if your SUB_IDs are always numerical or can also contain other symbols but in any case, you don't need to convert it to number. That's an unnecessary slow step. accumarray, ismember and some simple indexing will avoid any loop:

db = readtable('MatBookSample.xlsx'); %or read as cell array
[ids, ~, idlocations] = unique(db.SUB_ID);  %get all unique id values and their location
idcharge = accumarray(idlocations, db.CHRG_AMT); %calculate charge amount per id, in the same order as ids
idovers = find(idcharge > 10000);  %index of ids whose charge amount is above threshold
servovers = db.SERV_PROV(ismember(idlocations, idovers)); %corresponding serv_prov

To create the histogram you can use unique and histcounts:

[servs, ~, servasint] = unique(servovers);
servhist = histcounts(servasint, [1:numel(servs)+1]);
bar(servhist);
set(gca, 'XTickLabel', servs);
set(gca, 'XTickLabelRotation', 45);

[dpb]

My thinking above Guillaume, just figured if could check it functioned as thunk it should on some actual data before posting it...

[Julian Cruz]

Thanks to both of you for the help! I really appreciate the community helping novices like myself better understand this software and its myriad capabilities!