Adding date to timetable with just time

Hello,

I do have a timetable with just time in the first column, i would like to have the date too. I am able to get the date from the filename.

'03:08:48 PM' 4.67309300000000 23.2110000000000

'03:08:49 PM' 5.67272000000000 22.7290000000000

'03:08:50 PM' 6.67284700000000 22.2520000000000

'03:08:51 PM' 7.67297400000000 21.7820000000000

'03:08:52 PM' 8.67260100000000 21.3180000000000

'03:08:53 PM' 9.67322800000000 20.8370000000000

'03:08:54 PM' 10.6733600000000 20.3790000000000

'03:08:55 PM' 11.6729800000000 19.9230000000000

'03:08:56 PM' 12.6726100000000 19.4740000000000

'03:08:57 PM' 13.6727400000000 19.0420000000000

'03:08:58 PM' 14.6733600000000 18.6260000000000

'03:08:59 PM' 15.6729900000000 18.2250000000000

'03:09:00 PM' 16.6731200000000 17.8390000000000

'03:09:01 PM' 17.6727400000000 17.4660000000000

Answer 1

[Arif Hoq]

follow this

https://www.mathworks.com/matlabcentral/answers/288524-how-do-you-separate-the-contents-of-one-cell-into-separate-columns

https://www.mathworks.com/help/releases/R2019b/matlab/ref/regexp.html

A=readtable('Book1.xlsx', 'PreserveVariableNames', 0);
AA=table2array(A);
B = regexp(AA, '\s+', 'split');
C = vertcat(B{:})

C = 14×4 cell array
    {''03:08:48'}    {'PM''}    {'4.67309300000000'}    {'23.2110000000000'}
    {''03:08:49'}    {'PM''}    {'5.67272000000000'}    {'22.7290000000000'}
    {''03:08:50'}    {'PM''}    {'6.67284700000000'}    {'22.2520000000000'}
    {''03:08:51'}    {'PM''}    {'7.67297400000000'}    {'21.7820000000000'}
    {''03:08:52'}    {'PM''}    {'8.67260100000000'}    {'21.3180000000000'}
    {''03:08:53'}    {'PM''}    {'9.67322800000000'}    {'20.8370000000000'}
    {''03:08:54'}    {'PM''}    {'10.6733600000000'}    {'20.3790000000000'}
    {''03:08:55'}    {'PM''}    {'11.6729800000000'}    {'19.9230000000000'}
    {''03:08:56'}    {'PM''}    {'12.6726100000000'}    {'19.4740000000000'}
    {''03:08:57'}    {'PM''}    {'13.6727400000000'}    {'19.0420000000000'}
    {''03:08:58'}    {'PM''}    {'14.6733600000000'}    {'18.6260000000000'}
    {''03:08:59'}    {'PM''}    {'15.6729900000000'}    {'18.2250000000000'}
    {''03:09:00'}    {'PM''}    {'16.6731200000000'}    {'17.8390000000000'}
    {''03:09:01'}    {'PM''}    {'17.6727400000000'}    {'17.4660000000000'}

[Cristobal Gonzalez Diaz]

Thanks a lot for your help. Now i do need too add the data, for instance, the data of today. 15-02-2022 to be able to synchronize the timetable.

regards,

cristobal

[Arif Hoq]

A=readtable('Book2.xlsx', 'PreserveVariableNames', 0);
AA=table2array(A);
B = regexp(AA, '\s+', 'split');
C = vertcat(B{:});
t = datetime(now,'ConvertFrom','datenum','Format','dd-MM-yyyy HH:mm:ss a'); % today's Date
t1=cellstr(t);  
splitDate = regexp(t1 , '\s+', 'split');  % split 
D=vertcat(splitDate{:});
% D{1,1}=[];
add_data=[D(2:end) 5.234 23.456];   % data adding for today
synchonization=[C;add_data];  % add the today's data with previous data

[Cristobal Gonzalez Diaz]

Thanks a lot for your help, i understand more or less whay you code does. However i think i have not explained myself very well.

I do have many .dat files with just the time on it, from now, (i will be also saving the date).

The date is on the finame of the .dat (20220214_Dep.txt).

'08:38:01 AM' 0 25.9680000000000

