How can I create a mat file which changes the values of a column in excel table into the mat file's column headers?
4 views (last 30 days)
Show older comments
ADJE JEREMIE ALAGBE
on 22 Jul 2021
Commented: Walter Roberson
on 28 Jul 2021
Hi, everyone! I have an excel table "E" (see attachment) with 4 columns (timestamp, ID, YPos, and XPos). ID is the identity of an object, and YPos and XPos are respectively the Y and X coordinates of the object over time. How can I, from "E", create a mat file "m" that has same structure as the table in 'form' (see attachment), with the time in the first column, the IDs as other colums' headers, and their YPos and XPos coordinates.
Your answer would be greatly appreciated. Thank you!
2 Comments
Walter Roberson
on 22 Jul 2021
mat files do not have column headers. You could save a table() object to the mat file though.
Accepted Answer
Walter Roberson
on 27 Jul 2021
findgroups() on the IDs. splitapply() to split into groups -- you can use for example splitapply(@(x) {x}, Variables, Group)
Once you have grouped data, you can find the largest one. You can then do something like
cell_of_grouped_data = cellfun(@(one_group) [one_group; nan(LargestSize - size(one_group,1),size(one_group,2))], cell_of_grouped_data)
and now cell_of_grouped_data is all nan padded, so you can horzcat(cell_of_grouped_data{:}) to get your columns.
4 Comments
Walter Roberson
on 28 Jul 2021
You have not defined what the output should look like.
We can see from the contents of E that not every ID appears at every time stamp.
But we are not promised that every ID will occur at most once per time stamp. If that were promised, then it would be possible to create useful output.
More Answers (1)
Peter Perkins
on 26 Jul 2021
I thnk you'd want to use readtimetable or readtable to bring the first xlsx into matlab. But I'm confused: the second xlsx only has timestamps. Is that all you want?
Except your first xlsx is holding out on you: those timestamps have a hidden date, so readtable creates things like 19-May-2021 07:28:57. If all you want is the time of day, use the timeofday function on the datetime variable that readtable will create (or on the rowtime of the timetable:
>> t = readtable("E.xlsx");
>> head(t)
ans =
8×4 table
timestamp ID YPos XPos
____________________ ___ ______ ______
19-May-2021 07:28:57 1 -14.36 55.42
19-May-2021 07:28:57 10 -3.4 66.995
19-May-2021 07:28:57 236 -0.6 94.777
19-May-2021 07:28:57 248 -7.6 77.127
19-May-2021 07:28:57 241 -7.6 146.26
19-May-2021 07:28:57 250 -8.4 203.5
19-May-2021 07:28:57 244 -1.18 165.87
19-May-2021 07:28:57 193 2.6 85.335
>> tod = timeofday(t.timestamp);
>> tod(1:10)
ans =
10×1 duration array
07:28:57
07:28:57
07:28:57
07:28:57
07:28:57
07:28:57
07:28:57
07:28:57
07:28:57
07:28:57
See Also
Categories
Find more on Whos in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!