Arranging events based on hours of rainfall

5 Apr 2022
1 Answer
Answer Accepted
Updated 6 Apr 2022
6 Views (30 days)

0 votes

I have the following table of rainfall events separated by NaN.
Date Time (Hours) NumOfHours Total Rainfall(mm)
___________ ____________ __________ __________________
NaT NaN NaN 6
06-Apr-2019 09:00:00 1 1.02
06-Apr-2019 10:00:00 2 0.51
06-Apr-2019 11:00:00 3 0.25
NaT NaN NaN 7
17-Apr-2019 03:00:00 1 0.51
17-Apr-2019 04:00:00 2 0
17-Apr-2019 05:00:00 3 0
17-Apr-2019 06:00:00 4 0.25
17-Apr-2019 07:00:00 5 0.25
NaT NaN NaN 8
29-Apr-2019 08:00:00 1 0.76
29-Apr-2019 09:00:00 2 0.25
NaT NaN NaN 9
02-May-2019 22:00:00 1 3.3
02-May-2019 23:00:00 2 0
03-May-2019 00:00:00 3 0
03-May-2019 01:00:00 4 0.25
NaT NaN NaN 10
03-May-2019 16:00:00 1 1.02
03-May-2019 17:00:00 2 0.51
NaT NaN NaN 11
05-May-2019 06:00:00 1 0.76
NaT NaN NaN 12
13-May-2019 16:00:00 1 1.52
I would like to order the events based on the total number of hours in numerical order (and subsequently chronologinal order based on the date). So it looks like:
ThemeCopy
Date Time (Hours) NumOfHours Total Rainfall(mm)
___________ ____________ __________ __________________
NaT NaN NaN 11
05-May-2019 06:00:00 1 0.76
NaT NaN NaN 12
13-May-2019 16:00:00 1 1.52
NaT NaN NaN 10
03-May-2019 16:00:00 1 1.02
03-May-2019 17:00:00 2 0.51
NaT NaN NaN 8
29-Apr-2019 08:00:00 1 0.76
29-Apr-2019 09:00:00 2 0.25
NaT NaN NaN 6
06-Apr-2019 09:00:00 1 1.02
06-Apr-2019 10:00:00 2 0.51
06-Apr-2019 11:00:00 3 0.25
NaT NaN NaN 9
02-May-2019 22:00:00 1 3.3
02-May-2019 23:00:00 2 0
03-May-2019 00:00:00 3 0
03-May-2019 01:00:00 4 0.25
NaT NaN NaN 7
17-Apr-2019 03:00:00 1 0.51
17-Apr-2019 04:00:00 2 0
17-Apr-2019 05:00:00 3 0
17-Apr-2019 06:00:00 4 0.25
17-Apr-2019 07:00:00 5 0.25

Sign in to answer this question.

 Accepted Answer

Siddharth Bhutiya
Siddharth Bhutiya on 5 Apr 2022

0 votes

