I need to group certain hours of a day into day and night categories from my xlsx file in the format "yyyy-MM-dd'T'HH:mm:ss."

2 views (last 30 days)
Hello,
Still familiarizing mysef with MatLab... Essentially I am trying to distriubute my data into day and night to better understand what vessel types are present. The data looks like this, with the heading being bolded
BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType
2018-05-18T00:00:07 27.79192 -88.7611 13 148.2 144 SBI MAIA IMO9705304 V7MI4 70
ranging from 2018-05-18 to 2020-06-24. It is a very large exel flie so I an unable to attach it here.
so far I have:
clear variables;
close all;
fclose ('all');
% hold on
shipdata = readtable ('allship2018-2020_1_data.xlsx');
t= datetime(shipdata.BaseDateTime, 'InputFormat',"uuuu-MM-dd'T'HH:mm:ss");
day = timerange('06:00:00','18:00:00');
night = timerange('18:00:01','05:59:59');
for i = 1:length(t)
y(i,:) = isbetween(t,day,night);
end
yy = unique(y,'rows');
I was thinking about using "isbetween" to find my hours for day (06:00:00 to 18:00:00) and for night (18:00:01 to 05:59:59). I am not sure how to go about this... I tried this code I provide above and got the error "All inputs must be datetime arrays or date/time character vectors or date/time strings"
From there I will graph the data to compare.
Thanks for any help or advice.

Accepted Answer

Star Strider
Star Strider on 22 Nov 2022
The isbetween functin is the correct approach here, although if you have one set of 12-hour segments and you want to go over several days, a simple logical comparison is all you need.
t = datetime('now') + hours(0:2:64).';
daytime = hour(t)>=6 & hour(t)<18;
figure
plot(t(daytime), ones(size(t(daytime))), '.', 'DisplayName','Daytime')
hold on
plot(t(~daytime), -ones(size(t(~daytime))), '.', 'DisplayName','Nighttime')
hold off
grid
ylim([-1 1]*1.5)
legend('Location','best')
Experiment to get different results.
.
  4 Comments
Star Strider
Star Strider on 24 Nov 2022
opts = detectImportOptions('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1205258/allship2018-2020_1_data_2.xlsx');
opts.VariableTypes{1} = 'datetime';
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1205258/allship2018-2020_1_data_2.xlsx', opts)
T1 = 13543×10 table
BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType ____________________ ______ _______ ____ _____ _______ ________________ ______________ _________ __________ 18-May-2018 00:00:07 27.792 -88.761 13 148.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:00:13 27.716 -88.808 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:01:18 27.788 -88.758 13.1 147.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:01:23 27.712 -88.806 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:02:33 27.708 -88.804 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:02:28 27.785 -88.756 13.1 147.6 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:03:37 27.781 -88.753 13.1 148.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:04:38 27.778 -88.751 13.1 147.3 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:05:14 27.699 -88.799 14 153.8 151 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:05:47 27.774 -88.749 13.1 146.9 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:06:48 27.771 -88.746 13.1 147.4 145 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:06:23 27.695 -88.797 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:03:42 27.704 -88.802 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:08:42 27.686 -88.792 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:07:33 27.69 -88.794 14 153.4 151 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:07:57 27.768 -88.744 13.1 148.5 145 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70
t = T1.BaseDateTime;
Daytime = hour(t)>6 & hour(t)<=18;
addvars(T1, Daytime, 'After','BaseDateTime') % Insert 'Daytime' As Second Column (Optional)
ans = 13543×11 table
BaseDateTime Daytime LAT LON SOG COG Heading VesselName IMO CallSign VesselType ____________________ _______ ______ _______ ____ _____ _______ ________________ ______________ _________ __________ 18-May-2018 00:00:07 false 27.792 -88.761 13 148.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:00:13 false 27.716 -88.808 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:01:18 false 27.788 -88.758 13.1 147.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:01:23 false 27.712 -88.806 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:02:33 false 27.708 -88.804 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:02:28 false 27.785 -88.756 13.1 147.6 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:03:37 false 27.781 -88.753 13.1 148.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:04:38 false 27.778 -88.751 13.1 147.3 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:05:14 false 27.699 -88.799 14 153.8 151 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:05:47 false 27.774 -88.749 13.1 146.9 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:06:48 false 27.771 -88.746 13.1 147.4 145 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:06:23 false 27.695 -88.797 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:03:42 false 27.704 -88.802 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:08:42 false 27.686 -88.792 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:07:33 false 27.69 -88.794 14 153.4 151 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:07:57 false 27.768 -88.744 13.1 148.5 145 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70
% Check1 = [nnz(Daytime) nnz(~Daytime) size(T1,1)]
figure
plot(t(Daytime), 0.1*ones(size(t(Daytime))), '.', 'DisplayName','Daytime', 'MarkerSize',0.6)
hold on
plot(t(~Daytime), -0.1*ones(size(t(~Daytime))), '.', 'DisplayName','Nighttime', 'MarkerSize',0.6)
hold off
grid
ylim([-1 1]*0.2)
legend('Location','best')
I doubt that a bar plot would work here. A more reasonable approach would likely be a stairs plot. I will work with you to get that running if you want to use it. (I experimented with that.)
.

