I have timestamp_start and end data with other variables at the interval of 30 min yearly data. How can I sum/ avg daily/monthly/yearly avoiding -9999/Nan ?

1 view (last 30 days)
Suman Dhakal
Suman Dhakal on 24 Aug 2022
Edited: Seth Furman on 12 Sep 2022
201901010000 201901010030 -9999 -9999 -9999 -9999 -9999
201901010030 201901010100 -9999 -9999 -9999 -9999 -9999
201901010100 201901010130 -9999 -9999 -9999 -9999 -9999
201901010130 201901010200 -9999 -9999 -9999 -9999 -9999
201901010200 201901010230 -9999 -9999 -9999 -9999 -9999
201901010230 201901010300 -9999 -9999 -9999 -9999 -9999
201901010300 201901010330 -9999 -9999 -9999 -9999 -9999
201901010330 201901010400 -9999 -9999 -9999 -9999 -9999
201901010400 201901010430 -9999 -9999 -9999 -9999 -9999
201901010430 201901010500 -9999 -9999 -9999 -9999 -9999
201901010500 201901010530 -9999 -9999 -9999 -9999 -9999
201901010530 201901010600 -9999 -9999 -9999 -9999 -9999
201901010600 201901010630 -9999 -9999 -9999 -9999 -9999
201901010630 201901010700 -9999 -9999 -9999 -9999 -9999
201901010700 201901010730 -9999 -9999 -9999 -9999 -9999
201901010730 201901010800 -9999 -9999 -9999 -9999 -9999
201901010800 201901010830 -9999 -9999 -9999 -9999 -9999
201901010830 201901010900 -9999 -9999 -9999 -9999 -9999
201901010900 201901010930 -9999 -9999 -9999 -9999 -9999
201901010930 201901011000 -9999 -9999 -9999 -9999 -9999
201901011000 201901011030 215 9.1 2427.7 -9999 -10.6
201901011030 201901011100 130 9.6 2314.4 1.85 -11.3
201901011100 201901011130 165 9.0 2231.5 0.6 -32.6
201901011130 201901011200 421 7.7 2261.6 1.2 -12.6
201901011200 201901011230 421 7.5 2279.4 1.7 2.3
201901011230 201901011300 201 7.1 2258.2 4.1 2.1
201901011300 201901011330 105 6.9 2255.8 4.4 27.0
201901011330 201901011400 519 6.9 2259.5 3.9 10.1

Sign in to answer this question.

Answers (1)