'08:38:02 AM' 0.751595500000000 25.9680000000000

'08:38:02 AM' 1.50619100000000 25.9690000000000

'08:38:03 AM' 2.50631800000000 25.9680000000000

'08:38:04 AM' 3.50594500000000 25.9700000000000

'08:38:05 AM' 4.50607200000000 25.9710000000000

'08:38:06 AM' 5.50669900000000 25.9710000000000

'08:38:07 AM' 6.50582600000000 25.9710000000000

'08:38:08 AM' 7.50645300000000 25.9720000000000

'08:38:09 AM' 8.50608000000000 25.9740000000000

'08:38:10 AM' 9.50670700000000 25.9730000000000

'08:38:11 AM' 10.5063300000000 25.9730000000000

'08:38:12 AM' 11.5064600000000 25.9740000000000

'08:38:13 AM' 12.5065900000000 25.9740000000000

...

'00:00:01 AM' 1.50619100000000 25.9690000000000

'00:00:02 AM' 1.06619100000000 26.9690000000000

...

and i would like to include the date from the filename 20220214--> 14 feb 2022 in all the rows.

'14-02-2022 08:38:01' 0 25.9680000000000

'14-02-2022 08:38:02' 0.751595500000000 25.9680000000000

'14-02-2022 08:38:02' 1.50619100000000 25.9690000000000

...

'15-02-2022 00:00:01' 1.50619100000000 25.9690000000000

'15-02-2022 00:00:02' 1.06619100000000 26.9690000000000

...

[Arif Hoq]

A=readtable('20220214_Dep.txt');
DateStr = string({'2022-02-14';'2022-02-15'})  % make the date string

DateStr = 2×1 string array
    "2022-02-14"
    "2022-02-15"

t = datetime(DateStr,'InputFormat','yyyy-MM-dd');
datevector=[repmat(t(1),size(A,1)-2,1); repmat(t(2),2,1)];  % adjust the date with your table
newTable = table(datevector, 'VariableNames',{'Date'}); %  % Make a table with new Date
ExpectedTable = [ newTable,A]   % concatenate the new Date table with main Table

ExpectedTable = 16×5 table
       Date            Var1          Var2       Var3      Var4 
    ___________    _____________    _______    ______    ______

    14-Feb-2022    {''08:38:01'}    {'AM''}         0    25.968
    14-Feb-2022    {''08:38:02'}    {'AM''}    0.7516    25.968
    14-Feb-2022    {''08:38:02'}    {'AM''}    1.5062    25.969
    14-Feb-2022    {''08:38:03'}    {'AM''}    2.5063    25.968
    14-Feb-2022    {''08:38:04'}    {'AM''}    3.5059     25.97
    14-Feb-2022    {''08:38:05'}    {'AM''}    4.5061    25.971
    14-Feb-2022    {''08:38:06'}    {'AM''}    5.5067    25.971
    14-Feb-2022    {''08:38:07'}    {'AM''}    6.5058    25.971
    14-Feb-2022    {''08:38:08'}    {'AM''}    7.5065    25.972
    14-Feb-2022    {''08:38:09'}    {'AM''}    8.5061    25.974
    14-Feb-2022    {''08:38:10'}    {'AM''}    9.5067    25.973
    14-Feb-2022    {''08:38:11'}    {'AM''}    10.506    25.973
    14-Feb-2022    {''08:38:12'}    {'AM''}    11.506    25.974
    14-Feb-2022    {''08:38:13'}    {'AM''}    12.507    25.974
    15-Feb-2022    {''00:00:01'}    {'AM''}    1.5062    25.969
    15-Feb-2022    {''00:00:02'}    {'AM''}    1.0662    26.969

[Arif Hoq]

if you want export your data..

writetable(ExpectedTable,'Book2.xlsx','Sheet',1)  % if you want to write your table to excel
writetable(ExpectedTable,'Book1.txt','Delimiter','tab')  % if you want to write your table to text file

[Cristobal Gonzalez Diaz]

Dear Arif Hoq, Thanks a lot for your help, i think it could work, just need to add few lines to identified if the time belongs to the same day or to the next day or the day after the next day...

After i have to merge the date and the time in a sigle column:

ExpectedTable.Date.Format = 'dd.MM.uuuu HH:mm:ss';

