Excel Spreadsheet Analysis Help

1 次查看（过去 30 天）
matlabuser
matlabuser 2020-3-30
回答： Pranav Verma 2021-1-13
Hello,
I have an excel spreadsheet I need to analyze using Matlab. There are four colums, one for the year, the month, the day, and the dependent variable. There are about 13,000 rows. I attached a screenshot of the first 14 entries in the table. The first 3 columns represent the year, month, and day of the fourth column's (dependent variable) recording. For example, from left to right, row 7 reads: 1978, November, 3rd day, 10.7770 on that day. This continues for about 13,000 more days (rows).
My goal is to plot the monthly average of the dependent variable. In order to do so, I need to take the average value of the dependent variable for each month. Way too many data entries to do so manually, and I don't know how to do that using Matlab. The final step is a regression or a polynomial fit. I believe a linear regression will suffice. I also need some direction on this, but that's probably the easy part.
Thank you all in advance for the help.
  1 个评论
Tommy
Tommy 2020-3-30
For the averaging part, try this:
T = readtable('yourfile.xlsx');
T = sortrows(T, {'Year', 'Month'});
groups = findgroups(T.Year, T.Month); % associate each combination of Year and Month with a unique group number
avg = splitapply(@mean, T.Extent, groups);
For a linear regression, I believe this should work:
b = [ones(length(avg),1) avg]\groups;
plot(groups, avg, '.')
hold on;
plot(groups, b(1) + b(2)*avg)
from https://www.mathworks.com/help/matlab/data_analysis/linear-regression.html .

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Pranav Verma
Pranav Verma 2021-1-13
Hi matlabuser,
You can read in your data as:
T=readtable("file_name.xlsx");
To take the average value of each month, you can use the varfun function in MATLAB. Use the 'GroupingVariables' Name-Value parameter and specify value as month in the varfun and specify the function as 'mean'. This way you'll be returned a table with the mean of every month and then you can plot.
Quoting an example from the documentation:
A = table({'test2';'test1';'test2';'test3';'test1'},...
[0.71;-2.05;-0.35;-0.82;1.57],[0.23;0.12;-0.18;0.23;0.41]);
func = @mean;
B = varfun(func,A,'GroupingVariables','Var1');
Here A is defined as:
and after varfun, B is returnes as:
Thanks

类别

在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by