Summing all the times of a column

Question

It is possible to add a last row in the table where I can show the sum of values of the last column. I would like to get the total time value...

data = {
   {"Caixa Pokémon", DateObject[{2019, 8, 27}], 22.41, 
    TimeObject[{2, 35}]},
   {"Cortador Lindinha", DateObject[{2019, 8, 28}], 3.91, 
    TimeObject[{0, 41}]},
   {"Suporte Principal do Carretel Maior", DateObject[{2019, 9, 01}], 
    12.28, TimeObject[{2, 00}]},
   {"Cortador Woody", DateObject[{2019, 9, 01}], 5.62, 
    TimeObject[{1, 01}]},
   {"Suporte-Trava Carretel Maior", DateObject[{2019, 9, 06}], 2.08, 
    TimeObject[{0, 22}]},
   {"Suporte-Tampa Carretel Maior", DateObject[{2019, 9, 06}], 1.67, 
    TimeObject[{0, 16}]},
   {"Suporte-Trava Carretel Maior", DateObject[{2019, 9, 06}], 2.05, 
    TimeObject[{0, 21}]},
   {"Jogo de Hóquei do João Pedro", DateObject[{2019, 9, 06}], 6.48, 
    TimeObject[{0, 55}]}};
Text@Grid[Prepend[data, {"Projeto", "Data", "Metros", "Tempo"}], 
  Background -> {None, {Lighter[Yellow, .9], {White, 
      Lighter[Blend[{Blue, Green}], .8]}}}, 
  Dividers -> {{Darker[Gray, .6], {Lighter[Gray, .5]}, 
     Darker[Gray, .6]}, {Darker[Gray, .6], Darker[Gray, .6], {False}, 
     Darker[Gray, .6]}}, Alignment -> {{Center, Center, {Center}}}, 
  ItemSize -> {{10, Full, 5, Full}}, Frame -> Darker[Gray, .6], 
  ItemStyle -> 14, Spacings -> {15, .8}]

are you sure that TimeObject is what you need there? Can you comment on what your last column represents? Is that an absolute time or a time interval? If it's an absolute time (e.g. 11:30am), what do you mean with sum of two absolute times? — Fraccalo, Sep 07 '19 at 17:02

kglr · Answer 1 · 2019-09-08T06:44:49.837

Convert TimeObjects in column 3 to durations by taking the difference from TimeObject[{0,0}] and take to the sum:

to = TimeObject[{0, 0}];

durations = data[[All, -1]] - to;

{2.58333h, 41.min, 2.h, 1.01667h, 22.min, 16.min, 21.min, 55.min}

totalduration = Total @ durations

491.min

If needed convert to display in mixed units;

