How can I write a script to replace a specific integer value in my table, with the average of the data above and below that integer in the table

Basically, I am trying to write a script to be able to find the value 999 in my table, and add the nearest possible values above and below the 999.

For example, if 999 were a value in row 10, column 4, I would want to add 12+18, then divide by 2, and then replace that 999. If 12, 16, 18 were all set to 999, I would want to add 10 and 22, then divide by 2. Then replace 12, 16, 18 all with 16.

Complete beginner to matlab, so I don't understand the syntax of how I would do this. Is this even possible with any sort of loop?

[Stephen23]

@JJ: please upload the original data file by clicking the paperclip button.

You could try FILLMISSING with its LINEAR option:

https://www.mathworks.com/help/matlab/ref/fillmissing.html

[dpb]

With the apparent pattern, I think I'd replace with the next expected value in the series...

[JJ]

@dpb what function could make me fill it with the expected value?

[JJ]

@Stephen23 I will try the FILLMISSING, the original file is too big to attach.

Answer 1

[sai charan sampara]

Hello JJ,

You can use logical indexing to find all the index values containing the value "999". You can replace these values with "NaN" values and then use "fillmissing" with "linear" method. In this case if 12,16,18 were all set to 999 then the values will be re written as 13,16,19 instead of all three being 16,16,16. If having all three as 16 is necessary, you can try something similar to the code below:

load patients
T = table(Age,Height,Weight,Systolic,Diastolic, ...
          'RowNames',LastName);
T=T(1:20,:);
idx=[3,5,8,9,10,13,16,17];
T{idx,"Weight"}=999;% Generating example data
T

T = 20x5 table
                Age    Height    Weight    Systolic    Diastolic
                ___    ______    ______    ________    _________

    Smith       38       71       176        124          93    
    Johnson     43       69       163        109          77    
    Williams    38       64       999        125          83    
    Jones       40       67       133        117          75    
    Brown       49       64       999        122          80    
    Davis       46       68       142        121          70    
    Miller      33       64       142        130          88    
    Wilson      40       68       999        115          82    
    Moore       28       68       999        115          78    
    Taylor      31       66       999        118          86    
    Anderson    45       68       128        114          77    
    Thomas      42       66       137        115          68    
    Jackson     25       71       999        127          74    
    White       39       72       202        130          95    
    Harris      36       65       129        114          79    
    Martin      48       71       999        130          92    

idx2=find(T{:,'Weight'}==999);
arr_new=T{:,'Weight'};
for i=1:length(idx2)
    i_before=idx2(i)-1;
    i_after=idx2(i)+1;
    while(T{i_before,'Weight'}==999||T{i_after,'Weight'}==999)
        if(T{i_before,'Weight'}==999)
            i_before=i_before-1;
        end
        if(T{i_after,'Weight'}==999)
            i_after=i_after+1;
        end
    end
    arr_new(i_before+1:i_after-1)=(T{i_before,'Weight'}+T{i_after,'Weight'})*0.5.*ones(length(i_before+1:i_after-1),1);
end
T{:,"Weight"}=arr_new;
T

T = 20x5 table
                Age    Height    Weight    Systolic    Diastolic
                ___    ______    ______    ________    _________

    Smith       38       71        176       124          93    
    Johnson     43       69        163       109          77    
    Williams    38       64        148       125          83    
    Jones       40       67        133       117          75    
    Brown       49       64      137.5       122          80    
    Davis       46       68        142       121          70    
    Miller      33       64        142       130          88    
    Wilson      40       68        135       115          82    
    Moore       28       68        135       115          78    
    Taylor      31       66        135       118          86    
    Anderson    45       68        128       114          77    
    Thomas      42       66        137       115          68    
    Jackson     25       71      169.5       127          74    
    White       39       72        202       130          95    
    Harris      36       65        129       114          79    
    Martin      48       71        130       130          92    

[Walter Roberson]

You can use logical indexing to find all the index values containing the value "999". You can replace these values with "NaN" values and then use "fillmissing" with "linear" method.

You do not happen to show that algorithm.

One way to replace the 999 with NaN is to use standardizeMissing

Answer 2

[dpb]

"what function could make me fill it with the expected value?"

There isn't a builtin that will do that automagically, but if you save a lookup table of the pattern, then

HR_PAT=[0 4 6 10 12 16 18 22];                      % the pattern 
h=repmat(HR_PAT,1,5).';                             % base vector
N=5;                                                % number bad elements
isbad=sort(randperm(numel(h),N))

isbad = 1x5

7 14 16 17 37

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

h(isbad)=999;                                               % the bad locations
h(isbad)=interp1(HR_PAT-0.1,HR_PAT,h(isbad-1),'next',0);    % fill with the next; from previous, extrap --> 0
h

h = 40x1

0 4 6 10 12 16 18 22 0 4

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

Observe the value 18 in the seventh location above that was set to 999 as bad element.

The "trick" is using the HR_PAT-0.1 vector as the x variable in the interp1 call; even with the 'next' method, interp1 returns the exact location if it finds an exact match as it always would if the X lookup values were the exact pattern values. To illustrate that (imo unfortunate) design choice observe

>> interp1(HR_PAT,HR_PAT,22,'next',0)
ans =
         22.00

returned the same value as the lookup input value when is identical match.

To start over again, the value of a lookup for an input of 22 the next element is out of range so the 0 extrapolation value is returned.

>> interp1(HR_PAT-0.1,HR_PAT,22,'next',0)
ans =
             0 

returned the extrapolated value to begin the sequence over again. Note this is again using the "-0.1" X table values to ensure no exact match so the interpolant is the next table value.

The values to lookup are the location of the previous location prior to each bad value, hence h (isbad-1)