How to separate date and time into two tables?

Hi, i am new to Matlab. I have a problem with separating imported data of date and time over the year (8760 values) into two tables. Imported data looks like this. Its a cell of data in one column and i need to separate it to date in one table and time in second table.

'01/01  01:00:00'
'01/01  02:00:00'
'01/01  03:00:00'
       .
       .
'12/31  22:00:00'
'12/31  23:00:00'
'12/31  24:00:00'

Thank you for any help.

Answer 1

[Jing Xie]

Hi,

I hope the following piece of code may help you a little bit. I am not sure if the "table" in your question means the build-in func table in MATLAB. It is added at the end in case. The performance can be improved by using cellfun. As you are new to MATLAB, I guess the for loop structure may be more straightforward.

%%Params
input_cell={'01/01  01:00:00';
  '01/01  02:00:00';
  '01/01  03:00:00';
  '12/31  22:00:00';
  '12/31  23:00:00';
  '12/31  24:00:00'};
%%Process
num_data_rows=numel(input_cell);
table_date_cell=cell(num_data_rows,1);
table_time_cell=table_date_cell;
for i=1:num_data_rows
  cur_data_row=input_cell{i};
  cur_date_time_cell=strsplit(cur_data_row);
  table_date_cell(i)=cur_date_time_cell(1);
  table_time_cell(i)=cur_date_time_cell(2);
end
%%Display
table(table_date_cell,'VariableNames',{'Date'})
table(table_time_cell,'VariableNames',{'Time'})

[LamaObecna]

Hi, by table i mean table of double values. Matlab divided imported data into double tables of size(8760:1). I am ok with that, just date and time is cell. In the end i will put it everything into one big table a work with it further. I will try your code after i get to PC.

[LamaObecna]

After aplying this code I get wrong output. For date I get 8760 values of "12/31" and for time "24:00:00".

EDIT: my bad, I made mistake during rewriting code. It's working fine! Thank you.

[LamaObecna]

Eh, just one more question. When I'm putting together final table of all data e.g. like this: finalTable = table(table_time_cell,table_date_cell, power_data,...) date and time is displaying with apostrophe '01/01'. How can I rid of it? To have just this 01/01 format?

[Jing Xie]

Hi,

You can get rid of the apostrophe by using the built-in function 'categorical'. For example,

final_table = table(categorical(table_time_cell),categorical(table_date_cell))

Hope this solves your problem.

[LamaObecna]

Yes, solved it. Is there a way how to rename name of this categorical column? Column in table right now has name "var1". I am trying to use different syntax of categorical (using Matlab HELP) to rename it, but with no success. E.g. table_date_cell column has name "Date".

[Jing Xie]

Hi,

You can make it in this way:

final_table = table(categorical(table_time_cell),categorical(table_date_cell),'VariableNames',{'Time','Date'})

Answer 2

[Marc Jakobi]

What format do you need the dates in? As strings? If not, I would recommend using datevec() to convert it to a matrix in the form:

 [year, month, day, h, min, s]

so for example:

 v = datevec(timestamp, 'mm/dd  HH:MM:SS');

You can then separate v(1:3,:) (year, month & day) and v(4:6,:) (hours, minutes & seconds).

[LamaObecna]

When I try this I get error "Undefined function or variable 'timestamp'".

[Walter Roberson]

replace timestamp by the name of your variable that has the time strings.

[Steph]

how do I extract date and time variables in 2 seperate columns from a datevector that is 6 by double?