Interp1 question on excel data with multiple columns and blank/empty cells

Hello,

I have an excel data which is a digitized plot for pressure (p) vs, distance (d), at different flame speeds.

E.g. attached, I have 11 flame speeds, and hence 11 p-columns with their 11 d-columns. For each flame speed, the pair (p vs.d) is independent of other pairs. However, # of rows is not same in all colums. E.g., Flame Speed-1 has 173 data points (rows) and Speed-2 has 55 datapoints. So, MATLAB is reading the empty rows as NaN, as expected.

At a given flame speed, and using the corresponding (p-d) pair, I want to use interp1 to estimate p for a d.

Any p-d column that has empty cells and hence NaN in MATLAB is giving Interp1 "finite data" error, as expected.

Also, Interp1 also doesn't work if I replace all NaN by a number (say 0); I am getting "absence of unique values" error.

I tried rmmissing to remove "missing data from excel", but it truncated the longer columns to the shortest column length.

To summarize my issue: how do I interpolate to calculate p at a d of 0.054 at Flame Speed 2 (attached excel sheet)? Since the p-d pair columns for Speed 1, 3, and 7 don't have NaN values, it can easily interpolate at these flame speeds. But not for other flame speeds.

Thanks for your help.

KD

Answer 1

[Voss]

% read the file:
M = readmatrix('p_d_FlameSpeed.xlsx');

% construct a cell array of two-column matrices of d and p values
% with the NaNs removed:
N = size(M,2)/2;
C = cell(1,N);
for ii = 1:N
    C{ii} = M(:,(ii-1)*2+[1 2]);
    % remove any row containing a NaN:
    C{ii}(any(isnan(C{ii}),2),:) = [];
end
disp(C);

    {173×2 double}    {55×2 double}    {173×2 double}    {144×2 double}    {137×2 double}    {142×2 double}    {172×2 double}    {133×2 double}    {126×2 double}    {91×2 double}    {69×2 double}

% perform the interpolation to find p for each speed at d = 0.054:
d = 0.054;
v = zeros(1,N);
for ii = 1:N
    % some matrices contain non-unique d values (e.g., last two 
    % elements of Speed-d10), so just use the unique d and 
    % corresponding p for the interpolation:
    [u_d,jj] = unique(C{ii}(:,1));
    v(ii) = interp1(u_d,C{ii}(jj,2),d,'linear','extrap');
    % for some speeds, d = 0.054 is outside the range of data,
    % so I'm using extrapolation. to return NaNs in those 
    % cases instead, do the following:
%     v(ii) = interp1(u_d,C{ii}(jj,2),d);
end
disp(v);

  162.0349   99.7890   59.9832   36.0560   18.7795    9.0760    6.6703    5.6201    4.8827    2.2242    1.4585

[Kanchan]

Thanks Voss.

It helps. I only need p for a d at one flame speed, not all speeds.

I understood the code, I will modify it accordingly.

Thanks again for your help.

KD

[Voss]

OK, I see. You're welcome! Glad it helps.

[Kanchan]

Hi Voss,

Sorry for bugging you again. After multiple trial, I am seeking your help.

Everything worked as I wanted, thanks to your code.

For each 2x2 matrix (C{1},C{2} etc.), if d is less than the first d (cell 1,1 of a matrix), it should use the first p (cell 1,2 of a matrix).

Similarly, For each 2x2 matrix (C{1},C{2} etc.), if d is greater than the last d (last cell column 1 of a matrix), it should extrapolate to get the p value.

I used different way to read the first and last d, for each matrix; it is reading the 1st and the last d for the last 2x2 matrix irrespective of which matrix the loop is focusing on.

Of course, there's an error, as a newbie, I am not able to.

Can you please help?

This is how I am coding, I have only shown for d smaller than the cell (1,1). As mentioned earlier, each 2x2 matrix is independent of other.

Thanks a lot for your help.

KD

 for i8=1:NMAT
         [u_d,jj] = unique(MAT{i8}(:,1));
         U(i8)=u_d(1,1); % storing the first row, first column of a 2x2 matrix MAT{i8}
     end
    
  
           
            for k4 =1:size(Rstar) %Rstar is my d value, being 
                % calculated before (not shown here)
            for i1=1:NMAT
             
              
              % Overpressure calc
               [u_d,jj] = unique(MAT{i1}(:,1));
               
               

            if Rstar(k4,1) < U(1,i1) % Comparing Rstar with cell(1,1) of a 2x2 matrix
               Rstar(k4,1) = U(1,i1);
            else
               Rstar(k4,1) = Rstar(k4,1);
            end 
            

            Overpressurestar(k4,i1) = interp1(u_d,MAT{i1}(jj,2),Rstar(k4,1));
            
              
               
            Overpressure(k4,i1) = (1.0+Overpressurestar(k4,i1))*Pa; % Pa

           
            end
            end

[Voss]

You can try something like this:

[u_d,jj] = unique(C{ii}(:,1));
v(ii) = max(C{ii}(1,2),interp1(u_d,C{ii}(jj,2),d,'linear','extrap'));

This is the same as in my answer, except taking the max of C{ii}(1,2) and the interpolated/extrapolated result, so that high values are extrapolated and low values are set to C{ii}(1,2).

[Kanchan]

Thanks Voss