Datetimes in table not displayed
Mostrar comentarios más antiguos
Hi, I'm currently trying to finish my master thesis over the summer but I'm running into some problems with processing data in Matlab. I have a table with 401 rows of different measures of datetimes:
I want to export it to Excel with all datetimes, but the datetimes are stored in one cell, and can only be seen when clicking on the cell:
I want all the datetimes of each cell being shown in the original table on their respective row, so I can copy all the data all at once to Excel (instead of manually doing it for each row, 401 times). With what code can I change to table so that all datetimes are visible at once? Hope someone knows this, it would help me so much :).
4 comentarios
@Dennis: apparently some of those DATETIME objects contain timezone information. This is significant:
- DATETIME objects with and without timezones cannot be combined (as the answers below do, because this is the best approach)... but this is not your biggest problem:
- MS Excel does not store or interpret timezone information (the date is always relative to the local computer), so you need to decide how you want to convert those timezones.
Also some of the data are buggy. Lets check which ones:
S = load('datetimes dennis.mat');
C = S.datetimes
for k = 1:numel(C)
try
tzoffset(C{k});
catch
disp(k)
end
end
C{[23,32,36,39,47,62,66,72]} % these are not DATETIME objects
The more your data diverges the harder it is to parse: exceptions add complexity.
Dennis
el 12 de Jul. de 2023
Dennis
el 12 de Jul. de 2023
Respuesta aceptada
C = {datetime(2021,1,1:2);datetime(2022,2,3:6);datetime(2023,3,7:9)}
D = NaT(numel(C),0);
for k = 1:numel(C)
V = C{k};
D(k,1:numel(V)) = V;
end
display(D) % optional
writematrix(D,'test.xlsx') % much better than copy-and-paste
3 comentarios
Using your data (excluding empty arrays and ignoring timezones):
S = load('datetimes dennis.mat');
C = S.datetimes
D = NaT(numel(C),0);
for k = 1:numel(C)
V = C{k};
if numel(V)
V.TimeZone = '';
D(k,1:numel(V)) = V;
end
end
display(D) % optional
writematrix(D,'test.xlsx') % much better than copy-and-paste
Dennis
el 12 de Jul. de 2023
Peter Perkins
el 17 de Jul. de 2023
These are all tagged with the same Europe/Amsterdam time zone, so as long as you keep in mind that once they get to Excel, you need to interpret them as "local time" you're OK. But the day you start having different time zones, you are in trouble because Excel knows nothing about time zones. You;d want to convert all to the same tz.
The NaT trick is a good one, and that may be what you want, but you might also consider writing a loop to export one cell at a time to Excel. I forget what NaT ends up as in Excel, but if you only write the data you have, you will have empty cells where you don't have data, which may be better.
Más respuestas (1)
Malay Agarwal
el 12 de Jul. de 2023
I am assuming that your datetimes are in a 401x1 cell array, where each row in the cell array is a vector of datetimes. Please use the following code:
% Set the number of rows
numRows = 401;
% Initialize the cell array
randomDates = cell(numRows, 1);
% Generate random datetime values for each row
for i = 1:numRows
% Generate a random number of datetime values per row
numValues = randi([1, 10]); % Modify the range as per your requirement
% Generate random datetime values for the current row
randomDates{i} = datetime(randi([2000, 2021], numValues, 1), ... % Year
randi([1, 12], numValues, 1), ... % Month
randi([1, 28], numValues, 1), ... % Day
randi([0, 23], numValues, 1), ... % Hour
randi([0, 59], numValues, 1), ... % Minute
randi([0, 59], numValues, 1)); % Second
end
% Determine the maximum length of the datetime vectors
maxVectorLength = max(cellfun(@numel, randomDates));
% Create a NaN-filled matrix with the maximum length
dataMatrix = NaT(numRows, maxVectorLength);
% Populate the matrix with the datetime values
for i = 1:numRows
dataMatrix(i, 1:numel(randomDates{i})) = randomDates{i}';
end
% Write the matrix to an Excel file
filename = 'data.xlsx';
writematrix(dataMatrix, filename);
The exported file is attached to the answer.
4 comentarios
Dennis
el 12 de Jul. de 2023
Dennis
el 12 de Jul. de 2023
"How would I fill out the table with the already existing datetimes? "
You use your data, not the random dates.
We cannot test code on imaginary data, we need actual data to test code on. Because you did not upload any sample data, we had to invent some random dates ourselves. Of course you do not use our random dates, you use your data.
If you want us to test our code on your data, then please supply us with your data.
Dennis
el 12 de Jul. de 2023
Categorías
Más información sobre Data Import from MATLAB en Centro de ayuda y File Exchange.
el 12 de Jul. de 2023
el 17 de Jul. de 2023
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
