Analyse table value for calculations

Hi all,

i have a table with 2 cols, first is date with time and second one is with data (output). Because data in col2 is cumulating each day and they reset to 0 every midnight i looking for solution how to count yearly and monthly consume. It mean i need sum all data before they turn to zero and than sum specific month (January)

 year   month   day     hour    minute  second                  output
 117  1  2  23  48  51.2680000000000  801.500000000000
 117  1  2  23  51  51.3200000000000  803.500000000000
 117  1  2  23  54  51.3420000000000  805.500000000000
 117  1  2  23  57  51.3620000000000  1.30000000000000
 117  1  3  0  0  51.4110000000000  3.70000000000000
 117  1  3  0  3  52.0680000000000  5.60000000000000

i did it in excel with if function but i cant find solution for that in matlab :(..

[Eric Tao]

I have a doubt to your table. If the col 'output' is reset to 0 every midnight, how would the output in 4th row (1.3) be smaller than the one in 3rd row (805.5)? It is supposed to be larger than 805.5 since output is cumulating and the time did not meet 00:00:00(i.e., hour == 0 & minute == 0).

Answer 1

[Kai Domhardt]

First you will want to select the relevant subset of your data. For this example I will simply use the data of february.

feb_data = my_table.output( my_table.dates.Month == 2 );

Now you will only need to sum up the daily consumption in your subset, which is given by the maximum/last value for each day.

Since your data is shaped like a sawtooth wave, the value only decreases right after the local maximum.

feb_sum = sum( feb_data( diff(feb_data)<0 ) );

Finally, the you need to end the last datapoint of your subset to the sum, since the line above would not include it in the sum.

feb_sum = feb_sum + feb_data(end);

-----

You can also be more concise and combine the two code lines above into one:

feb_sum = sum( [feb_data( diff(feb_data)<0 ); feb_data(end)] );

Answer 2

[Peter Perkins]

Like Eric, I will observe that your example seems wrong, and it's not at all clear what you are starting with and what you want to end up with.

Depending on what you have and what you want, you should be able to use either varfun or rowfun on the table.

If you have access to R2016b or later, it's likely that you want to convert your six date/time variables to a datetime vector, and create a timetable with just one variable, and then use retime. Hard to say without knowing the rest of the context.