How to make a linear regression from an excel file

Hi all,

This question is related to mechanics of materials if anyone can help. I have an excel file (a large one, 102k rows) with three columns: cycle count, force and strain.

Data was taken at a frequency of 8Hz (8 datapoints/sec, each cycle count lasts for about 40 rows) and I need to write a code to find the two points in each cycle (one in the increasing portion of the cycle, the other in the decreasing portion) where the correlation between strain and force becomes no longer linear (i.e. R^2 less than 95%), that is the elastic limit of the material. Basically the output would be a matrix with a number of rows equal to the number of cycles and three columns: cycle count, max elastic limit, min elastic limit.

Thank you for anything!

[Rik]

The first step would be to load your data to Matlab. Then you will need to separate the cycles. Only then you can start fitting a linear function on parts of your data. Which step is causing you problems? Note that attaching example data might help in solving your issue.

Have a read here and here. It will greatly improve your chances of getting an answer.

[Nick Elias]

I have no issue loading the file onto Matlab, the coding part is what's causing me a headache (I'm better at Excel but it can't handle this sort of stuff). I attached a small fraction of a data file.

[Rik]

OK, so which part of the coding is causing you trouble? From what I understand you want to do an analysis on the parts of your data separatly for every value of cycle. How did you try to separate the several chunks?

[Nick Elias]

I have no clue about how to go about that in Matlab, basically the array size will not be the same for every cycle as I have to keep track of the absolute value of strain and calculate the correlation for a set of datapoints until the strain trend changes direction.

Answer 1

[Rik]

You can use the unique function and a loop.

(edited to include a custom function to extract a specific strain value)

data=xlsread('Sample Data.xlsx');
count=data(:,1);
strain=data(:,2);
force=data(:,3);
unique_counts=unique(count);
elasticlimit=zeros(size(unique_counts));
meanstrain=zeros(size(unique_counts));
for n=1:numel(unique_counts)
    c=unique_counts(n);
    rows=ismember(count,c);
    strain_=strain(rows);
    force_=force(rows);
    
    %now you have the strain and the force for a specific cycle
    %here you can do the processing you need
    
    elasticlimit(n)=find_elasticlimit(force_,strain_);
    %you can also use simple functions here, like mean():
    meanstrain(n)=mean(strain_);
