Help reorganizing data in a table
6 views (last 30 days)
I have an excel sheet with lots of data based on recordings and measurements. These recordings and measurements are displayed in the excel sheet in a certain way. I need to reorganise this data and display it in another way. I have attached an example excel sheet with two days worth of data. The attached excel sheet also shows how I need the data to be displayed.
I have been trying to use MATLAB for nearly a week to try to get this working but I don't think I am any closer to figuring it out. I am new to MATLAB and don't have much experience using it for something like this.
I would really appreciate it if someone could explain to me in simple terms how to do this. I have lots of data to work with so doing this manually is not an option.
Cris LaPierre on 5 Aug 2021
I like Konrad's approach. You can easily reorder the results to be what you want, and it's easy to understand. I went down a rabbit hole trying to get the table to be closer to what you showed. I share it only because I already have the working code. It splits the data into separate tables, then joins them together.
% Load the data
opts = detectImportOptions("example_sheet.xlsx");
opts = setvartype(opts,["Name","Day","Category"],"categorical");
opts = setvaropts(opts,"Name","Ordinal",true);
data = readtable('example_sheet.xlsx',opts);
data = rmmissing(data,'DataVariables',"Name");
data.Name = reordercats(data.Name,"Person "+(1:14));
% Split data into subtables by activity/measurement
a1M1 = data(ismember(data.Category,"Activity 1") & ~isnan(data.Measurement1),[1:3 5:7]);
a1M1.Properties.VariableNames(4:6) = "Activity 1: "+a1M1.Properties.VariableNames(4:6);
a1M2 = data(ismember(data.Category,"Activity 1") & ~isnan(data.Measurement2),[1:3 5:6 8]);
a1M2.Properties.VariableNames(4:6) = "Activity 1: "+a1M2.Properties.VariableNames(4:6);
a2 = data(ismember(data.Category,"Activity 2"),[1:3 5:6]);
a2.Properties.VariableNames(4:5) = "Activity 2: "+a2.Properties.VariableNames(4:5);
% Join tables back into a single table
joinAct1 = outerjoin(a1M1,a1M2,"Keys",["Name","Day","Date"],...
joinAct2 = outerjoin(joinAct1,a2,"Keys",["Name","Day","Date"],...
joinedData = sortrows(joinAct2,["Date","Name"])