Mean and median at each interval

1 view (last 30 days)
IMC on 30 Sep 2021
Commented: IMC on 30 Sep 2021
My excel file has 3 columns representing Temperature (in Kelvin), Volume and Mass. At each 1°C interval I want a mean of Volume and a median of Mass. The data is from hdf file. So, basically at each temperature interval there are alot of pixels of Volume that fall at one interval (and I just need one value i.e. mean) and same goes for Mass. So, result should be something like;
Temp Volume Mass
-10 7 32
23 8 21
Data is attached. I just don't understand how to use findgroups function here. Any help would be highly appreciated. Thank you.
P.S. Exclude all the pixels with 0 and 1 value from Volume.

Accepted Answer

Konrad on 30 Sep 2021
Edited: Konrad on 30 Sep 2021
data = readtable('Data.xlsx'); % read data
data = data(~ismember(data.Volume,[0 1]),:); % exclude rows where volume=0 or volume=1
[G,grpTemp] = findgroups(round(data.Temp)); % findgroups for rounded temps, grpTemp contains the temperature for each group
meanVol = splitapply(@mean,data.Volume,G); % mean volume
medianMass = splitapply(@median,data.Mass,G); % median mass
table(grpTemp, meanVol, medianMass) % display results
Best, K.
IMC on 30 Sep 2021
Got it, Thanks alot!

Sign in to comment.

More Answers (1)

Kevin Holly
Kevin Holly on 30 Sep 2021
Edited: Kevin Holly on 30 Sep 2021
Data = importfile('Data.xls')
% Histogram
xlabel('Temperature ({\circ}C)')
% Preallocate variables
mean_Volume = zeros(size(Temp_range));
median_Mass= zeros(size(Temp_range));
Temp_range = min(floor(Data.Temp)):1:max(floor(Data.Temp));
for i = 1:1:length(Temp_range)
mean_Volume(i) = mean(Data.Volume(floor(Data.Temp)==Temp_range(i)),'omitnan');
median_Mass(i) = median(Data.Mass(floor(Data.Temp)==Temp_range(i)),'omitnan');
% Mean Volume vs Temperature (*K)
xlabel('Temperature ({\circ}K)')
ylabel('Mean Volume (units)')
title('Mean Volume vs Temperature ({\circ}K)')
% Median Mass vs Temperature (*K)
xlabel('Temperature ({\circ}K)')
ylabel('Median Mass (g)')
title('Median Mass vs Temperature ({\circ}K)')
% Create Table
function Data = importfile(workbookFile, sheetName, dataLines)
%IMPORTFILE Import data from a spreadsheet
% DATA = IMPORTFILE(FILE) reads data from the first worksheet in the
% Microsoft Excel spreadsheet file named FILE. Returns the data as a
% table.
% DATA = IMPORTFILE(FILE, SHEET) reads from the specified worksheet.
% DATA = IMPORTFILE(FILE, SHEET, DATALINES) reads from the specified
% worksheet for the specified row interval(s). Specify DATALINES as a
% positive scalar integer or a N-by-2 array of positive scalar integers
% for dis-contiguous row intervals.
% Example:
% Data = importfile("C:\Users\kevinh\Downloads\Data.xlsx", "Sheet1", [1, 3399]);
% See also READTABLE.
% Auto-generated by MATLAB on 30-Sep-2021 03:26:17
%% Input handling
% If no sheet is specified, read first sheet
if nargin == 1 || isempty(sheetName)
sheetName = 1;
% If row start and end points are not specified, define defaults
if nargin <= 2
dataLines = [1, 3399];
%% Set up the Import Options and import the data
opts = spreadsheetImportOptions("NumVariables", 3);
% Specify sheet and range
opts.Sheet = sheetName;
opts.DataRange = "A" + dataLines(1, 1) + ":C" + dataLines(1, 2);
% Specify column names and types
opts.VariableNames = ["Temp", "Volume", "Mass"];
opts.VariableTypes = ["double", "double", "double"];
% Import the data
Data = readtable(workbookFile, opts, "UseExcel", false);
for idx = 2:size(dataLines, 1)
opts.DataRange = "A" + dataLines(idx, 1) + ":C" + dataLines(idx, 2);
tb = readtable(workbookFile, opts, "UseExcel", false);
Data = [Data; tb]; %#ok<AGROW>

Community Treasure Hunt

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

Start Hunting!

Translated by