Help with converting decimal dates in a loop

15 ビュー (過去 30 日間)
C G
C G 2018 年 7 月 17 日
回答済み: C G 2018 年 8 月 30 日
I am having issues with my loop. I can get the individual elements to work individually, but not together in the loop.
Here is my problem. I have a very long list of decimal dates, dates that look like 1998.95918367347. I know this corresponds to sometime on 16 Dec 1998, or MATLAB date 730105 or 730110, depending if the time is included. What I need is both day month year and the matlab date for different elements in my work.
I was able to convert the decimal dates to day, month, year, time just fine, even in a loop, but when I try to add the conversion to matlab date, the loop falls apart. When I try the second conversion outside the loop, it works fine. I am not sure what is going on. With some tweaking, I was able to narrow down which line is the problem. I think it has to do with the first conversion setting up my date in one format and the second conversion needing a different one, but I don't know how to fix it. With 5555 dates in this batch and other large batches coming soon, it would help to have it in a loop so I don't have to do each decimal date individually.
It would help to have this in a text file to share with others, so that is how the loop was designed.
If anyone knows of a shorter way to get both day,month,year and the matlab date, I would appreciate it. As far as the data does. I suggest starting with 1900.123456789 and then adding some random decimal places until you have a few samples.
I am also having a random issue with the matlab dates. Sometimes it pops up and other times it doesn't. There are some instances when it is working in the loop, where the matlab dates do not work earlier than year 178CE. It appears to be random.
Below is my code.
A = xlsread('Example.xlsx');
B = num2cell(A);
[fid,msg] = fopen(sprintf('Example3.txt'),'wt');
assert(fid>=0,msg)
for q = 1:5555
DecDate = B{q,5}; %This is the column with the decimal dates in it.
n = datestr(datenum(DecDate,1,0));
fprintf(fid,'%s\n', n);
DateString = datestr(datenum(DecDate,1,0));%This line is optional?
formatIn = 'dd-mmm-yyyy HH:MM:SS';
p = datenum(n,formatIn); %This is where the problem is.
%p = datenum(DateString,formatIn); %This line also caused problems.
fprintf(fid,'%s', ' ');
fprintf(fid,'%s', p);
end
fclose(fid);
  13 件のコメント
dpb
dpb 2018 年 7 月 17 日
編集済み: dpb 2018 年 7 月 19 日
What's wrong with datetime? datenum has been deprecated.
But, if you insist, then just like for datestr,
dates=A(:,5); % convenient variable for dates column
yr=fix(dates); % year
secs=(dates-yr).*(365+isleapyr(yr))*24*3600; % seconds in year accounting for leap yrs
dn=datenum(yr,1,0,0,0,secs); % ML datenum
Walter Roberson
Walter Roberson 2018 年 7 月 19 日
Please so not close questions that have an Answer.

サインインしてコメントする。

採用された回答

C G
C G 2018 年 7 月 19 日
A=[1.18750 1.90179 2.36709 2.87342 3.27273 3.86364 4.22727 4.51948 4.77922 5.06122 5.46939 5.87755];
yr=fix(A);
secs=(A-yr).*(365+isleapyr(yr)).*24.*3600;
dn=fix(datenum(yr,1,0,0,0,secs)).'
ds=datestr(dn) % Note: Struggles with BCE values
%Better to use the loop. It allows you to convert it easily to use in other programs.
A = xlsread('Example.xlsx');
B = num2cell(A);
[fid,msg] = fopen(sprintf('Example3.txt'),'wt');
assert(fid>=0,msg)
for q = 1:5555
DecDate = B{q,5};
n = datestr(datenum(DecDate,1,0));
fprintf(fid,'%s\n', n);
end
fclose(fid);
  2 件のコメント
Walter Roberson
Walter Roberson 2018 年 7 月 19 日
Please so not close questions that have an Answer.
dpb
dpb 2018 年 7 月 19 日
"ds=datestr(dn) % Note: Struggles with BCE values"
Per documentation.
You might instead consider using juliandate which does handle BCE dates but is in days and fractions vis a vis years and fractions from its common epoch. I haven't taken the time to think much of conversion but datetime supports that _'ConvertFrom','juliandate' named argument.
>> datetime(0,'ConvertFrom','juliandate')
ans =
datetime
24-Nov--4713 12:00:00
>>

サインインしてコメントする。

その他の回答 (4 件)

dpb
dpb 2018 年 7 月 17 日
編集済み: dpb 2018 年 7 月 19 日
A = xlsread('Example.xlsx');
[fid,msg] = fopen(sprintf('Example3.txt'),'wt');
assert(fid>=0,msg)
dates=A(:,5); % convenient variable for dates column
yr=fix(dates); % year
secs=(dates-yr).*(365+isleapyr(yr))*24*3600; % seconds in year accounting for leap yrs
dt=datetime(yr,1,0,0,0,secs); % ML datetime
fprintf(fid,'%s\n', dt); % write to file
fid=fclose(fid);
isleapyr is my utility function--
>> type isleapyr
function is=isleapyr(yr)
% returns T for input year being a leapyear
% dpbozarth Rev 0 07Jun1998 Initial
% Rev 1 31Mar2005 Use eomday() vs number days in year
% Rev 2 04Feb2015 Handle new datetime class
if isdatetime(yr), yr=year(yr); end
is=eomday(yr,2)==29;
If you really want to ignore before 1900, you can "fix" the function to return false but it'll screw up with Matlab whether use datenum or datetime as they both incorporate leap years in their calculations. It'd be easy-enough in just the function;
is=eomday(yr,2)==29 & yr>1900;
But then the ML functions will be off and it'll be compounded the farther you go back. I forget when the initial epoch for datetime is, but it's based on the ISO Standard. What and how that's related to your problem I've no klew... :)
Without those, your problem is much more of a pit(proverbial)a(ppendage) to deal with. Although it raises the question of who defined the values and what did they use is the key to decoding the values.
  15 件のコメント
