Why will my data repeated itself when writing to Excel?

Hello!

I found a problem when I tried my codes to write out the data of connected components. In my picture, there are only 91 objects were chose, but in my read-out Excel file I got 98 objects. I want to see why, and so I compared every line of the data and find out some repetitions in my Excel file! As I show in the picture, line 93,94,95 are repetition of line 90,91,and 92, then another repetition of the three lines and line 99 is the repletion of line 98. I don't know why this happens, because the data in the workspace are correct without any repetition. The repetition happens nearly in all of my file, except the pattern of repetition is different, as I show in the second picture from another file.

I guess it could be that my script for writing out the data is not robust. But I can't improve it. Can somebody help? Thanks!

My script:

KeeperComponentsMeasurement=regionprops(keeperComponents,'Centroid','PixelIdxList','Area'); %find the components and measure them
p=[];
q={};
    for k = 1:numel(KeeperComponentsMeasurement)
        oneIdxlist = KeeperComponentsMeasurement(k).PixelIdxList; %calculate the luminance 
        oneGray = d(oneIdxlist); %calculate the luminance
        meanGray = uint8(mean(oneGray)); %calculate the luminance
        p=[p meanGray]; %create array for luminance
        q=[q k]; % create a array for the index of components
    end
% write out the data of connected components
    pp=num2cell(transpose(p));
    qq=num2cell(transpose(q));
    KeeperComponentsMeasurement2 = rmfield(KeeperComponentsMeasurement,'PixelIdxList');
    [KeeperComponentsMeasurement2(:).MeanGrey]=pp{:}; %add a column
    [KeeperComponentsMeasurement2(:).ColonyNum]=qq{:};
    worldArea=num2cell([KeeperComponentsMeasurement.Area].*caliPara.^2); %turn the pixel into cm^2
    [KeeperComponentsMeasurement2(:).worldAreacm]=worldArea{:}; 
    xlsfilename=[name '-CC' '.xls'];
    writetable(struct2table(KeeperComponentsMeasurement2),fullfile(DataFolder, xlsfilename));

[dpb]

Not possible to diagnose without the data, sorry...undoubtedly there is repeated data somewhere; probably owing to the struct2table transition needing to duplicate fields for regularity in the transition from a structure array to a table.

It would seem cleaner to build a table and/or array instead directly.

Answer 1

[Fangjun Jiang]

I know xlswrite() has this type of problem. If the Excel file already exists, it writes the data to the existing sheet without wiping out the existing data first. Your problem might be similar. The data is there because it was the result of your previous run. Run the code below to see my point.

So delete the Excel file before you write to avoid this problem.

xlswrite('test',magic(5));
xlswrite('test',rand(3));
winopen('test.xls');

[Ziming Zhong]

Thanks Fangjun! Yeah, I get your point and also agree that running a loop for 100 analysis could cause this problem, because I tried to run the analysis one by one with this code and there was no this repeated data problem. How can I delete the Excel file in Matlab? There is no Excel table in Matlab, right? You mean I should delete KeeperComponentsMeasurement2 table?

[dpb]

Ah! Your initial code snippet doesn't illustrate that problem; there's only a single call to writetable

As the respondent says, "Don't do that!" -- either save all the data in memory first and write to a clean file/worksheet or ensure if you are writing sequentially that you START with a clean worksheet and write each subsequent section in the proper location. This would mean keeping track of how many lines have been written so far and setting the sheet RANGE input appropriately for each call.

[Ziming Zhong]

Thanks dpb! So you mean every time before I start to write my table out, I should build a new Excel file and then write my data in it? How should i build a new Excel file with using _ *xlswrite*_? I use a Mac and _ *xlswrite*_ doesn't work on my computer with no matter what I change the structure to.

[Fangjun Jiang]

Even without the loop, the problem could happen when you debug your code and you have to run your code many times with different data set, or with same data set but different results due to debugging. Anyway, in your code, add the following line should resolve the problem.

