Excel Spreadsheet Analysis Help

Question

matlabuser 2020-3-30

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/514089-excel-spreadsheet-analysis-help

回答： Pranav Verma 2021-1-13

Screen Shot 2020-03-30 at 2.49.58 PM.png

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

Tommy 2020-3-30

在 MATLAB Online 中打开

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 .

请先登录，再进行评论。

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

Answer 1

Pranav Verma 2021-1-13

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/514089-excel-spreadsheet-analysis-help#answer_596798

在 MATLAB Online 中打开

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');