Sign in to comment.

More Answers (2)

Steven Lord
Steven Lord on 22 Nov 2022
Let's look at a sample datetime.
t = datetime('now')
t = datetime
22-Nov-2022 22:46:03
What time of day does that represent?
tod = timeofday(t)
tod = duration
22:46:03
Is that time of day during the day? You can either explicitly create a duration by calling duration or just indicate the number of hours after midnight.
dawn = duration(6, 0, 0)
dawn = duration
06:00:00
dusk = hours(18)
dusk = duration
18 hr
isDuringTheDay = isbetween(tod, dawn, dusk)
isDuringTheDay = logical
0
How about 12 hours from now?
then = t+hours(12)
then = datetime
23-Nov-2022 10:46:03
todThen = timeofday(then)
todThen = duration
10:46:03
isDuringTheDay12 = isbetween(todThen, dawn, dusk)
isDuringTheDay12 = logical
1
Of course, if it's not during the day then it's during the night. It's easier to ask the question "is it daytime" because asking "is it nighttime" would require asking "is it between midnight and dawn" and "is it between dusk and midnight" separately.
A timerange object is used for indexing into a timetable.

Stephen23
Stephen23 on 24 Nov 2022
Edited: Stephen23 on 24 Nov 2022
fnm = 'allship2018-2020_1_data_2.xlsx';
obj = detectImportOptions(fnm);
obj = setvartype(obj,'BaseDateTime','datetime');
tbl = readtable(fnm,obj)
tbl = 13543×10 table
BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType ____________________ ______ _______ ____ _____ _______ ________________ ______________ _________ __________ 18-May-2018 00:00:07 27.792 -88.761 13 148.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:00:13 27.716 -88.808 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:01:18 27.788 -88.758 13.1 147.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:01:23 27.712 -88.806 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:02:33 27.708 -88.804 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:02:28 27.785 -88.756 13.1 147.6 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:03:37 27.781 -88.753 13.1 148.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:04:38 27.778 -88.751 13.1 147.3 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:05:14 27.699 -88.799 14 153.8 151 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:05:47 27.774 -88.749 13.1 146.9 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:06:48 27.771 -88.746 13.1 147.4 145 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:06:23 27.695 -88.797 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:03:42 27.704 -88.802 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:08:42 27.686 -88.792 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:07:33 27.69 -88.794 14 153.4 151 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:07:57 27.768 -88.744 13.1 148.5 145 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70
tod = timeofday(tbl.BaseDateTime);
isd = isbetween(tod, duration(6,0,0), duration(18,0,0)); % logical index = is daytime
tbl_day = tbl(isd,:)
tbl_day = 7191×10 table
BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType ____________________ ______ _______ ____ ______ _______ ________________ ______________ ___________ __________ 18-May-2018 12:19:26 27.866 -88.758 0.2 139.1 174 {'APPALOOSA' } {'IMO9646704'} {'V7CH8' } 70 18-May-2018 12:22:56 27.866 -88.758 0.2 131.8 177 {'APPALOOSA' } {'IMO9646704'} {'V7CH8' } 70 18-May-2018 12:18:18 27.597 -88.959 14.4 -139.8 268 {'INDEPENDENCE'} {'IMO9710191'} {'WSID' } 80 18-May-2018 12:24:18 27.597 -88.986 14.5 -139.7 268 {'INDEPENDENCE'} {'IMO9710191'} {'WSID' } 80 18-May-2018 12:26:44 27.939 -88.898 12.8 155 154 {'UBC TOKYO' } {'IMO9300752'} {'C4DT2' } 70 18-May-2018 12:20:24 27.96 -88.909 12.8 154 154 {'UBC TOKYO' } {'IMO9300752'} {'C4DT2' } 70 18-May-2018 12:21:23 27.597 -88.973 14.5 -139.7 268 {'INDEPENDENCE'} {'IMO9710191'} {'WSID' } 80 18-May-2018 12:25:23 27.597 -88.991 14.5 -139.7 268 {'INDEPENDENCE'} {'IMO9710191'} {'WSID' } 80 18-May-2018 12:20:45 27.866 -88.758 0.2 132.5 176 {'APPALOOSA' } {'IMO9646704'} {'V7CH8' } 70 18-May-2018 12:27:48 27.658 -88.816 0.7 30.7 345 {'PELICAN' } {'IMO8517009'} {'WDD6114'} 90 18-May-2018 12:25:15 27.866 -88.758 0.2 134.8 178 {'APPALOOSA' } {'IMO9646704'} {'V7CH8' } 70 18-May-2018 12:26:48 27.597 -88.997 14.5 -139.6 268 {'INDEPENDENCE'} {'IMO9710191'} {'WSID' } 80 18-May-2018 11:48:11 27.597 -88.822 14.6 -139.5 268 {'INDEPENDENCE'} {'IMO9710191'} {'WSID' } 80 18-May-2018 12:21:55 27.866 -88.758 0.2 148.4 176 {'APPALOOSA' } {'IMO9646704'} {'V7CH8' } 70 18-May-2018 12:26:25 27.866 -88.758 0.2 132.4 178 {'APPALOOSA' } {'IMO9646704'} {'V7CH8' } 70 18-May-2018 12:27:54 27.936 -88.896 12.8 154 154 {'UBC TOKYO' } {'IMO9300752'} {'C4DT2' } 70
tbl_night = tbl(~isd,:)
tbl_night = 6352×10 table
BaseDateTime LAT LON SOG COG Heading VesselName IMO CallSign VesselType ____________________ ______ _______ ____ _____ _______ ________________ ______________ _________ __________ 18-May-2018 00:00:07 27.792 -88.761 13 148.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:00:13 27.716 -88.808 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:01:18 27.788 -88.758 13.1 147.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:01:23 27.712 -88.806 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:02:33 27.708 -88.804 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:02:28 27.785 -88.756 13.1 147.6 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:03:37 27.781 -88.753 13.1 148.2 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:04:38 27.778 -88.751 13.1 147.3 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:05:14 27.699 -88.799 14 153.8 151 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:05:47 27.774 -88.749 13.1 146.9 144 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:06:48 27.771 -88.746 13.1 147.4 145 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70 18-May-2018 00:06:23 27.695 -88.797 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:03:42 27.704 -88.802 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:08:42 27.686 -88.792 14 154.4 152 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:07:33 27.69 -88.794 14 153.4 151 {'ZALIV BAIKAL'} {'IMO9360128'} {'A8RM5'} 80 18-May-2018 00:07:57 27.768 -88.744 13.1 148.5 145 {'SBI MAIA' } {'IMO9705304'} {'V7MI4'} 70

Categories

Find more on Matrices and Arrays in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by