How to convert cell array of characters to a datetime array without looping through each cell individually?

15 views (last 30 days)
I have a very large cell array (32001x1) for which a very small portion looks like this
' " 8 / 2 4 / 2 0 1 8 8 : 5 9 : 1 8 A M " '
' " 8 / 2 4 / 2 0 1 8 9 : 0 4 : 1 8 A M " '
' " 8 / 2 4 / 2 0 1 8 9 : 0 9 : 1 8 A M " '
' " 8 / 2 4 / 2 0 1 8 1 : 0 4 : 1 8 P M " '
' " 1 0 / 4 / 2 0 1 8 1 : 2 4 : 1 8 P M " '
I want to convert this cell array into a datetime array to get the following
'24-Aug-2018 08:59:18'
'24-Aug-2018 09:04:18'
'24-Aug-2018 09:09:18'
'24-Aug-2018 13:04:18'
'04-Oct-2018 13:24:18'
Currently, I am using a for loop that iterates through each cell array, but that takes a long time considering the size of my cell array (32001x1). I want to figure out a faster way to perform the conversion by eliminating the need to loop through each element of the cell array and perhaps perform something once to change every cell of the array at once. I am also using MATLAB R2017a. Any ideas on how I can achieve this?
Below, I've created a sample code that uses the very small portion of the cell array as mentioned above with the method I am using.
% Create sample cell array of "dates"
cellDates = {' " 8 / 2 4 / 2 0 1 8 8 : 5 9 : 1 8 A M " ';...
' " 8 / 2 4 / 2 0 1 8 9 : 0 4 : 1 8 A M " ';...
' " 8 / 2 4 / 2 0 1 8 9 : 0 9 : 1 8 A M " ';...
' " 8 / 2 4 / 2 0 1 8 1 : 0 4 : 1 8 P M " ';...
' " 1 0 / 4 / 2 0 1 8 1 : 2 4 : 1 8 P M " '};
% Convert cellDates to a table
date_time = cell2table(cellDates);
% Iterate through each cell
for i = 1:(size(date_time,1))
% Get each value once at a time from "date_time" table
tempVar = date_time.cellDates(i); % Extracts each individual cell
% Convert from cell to string
tempVar = tempVar{1}; % Extracts character string from cell
% Split that string to separate date from time (creates 1x3 cell array)
tempVar = strsplit(tempVar,' ');
% Get string with data
dateVal = tempVar{1};
% Determine weird characters " and space (they are the 1st and 2nd
% characters in dateVal)
bad = dateVal(1);
bad1 = dateVal(2);
% Remove unecessary characters
dateVal = dateVal(dateVal~=bad); % Removing spaces
dateVal = dateVal(dateVal~=bad1); % Removing quotations
% Repeat
timeVal = tempVar{2};
timeVal = timeVal(timeVal~=bad);
timeVal = timeVal(timeVal~=bad1);
% Repeat
amPM = tempVar{3};
amPM = amPM(amPM~=bad);
amPM = amPM(amPM~=bad1);
% Convert to datetime value
dateTime(i,1) = datetime([dateVal ' ' timeVal ' ' amPM], 'InputFormat', 'M/d/yyyy h:m:s a');
end

Accepted Answer

Sean de Wolski
Sean de Wolski on 31 May 2019
Edited: Sean de Wolski on 31 May 2019
Converting the cellstr to a string makes this much easier:
cellDates = {' " 8 / 2 4 / 2 0 1 8 8 : 5 9 : 1 8 A M " ';...
' " 8 / 2 4 / 2 0 1 8 9 : 0 4 : 1 8 A M " ';...
' " 8 / 2 4 / 2 0 1 8 9 : 0 9 : 1 8 A M " ';...
' " 8 / 2 4 / 2 0 1 8 1 : 0 4 : 1 8 P M " ';...
' " 1 0 / 4 / 2 0 1 8 1 : 2 4 : 1 8 P M " '};
strDates = string(cellDates); % convert to string
strDates = erase(strDates, ["""", " "]); % remove spaces, " to save on typing
datetime(strDates, 'InputFormat', 'M/d/yyyyh:mm:ssa') % Convert
5×1 datetime array
24-Aug-2018 08:59:18
24-Aug-2018 09:04:18
24-Aug-2018 09:09:18
24-Aug-2018 13:04:18
04-Oct-2018 13:24:18
You can now adjust the datetime.Format as you wish.
  2 Comments
Taylor Begay
Taylor Begay on 31 May 2019
Edited: Taylor Begay on 31 May 2019
Hi Sean,
Thanks alot for the quick reply!
However, it seems to be that not all of my "spaces" are being deleted from the cell array in my main script.
The code you provided definitely does what I ask with the provided cell array that I gave (i.e. "cellDates"), but from the data I am importing from a .csv file, it still includes "spaces". However, I don't think they are spaces but something else.
Take the isspace command I am using below for example.
When I convert the provided cell array into a character array and use the isspace command, it indeed defines all of the spaces in that character array.
% Convert cell array to character array
a = char(cellDates(1)) % First cell of the array I provided
a =
' " 8 / 2 4 / 2 0 1 8 8 : 5 9 : 1 8 A M " '
% Use isspace to locate spaces in character array
isspace(a)
ans =
1×45 logical array
1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 1 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 1 1 0 1 0 1 0 1
However, from the data that I am importing from excel, it appears that these "spaces" are not actually spaces but something else.
% Convert cell array to character array
b = char(Log{1,2}(1)) % 'Log' is a variable that I am storing my large cell array in my main script
b =
' " 8 / 2 4 / 2 0 1 8 8 : 5 9 : 1 8 A M " '
% Use isspace to locate spaces in character array
isspace(b)
ans =
1×45 logical array
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0
These two character arrays are the same size, but the 'b' character array's "spaces" are not considered spaces it seems. Any idea on what is going on here or what these "space" characters actually are?
Taylor Begay
Taylor Begay on 31 May 2019
I think I figured it out, instead of worrying about whether the "space" was actually a space, I just created a variable equal to that "space", then used that variable in the erase command and now these "spaces" are gone. Thanks for the earlier code, that helped me a lot!

Sign in to comment.

More Answers (0)

Categories

Find more on Characters and Strings 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!