dpb
dpb 2018 年 7 月 19 日
編集済み: dpb 2018 年 7 月 19 日
Chill, dood.
Attach as a file, please.
The code is above. (Altho I forgot one .*)
>> A=[1.18750 1.90179 2.36709 2.87342 3.27273 3.86364 4.22727 4.51948 4.77922 5.06122 5.46939 5.87755];
>> yr=fix(A);
>> secs=(A-yr).*(365+isleapyr(yr))*24*3600;
>> dn=fix(datenum(yr,1,0,0,0,secs)).'
dn =
434
695
864
1049
1195
1411
1544
1651
1746
1849
1998
2147
>> ds=datestr(dn)
ds =
12×11 char array
'09-Mar-0001'
'25-Nov-0001'
'13-May-0002'
'14-Nov-0002'
'09-Apr-0003'
'11-Nov-0003'
'23-Mar-0004'
'08-Jul-0004'
'11-Oct-0004'
'22-Jan-0005'
'20-Jun-0005'
'16-Nov-0005'
>>
ADDENDUM
Forgot the specific output format for date...
>> ds=datestr(dn,24)
ds =
12×10 char array
'09/03/0001'
'25/11/0001'
'13/05/0002'
'14/11/0002'
'09/04/0003'
'11/11/0003'
'23/03/0004'
'08/07/0004'
'11/10/0004'
'22/01/0005'
'20/06/0005'
'16/11/0005'
>>
C G
C G 2018 年 7 月 19 日
It is a long column of decimal numbers, but sure. Here you are.

サインインしてコメントする。


dpb
dpb 2018 年 7 月 19 日
>> t=xlsread('Example.xlsx');
>> [dn,ds]=convertTN(t);
>> whos t dn ds
Name Size Bytes Class Attributes
dn 5555x1 44440 double
ds 5555x1 733260 cell
t 5555x1 44440 double
>> save example t dn ds
>> dn(end)
ans =
737791
>> ds(end)
ans =
1×1 cell array
{'01/01/2020'}
>>
>> type convertTN
function [dn,ds]=convertTN(t)
% Returns ML datenum and datestr for input fractional year
yr=fix(t(:));
secs=(t-yr).*(365+isleapyr(yr))*24*3600;
dn=fix(datenum(yr,1,0,0,0,secs));
ds=cellstr(datestr(dn,24));
end
>>
Don't have a klew whether means anything or not, but processes the whole file w/o error...

Walter Roberson
Walter Roberson 2018 年 7 月 19 日
t = readtable('Example.xlsx');
Input = t{:,1};
Output2 = datenum(floor(Input(:)), 0, 1) + (Input - floor(Input)) * 365;
Output1 = cellstr( datestr(Output2, 'dd/mmm/yyyy') );
This produces dates in the year range 9998 for the three negative decimal dates in your input, -1.87109375 -1.51953125 -1.2265625. In order to correct that, you will need to tell us the date representation you want to use for BCE, and you also need to clarify whether an entry of -0.25 would mean 1/4 year before the beginning of the common era (so, September) or if -0.25 should mean 1/4 year into the year before the common era (so, April).

C G
C G 2018 年 8 月 30 日
I improved upon the code a little bit. It is faster and easier to manage. What this code will do is generate a list of Decimal years from a obnoxiously long numeric matrix of data, known in this case as t1data. The first line in the for loop is to skip over the columns in the data that are not related to the date. The 1900 at the front is to make the decimal year take on 4 digits and be in the 20th century. Thankfully, my list does not cross into the 21st century. The first DateNumber uses all rows. The one in the loop focuses on the date for the second row only. For reference, there are 11 columns of data related to one decimal year per row. The columns are arranged like this, "Year, Month, Day, Hour, Minute, Second, Data, Data, Data, Data, Data". The sequence repeats itself horizontally for over 90000 columns.
DateNumber = 1900 + (decyear([t1Data(:,1),t1Data(:,2),t1Data(:,3),t1Data(:,4),t1Data(:,5),t1Data(:,6)]));
for j = 12:11:size(t1Data,2)
DateNumber_1(j) = 1900 + (decyear([t1Data(2,j),t1Data(2,j+1),t1Data(2,j+2),t1Data(2,j+3),t1Data(2,j+4),t1Data(2,j+5)]));
end
Running the for loop will create a single row with a date every 12th column. To transpose the row, simply use,
DateNumber_1 = DateNumber_1.';
To plot the datenumber with some data try this outside of the loop. This will create a scatter plot of whatever you are looking for and will skip all of the zeros in the DateNumber_1 dataset.
scatter(DateNumber_1(12:11:end),somedata(12:11:end));

カテゴリ

Help Center および File ExchangeDates and Time についてさらに検索

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by