totalinmixedunits = UnitConvert[totalduration, MixedUnit[{"Hours", "Minutes"}]

To combine all steps in a single step, use

totalinmixedunits = UnitConvert[Total[data[[All, -1]] - TimeObject[{0, 0}]], 
   MixedUnit[{"Hours", "Minutes"}]]

Use totalinmixedunits as the last item in the new row:

lastRow = {"Total Duration", "", "", totalinmixedunits};

Text @ Grid[Append[Prepend[data, {"Projeto", "Data", "Metros", "Tempo"}], lastRow], 
    Background -> {None, 
       {Lighter[Yellow, .9], {White, Lighter[Blend[{Blue, Green}], .8]}, 
        Lighter[Red, .9]}}, 
    Dividers -> {{Darker[Gray, .6], {Lighter[Gray, .5]}, Darker[Gray, .6]}, 
       {Darker[Gray, .6], Darker[Gray, .6], {False}, Darker[Gray, .6]}}, 
    Alignment -> {{Center, Center, {Center}}}, 
    ItemSize -> {{Full, Full, 10, Full}}, Frame -> Darker[Gray, .6], 
    ItemStyle -> 14, Spacings -> {5, .8}]

Alternatively, you can Dot the TimeObjects with {60, 1} and take the sum:

Total[data[[All, -1, 1]].{60, 1}]

491

UnitConvert[Quantity[%, "Minutes"], MixedUnit[{"Hours", "Minutes"}]]

8 h 11 min

score 2 · Accepted Answer · edited Sep 08 '19 at 00:42

If I understood correctly your aim, I think you shouldn't use a TimeObject as last column of your table, you should use a Quantity, because it looks like you are considering time intervals and not absolute times.

If that's true, TimeObject is probably not the correct choice, as it "represents a time object of standard normalized form" (quote from documentation): this is an absolute time and not a "DeltaT", and summing two absolute times doesn't make much sense (while you can sum an absolute time with a time quantity, as documented).

See example below for using time quantities instead of TimeObjects:

toQuant[inp_] := 
 Quantity[MixedMagnitude[inp], MixedUnit[{"Hours", "Minutes"}]]

data = {
   {"Caixa Pokémon", DateObject[{2019, 8, 27}], 22.41, 
    toQuant[{2, 35}]},
   {"Cortador Lindinha", DateObject[{2019, 8, 28}], 3.91, 
    toQuant[{0, 41}]},
   {"Suporte Principal do Carretel Maior", DateObject[{2019, 9, 01}], 
    12.28, toQuant[{2, 00}]},
   {"Cortador Woody", DateObject[{2019, 9, 01}], 5.62, 
    toQuant[{1, 01}]},
   {"Suporte-Trava Carretel Maior", DateObject[{2019, 9, 06}], 2.08, 
    toQuant[{0, 22}]},
   {"Suporte-Tampa Carretel Maior", DateObject[{2019, 9, 06}], 1.67, 
    toQuant[{0, 16}]},
   {"Suporte-Trava Carretel Maior", DateObject[{2019, 9, 06}], 2.05, 
    toQuant[{0, 21}]},
   {"Jogo de Hóquei do João Pedro", DateObject[{2019, 9, 06}], 6.48, 
    toQuant[{0, 55}]}};

totalTime = 
  UnitConvert[Total[data[[;; , -1]]], 
   MixedUnit[{"Hours", "Minutes"}]]

8min 11s

EDIT AUTHOR:

totalMeters = data[[#, 3]] & /@ Range[Length[data]] // Total;

And the table (code taken from Alx reply):

Text@Grid[
  Append[Prepend[
    data, {"Projeto", "Data", "Metros", "Tempo"}], {"TotalDuration", 
    "", totalMeters, totalTime}], 
  Background -> {None, {Lighter[Yellow, .9], {White, 
      Lighter[Blend[{Blue, Green}], .8]}, Lighter[Red, .9]}}, 
  Dividers -> {{Darker[Gray, .6], {Lighter[Gray, .5]}, 
     Darker[Gray, .6]}, {Darker[Gray, .6], Darker[Gray, .6], {False}, 
     Darker[Gray, .6]}}, Alignment -> {{Center, Center, {Center}}}, 
  ItemSize -> {{Full, Full, 10, Full}}, Frame -> Darker[Gray, .6], 
  ItemStyle -> 14, Spacings -> {5, .8}]

Of course, if you meant different units (and not hours/minutes), the code can be easily adapted ;)

I liked your method and just tried to add one more line of code — LCarvalho, Sep 08 '19 at 00:44
Thanks! Obviously you can apply the "meters" unit to the second last column if you need it — Fraccalo, Sep 08 '19 at 07:01

score 1 · Answer 3 · answered Sep 07 '19 at 16:42

It is strange that Mathematica can take difference of two TimeObjects but not their sum. So we need to strip TimeObject wrapper and sum up all the internal lists. Then we need to convert the resulting list to {h,m,s} form and wrap it in TimeObject again to get better representation. So the function proposed is this:

totalTime={PadRight[#1, 3], PadLeft[#2, 3]} & @@ (QuotientRemainder[#, 60] & /@ 
 Total[First /@ data[[All, -1]]]) // Total // TimeObject

And the table:

Text@Grid[Append[Prepend[data, {"Projeto", "Data", "Metros", "Tempo"}], {"Total    Duration","", "", totalTime}], 
Background -> {None, {Lighter[Yellow, .9], {White, 
Lighter[Blend[{Blue, Green}], .8]}, Lighter[Red, .9]}}, 
Dividers -> {{Darker[Gray, .6], {Lighter[Gray, .5]}, 
Darker[Gray, .6]}, {Darker[Gray, .6], Darker[Gray, .6], {False}, 
Darker[Gray, .6]}}, Alignment -> {{Center, Center, {Center}}}, 
ItemSize -> {{Full, Full, 10, Full}}, Frame -> Darker[Gray, .6], 
ItemStyle -> 14, Spacings -> {5, .8}]

If one do not need hour (00) it can be easily removed:

totalTime={PadRight[#1, 3], PadLeft[#2, 3]} & @@ (QuotientRemainder[#, 60] & /@ 
  Total[First /@ data[[All, -1]]]) // Total // #[[2 ;;]] & // TimeObject

I don't think it's strange: just looking at the documentation of TimeObjects, it looks like TimeObjects are thought as absolute times, not time intervals, e.g., Represent 11:30am: TimeObject[{11, 30}]. Summing absolute times doesn't make much sense: for example, what would be the meaning of 11:30am + 12:30am? On the other hand, difference makes perfect sense, at least from an intuitive point of view :) — Fraccalo, Sep 07 '19 at 17:00

Summing all the times of a column

3 Answers3