Databases
StackOverflow2013
Postsdbo
POsumproduct on condition that text strings match
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsumproduct on condition that text strings match
    primarykey
    data
    text
    <p>and thank you for showing interest in my post :)</p> <p><strong>Let me quickly describe the data I'm working with:</strong></p> <p>I'm important data from excel into my matlab script so that I end up with 3 vectors. These are:</p> <ul> <li><strong>"FIT_txt"</strong> (~250k rows containing mixed letters &amp; numbers in each cell. repeated entries)</li> <li><strong>"FIT_num"</strong> (same number of rows as "FIT_txt" containing numbers only)</li> <li><strong>"sector"</strong> (~5k rows containing mixed letters &amp; numbers in each cell. unique entries)</li> </ul> <p><strong>Now, what I want to achieve:</strong></p> <ol> <li><p>create a vector that counts the number of times each row in "sector" is appearing in "FIT_txt". E.g.: say the first entry in "sector" is "AB10", then I want to create a vector that counts the number of times "AB10" occurs in "FIT_txt"; this should be saved in the first row of the new vector. The second row of the new vector counts the appearances of row 2 from "sector" (say "AB11") in "FIT_txt", etc.</p></li> <li><p>create a vector that adds all the numbers in "FIT_num" that occur in the same row as the entries in "FIT_txt" that correspond to a "sector" entry, and sort it accordingly. E.g.: "AB10" in row 1 of "sector" occurs 3 times in "FIT_txt" - it occurs in row 2, 500 &amp; 2000. I want to add up rows 2, 500 &amp; 2000 in "FIT_num" and put them into the first row of the new vector.</p></li> </ol> <p>Now, this is a simplification of my problem; essentially, I am using a lot more data and I'm repeating this process to find a much larger number of new vectors/matrices. With regard to the simplification, I have managed to solve the problem by doing this:</p> <pre><code>units = zeros(length(sector),1); installed = zeros(length(sector),1); for a = 1:length(sector) for z = 1:length(FIT_txt(:,1)) if strcmp((FIT_txt(z,1)),(sector(a)))==1 units(a,1) = units(a,1) + 1; installed(a,1) = installed(a,1) + FIT_num(z,1); end end end </code></pre> <p>Unfortunately, I fear this is very inefficient and takes way too long to calculate.</p> <p>I have managed to achieve decent results for my first question (counting the appearances) using this:</p> <pre><code>units = zeros(length(sector),1); for a = 1:length(sector) units(a,1) = sum(strcmp((FIT_txt(:,1)),(sector(a))) end </code></pre> <p>That works fine (although still taking a bit longer than I would like), however, I don't know how to solve my second question (counting the values from "FIT_num" that correspond). </p> <p>I would appreciate if you could assist me in finding a solution that is as efficient as possible in solving my problem.</p> <p>Thank you very much in advance.</p> <p>John</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USFranck Dernoncourt
    1. USjcv
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POsumproduct on condition that text strings match
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POsumproduct on condition that text strings match
      1. This table or related slice is empty.
      1. VTUpMod
    1. COcan a row from `sector` appear twice per row of `FIT_txt` ? and how should the summation than go? weighted by the number of occurrences or just add or not add? Also, what datatype is FIT_txt, 250x1 cell array?
      singulars
      1. POsumproduct on condition that text strings match
      1. USGunther Struyf
    2. COThanks Gunther for your reply. Most rows in "sector" appear multiple times in "FIT_txt". I want to a) count how many times each entry from "sector" appears in "FIT_txt". b) sum all values from "FIT_num" that are on the same rows as "FIT_txt" that correspond to the same entry in "sector". Does that make sense? Sorry I find it a little difficult to express.
      singulars
      1. POsumproduct on condition that text strings match
      1. USjcv
    3. COBut can one single sector row appear multiple times in one single FIT_txt row? eg, `sector(1) = 'AB10'` and that appears two times on the first row of `FIT_txt`. Should the corresponding value from `FIT_num` now be counted twice?
      singulars
      1. POsumproduct on condition that text strings match
      1. USGunther Struyf
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload