MATLAB: Fill a timetable outside the gaps

MATLABtabletimetime series

Consider the following timetable, which is just a toy to explain the problem
dates = [datetime(2020,1,1) datetime(2020,1,1) datetime(2020,1,2) datetime(2020,1,2) datetime(2020,1,3) datetime(2020,1,3) datetime(2020,1,4)];
t = timetable(dates',["a" "c" "a" "b" "a" "a" "c"]',[2 1 3 2 1 2 1]')
       Time        Var1    Var2
    ___________    ____    ____
    01-Jan-2020    "a"      2  
    01-Jan-2020    "c"      1  
    02-Jan-2020    "a"      3  
    02-Jan-2020    "b"      2  
    03-Jan-2020    "a"      1  
    03-Jan-2020    "a"      2  
    04-Jan-2020    "c"      1
For each date and for each unique string in Var1 I would like to obtain the sum of the values in Var2. That is:
(1) if for a specific date a string does not appear, then 0 must be returned
(2) if for a specific date a string appears only once, then the corresponding value in Var2 must be returned
(3) if for a specific date a string appears multiple times, then the sum of the corresponding values in Var2 must be returned
The function retime satisfy (2) and (3) completely, but (1) only partially in the sense that it returns 0 only if the string appears in a previous date AND in a following date, that is retime only fills the gaps.
In the following the row with 04-Jan-2020 and 0 is missing
retime(t(t.Var1=="a","Var2"),'daily','sum')
       Time        Var2
    ___________    ____
    01-Jan-2020     2  
    02-Jan-2020     3  
    03-Jan-2020     3
In the following the rows with dates 1, 3, 4 january (and 0 in Var2) are missing
retime(t(t.Var1=="b","Var2"),'daily','sum')
       Time        Var2
    ___________    ____
    02-Jan-2020     2
The following is correct
retime(t(t.Var1=="c","Var2"),'daily','sum')
       Time        Var2
    ___________    ____
    01-Jan-2020     1  
    02-Jan-2020     0  
    03-Jan-2020     0  
    04-Jan-2020     1
Is there an easy way to resolve the problem and without using for loops? The data I'm working with has thousands of rows and tens of columns, I tried with loops but it's time consuming.
In the toy example an easy workaround is to manually add the missing strings at the start and at the end of the timetable, in such a way we can extend the gaps to cover all the datetimes and then retime can fill all the missing values.
However, this is easy only for timetables with a simple structure, moreover it might have some unwanted side effects
t = [timetable(datetime(2020,1,1),"b",0) ; t ; timetable([datetime(2020,1,4);datetime(2020,1,4)],["a";"b"],[0;0])]
       Time        Var1    Var2
    ___________    ____    ____
    01-Jan-2020    "b"      0  
    01-Jan-2020    "a"      2  
    01-Jan-2020    "c"      1  
    02-Jan-2020    "a"      3  
    02-Jan-2020    "b"      2  
    03-Jan-2020    "a"      1  
    03-Jan-2020    "a"      2  
    04-Jan-2020    "c"      1  
    04-Jan-2020    "a"      0  
    04-Jan-2020    "b"      0

Best Answer

You could use groupsummary to do this.
In your case your grouping variables would be Time and Var1 and the aggregation method you want to use would be sum. Since you want all permutations of the grouping variables to show up in your output, you can specify the "IncludeEmptyGroups" as true and that should give you the desired output.
groupsummary(t,["Time","Var1"],"sum","Var2","IncludeEmptyGroups",true)
ans =
  12×4 table
       Time        Var1    GroupCount    sum_Var2
    ___________    ____    __________    ________
    01-Jan-2020    "a"         1            2    
    01-Jan-2020    "b"         0            0    
    01-Jan-2020    "c"         1            1    
    02-Jan-2020    "a"         1            3    
    02-Jan-2020    "b"         1            2    
    02-Jan-2020    "c"         0            0    
    03-Jan-2020    "a"         2            3    
    03-Jan-2020    "b"         0            0    
    03-Jan-2020    "c"         0            0    
    04-Jan-2020    "a"         0            0    
    04-Jan-2020    "b"         0            0    
    04-Jan-2020    "c"         1            1
Related Question