現在この質問をフォロー中です
- フォローしているコンテンツ フィードに更新が表示されます。
- コミュニケーション基本設定に応じて電子メールを受け取ることができます。
How to make a linear regression from an excel file
22 ビュー (過去 30 日間)
古いコメントを表示
Nick Elias
2021 年 3 月 15 日
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!
4 件のコメント
Rik
2021 年 3 月 15 日
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.
Nick Elias
2021 年 3 月 15 日
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
2021 年 3 月 15 日
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
2021 年 3 月 15 日
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.
回答 (1 件)
Rik
2021 年 3 月 15 日
編集済み: Rik
2021 年 3 月 18 日
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
27 件のコメント
Nick Elias
2021 年 3 月 16 日
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
2021 年 3 月 16 日
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
2021 年 3 月 16 日
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
2021 年 3 月 16 日
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
2021 年 3 月 17 日
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
2021 年 3 月 17 日
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
2021 年 3 月 17 日
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
2021 年 3 月 17 日
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
2021 年 3 月 17 日
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
2021 年 3 月 17 日
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
2021 年 3 月 17 日
編集済み: Nick Elias
2021 年 3 月 17 日
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
2021 年 3 月 17 日
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
2021 年 3 月 17 日
Right on, I was trying to nest the function inside the script.
Perfect now, thank you Rik, MVP for a reason :)
Rik
2021 年 3 月 17 日
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
2021 年 3 月 17 日
編集済み: Nick Elias
2021 年 3 月 17 日
Thanks Rik, can you please put back the code to retrieve the max/mean/min strain out of each cycle?
Rik
2021 年 3 月 17 日
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
2021 年 3 月 17 日
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
2021 年 3 月 19 日
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
2021 年 3 月 19 日
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
2021 年 3 月 19 日
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
2021 年 3 月 19 日
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
2021 年 3 月 19 日
I couldn't find the right documentation. I searched for second output but couldn't find something close to my situation.
Rik
2021 年 3 月 19 日
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
2021 年 3 月 23 日
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
2021 年 3 月 23 日
編集済み: Rik
2021 年 3 月 23 日
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
参考
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!エラーが発生しました
ページに変更が加えられたため、アクションを完了できません。ページを再度読み込み、更新された状態を確認してください。
Web サイトの選択
Web サイトを選択すると、翻訳されたコンテンツにアクセスし、地域のイベントやサービスを確認できます。現在の位置情報に基づき、次のサイトの選択を推奨します:
また、以下のリストから Web サイトを選択することもできます。
最適なサイトパフォーマンスの取得方法
中国のサイト (中国語または英語) を選択することで、最適なサイトパフォーマンスが得られます。その他の国の MathWorks のサイトは、お客様の地域からのアクセスが最適化されていません。
南北アメリカ
- América Latina (Español)
- Canada (English)
- United States (English)
ヨーロッパ
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
アジア太平洋地域
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)