Borrar filtros
Borrar filtros

Arranging events based on hours of rainfall

2 visualizaciones (últimos 30 días)
Queena Edwards
Queena Edwards el 5 de Abr. de 2022
Comentada: Queena Edwards el 6 de Abr. de 2022
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

Respuesta aceptada

Siddharth Bhutiya
Siddharth Bhutiya el 5 de Abr. de 2022
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.

Más respuestas (0)

Categorías

Más información sobre Tables en Help Center y File Exchange.

Community Treasure Hunt

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

Start Hunting!

Translated by