ExpectedTable.TimePC.Format = 'dd.MM.uuuu HH:mm:ss';

myDatetime = ExpectedTable.Date + timeofday(ExpectedTable.TimePC);

newTable_dateTime = table(myDatetime, 'VariableNames',{'Date_Time'});

FinalTable = [newTable_dateTime,ExpectedTable(:,3:4)];

Thanks a lot for your help,

[Steven Lord]

t = datetime(now,'ConvertFrom','datenum','Format','dd-MM-yyyy HH:mm:ss a'); % today's Date

You don't need to use now if you want to know today's date (with the time portion representing midnight.)

t = datetime('today')

t = datetime
   17-Feb-2022

t.Format = 'dd-MM-yyyy hh:mm:ss a'

t = datetime
   17-02-2022 12:00:00 AM

So what's 16 hours 23 minutes after midnight?

t2A = t + hours(16) + minutes(23) % or

t2A = datetime
   17-02-2022 04:23:00 PM

t2B = t + duration(16, 23, 0)

t2B = datetime
   17-02-2022 04:23:00 PM

[Cristobal Gonzalez Diaz]

Thanks a lot for your help.

[Cristobal Gonzalez Diaz]

Hello again, it turns, i have not done any thing wrong, that it is even easier to add the date (from the filename) to the time column,

Just need to get the date from the filename = 20220214_Dep.txt

year = filename(1:4);

month = filename(5:6);

day = filename(7:8);

and added in the Format of that datetime table once the table has been loaded.

AA.TimePC.Format= [day '-' month '-' year ' HH:mm:ss'];

Thanks a lot.

[Arif Hoq]

so, did you get your solution ?

[Cristobal Gonzalez Diaz]

more or less, but i am still trying to find a better solution, since after importing the data using

AA.TimePC.Format= [day '-' month '-' year ' HH:mm:ss'];

i do write the data to txt file.

writetable(data_table,'text.txt');

and after i do import it with

opts = setvaropts(opts, "TimePC", "InputFormat", "dd-MM-yyyy hh:mm:ss");
data_table = readtable('text.txt',opts);
data_table = table2timetable(dataAsper_table);
delete('text.txt');

I need to find a better solution, and also if the data has been recorded during 2 days, i should take that into account.

Thanks for asking.

Regards,

[Walter Roberson]

year    = str2double(filename(1:4));
month   = str2double(filename(5:6));
day     = str2double(filename(7:8));
basedt = datetime(year, month, day, 'Format', 'd-M-yyyy HH:mm:ss');

AA.TimePC = basedt + AppropriateDurationVariable;

Answer 2

[Cristobal Gonzalez Diaz]

Dear all, Thanks a lot for your help. Finally i have managed.

time_filename = datetime(str_date,'InputFormat','yyyyMMdd'); 
time_filename.Format = 'dd-MM-yyyy HH:mm:ss';

AA.TimePC = time_filename + timeofday(AA.TimePC);
AA.TimePC.Format=  'dd-MM-yyyy HH:mm:ss';

[I] = find(timeofday(AA.TimePC) == '00:00:00'); 
if length(I)>1
    for nn = 1: (length(I))-1
        AA.TimePC(I(nn):I(nn+1)) = AA.TimePC(I(nn):I(nn+1)) + caldays(nn);
    end
    AA.TimePC(I(nn+1):end) = AA.TimePC(I(nn+1):end) + caldays(nn+1);
else
    AA.TimePC(I:end) = AA.TimePC(I:end) + caldays(1);
end

[Walter Roberson]

The logic is a bit weak. As outside observers we would question whether it is guaranteed that there are no skipped days. For example if no data was collected for February 17th because of a storm, then the files might go from 16th to 18th, but your code assumes each 00:00:00 is exactly one day after the previous.

[Cristobal Gonzalez Diaz]

Dear Water, you are right about that fact, but in my case when something like that happens (storm, power....) no data at all is stored...so i there are no skipped days. My experiments last in general less that a day. But to avoid such problems, the instruments is now also recording the date for each row.

Before i did not need to synchronize the data, but now i want to synchronize it with the data recorded from a different device, therefore I think i need to have both date and time to be able to synchronize them.

Thanks a lot for your help.