Possible to Concatenate a Duration With a Non-duration Array?

Good morning, MATLAB universe. I'm ready to beat my head into the wall over this, so any assistance/information would be appreciated. I have the following snippet of a 14244-by-8 cell array:

'01:08:26' 'AM' 'a' '101017' '4152' '176036' '0.0' '0.0'

'01:10:28' 'AM' 'a' '102454' '4876' '180400' '0.0' '0.0'

'11:00:09' 'PM' 'b' '106776' '1249480' '29113700' '1.9' '23.1'

'11:00:09' 'PM' 'b' '106832' '1529932' '30648184' '2.3' '47.5'

'11:01:10' 'PM' 'b '106776' '1252528' '29646212' '1.9' '11.1'

'11:01:10' 'PM' 'b' '106832' '1534468' '31182744' '2.3' '22.8'

'11:02:11' 'PM' 'b '106776' '1302992' '31376876' '1.9' '7.6'

'11:02:11' 'PM' 'c' '106832' '1538680' '32263188' '2.3' '15.2'

'11:03:12' 'PM' 'c' '106776' '1304560' '32109080' '1.9' '5.7'

'11:03:12' 'PM' 'c' '106832' '1539608' '32271412' '2.3' '11.4'

'11:04:13' 'PM' 'd' '106776' '1307908' '32254544' '1.9' '4.6'

'11:04:13' 'PM' 'd' '106832' '1543748' '32284776' '2.3' '9.2'

'11:05:14' 'PM' 'd' '106776' '1307932' '32263796' '1.9' '3.8'

'11:05:14' 'PM' 'd' '106832' '1544500' '32293000' '2.3' '7.7'

'11:06:16' 'PM' 'd' '106776' '1308228' '32274076' '1.9' '3.3'

'11:06:16' 'PM' 'd' '106832' '1549092' '32319728' '2.3' '6.7'

Individually, I can separate these into cells based on the 3rd Column easily to manipulate the time in the 1st Column, which is where the problem starts. Since this is just part of the whole, each letter designator goes on for multiple days in the 12-hr (AM/PM) format. I determined it would be easiest to convert this to 24-hr format, so I came up with a simple 'if' statement to accomplish that task based on the 2nd Column (if PM, add 12-hrs sort of thing).

What I saw, though, was an issue adding 12-hours to the time (MATLAB seemed to treat it as a character array), which I addressed first by attempting to convert the 1st Column into a duration array while still inside the larger cell array. MATLAB didn't like that. My second attempt centered around extracting the 1st Column, converting it to a duration array, and concatenating it with the original cell array using horzcat, strcat, or A = [duration_array original_array], which MATLAB wasn't happy about either.

None of the research I did turned up anything conclusive, so at this point, I'm not even sure if a duration array can be concatenated with a non-duration array. Does anyone know of an alternative to my concatenation method(s)? If not, any recommendations on a direction in which to proceed? For a bit more background, note that based on the data format, it was read into MATLAB (using importdata) as a cell array thanks to having numeric and non-numeric data. Frankly, I am nowhere near a savvy enough scripter to know if that makes a difference.

[Luna]

Have you ever checked timetables? See below maybe that could help a bit.

Timetables

[Jeremy]

Thanks a lot for the heads-up, but it unfortunately didn't pan out. I appreciate the help!

Answer 1

[Cris LaPierre]

I'm not sure what format you want the final table to be in, but I took a stab. Thanks for the entertaining project ;)

My goal was to create a timetable. To do that, I had to get the first 3 columns converted to a datetime. I was unsure what column 3 represented, but I assumed each letter represented a new day. I ran into an issue with how to handle the 24 hour time, especially since no date is connected with the times. I elected to go with elapsed time from midnight on day 1 using my previous assumption about column 3. This was achieved by adding duration in days to time, and then converting datetime to durations by subtracting a t0 from all the datetimes.

Converting the cell's numeric data to a matrix was straightforward, as was creating a table with the elapsed time in column 1 and numeric data in the other columns.

I could only test using your snippet of data, but it appears to be working. I converted that to a cell array.

data = {'01:08:26'	 'AM'	'a'	'101017'	'4152'	'176036'	         '0.0'	 '0.0';
'01:10:28'	 'AM'	'a'	'102454'	'4876'	'180400'	         '0.0'	 '0.0';
'11:00:09'	 'PM'	'b'	'106776'	'1249480'	'29113700'	'1.9'	'23.1';
'11:00:09'	 'PM'	'b'	'106832'	'1529932'	'30648184'	'2.3'	'47.5';
'11:01:10'	 'PM'	'b'	'106776'	'1252528'	'29646212'	'1.9'	'11.1';
'11:01:10'	 'PM'	'b'	'106832'	'1534468'	'31182744'	'2.3'	'22.8';
'11:02:11'	 'PM'	'b'	'106776'	'1302992'	'31376876'	'1.9'	'7.6';
'11:02:11'	 'PM'	'c'	'106832'	'1538680'	'32263188'	'2.3'	'15.2';
'11:03:12'	 'PM'	'c'	'106776'	'1304560'	'32109080'	'1.9'	'5.7';
'11:03:12'	 'PM'	'c'	'106832'	'1539608'	'32271412'	'2.3'	'11.4';
'11:04:13'	 'PM'	'd'	'106776'	'1307908'	'32254544'	'1.9'	'4.6';
'11:04:13'	 'PM'	'd'	'106832'	'1543748'	'32284776'	'2.3'	'9.2';
'11:05:14'	 'PM'	'd'	'106776'	'1307932'	'32263796'	'1.9'	'3.8';
'11:05:14'	 'PM'	'd'	'106832'	'1544500'	'32293000'	'2.3'	'7.7';
'11:06:16'	 'PM'	'd'	'106776'	'1308228'	'32274076'	'1.9'	'3.3';
'11:06:16'	 'PM'	'd'	'106832'	'1549092'	'32319728'	'2.3'	'6.7'};

Note that there were 2 apostrophes missing (rows 5 and 7, column 3). I assumed that was a typo. And finally, the working code.

% convert first two columns to time
tm = datetime([vertcat(data{:,1}),vertcat(data{:,2})],'InputFormat','hh:mm:ssa');

% convert third column to duraction in days (assuming each letter is a new day)
durDay = double([data{:,3}]);
durDay = durDay' - durDay(1);

% Extract start date
[y,m,d] = ymd(tm(1));
t0 = datetime(y,m,d);

% Compute elapsed time from midnight on day 1
tm = tm + days(durDay) - t0;

% Convert numeric data to matrix
nums = str2double(data(:,4:end));

% Create a table with the processed data
dataTable = table(tm,nums);
dataTable = splitvars(dataTable); % make each column of nums its own variable

% Create a timetable
dataTT = table2timetable(dataTable);

Note that I'm using 2018b. Earlier verions might not have all the same functionality.

[Jeremy]

I should thank you for the entertaining answer, as I would never, in a thousand years, have thought to do any of that. I'm going to play around with this for a bit, but modifying this to gel with what I currently have looks extremely promising. Thank you very much for taking on this challenge!

[Cris LaPierre]

A couple follow-up comments.

You can assign variable names to each column of a table. It makes it much easier to remember what data is where.

Also, rather than hard code all the data conversions, consider using the import tool to load your data into MATLAB. You can set variable named, data type, etc.