Converting date range from numeric to date format in mat file

Question

Ameer Fahmi 2019-6-27

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/469265-converting-date-range-from-numeric-to-date-format-in-mat-file

编辑： dpb 2019-6-30

I have a mat file in which I store the data to use for analysis when needed, I wanted to past a data from excel to the mat file

This is the data I need to copy to the mat file from excel

date	            Inflows	           HPI
6/30/1985'	-738.4928374	-1.376979203
9/30/1985'	-149.8324882	-1.318316639
12/30/1985'	-389.9021275	-1.404933598
3/30/1986'	-1250.503701	-0.137879304
6/30/1986'	-944.0048226	-3.060762411
9/30/1986'	-64.68902275	0.169427825
12/30/1986'	-278.1197318	0.049207493
3/30/1987'	-507.793333	4.174160222

However, when I past the data, the date column is taken as numeric as seen below

'date'	         'Inflows'	               'HPI'
000151133501259446	-149.832488200000	-1.31831663900000
000201511335012594	-389.902127500000	-1.40493359800000
03524672708963e-05	-1250.50370100000	-0.137879304000000
000100704934541793	-944.004822600000	-3.06076241100000
000151057401812689	-64.6890227500000	0.169427825000000
000201409869083585	-278.119731800000	0.0492074930000000
03271263210871e-05	-507.793333000000	4.17416022200000
000100654252642174	-317.350932300000	3.98837463000000
000150981378963261	-289.777479600000	2.87872744700000

Is there any way to convert the date column from numeric to date format

12 个评论
显示 10更早的评论隐藏 10更早的评论

dpb 2019-6-28

编辑：dpb 2019-6-28

在 MATLAB Online 中打开

Where does the sequence start to generate the data in the beginning? Need to fix the sequence of creating it to use the correct form at that point and be consistent. In Matlab, use the builtin datetime class for time data variables and a table containing the variables will then each have the proper storage format when SAVE/LOAD from a .mat file.

How you would have gotten such as above specifically isn't clear to me--those aren't Excel nor old ML date numbers with such small exponents so you've done something unusual somewhere...

ADDENDUM

I don't know how you managed it and we can only guess since you don't show us any code, but the answer you have for the date column is actually the result of interpreting the date string as an expression--

>> fprintf('%.14f\n',12/30/1986)
0.00020140986908
>> 

matches one of the values that match between the two datasets above.

Again, how you did that is a mystery, but that's the root of your problem.

Ameer Fahmi 2019-6-28

在 MATLAB Online 中打开

Thanks for your comment, I have solved this problem by importing my data (SAdata) into Matlab then pass it to the GC mat file by doing this

GC.y=SAdata
GC = 
  struct with fields:
    q: {142×5 cell}
    m: {424×5 cell}
    y: {136×3 cell}

The GC mat file is structure file with three fields as seen above. This is how my data looks after passing it to the mat file

    "date"	  "Inflows"	             "HPI"
"6/30/1985"	-738.492837412390	-1.37697920341393
"9/30/1985"	-149.832488184277	-1.31831663921641
"12/31/1985"	-389.902127478935	-1.40493359803057
"3/31/1986"	-1250.50370119007	-0.137879304433611
"6/30/1986"	-944.004822635733	-3.06076241123973
"9/30/1986"	-64.6890227458389	0.169427824516215
"12/31/1986"	-278.119731812238	0.0492074928011591
"3/31/1987"	-507.793332986046	4.17416022215573
"6/30/1987"	-317.350932335587	3.98837462979420
"9/30/1987"	-289.777479612941	2.87872744682405

However, I tired to seperate the data column by doing this

data=GC.y;
date=datenum(cell2mat(data(2:end,1)),'mm/dd/yyyy');

But I got this error

Error using cell2mat (line 52)
CELL2MAT does not support cell arrays containing cell arrays or objects.

how can I solve this problem?

