problems with intersect and cellfun functions

Question

Mark Bodner 2018-8-6

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/413797-problems-with-intersect-and-cellfun-functions

评论： Mark Bodner 2018-8-8

I'm using the script below to try to find all the matches in a column of 2 excel files. The intersect function returns indices which are out of bounds for the first cell (patternsize is 41351 x 1, dataP is 2000 x 1). The first cell of "ic" is 41352, and for "id" it is 6001. The first cell of D is then blank. When I try to pass all of this on in the next line, everything blows up and I get the error 'Index exceeds matrix dimensions'. The script seemed to work fine with other files with different dimensions. I'm pretty much a novice with this, so any suggestions as to what is going wrong is greatly appreciated.

[numdataP,patternsizeP]=xlsread('state.xlsx');
[~,dataP]=xlsread('pinellas.xlsx');
[D,ic,id] = intersect(patternsizeP,dataP)
indexP=cellfun(@(x)find(ismember(dataP,x)==1),D,'uniformoutput',false)

6 个评论
显示 4更早的评论隐藏 4更早的评论

Mark Bodner 2018-8-7

Attached are the sample files. What I’ve been trying to do is the following. The file “state” has in the first column single entries of different codes (e.g. ‘PIN159614’). The file ‘pinellas’ can have multiple matches to these codes (e.g. 10 to 30 matches to ‘Pin159614). The goal is to find all the matches, and then output all of those matches in one column of a table or array, along with other information grabbed from other columns from the ‘state’ and ‘pinellas’ files. So for example all the matches for PIN159614 from column 1 in state that are found and put in the first column, and all of the corresponding entries from column 2 in Pinellas are put in the next column of the output, and the single entry from column 16 in state (which could be repeated for each match in Pinellas) would be put into the output in column 3 etc. So the final output could be something like

PIN159614 SO5 358

PIN159614 SOA 358

PIN159614 SOD 358

.

I had a script which did this using while/for loops that took days or even weeks to run with the files (which are on the order of 700,000 x 25). Using the code utilizing the function is very rapid but I can’t get it to index properly and return what I need. Attached are examples of the data. I’m really sorry if I breached some protocol which I was truly unaware of regarding the help (i.e. comment regard OP cooperating). If so let me know so there are no further breaches. I really appreciate the help. Thanks very much.

jonas 2018-8-7

编辑：jonas 2018-8-7

I am the same person who worked with you previously, so I know the background.

The script works fine for me, probably because you did not include the entire data set. For some reason, xlsread grabs an empty row at the end of the excel-sheet when importing state.xlsx. That's why you get an empty cell. You should make sure to either not import this cell (either directly on import or remove it afterwards). As a first test, you can manually specify the range of cells to import from excel. I think this may solve your problem.

In addition, I believe you are only using the first column of patternsizeP so make sure to REMOVE all other columns (or better yet, don't import them). Right now you are jamming a bunch of unnecessary data into the intersect function, which is likely to cause issues.

If none of the above works, make sure to upload a segment of data for which the error is reproduced, so that we can find the bug.

I think dsb was referring to you cooperating by submitting data, don't worry about it :)

Mark Bodner 2018-8-8

Thanks. Yes I realized that it's grabbing an empty cell at the end, but no matter what I do, it does so. The program seems to ignore whatever I import into the workspace and just goes for the files in the matlab folder. No matter what I do to these files or what I import, the empty cell still gets grabbed. I am using column 17 (and would like to use more) from patternsizeP, but I'll try removing a bunch of those columns to see what effect it has. Thanks again.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Stephen23 2018-8-7

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/413797-problems-with-intersect-and-cellfun-functions#answer_331855

编辑：Stephen23 2018-8-7

在 MATLAB Online 中打开

>> [~,~,rawS] = xlsread('state.xlsx');
>> [~,~,rawP] = xlsread('pinellas.xlsx');
>> [idxP,idxS] = ismember(rawP(:,1),rawS(:,1));
>> out = rawS(idxS(idxP),[1,1,16]);
>> out(:,2) = rawP(idxP,2)
out = 
    'PIN159614'    'SOO'    [271]
    'PIN159614'    'SO1'    [271]
    'PIN159614'    'SOA'    [271]
    'PIN159614'    'SO0'    [271]
    'PIN159614'    'SO3'    [271]
    'PIN159614'    'SO2'    [271]
    'PIN159614'    'SO5'    [271]
    'PIN159614'    'SOE'    [271]
    'PIN159614'    'SOD'    [271]
    'PIN159614'    'SOG'    [271]
    'PIN159614'    'SO6'    [271]
    'PIN725789'    'SOA'    [262]
    'PIN725789'    'SOC'    [262]
    'PIN725789'    'SOB'    [262]
    'PIN725789'    'SOE'    [262]
    'PIN725789'    'SOD'    [262]
    'PIN725789'    'SOG'    [262]
    'PIN725789'    'SOL'    [262]
    'PIN725789'    'SOO'    [262]
    'PIN725789'    'SO1'    [262]
    'PIN725789'    'SO0'    [262]
    'PIN725789'    'SO3'    [262]
    'PIN725789'    'SO2'    [262]
    'PIN725789'    'SO5'    [262]
    'PIN725789'    'SO7'    [262]
    'PIN725789'    'SO6'    [262]
    'PIN725789'    'SO8'    [262]

5 个评论
显示 3更早的评论隐藏 3更早的评论

Mark Bodner 2018-8-8

newscriptcode.m

Yes that is the name. The output of the command is C:\Users\mbodner\Downloads\MathWorks\R2017b\newscriptcode.m

The files 'pinellas' and 'state' are in the same location. The file is open and I'm just hitting the green "run" arrow.

Mark Bodner 2018-8-8

Solved the problem. Foolishly copied over >> from your code in each line. Removed it and everything works great.

请先登录，再进行评论。

problems with intersect and cellfun functions

6 个评论
显示 4更早的评论隐藏 4更早的评论

采纳的回答

5 个评论
显示 3更早的评论隐藏 3更早的评论

更多回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

problems with intersect and cellfun functions

6 个评论 显示 4更早的评论隐藏 4更早的评论

采纳的回答

5 个评论 显示 3更早的评论隐藏 3更早的评论

更多回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

6 个评论
显示 4更早的评论隐藏 4更早的评论

5 个评论
显示 3更早的评论隐藏 3更早的评论