Creating a datetime variable - unconventionally

Question

Lewis Waswa 2022-3-17

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1673809-creating-a-datetime-variable-unconventionally

评论： Star Strider 2022-3-18

I have an excel sheet which has several columns including a date column of which I would like to create a datetime object from. The snippet of this data is as shown below.

I would like to create a datetime object such that shows the year and the month alone ( in this case 2003-January) with 48 intervals for each month - for the whole range of the year. It is more of replicating the 2003-January datetime 48 times and then moving next to 2003-February till when we reach December. Any help will be appreciated.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Star Strider 2022-3-17

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1673809-creating-a-datetime-variable-unconventionally#answer_919994

在 MATLAB Online 中打开

Using months instead of minutes —

Date = {'200301'; '200306'; '200312'}
Date = 3×1 cell array
    {'200301'}
    {'200306'}
    {'200312'}
DateDT = datetime(Date, 'InputFormat','yyyyMM')
DateDT = 3×1 datetime array
   01-Jan-2003
   01-Jun-2003
   01-Dec-2003
DateDT.Format = 'yyyyMM'
DateDT = 3×1 datetime array
   200301
   200306
   200312

.

6 个评论
显示 4更早的评论隐藏 4更早的评论

Star Strider 2022-3-17

编辑：Star Strider 2022-3-17

在 MATLAB Online 中打开

Yes. Just change the Format string.

Date = {'200301'; '200306'; '200312'}
Date = 3×1 cell array
    {'200301'}
    {'200306'}
    {'200312'}
DateDT = datetime(Date, 'InputFormat','yyyyMM')
DateDT = 3×1 datetime array
   01-Jan-2003
   01-Jun-2003
   01-Dec-2003
DateDT.Format = 'MMM-yyyy'
DateDT = 3×1 datetime array
   Jan-2003
   Jun-2003
   Dec-2003

EDIT — (17 Mar 2022 at 14:12)

The problem is that beginning at row 576 the ‘month’ switches from 12 to 13. and checking the last two digits of the ‘Date’ variable shows them to actually be days of the month rather than months —

format longg
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/930604/Data-E.xlsx', 'VariableNamingRule','preserve');
CheckTable = T1(570:580,:)
CheckTable = 11×10 table
     Date     Hour    Increment     Unit      KW     Var6    Kvar    Var8          KVA                  Pf        
    ______    ____    _________    ______    ____    ____    ____    ____    ________________    _________________

    200312    2100       30        {'KW'}    6581     0      2036     0      6888.74858011236    0.955325909120734
    200312    2130       30        {'KW'}    5724     0      1863     0      6019.54690985958    0.950902133618148
    200312    2200       30        {'KW'}    5119     0      1874     0      5451.24178513483    0.939052091572817
    200312    2230       30        {'KW'}    4464     0      1923     0      4860.57866925328    0.918409165607805
    200312    2300       30        {'KW'}    4041     0      1864     0      4450.18842297717    0.908051438706629
    200312    2330       30        {'KW'}    3766     0      1839     0      4191.02338337547    0.898587207825799
    200312    2400       30        {'KW'}    3624     0      1752     0      4025.28011447651    0.900310014939496
    200313      30       30        {'KW'}    3458     0      1732     0      3867.50410471663    0.894116698100664
    200313     100       30        {'KW'}    3378     0      1741     0       3800.2585438362    0.888886890466683
    200313     130       30        {'KW'}    3390     0      1724     0      3803.19286915613    0.891356319973379
    200313     200       30        {'KW'}    3326     0      1670     0       3721.7168081411    0.893673584385709
CheckDate = [min(rem(T1{:,1},100)); max(rem(T1{:,1},100))]      % Last Two Digits Of 'Date'
CheckDate = 2×1
     1
    31

I have no idea what the ‘Date’ numbers actually represent, however the last two digits are not months. I have no idea where the month designations would be, since I don’t see anything in the table that corresponds to them.

.

Star Strider 2022-3-17

编辑：Star Strider 2022-3-17

在 MATLAB Online 中打开

Yes.

See my edited comment above.

EDIT — (17 Mar 2022 at 14:40)

The last two digits appear to be days of the month, not months, so that is the reason for the NaT results. There do not appear to be any month data in the file, so I am not certain that defining the months will be possible.

It took me a few minutes to determine that.

EDIT — (17 Mar 2022 at 16:34)

I finally figured out how to parse this file.

Try this —