In your timetable you are using NaN/NaT to represent event boundaries which would make using any existing functionality difficult. It might be better to use a grouping variable to identify that these rows belong to the N-th event. I tried transforming your data into that structure.
tt =
25×2 timetable
Date NumOfHours TotalRainfall
____________________ __________ _____________
NaT NaN 6
06-Apr-2019 09:00:00 1 1.02
06-Apr-2019 10:00:00 2 0.51
06-Apr-2019 11:00:00 3 0.25
NaT NaN 7
17-Apr-2019 03:00:00 1 0.51
17-Apr-2019 04:00:00 2 0
17-Apr-2019 05:00:00 3 0
17-Apr-2019 06:00:00 4 0.25
17-Apr-2019 07:00:00 5 0.25
NaT NaN 8
29-Apr-2019 08:00:00 1 0.76
29-Apr-2019 09:00:00 2 0.25
NaT NaN 9
02-May-2019 22:00:00 1 3.3
02-May-2019 23:00:00 2 0
03-May-2019 00:00:00 3 0
03-May-2019 01:00:00 4 0.25
NaT NaN 10
03-May-2019 16:00:00 1 1.02
03-May-2019 17:00:00 2 0.51
NaT NaN 11
05-May-2019 06:00:00 1 0.76
NaT NaN 12
13-May-2019 16:00:00 1 1.52
>> tt.Group = ismissing(tt.NumOfHours) % Find the NaN boundaries
tt =
25×3 timetable
Date NumOfHours TotalRainfall Group
____________________ __________ _____________ _____
NaT NaN 6 true
06-Apr-2019 09:00:00 1 1.02 false
06-Apr-2019 10:00:00 2 0.51 false
06-Apr-2019 11:00:00 3 0.25 false
NaT NaN 7 true
17-Apr-2019 03:00:00 1 0.51 false
17-Apr-2019 04:00:00 2 0 false
17-Apr-2019 05:00:00 3 0 false
17-Apr-2019 06:00:00 4 0.25 false
17-Apr-2019 07:00:00 5 0.25 false
NaT NaN 8 true
29-Apr-2019 08:00:00 1 0.76 false
29-Apr-2019 09:00:00 2 0.25 false
NaT NaN 9 true
02-May-2019 22:00:00 1 3.3 false
02-May-2019 23:00:00 2 0 false
03-May-2019 00:00:00 3 0 false
03-May-2019 01:00:00 4 0.25 false
NaT NaN 10 true
03-May-2019 16:00:00 1 1.02 false
03-May-2019 17:00:00 2 0.51 false
NaT NaN 11 true
05-May-2019 06:00:00 1 0.76 false
NaT NaN 12 true
13-May-2019 16:00:00 1 1.52 false
>> tt.Group = cumsum(tt.Group) % Give a unique number to each unique event
tt =
25×3 timetable
Date NumOfHours TotalRainfall Group
____________________ __________ _____________ _____
NaT NaN 6 1
06-Apr-2019 09:00:00 1 1.02 1
06-Apr-2019 10:00:00 2 0.51 1
06-Apr-2019 11:00:00 3 0.25 1
NaT NaN 7 2
17-Apr-2019 03:00:00 1 0.51 2
17-Apr-2019 04:00:00 2 0 2
17-Apr-2019 05:00:00 3 0 2
17-Apr-2019 06:00:00 4 0.25 2
17-Apr-2019 07:00:00 5 0.25 2
NaT NaN 8 3
29-Apr-2019 08:00:00 1 0.76 3
29-Apr-2019 09:00:00 2 0.25 3
NaT NaN 9 4
02-May-2019 22:00:00 1 3.3 4
02-May-2019 23:00:00 2 0 4
03-May-2019 00:00:00 3 0 4
03-May-2019 01:00:00 4 0.25 4
NaT NaN 10 5
03-May-2019 16:00:00 1 1.02 5
03-May-2019 17:00:00 2 0.51 5
NaT NaN 11 6
05-May-2019 06:00:00 1 0.76 6
NaT NaN 12 7
13-May-2019 16:00:00 1 1.52 7
>> tt(ismissing(tt.NumOfHours),:) = []; % Now remove the artificial boundary NaNs
>> tt
tt =
18×3 timetable
Date NumOfHours TotalRainfall Group
____________________ __________ _____________ _____
06-Apr-2019 09:00:00 1 1.02 1
06-Apr-2019 10:00:00 2 0.51 1
06-Apr-2019 11:00:00 3 0.25 1
17-Apr-2019 03:00:00 1 0.51 2
17-Apr-2019 04:00:00 2 0 2
17-Apr-2019 05:00:00 3 0 2
17-Apr-2019 06:00:00 4 0.25 2
17-Apr-2019 07:00:00 5 0.25 2
29-Apr-2019 08:00:00 1 0.76 3
29-Apr-2019 09:00:00 2 0.25 3
02-May-2019 22:00:00 1 3.3 4
02-May-2019 23:00:00 2 0 4
03-May-2019 00:00:00 3 0 4
03-May-2019 01:00:00 4 0.25 4
03-May-2019 16:00:00 1 1.02 5
03-May-2019 17:00:00 2 0.51 5
05-May-2019 06:00:00 1 0.76 6
13-May-2019 16:00:00 1 1.52 7
Once we have the data in this form, we can use grouping functionality to figure out the counts of each group and since each row corresponds to 1 hr in your data, this should give us the total number of hours for each group.
>> grpCounts = groupcounts(tt,"Group")
grpCounts =
7×3 table
Group GroupCount Percent
_____ __________ _______
1 3 16.667
2 5 27.778
3 2 11.111
4 4 22.222
5 2 11.111
6 1 5.5556
7 1 5.5556
Now we sort the table based on these counts to get the order for out "Group" and then use that new order to reorder our original timetable
>> grpCounts = sortrows(grpCounts,'GroupCount')
grpCounts =
7×3 table
Group GroupCount Percent
_____ __________ _______
6 1 5.5556
7 1 5.5556
3 2 11.111
5 2 11.111
1 3 16.667
4 4 22.222
2 5 27.778
>> newOrder = grpCounts.Group
newOrder =
6
7
3
5
1
4
2
I'll use this order to convert my Group variable into an Ordinal categorical and then sort my table to get the final answer
>> tt.Group = categorical(tt.Group,newOrder,'Ordinal',true)
tt =
18×3 timetable
Date NumOfHours TotalRainfall Group
____________________ __________ _____________ _____
06-Apr-2019 09:00:00 1 1.02 1
06-Apr-2019 10:00:00 2 0.51 1
06-Apr-2019 11:00:00 3 0.25 1
17-Apr-2019 03:00:00 1 0.51 2
17-Apr-2019 04:00:00 2 0 2
17-Apr-2019 05:00:00 3 0 2
17-Apr-2019 06:00:00 4 0.25 2
17-Apr-2019 07:00:00 5 0.25 2
29-Apr-2019 08:00:00 1 0.76 3
29-Apr-2019 09:00:00 2 0.25 3
02-May-2019 22:00:00 1 3.3 4
02-May-2019 23:00:00 2 0 4
03-May-2019 00:00:00 3 0 4
03-May-2019 01:00:00 4 0.25 4
03-May-2019 16:00:00 1 1.02 5
03-May-2019 17:00:00 2 0.51 5
05-May-2019 06:00:00 1 0.76 6
13-May-2019 16:00:00 1 1.52 7
>> tt = sortrows(tt,'Group')
tt =
18×3 timetable
Date NumOfHours TotalRainfall Group
____________________ __________ _____________ _____
05-May-2019 06:00:00 1 0.76 6
13-May-2019 16:00:00 1 1.52 7
29-Apr-2019 08:00:00 1 0.76 3
29-Apr-2019 09:00:00 2 0.25 3
03-May-2019 16:00:00 1 1.02 5
03-May-2019 17:00:00 2 0.51 5
06-Apr-2019 09:00:00 1 1.02 1
06-Apr-2019 10:00:00 2 0.51 1
06-Apr-2019 11:00:00 3 0.25 1
02-May-2019 22:00:00 1 3.3 4
02-May-2019 23:00:00 2 0 4
03-May-2019 00:00:00 3 0 4
03-May-2019 01:00:00 4 0.25 4
17-Apr-2019 03:00:00 1 0.51 2
17-Apr-2019 04:00:00 2 0 2
17-Apr-2019 05:00:00 3 0 2
17-Apr-2019 06:00:00 4 0.25 2
17-Apr-2019 07:00:00 5 0.25 2
There might be a simpler way to do this. I'll try and look at this again later and update the answer if I can come up with a simpler way to do this.

More Answers (0)

Categories

Find more on Timetables in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!