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

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!

[Walter Roberson]

mat files do not have column headers. You could save a table() object to the mat file though.

[ADJE JEREMIE ALAGBE]

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?

Answer 1

[Walter Roberson]

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.

[ADJE JEREMIE ALAGBE]

I'm sorry @Walter Roberson that I didn't mention that I'm not so andvanced in matlab, so even if I believe you provided me a helpful answer, but I can't understand everything you suggested there. So if you can just apply the solution you are providing directly to the data I have attached (see E.xlsx) then it will be fine and straightforward to understand. Thank you!

[Walter Roberson]

filename = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/692104/E.xlsx';
t = readtable(filename);
[G, IDs] = findgroups(t.ID);
cell_of_groups = splitapply(@(x) {x}, t{:,3:4}, G);
cell_of_groups(1:2)

ans = 2×1 cell array
    {245×2 double}
    {124×2 double}

maxlen = max(cellfun(@(C) size(C,1), cell_of_groups));
array_of_groups = cell2mat(cellfun(@(C) [C;nan(maxlen - size(C,1),size(C,2))], cell_of_groups, 'uniform', 0).');

IDstr = string(IDs);
varnames = reshape((["Xpos_", "YPos_"] + IDstr).', 1, []);
varnames(1:5)

ans = 1×5 string array
    "Xpos_1"    "YPos_1"    "Xpos_2"    "YPos_2"    "Xpos_3"

size(array_of_groups)

ans = 1×2

679 510

size(varnames)

ans = 1×2

1 510

outtable = array2table(array_of_groups, 'VariableNames', varnames);
outtable(1,:)

ans = 1×510 table
    Xpos_1    YPos_1    Xpos_2    YPos_2    Xpos_3    YPos_3    Xpos_4    YPos_4    Xpos_5    YPos_5    Xpos_6    YPos_6    Xpos_7    YPos_7    Xpos_8    YPos_8    Xpos_9    YPos_9    Xpos_10    YPos_10    Xpos_11    YPos_11    Xpos_12    YPos_12    Xpos_13    YPos_13    Xpos_14    YPos_14    Xpos_15    YPos_15    Xpos_16    YPos_16    Xpos_17    YPos_17    Xpos_18    YPos_18    Xpos_19    YPos_19    Xpos_20    YPos_20    Xpos_21    YPos_21    Xpos_22    YPos_22    Xpos_23    YPos_23    Xpos_24    YPos_24    Xpos_25    YPos_25    Xpos_26    YPos_26    Xpos_27    YPos_27    Xpos_28    YPos_28    Xpos_29    YPos_29    Xpos_30    YPos_30    Xpos_31    YPos_31    Xpos_32    YPos_32    Xpos_33    YPos_33    Xpos_34    YPos_34    Xpos_35    YPos_35    Xpos_36    YPos_36    Xpos_37    YPos_37    Xpos_38    YPos_38    Xpos_39    YPos_39    Xpos_40    YPos_40    Xpos_41    YPos_41    Xpos_42    YPos_42    Xpos_43     YPos_43    Xpos_44    YPos_44    Xpos_45    YPos_45    Xpos_46    YPos_46    Xpos_47    YPos_47    Xpos_48    YPos_48    Xpos_49    YPos_49    Xpos_50    YPos_50    Xpos_51    YPos_51    Xpos_52    YPos_52    Xpos_53    YPos_53    Xpos_54    YPos_54    Xpos_55    YPos_55    Xpos_56    YPos_56    Xpos_57    YPos_57    Xpos_58    YPos_58    Xpos_59    YPos_59    Xpos_60    YPos_60    Xpos_61    YPos_61    Xpos_62    YPos_62    Xpos_63    YPos_63    Xpos_64    YPos_64    Xpos_65    YPos_65    Xpos_66    YPos_66    Xpos_67    YPos_67    Xpos_68    YPos_68    Xpos_69    YPos_69    Xpos_70    YPos_70    Xpos_71    YPos_71    Xpos_72    YPos_72     Xpos_73     YPos_73    Xpos_74    YPos_74    Xpos_75    YPos_75    Xpos_76    YPos_76    Xpos_77    YPos_77    Xpos_78    YPos_78    Xpos_79    YPos_79    Xpos_80    YPos_80    Xpos_81    YPos_81    Xpos_82    YPos_82    Xpos_83    YPos_83    Xpos_84    YPos_84    Xpos_85    YPos_85    Xpos_86    YPos_86    Xpos_87    YPos_87    Xpos_88    YPos_88    Xpos_89    YPos_89    Xpos_90    YPos_90    Xpos_91    YPos_91    Xpos_92    YPos_92    Xpos_93    YPos_93    Xpos_94    YPos_94    Xpos_95    YPos_95    Xpos_96    YPos_96    Xpos_97    YPos_97    Xpos_98    YPos_98    Xpos_99    YPos_99    Xpos_100    YPos_100    Xpos_101    YPos_101    Xpos_102    YPos_102    Xpos_103    YPos_103    Xpos_104    YPos_104    Xpos_105    YPos_105    Xpos_106    YPos_106    Xpos_107    YPos_107    Xpos_108    YPos_108    Xpos_109    YPos_109    Xpos_110    YPos_110    Xpos_111    YPos_111    Xpos_112    YPos_112    Xpos_113    YPos_113    Xpos_114    YPos_114    Xpos_115    YPos_115    Xpos_116    YPos_116    Xpos_117    YPos_117    Xpos_118    YPos_118    Xpos_119    YPos_119    Xpos_120    YPos_120    Xpos_121    YPos_121    Xpos_122    YPos_122    Xpos_123    YPos_123    Xpos_124    YPos_124    Xpos_125    YPos_125    Xpos_126    YPos_126    Xpos_127    YPos_127    Xpos_128    YPos_128    Xpos_129    YPos_129    Xpos_130    YPos_130    Xpos_131    YPos_131    Xpos_132    YPos_132    Xpos_133    YPos_133    Xpos_134    YPos_134    Xpos_135    YPos_135    Xpos_136    YPos_136    Xpos_137    YPos_137    Xpos_138    YPos_138    Xpos_139    YPos_139    Xpos_140    YPos_140    Xpos_141    YPos_141    Xpos_142    YPos_142    Xpos_143    YPos_143    Xpos_144    YPos_144    Xpos_145    YPos_145    Xpos_146    YPos_146    Xpos_147    YPos_147    Xpos_148    YPos_148    Xpos_149    YPos_149    Xpos_150    YPos_150    Xpos_151    YPos_151    Xpos_152    YPos_152    Xpos_153    YPos_153    Xpos_154    YPos_154    Xpos_155    YPos_155    Xpos_156    YPos_156    Xpos_157    YPos_157    Xpos_158    YPos_158    Xpos_159    YPos_159    Xpos_160    YPos_160    Xpos_161    YPos_161    Xpos_162    YPos_162    Xpos_163    YPos_163    Xpos_164    YPos_164    Xpos_165    YPos_165    Xpos_166    YPos_166    Xpos_167    YPos_167    Xpos_168    YPos_168    Xpos_169    YPos_169    Xpos_170    YPos_170    Xpos_171    YPos_171    Xpos_172    YPos_172    Xpos_173    YPos_173    Xpos_174    YPos_174    Xpos_175    YPos_175    Xpos_176    YPos_176    Xpos_177    YPos_177    Xpos_178    YPos_178    Xpos_179    YPos_179    Xpos_180    YPos_180    Xpos_181    YPos_181    Xpos_182    YPos_182    Xpos_183    YPos_183    Xpos_184    YPos_184    Xpos_185    YPos_185    Xpos_186    YPos_186    Xpos_187    YPos_187    Xpos_188    YPos_188    Xpos_189    YPos_189    Xpos_190    YPos_190    Xpos_191    YPos_191    Xpos_192    YPos_192    Xpos_193    YPos_193    Xpos_194    YPos_194    Xpos_195    YPos_195    Xpos_196    YPos_196    Xpos_197    YPos_197    Xpos_198    YPos_198    Xpos_199    YPos_199    Xpos_200    YPos_200    Xpos_201    YPos_201    Xpos_202    YPos_202    Xpos_203    YPos_203    Xpos_204    YPos_204    Xpos_205    YPos_205    Xpos_206    YPos_206    Xpos_207    YPos_207    Xpos_208    YPos_208    Xpos_209    YPos_209    Xpos_210    YPos_210    Xpos_211    YPos_211    Xpos_212    YPos_212    Xpos_213    YPos_213    Xpos_214    YPos_214    Xpos_215    YPos_215    Xpos_216    YPos_216    Xpos_217    YPos_217    Xpos_218    YPos_218    Xpos_219    YPos_219    Xpos_220    YPos_220    Xpos_221    YPos_221    Xpos_222    YPos_222    Xpos_223    YPos_223    Xpos_224    YPos_224    Xpos_225    YPos_225    Xpos_226    YPos_226    Xpos_227    YPos_227    Xpos_228    YPos_228    Xpos_229    YPos_229    Xpos_230    YPos_230    Xpos_231    YPos_231    Xpos_232    YPos_232    Xpos_233    YPos_233    Xpos_234    YPos_234    Xpos_235    YPos_235    Xpos_236    YPos_236    Xpos_237    YPos_237    Xpos_238    YPos_238    Xpos_239    YPos_239    Xpos_240    YPos_240    Xpos_241    YPos_241    Xpos_242    YPos_242    Xpos_243    YPos_243    Xpos_244    YPos_244    Xpos_245    YPos_245    Xpos_246    YPos_246    Xpos_247    YPos_247    Xpos_248    YPos_248    Xpos_249    YPos_249    Xpos_250    YPos_250    Xpos_251    YPos_251    Xpos_252    YPos_252    Xpos_253    YPos_253    Xpos_254    YPos_254    Xpos_255    YPos_255
    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    ________    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _________    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    _______    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________    ________

    -14.36    55.42      2.32     190.69    -10.6     93.775     -7.4     56.829    -10.1     55.219    -13.6     63.468     8.58     63.669     8.54     69.296     2.6      200.69     -3.4      66.995      5.55       204.9      -7.44     54.816       2.6      64.473      -9.06     90.567     -14.36     200.29      -1.2      200.09      -9.66     49.576      -8.6      196.09        1        201.9      -11.8     47.558       4.2      194.89      9.12        105       5.14       202.3      -1.55     189.29     -10.92      55.42      -12.2     83.748      -1.96     166.47      -11.1     64.674      9.14      53.406      -15.6     54.615      10.6      59.446      7.275     54.413      -2.3      144.85      4.225      203.9      8.78      101.59      -6.2      159.47      8.78       106.8      -5.18     188.09        9       59.446      5.12      87.559     -11.95      201.7     -11.62     161.07     -0.99999    108.61      7.52      132.64      5.54      161.47     -12.42      205.7      8.54      119.22       7.8       108.2      8.24      70.501     -10.18     122.43      -1.42     53.204      7.94      76.926      -8.2      155.06       1.4      100.99      -3.4       74.58      8.78      82.344     -12.46     131.84      -7.02     143.45      -8.2      191.29      -0.8      97.784      -0.9       139.8      8.34      97.584       5.6      113.58        2       98.385     -10.61     79.735      4.58      137.85      1.26      124.03      -2.6      145.05      -7.04     82.143      -10.4     87.559       -16      61.457      -0.4      176.08     -0.059998    165.27      6.92      137.85      -1.14     193.09      -3.38     48.567      5.42      172.28      -15.1     48.567      -8.12     53.204     -11.24     64.272      5.48      179.28        2        202.1       5.6      100.03     0.79999     201.3       6.4      150.76      -3.4      81.941       5.6      98.249      -3.4      89.421      9.04      76.324     -15.34     56.628     -12.96     188.49      -5.36     179.68       8.6      83.949      -1.02     198.09      5.14      193.49      3.06      58.037      -3.02       108        8.4      90.768      -6.8      139.05       5.6        90.723       5.74       200.69       -3.4       74.271       7.8        101.19       2.4        197.09       2.6        104.45      -3.88       46.952      -13.18      60.854      -12.66      65.277       1.6        204.3       -3.42       51.794      -10.46      50.584       -3.2       49.979       2.58       200.69      -6.475      56.226       1.8        48.365      -2.72       48.769      -13.12      58.037       5.6        105.73       -0.4       83.239      -9.84       93.174       2.6        111.71       5.2        75.119       2.6        104.31      -11.04      99.187       8.2        91.57       10.45       56.427       5.8        198.49       2.6        91.146       0.66       173.48       8.76       90.968       1.88       165.67      -14.6       58.238       8.6        62.463      10.275      56.829      -9.28       59.244       1.8        136.04       -9.2       55.823      -1.22       199.09      -11.24      57.433       5.6        72.632       -7.6       156.47      -4.44       51.995        -9        78.732       -1.4       189.49     -0.99999     180.48      -8.94       56.226       -3.4       81.708      -13.06      101.59       2.28       146.26       2.08       186.09     -3.8667      48.567       2.5        190.04       8.52       55.017       -3.6       53.406        -3        92.773      10.48       54.615       8.6        63.87        -2.8       60.854       -0.4       204.93       -3.4       81.547      -3.18       69.698       8.34       68.492      -1.64       183.89       9.74       59.043       8.86        70.3        2.46       52.801       1.82       196.29       2.32       202.7        8.62       77.929      -1.38       189.29       8.64       85.955       3.85       195.09       8.2        94.376       -14        56.024       4.98       194.69      -6.36       55.42       -11.86      59.446       0.9        204.1       -15.48      60.25       -14.34      57.232      -16.8       57.635      -13.66      63.267       2.35       201.5         9         103.2       -14.8       61.055     0.89999      200.89      -9.56       69.497      -15.16      53.809      -12.12      69.698      -15.74      61.055      -12.6       78.732       8.95       104.6        2.6        85.335      -10.2       55.621      -11.56      75.119      -14.18      101.79      -4.12       58.037      4.525       203.5        -11        83.548       -5.2       74.718      -11.04      94.777      -6.02       55.42       -11.4       97.784       2.48       187.69      -3.88       49.172      -16.3       54.413      -11.36      53.809      -13.28      55.823      -15.6       50.786      10.325      56.427      -7.65       55.017       2.2        203.9       -11.2       126.03       3.08       195.29       -0.8       194.29       9.8        107.8        1.25       67.689      3.1333      193.29       3.64       193.69      -11.36      46.346      -10.36      200.09       8.98       104.2        2.92       205.1         2         200.09       7.8        95.379      11.04       181.08      -16.32      46.952     -13.225      47.962      -13.2       54.413        -7        46.952       5.38       202.3        4.2        189.69       11.2       166.07      -11.52      95.579        -3        138.65       -0.6       94.777       3.6        51.995       9.16       61.256       8.2        122.63       5.6        119.73       -7.6       146.26       8.2        71.706       -8.4       180.88      -1.18       165.87       2.34       65.88        2.8        91.369       -9.4       46.548       -7.6       77.127      -12.08      56.628       -8.4       203.5        5.6        127.94       1.32       191.29      -11.92      71.706      -11.84      66.684       -5.8       56.628 

[ADJE JEREMIE ALAGBE]

Thank you so much @Walter Roberson for your great effort to help. This solution solved already the most part of the problem, but only that the timestamp column disappeared. I need the values for coordinates to be arranged in the 'outtable' according to the time in the table in 'E'.

[Walter Roberson]

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.

Answer 2

[Peter Perkins]

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

[ADJE JEREMIE ALAGBE]

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!