Inserting a column of repeated values

5 views (last 30 days)
I have the following table
T2 =
3557×3 table
Date Time (Hours) Total Rainfall(mm)
___________ ____________ __________________
NaT NaN 1
03-Mar-2019 10:00:00 0.51
03-Mar-2019 11:00:00 0
03-Mar-2019 12:00:00 0
03-Mar-2019 13:00:00 0
03-Mar-2019 14:00:00 0
03-Mar-2019 15:00:00 1.27
03-Mar-2019 16:00:00 0
03-Mar-2019 17:00:00 0
03-Mar-2019 18:00:00 1.02
NaT NaN 2
11-Mar-2019 17:00:00 3.3
NaT NaN 3
12-Mar-2019 18:00:00 1.78
12-Mar-2019 19:00:00 1.78
12-Mar-2019 20:00:00 0.51
12-Mar-2019 21:00:00 0
12-Mar-2019 22:00:00 11.17
NaT NaN 4
I would like to insert a column stating the number of hours between the NaN. It should look like:
T2 =
3557×3 table
Date Time (Hours) No.of Hours Total Rainfall(mm)
___________ ____________ ___________ __________________
NaT NaN 1
03-Mar-2019 10:00:00 1 0.51
03-Mar-2019 11:00:00 2 0
03-Mar-2019 12:00:00 3 0
03-Mar-2019 13:00:00 4 0
03-Mar-2019 14:00:00 5 0
03-Mar-2019 15:00:00 6 1.27
03-Mar-2019 16:00:00 7 0
03-Mar-2019 17:00:00 8 0
03-Mar-2019 18:00:00 9 1.02
NaT NaN 2
11-Mar-2019 17:00:00 1 3.3
NaT NaN 3
12-Mar-2019 18:00:00 1 1.78
12-Mar-2019 19:00:00 2 1.78
12-Mar-2019 20:00:00 3 0.51
12-Mar-2019 21:00:00 4 0
12-Mar-2019 22:00:00 5 11.17

Accepted Answer

Akira Agata
Akira Agata on 1 Apr 2022
How about the following method?
% Sample data
load('outdoors.mat');
T = timetable2table(outdoors);
T.Humidity([1, 6, 15]) = NaN; % Put some NaNs
% Create group ID
idx = isnan(T.Humidity);
group = cumsum(idx);
% Apply cumsum for each group
NumOfHours = splitapply(@(x){cumsum(x)}, ~idx, group);
NumOfHours = cell2mat(NumOfHours);
NumOfHours(idx) = NaN;
% Add and move the column
T.NumOfHous = NumOfHours;
T = movevars(T, 'NumOfHous', 'After', 'Humidity');
% Show the result
disp(T)
Time Humidity NumOfHous TemperatureF PressureHg ___________________ ________ _________ ____________ __________ 2015-11-15 00:00:24 NaN NaN 51.3 29.61 2015-11-15 01:30:24 48.9 1 51.5 29.61 2015-11-15 03:00:24 48.9 2 51.5 29.61 2015-11-15 04:30:24 48.8 3 51.5 29.61 2015-11-15 06:00:24 48.7 4 51.5 29.6 2015-11-15 07:30:24 NaN NaN 51.5 29.6 2015-11-15 09:00:24 49 1 51.5 29.6 2015-11-15 10:30:24 49.1 2 51.3 29.6 2015-11-15 12:00:24 49.1 3 51.3 29.61 2015-11-15 13:30:24 49.1 4 51.5 29.61 2015-11-15 15:00:24 48.9 5 51.5 29.61 2015-11-15 16:30:24 49 6 51.5 29.61 2015-11-15 18:00:24 49 7 51.3 29.61 2015-11-15 19:30:24 48.9 8 51.3 29.6 2015-11-15 21:00:24 NaN NaN 51.2 29.61 2015-11-15 22:30:24 48.9 1 51.1 29.61 2015-11-16 00:00:24 48.7 2 51.2 29.61 2015-11-16 01:30:24 48.6 3 51.3 29.61 2015-11-16 03:00:24 48.4 4 51.5 29.61 2015-11-16 04:30:24 48.3 5 51.6 29.61 2015-11-16 06:00:24 47.7 6 51.6 29.61 2015-11-16 07:30:24 47.9 7 51.6 29.61 2015-11-16 09:00:24 48.2 8 51.6 29.61 2015-11-16 10:30:24 48 9 51.6 29.61 2015-11-16 12:00:24 47.9 10 51.6 29.61 2015-11-16 13:30:24 47.8 11 51.6 29.61 2015-11-16 15:00:24 47.9 12 51.5 29.61 2015-11-16 16:30:24 47.6 13 51.6 29.61 2015-11-16 18:00:00 47.6 14 51.6 29.61 2015-11-16 18:00:24 47.7 15 51.6 29.61 2015-11-16 19:30:24 47.5 16 51.6 29.61 2015-11-16 21:00:24 47.6 17 51.8 29.62 2015-11-16 22:30:24 47.3 18 51.8 29.62 2015-11-17 00:00:24 47.8 19 51.8 29.61 2015-11-17 01:30:24 48 20 51.6 29.62 2015-11-17 03:00:24 47.9 21 51.6 29.62 2015-11-17 04:30:24 48 22 51.6 29.62 2015-11-17 06:00:24 48.1 23 51.6 29.61 2015-11-17 07:30:24 48.1 24 51.5 29.62 2015-11-17 09:00:24 48.3 25 51.2 29.62 2015-11-17 10:30:24 48.5 26 51.1 29.61 2015-11-17 12:00:24 48.4 27 51.1 29.61 2015-11-17 13:30:24 48.5 28 51.1 29.61 2015-11-17 15:00:24 48.5 29 51.2 29.61 2015-11-17 16:30:24 48.6 30 51.2 29.61 2015-11-17 18:00:24 48.7 31 50.9 29.61 2015-11-17 19:30:24 49 32 50.8 29.61 2015-11-17 21:00:24 49.3 33 50.8 29.61 2015-11-17 22:30:24 49.5 34 50.7 29.61 2015-11-18 00:00:24 49.5 35 50.8 29.61 2015-11-18 01:30:24 49.5 36 50.8 29.61
  1 Comment
Queena Edwards
Queena Edwards on 1 Apr 2022
I made some changes based on my code but this works. Thank you so much!

Sign in to comment.

More Answers (0)

Categories

Find more on Tables 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!