xlsfilename=[name '-CC' '.xls'];
xlsfilename=fullfile(DataFolder, xlsfilename);
if exist(xlsfilename,'file')
delete(xlsfilename);
end
if ~exist(xlsfilename,'file')
writetable(struct2table(KeeperComponentsMeasurement2),xlsfilename);
end

[Ziming Zhong]

hmm, those file names are not the same and if I run the code for the first time, they will not exist there. So I guess the exist function doesn't really work here in my case. I tried the code you provide though, and I still get the repetition but in different patten now.

[dpb]

I "know nuthink!" about what goes on behind the scenes on a non-Windows machine without Excel when you ask for a spreadsheet file to be written by the .xls extension.

Do you somehow get a file that is Excel-compatible .xls file or is it just a text file that happens to be named with the .xls extension?

It's still not really clear what your expected output is; are you writing data sequentially adding onto existing or are you trying to write over existing data and each pass should be complete in and of itself?

Need better definition of the objective and how you're doing things up front rather than just the description of what went wrong with what you did.

[Fangjun Jiang]

Could you take care of your usage of fullfile() in your code (see modified comments above) and make sure the exact file is deleted and checked for non-existence before writing to it? In my Windows MATLAB R2017b, writetable() has the similar problem.

T=table([1:5]');
writetable(T,'test.xlsx');
T=table(rand(3,1));
writetable(T,'test.xlsx');
winopen('test.xlsx');

[Ziming Zhong]

Well, I use a Mac with Excel. When I used xlswrite function, MatLab give me an error and can not go on writing the data. So no excel file in the end.

What I’m trying to do here is to analyze 100 different pictures with different names by function regionprop store all my data from one picture in memory and write all the results from one analysis (one picture for one analysis) out at one time by writetable function. Sorry I thought it was clear. Sorry. Anyway in the end I want one excel file for each analysis. So if it’s a loop for 100 pictures, then I will end up with 100 Excel files.

Sorry that I can not list all my codes here and attach the picture for test analysis, as the whole analysis code have more than 200 lines. But I guess those picture analyzing codes are fine. It’s only the data export part cause this repeatition problem.

[Ziming Zhong]

Thanks Fungjun! But I guess my case here is a bit different with what you are saying. In the two pictures I showed in my question, 1) the repeatition only happen within the same data set. I mean it’s only the last several lines of each real data were repeated in the same file,though in different patterns. 2) If it’s the problem as you said, then all my files should have the same number of rows of data. But that’s not true. They have varied lines of data. Never too many repeatition but several rows.

[dpb]

Start over by first physically deleting all .xls files you've created to date by this process to ensure what you're looking at from here on is fresh.

Then, I'd recommend trimming the loop down to just a few and work through checking that the result is correct and that your naming scheme for each file generates a unique name.

In this way, there's no way the above issue of overwriting the data of a previous case with a smaller subset or the like can be the issue.

If you then have duplicate lines there are only two choices for why --

1. your data actually is being duplicated somehow a la my initial comment, or
2. you've found a bug.

You are correct the xlswrite is implemented for Excel files only for Windows systems; writetable uses builtin or licensed methods or whatever it takes to work on Mac.

It's always best in cases such as this to just be doubly-sure talking same intent/operations even if think there can't be any misunderstandings; the code snippet shown won't write multiple files so the "how" of that wasn't defined unequivocally.

[dpb]

"Sorry that I can not list all my codes here and attach the picture for test analysis, as the whole analysis code have more than 200 lines. But I guess those picture analyzing codes are fine. It’s only the data export part cause this repeatition problem."

There's nothing preventing attaching a .mat file of an image and the code file that will reproduce the symptom on your machine.

If you can build such a sample case; that could help immeasurably as having something to actually work on beats guessing at what might be going on all to pieces.

It might be a useful exercise to just try to do the above anyway; if you can't reproduce it for a single case, that would indicate the issue is something about the previous state/file and that alone may be enough for you to find the problem yourself.