Excel Spreadsheet Analysis Help

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.

[Tommy]

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]

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