Ameer Fahmi 2019-6-28

编辑：Ameer Fahmi 2019-6-28

在 MATLAB Online 中打开

The GC file is part of a mat file used in a code of time-varying causality, in this code there is mat file called Slope_Macro.mat in which there is structure file called GC, the latter has 2 fields. By adding my data (SAdata) it will be three fields. The function named RGC_Macro_Finance_monthly.m in the code loads the data from the GC file in the mat file to estimate the the recusive evolving granger causality. This is the formula used in the code of this function to load the data

load('Slope_Macro.mat');
data=GC.m;
date=datenum(cell2mat(data(2:end,1)),'dd/mm/yyyy');
Inf=cell2mat(data(2:end,2));
gip=cell2mat(data(2:end,3));
frate=cell2mat(data(2:end,4));
spread=cell2mat(data(2:end,5));
y=[gip Inf frate spread];

This is why I needed to pass my data to the GC file. To do that I first tried to past my data directly from excel to the GC file in Matlab as I referred to in my first post but the date column was taken in strange format as you said. Then I managed to get around this problem by importing my data (SAdata) into Matlab which originally looks like this in excel

date	          Inflows	     HPI
6/30/1985	-738.4928374	-1.376979203
9/30/1985	-149.8324882	-1.318316639
12/31/1985	-389.9021275	-1.404933598
3/31/1986	-1250.503701	-0.137879304
6/30/1986	-944.0048226	-3.060762411
9/30/1986	-64.68902275	0.169427825
12/31/1986	-278.1197318	0.049207493
3/31/1987	-507.793333	4.174160222
6/30/1987	-317.3509323	3.98837463
9/30/1987	-289.7774796	2.878727447
12/31/1987	-530.6950067	5.050070933
3/31/1988	-155.5525937	3.608376136
6/30/1988	-14.48986615	2.223192278

After importing my data into matlab in array format, I passed it to the GC file by doing this

GC.y=SAdata
GC = 
  struct with fields:
    q: {142×5 cell}
    m: {424×5 cell}
    y: {136×3 cell}

But this data in the filed y need to be sepereated again, because the funaction I referred to previously use each column of the data seperately. So I took it again and named it as data by doing this

data=GC.y;

Then tried to seperate date column by doing this

date=datenum(cell2mat(data(2:end,1)),'mm/dd/yyyy');

The other variables in y field were easilly seperated. The problem is in the dates column whic I couldn't seperate. I tried datetime and datenum funtions but nothing worked. I always had the errors I referred to in my previous comments.

Ameer Fahmi 2019-6-29

编辑：Ameer Fahmi 2019-6-29

SAdata.xlsx

The first step in which I got that strange format of dates column was done by pasting the data from excel into an existing field in the GC file, as I referred to previously the structure GC file had two fields which are the original data of the code. I opened the GC.m field and I tried to past my data from excel to the field directly, the outcome of the process is what you see in the first post. But this is no longer an issue for me because I imported my data from excel into matlab then I passed my data into the GC file under the name GC.y as I explained.

Regarding the path of the file, the GC file is inside a mat file named "Slope_Macro.mat". The GC file is structure file with three fields contains the data that will be used in the code.

The only source of my data is excel, I imported my data into excel using the array formats. then passed it to the GC file. the problem is that the function cell2mat does not work on the dates column when I try to seperate the variables of my data, but it works with the other variables with no problem.

Hope this will make it more clear, I'm sorry that I make it hard for you to understand, but the process how data is handled in the code I'm using is complicated. I don't know why the writer of the code didn't use simple loading from excel or csv files instead of using this long path of mat file then structure file in the mat file and lastly creating fields in the structure file

Anyway, I have attached my data

dpb 2019-6-29

编辑：dpb 2019-6-29

在 MATLAB Online 中打开

Is the code an m-file? Shouldn't be difficult to modify to simplify at least the interface if not the internals.