end
disp(elasticlimit.')

    0.0109   -0.0084    0.0060   -0.0093    0.0070   -0.0098       NaN

function elasticlimit=find_elasticlimit(force,strain,thres)
%Write an explanation for what this function does here.
if nargin<3,thres=0.99;end%set default
found=false;
for N=2:numel(strain)
    R=corrcoef(strain(1:N),force(1:N));
    R=R(2);
    if R<thres,found=true;break,end
end
if found
    elasticlimit=strain(N-1);
else
    %return an error condition or error
    %you should decide what is appropriate
    elasticlimit=NaN;
end
end

[Nick Elias]

I think something was lost in the way I communicated. For each unique cycle count, I need the max and min of force and strain during that cycle count to be able to compute the elastic limit.

[Rik]

I showed you how to do something with the force and the strain for each unique cycle count. How can you modify my code to reach the result you need? Your comment sounds like you just need to use min and max instead of mean.

[Nick Elias]

It worked (partially), after spending sometime analyzing the results the methodology I thought of to bypass correlation didn't yield accurate results.

Basically every cycle can be split into 4 stages: strain & force increase (positive strain sign) to the peak strain, then decrese (positive sign), cross 0 and keep decreasing (negative sign) down to the peak (negative) strain before increasing back up (negative sign) and once the strain is positive again it marks the transition to the next cycle. The stage of interest to retrieve the elastic limit strain is the first one.

Looking at the excerpt from the data file, I attached an updated version that reflects what I would like to achieve. Basically for the first stage from every cycle, we need to calculate the correlation between strain and force vectors until it drops below 99%. The last strain point at which the calculated correlation is 99% or higher is our elastic limit and is extracted. I highlighted it on the attached worksheet.

Thank you inifintely for your help on this, Rik!

[Rik]

You can use my answer to split the cycles. That is the first step. Now you continue to the next step. 1 Google search taught me how to calculate the correlation coefficient with Matlab:

N=16;
R=corrcoef(strain_(1:N),force_(1:N));
R=R(2);
fprintf('R=%.6f\nstrain=%.6f\n',R,strain_(N))

R=0.990589
strain=0.010875

So now you have to figure out how you can determine the value of N where the next value of R will drop below 0.99. That should not be too hard by now (hint: don't calculate what the next R will be, just calculate R and subtract 1 from N if you drop below 0.99, and be careful to not make N larger than the number of elements in strain_ and force_).

[Nick Elias]

My brain is fried on this one, I didn't get your logic on this one and why you picked a specific number for N.

The desired result would be an array of strains with one column and number of cycles as rows (variable c in your code). Once an elastic strain is obtained for one cycle we need to move to the next one until c.

[Rik]

I didn't pick N, you did. You can use a loop that increments N until you reach your required condition.

I will write something once I'm back at computer.

[Nick Elias]

Great thank you!

[Rik]

In the meantime you can try if you can use a loop to find a solution yourself.

[Nick Elias]

I tried placing a loop inside yours but didn't seem to work, it's deep into the night here and can't think of it properly.

first define the elastic limit array and prepopulate it

elasticlimit = zeros(c,1);
for k=1:c
if(R=corrcoef(strain_(1:k),force_(1:k))<0.99)
elasticlimit(k,1)==strain_(k-1)
end

[Rik]

Your instinct is correct: your code is doing too many things. You can split the work by creating a new function:

data=xlsread('Sample Data[1].xlsx');
count=data(:,1);
strain=data(:,2);
force=data(:,3);
unique_counts=unique(count);
elasticlimit=zeros(size(unique_counts));
for n=1:numel(unique_counts)
    c=unique_counts(n);
    rows=ismember(count,c);
    strain_=strain(rows);
    force_=force(rows);
    
    %now you have the strain and the force for a specific cycle
    %here you can do the processing you need
    elasticlimit(n)=find_elasticlimit(force_,strain_);
end
disp(elasticlimit.')

    0.0109   -0.0084    0.0060   -0.0093    0.0070   -0.0098       NaN

function elasticlimit=find_elasticlimit(force,strain,thres)
%Write an explanation for what this function does here.
if nargin<3,thres=0.99;end%set default
found=false;
for N=2:numel(strain)
    R=corrcoef(strain(1:N),force(1:N));
    R=R(2);
    if R<thres,found=true;break,end
end
if found
    elasticlimit=strain(N-1);
else
    %return an error condition or error
    %you should decide what is appropriate
    elasticlimit=NaN;
end
end

[Nick Elias]

It doesn't work, the output I get is just a zeroed out column, basically this: elasticlimit=zeros(size(unique_counts));

I'm running the analysis on the actual large file, I'm attaching it for reference.

[Rik]

I don't get only zeros. Did you run the exact code I posted?

I do get many NaN as a result, which signals that in many cases the correlation doesn't drop below 0.99.

[Nick Elias]

Same exact code, running on 2016a.

function is highlighted with this error: Parse error at function usage might be invalid matlab syntax

Could it be the reason?

[Rik]

Yes, you will need to put the function in a separate file, or make your script a function.

Putting a function inside a script file is only a valid syntax since R2016b.

[Nick Elias]

Right on, I was trying to nest the function inside the script.

Perfect now, thank you Rik, MVP for a reason :)

[Rik]

You're welcome.

I will edit my answer to include the function.

If you feel my answer solved your issue, please consider marking it as accepted answer. Otherwise, feel free to post a comment with your remaining issues.

[Nick Elias]

Thanks Rik, can you please put back the code to retrieve the max/mean/min strain out of each cycle?

[Rik]

Why? Doesn't the current form of the answer make more sense? You can easily put it in your code if you need it.

[Nick Elias]

No the current code is fine. It's just for future reference as I'm trying more complicated pieces of data (a lot more columns than the three we're dealing with here). I know that chunk of code was up there (with mean I reckon) but you redacted it.

[Nick Elias]

Rik,

Not sure if you're still around but I came across another issue.

As I said I wanted I needed the max and min strains/forces from each cycle but at times the highest and min force from a certain cycle shows up on the following cycle (due to fast data collection) and I end up with a duplicate.

So basically I need to retrieve the highest strain from each cycle, which I get by this line:

minstrain = min(strain_);
maxstrain(n) = max(strain_);

Though I need to retrieve the force corresponding to that max strain in every cycle, which is not necessarily the max force in every cycle. Same for the min force corresponding to the min strain in that cycle.

Thanks!

[Rik]

I don't fully understand what you mean, but it sounds like you want the second output of max and min. If you want the max force over your entire dataset, why would you use force_?

[Nick Elias]

The output I need is the force recorded at each of maximum and minimum strain in each cycle. Basically for each minstrain(n) and maxstrain(n) I need the force recorded for that row.

[Rik]

That does indeed sound like you need the second output of max and min. Did you read the documentation and try to understand the examples?

[Nick Elias]

I couldn't find the right documentation. I searched for second output but couldn't find something close to my situation.

[Rik]

Both the min and max functions have a second output parameter. What does it do? It gives you the position in the vector where this minimum/maximum value was found. You need to use that to extract the corresponding value from the other array.

I don't know if Onramp will teach you this specific thing, but it seems like you could use a good tutorial. You might also try to find someone physically near you who can guide you in an interactive manner.

[Nick Elias]

I tried going over Onramp but there wasn't something covering the second output of max/min. No Matlab users around me either so I'm stuck. I used the search function but couldn't come across a similar situation.

[Rik]

Unaccepting my answer comes across as a bit childish.

You need to use the second output of min. That second output contains indices. You can use those indices on the original vector you use in min, or for another vector.

v1=[9 0 6];
v2=[2 4 6];
[val,ind]=min(v1)

val = 0

ind = 2

v2(ind)

ans = 4