format longg                                                                        % Optional
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/930604/Data-E.xlsx', 'VariableNamingRule','preserve');
T1.Hour(T1.Hour==2400) = 0;                                                         % Set '2400' To '0000'
Hourdt = datetime(compose('%04d',T1.Hour),'InputFormat','HHmm', 'Format','HH:mm');  % Convert 'Hour' To 'datetime'
Datedt = datetime(string(T1.Date),'InputFormat',"yyyydd");                          % Parse 'Date' Correctly
T1.Date = Datedt+timeofday(Hourdt);                                                 % Combine Them
T1 = removevars(T1,{'Hour'});                                                       % 'Hour' Is No Longer Necessary
T1.Date.Format = 'yyyy-dd HH:mm'                                                    % Define Fromat & Display Result
T1 = 9362×9 table
DateIncrementUnitKWVar6KvarVar8KVAPf_____________________________________________________________________________

    2003-01 00:30       30        {'KW'}    3355     0      1749     0      3783.52031843361    0.886740315270456
    2003-01 01:00       30        {'KW'}    3227     0      1682     0      3639.04561664181    0.886770966882779
    2003-01 01:30       30        {'KW'}    3240     0      1714     0      3665.43258020114    0.883933868406387
    2003-01 02:00       30        {'KW'}    3147     0      1676     0      3565.47121710441    0.882632282909225
    2003-01 02:30       30        {'KW'}    3120     0      1535     0       3477.1576035607    0.897284608786509
    2003-01 03:00       30        {'KW'}    3099     0      1495     0      3440.75950917817    0.900673235584605
    2003-01 03:30       30        {'KW'}    3083     0      1465     0      3413.37281878203    0.903212207888877
    2003-01 04:00       30        {'KW'}    3111     0      1452     0       3433.1654489698     0.90616081463057
    2003-01 04:30       30        {'KW'}    3249     0      1492     0       3575.2013929288    0.908759995010639
    2003-01 05:00       30        {'KW'}    3325     0      1466     0      3633.83832881982    0.915010437759315
    2003-01 05:30       30        {'KW'}    3547     0      1484     0      3844.92717746383    0.922514221020865
    2003-01 06:00       30        {'KW'}    3843     0      1492     0      4122.46443283626    0.932209376845008
    2003-01 06:30       30        {'KW'}    4204     0      1675     0       4525.3995403721    0.928978748173543
    2003-01 07:00       30        {'KW'}    4575     0      1721     0      4887.99202127008    0.935967157902857
    2003-01 07:30       30        {'KW'}    5116     0      1832     0       5434.1218241773    0.941458466617012
    2003-01 08:00       30        {'KW'}    5570     0      1937     0      5897.19161974579    0.944517383723764
% TT1 = table2timetable(T1);                                                          % Optional

There are a couple missing values at the end. The rest are read and parsed correctly.

.

Lewis Waswa 2022-3-18

Thank you @Star Strider. This works well.

Star Strider 2022-3-18

As always, my pleasure!

请先登录，再进行评论。

Answer 2

Arif Hoq 2022-3-17

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1673809-creating-a-datetime-variable-unconventionally#answer_919989

在 MATLAB Online 中打开

As you did not attach your data. let's try with your first data

A='200301';
date=datetime(A,'InputFormat','yyyymm');
date2=datetime(date,'Format','yyyy-mm')
date2 = datetime
   2003-01

then every 48 interval try this

T.Date(1:48:end,:) % if T is your table

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

Lewis Waswa 2022-3-17

Thank you @Arif Hoq.

请先登录，再进行评论。

Answer 3

Arif Hoq 2022-3-17

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1673809-creating-a-datetime-variable-unconventionally#answer_920149

在 MATLAB Online 中打开

try this:

B=readtable('Data-E.xlsx');
C=B(1:48:end,:);
dat=string(table2cell(C(:,1)));
date = datetime(dat, 'InputFormat','yyyyMM');
date2=datetime(date,'Format','MMM-yyyy');
date3=rmmissing(date2);

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

Arif Hoq 2022-3-17

在 MATLAB Online 中打开

or try this:

B=readtable('Data-E.xlsx');
C=B(1:48:end,:);
dat=string(table2cell(C(:,1)));
date = datetime(dat, 'InputFormat','yyyyMM');
date2=datetime(date,'Format','MMM-yyyy');
% [R,TF]=rmmissing(date2)
D=cellstr(date2)
[D Lia Lib]=unique(D,'rows','stable')
E=C(Lia,:)
E(13,:)=[] % deleting Nat value
value=string(table2cell(E(:,2:end)));
dat2=string(table2cell(E(:,1)));
date3 = datetime(dat2, 'InputFormat','yyyyMM');
date4=datetime(date3,'Format','MMM-yyyy');
T=timetable(date4,value) % final array

请先登录，再进行评论。

Creating a datetime variable - unconventionally

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

6 个评论
显示 4更早的评论隐藏 4更早的评论

更多回答（2 个）

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

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

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Creating a datetime variable - unconventionally

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

6 个评论 显示 4更早的评论隐藏 4更早的评论

更多回答（2 个）

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

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

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

WeChat

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

6 个评论
显示 4更早的评论隐藏 4更早的评论

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

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