As for the specific question, the "paste" operation undoubtedly is what caused the problem with the date being interpreted as an expression -- when did that, lost the type as being a date string representation and the slash separators looked to the interpreter like math operators and that's what it did--divided the three numbers and stored the result. That mystery of how that could have occurred now seems understandable. The solution is "don't do that!" :)

We still don't have the actual code you used, but to read the Excel sheet, simply

SAData=readtable('SAData.xlsx');

returns

>> SAData=readtable('SAdata.xlsx');
>> whos SAData
  Name          Size            Bytes  Class    Attributes
  SAData      135x3              4588  table              
>> SAData(1:5,:)
ans =
  5×3 table
       date       Inflows      HPI   
    __________    _______    ________
    6/30/1985     -738.49      -1.377
    9/30/1985     -149.83     -1.3183
    12/31/1985     -389.9     -1.4049
    3/31/1986     -1250.5    -0.13788
    6/30/1986        -944     -3.0608
>> SAData.date(1:5)
ans = 
  5×1 datetime array
   6/30/1985 
   9/30/1985 
   12/31/1985
   3/31/1986 
   6/30/1986 
>> 

so you can simply address whichever piece of the file you wish as the table column variable name and put them into whatever struct you wish. As the above shows, the date field is now a Matlab datetime array, the other two are doubles and there are no further cell arrays to do anything with nor to try to convert to datetimes--all done for you when you read the data in the correct format (and in this case, readtable is smart enough to figure it out for you automagically).

Rather than that, as you say, if the other function is a Matlab function, I'd probably just fix its interface to be more user friendly in taking fewer outside steps.

ADDENDUM

Note, however, you cannot put datetime array values into the same array as doubles -- the only way that can be is to use a cell array and that's an unnecessary complication it would seem.

But, if for some reason you are compelled to do so, you just dereference the cell array content with "the curlies", the {} braces instead of regular parentheses ()

Ameer Fahmi 2019-6-30

I had to do it, even though for me didn't make any sense, because the functions in the m-file code I'm using use the dates as numeric, and the code works fine with it.

dpb 2019-6-30

Still, if you have to read the Excel file for the data, it's already parsed as datetime ignoring the header so just convert that representation to datenum directly instead of parsing the same data over yet again...

Or, of course, fix the function itself to use the more better datetime instead... :)

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Steven Lord 2019-6-28

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/469265-converting-date-range-from-numeric-to-date-format-in-mat-file#answer_381237

If you want to store date and time data in MATLAB I strongly recommend storing it as a datetime array or in a timetable with a datetime array as the RowTimes property of the timetable. Once you have the data in a form that stores your data as datetime use something like writetable (introduced in release R2013a), writematrix (R2019a) or writetimetable (R2019a) or convert the datetime to an Excel serial date number with exceltime.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Ameer Fahmi 2019-6-28

编辑：Ameer Fahmi 2019-6-28

在 MATLAB Online 中打开

I tried what you've seggested, then I pasted the data I imported in form of timetable (SAdata1) to the GC mat file

GC.y=SAdata1;

But I had the same problem when trying to seperate the date column using the datenum function as follows

data=GC.y;
date=datenum(cell2mat(data(2:end,1)),'mm/dd/yyyy');

But this time I got different error

Error using cell2mat (line 42)
Subscripting a table using linear indexing (one subscript) or multidimensional indexing (three or more
subscripts) is not supported. Use a row subscript and a variable subscript.

请先登录，再进行评论。

Converting date range from numeric to date format in mat file

12 个评论
显示 10更早的评论隐藏 10更早的评论

回答（1 个）

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Converting date range from numeric to date format in mat file

12 个评论 显示 10更早的评论隐藏 10更早的评论

回答（1 个）

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

12 个评论
显示 10更早的评论隐藏 10更早的评论

1 个评论
显示 -1更早的评论隐藏 -1更早的评论