Seth Furman
Seth Furman on 12 Sep 2022
Edited: Seth Furman on 12 Sep 2022
Ran in:
Take a look at the following:
data = [
201901010000 201901010030 -9999 -9999 -9999 -9999 -9999
201901010030 201901010100 -9999 -9999 -9999 -9999 -9999
201901010100 201901010130 -9999 -9999 -9999 -9999 -9999
201901010130 201901010200 -9999 -9999 -9999 -9999 -9999
201901010200 201901010230 -9999 -9999 -9999 -9999 -9999
201901010230 201901010300 -9999 -9999 -9999 -9999 -9999
201901010300 201901010330 -9999 -9999 -9999 -9999 -9999
201901010330 201901010400 -9999 -9999 -9999 -9999 -9999
201901010400 201901010430 -9999 -9999 -9999 -9999 -9999
201901010430 201901010500 -9999 -9999 -9999 -9999 -9999
201901010500 201901010530 -9999 -9999 -9999 -9999 -9999
201901010530 201901010600 -9999 -9999 -9999 -9999 -9999
201901010600 201901010630 -9999 -9999 -9999 -9999 -9999
201901010630 201901010700 -9999 -9999 -9999 -9999 -9999
201901010700 201901010730 -9999 -9999 -9999 -9999 -9999
201901010730 201901010800 -9999 -9999 -9999 -9999 -9999
201901010800 201901010830 -9999 -9999 -9999 -9999 -9999
201901010830 201901010900 -9999 -9999 -9999 -9999 -9999
201901010900 201901010930 -9999 -9999 -9999 -9999 -9999
201901010930 201901011000 -9999 -9999 -9999 -9999 -9999
201901011000 201901011030 215 9.1 2427.7 -9999 -10.6
201901011030 201901011100 130 9.6 2314.4 1.85 -11.3
201901011100 201901011130 165 9.0 2231.5 0.6 -32.6
201901011130 201901011200 421 7.7 2261.6 1.2 -12.6
201901011200 201901011230 421 7.5 2279.4 1.7 2.3
201901011230 201901011300 201 7.1 2258.2 4.1 2.1
201901011300 201901011330 105 6.9 2255.8 4.4 27.0
201901011330 201901011400 519 6.9 2259.5 3.9 10.1
];
t = array2table(data);
t.Properties.VariableNames(1:2) = ["Start","End"]
t = 28×7 table
Start End data3 data4 data5 data6 data7 _________ _________ _____ _____ _____ _____ _____ 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999 2.019e+11 2.019e+11 -9999 -9999 -9999 -9999 -9999
t.Start = datetime(string(t.Start),InputFormat="yyyyMMddHHmm")
t = 28×7 table
Start End data3 data4 data5 data6 data7 ____________________ _________ _____ _____ _____ _____ _____ 01-Jan-2019 00:00:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 00:30:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 01:00:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 01:30:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 02:00:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 02:30:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 03:00:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 03:30:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 04:00:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 04:30:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 05:00:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 05:30:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 06:00:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 06:30:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 07:00:00 2.019e+11 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 07:30:00 2.019e+11 -9999 -9999 -9999 -9999 -9999
t.End = datetime(string(t.End),InputFormat="yyyyMMddHHmm")
t = 28×7 table
Start End data3 data4 data5 data6 data7 ____________________ ____________________ _____ _____ _____ _____ _____ 01-Jan-2019 00:00:00 01-Jan-2019 00:30:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 00:30:00 01-Jan-2019 01:00:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 01:00:00 01-Jan-2019 01:30:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 01:30:00 01-Jan-2019 02:00:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 02:00:00 01-Jan-2019 02:30:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 02:30:00 01-Jan-2019 03:00:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 03:00:00 01-Jan-2019 03:30:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 03:30:00 01-Jan-2019 04:00:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 04:00:00 01-Jan-2019 04:30:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 04:30:00 01-Jan-2019 05:00:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 05:00:00 01-Jan-2019 05:30:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 05:30:00 01-Jan-2019 06:00:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 06:00:00 01-Jan-2019 06:30:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 06:30:00 01-Jan-2019 07:00:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 07:00:00 01-Jan-2019 07:30:00 -9999 -9999 -9999 -9999 -9999 01-Jan-2019 07:30:00 01-Jan-2019 08:00:00 -9999 -9999 -9999 -9999 -9999
t = standardizeMissing(t,-9999,"DataVariables",t.Properties.VariableNames(3:end))
t = 28×7 table
Start End data3 data4 data5 data6 data7 ____________________ ____________________ _____ _____ _____ _____ _____ 01-Jan-2019 00:00:00 01-Jan-2019 00:30:00 NaN NaN NaN NaN NaN 01-Jan-2019 00:30:00 01-Jan-2019 01:00:00 NaN NaN NaN NaN NaN 01-Jan-2019 01:00:00 01-Jan-2019 01:30:00 NaN NaN NaN NaN NaN 01-Jan-2019 01:30:00 01-Jan-2019 02:00:00 NaN NaN NaN NaN NaN 01-Jan-2019 02:00:00 01-Jan-2019 02:30:00 NaN NaN NaN NaN NaN 01-Jan-2019 02:30:00 01-Jan-2019 03:00:00 NaN NaN NaN NaN NaN 01-Jan-2019 03:00:00 01-Jan-2019 03:30:00 NaN NaN NaN NaN NaN 01-Jan-2019 03:30:00 01-Jan-2019 04:00:00 NaN NaN NaN NaN NaN 01-Jan-2019 04:00:00 01-Jan-2019 04:30:00 NaN NaN NaN NaN NaN 01-Jan-2019 04:30:00 01-Jan-2019 05:00:00 NaN NaN NaN NaN NaN 01-Jan-2019 05:00:00 01-Jan-2019 05:30:00 NaN NaN NaN NaN NaN 01-Jan-2019 05:30:00 01-Jan-2019 06:00:00 NaN NaN NaN NaN NaN 01-Jan-2019 06:00:00 01-Jan-2019 06:30:00 NaN NaN NaN NaN NaN 01-Jan-2019 06:30:00 01-Jan-2019 07:00:00 NaN NaN NaN NaN NaN 01-Jan-2019 07:00:00 01-Jan-2019 07:30:00 NaN NaN NaN NaN NaN 01-Jan-2019 07:30:00 01-Jan-2019 08:00:00 NaN NaN NaN NaN NaN
tNoMissing = rmmissing(t,"DataVariables",t.Properties.VariableNames(3:end));
tNoMissing = table2timetable(tNoMissing)
tNoMissing = 7×6 timetable
Start End data3 data4 data5 data6 data7 ____________________ ____________________ _____ _____ ______ _____ _____ 01-Jan-2019 10:30:00 01-Jan-2019 11:00:00 130 9.6 2314.4 1.85 -11.3 01-Jan-2019 11:00:00 01-Jan-2019 11:30:00 165 9 2231.5 0.6 -32.6 01-Jan-2019 11:30:00 01-Jan-2019 12:00:00 421 7.7 2261.6 1.2 -12.6 01-Jan-2019 12:00:00 01-Jan-2019 12:30:00 421 7.5 2279.4 1.7 2.3 01-Jan-2019 12:30:00 01-Jan-2019 13:00:00 201 7.1 2258.2 4.1 2.1 01-Jan-2019 13:00:00 01-Jan-2019 13:30:00 105 6.9 2255.8 4.4 27 01-Jan-2019 13:30:00 01-Jan-2019 14:00:00 519 6.9 2259.5 3.9 10.1
retime(tNoMissing(:,3:end),"daily","mean")
ans = 1×4 timetable
Start data4 data5 data6 data7 ___________ ______ ______ ______ _______ 01-Jan-2019 7.8143 2265.8 2.5357 -2.1429
retime(tNoMissing(:,3:end),"monthly","sum")
ans = 1×4 timetable
Start data4 data5 data6 data7 ___________ _____ _____ _____ _____ 01-Jan-2019 54.7 15860 17.75 -15
retime(tNoMissing(:,3:end),"yearly","mean")
ans = 1×4 timetable
Start data4 data5 data6 data7 ___________ ______ ______ ______ _______ 01-Jan-2019 7.8143 2265.8 2.5357 -2.1429

Categories

Find more on Calendar in Help Center and File Exchange

Tags

Products


Release

R2022a

Community Treasure Hunt

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

Start Hunting!