Help reorganizing data in a table

3 views (last 30 days)
ANDREW Feenan
ANDREW Feenan on 5 Aug 2021
Edited: Konrad on 6 Aug 2021
Hi,
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.
Andrew
  2 Comments
Peter Perkins
Peter Perkins on 5 Aug 2021
Andrew, this is essentially the same question you asked earlier: unstacking multiple variables. I recommend you look at the answer there.

Sign in to comment.

Accepted Answer

Cris LaPierre
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);
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
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"],...
"MergeKeys",true);
joinAct2 = outerjoin(joinAct1,a2,"Keys",["Name","Day","Date"],...
"MergeKeys",true);
joinedData = sortrows(joinAct2,["Date","Name"])
joinedData = 28×11 table
Name Day Date Activity 1: Duration_a1M1 Activity 1: Rating_a1M1 Activity 1: Measurement1 Activity 1: Duration_a1M2 Activity 1: Rating_a1M2 Activity 1: Measurement2 Activity 2: Duration Activity 2: Rating _________ _______ ___________ _________________________ _______________________ ________________________ _________________________ _______________________ ________________________ ____________________ __________________ Person 1 Monday 01-Mar-2021 NaN NaN NaN 100 4 5000 45 6 Person 2 Monday 01-Mar-2021 NaN NaN NaN 85 4 5000 60 7 Person 3 Monday 01-Mar-2021 90 3 5000 90 3 5000 50 6 Person 4 Monday 01-Mar-2021 100 4 5000 105 3 5000 60 5 Person 5 Monday 01-Mar-2021 90 4 5000 110 4 5000 60 8 Person 6 Monday 01-Mar-2021 NaN NaN NaN NaN NaN NaN NaN NaN Person 7 Monday 01-Mar-2021 NaN NaN NaN 100 5 5000 60 6 Person 8 Monday 01-Mar-2021 85 5 5000 95 7 5000 15 2 Person 9 Monday 01-Mar-2021 110 4 5000 110 5 5000 75 7 Person 10 Monday 01-Mar-2021 90 3 5000 110 4 5000 NaN NaN Person 11 Monday 01-Mar-2021 NaN NaN NaN NaN NaN NaN NaN NaN Person 12 Monday 01-Mar-2021 NaN NaN NaN 105 3 5000 NaN NaN Person 13 Monday 01-Mar-2021 95 5 5000 NaN NaN NaN NaN NaN Person 14 Monday 01-Mar-2021 95 4 5000 80 7 4000 60 7 Person 1 Tuesday 02-Mar-2021 90 3 5000 95 4 5000 55 5 Person 2 Tuesday 02-Mar-2021 95 3 5000 95 5 5000 NaN NaN
  1 Comment
ANDREW Feenan
ANDREW Feenan on 5 Aug 2021
Hi Cris,
Thank you for your help.
Andrew

Sign in to comment.

More Answers (1)

Konrad
Konrad on 5 Aug 2021
Edited: Konrad on 6 Aug 2021
Hi Andrew,
I think unstack() is what you're looking for:
T=readtable('example_sheet.xlsx');
uT = unstack(T,{'Duration' 'Rating' 'Measurement1' 'Measurement2'},'Category');
Best, Konrad
  5 Comments
Konrad
Konrad on 6 Aug 2021
Just to promote regular expressions (and because I love regexp, I admit):
The desired column names can be achieved in one line:
uT.Properties.VariableNames = regexprep(uT.Properties.VariableNames, '(^[^_]+)_([^_]+)$', '$2: $1')

Sign in to comment.

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by