How can I create a mat file which changes the values of a column in excel table into the mat file's column headers?

1 view (last 30 days)
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!
Oh, sorry for wrong expression. What I could say is: after T = readtable('E.xlsx'), how can I transform T into the way in 'form', then later wite the result into excel sheet or save to mat file?

Sign in to comment.

Accepted Answer

Walter Roberson
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.
Walter Roberson
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.

Sign in to comment.

More Answers (1)

Peter Perkins
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
  1 Comment
Thanks so much @Peter Perkins for your useful answer that can be considered to improve the data. However, my original problem is still not solved yet. What I want to do actually is to create a new xlsx from the fisrt xlsx E, so that each ID value in xlsx E becomes a column name in the new xlsx (it should look like something like the table in the second xlsx 'form', which will be filled with the coordinates in the first xlsx 'E'). Notice that there are 1 to 255 ID values in xlsx E, and each ID appears many times with its associated YPos and XPos coordinates; so that's why in the new xlsx the column names (each ID) will be in duplicate, with each duplicate for YPos and XPos cordinates respectively. Is this possible or am I thinking of doing something impossible? But I beleave that if I manage to get that kind of table, then it will be very helpful in visualising the data. I know that new table will include many NANs, but that doesn't matter. Please if you have an idea on this, your valuable time for help would be greatly apprecited. Thanks!

Sign in to comment.


Community Treasure Hunt

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

Start Hunting!

Translated by