Merging Table with Duplicate Dates

Hi all! I'm writing this post in the hope that someone can help me out with a little problem that requires a good solution. I have a table with the following structure:

column1 = datenum | column2 = country | column3 = value1 | column4 = value2

Let's say I load the following dataset:

1	736561	'USA'		2752	251
2	736561	'USA'		184	53
3	736561	'USA'		40	0
4	736572	'England'	1	0
5	736573	'USA'		1	0
6	736575	'USA'		1	0
7	736576	'England'	1	0
8	736577	'USA'		2	0
9	736580	'USA'		1	1
10	736581	'USA'		1	0
11	736582	'USA'		1	0
12	736599	'USA'		1	0
13	736619	'USA'		5	0
14	736619	France'		1	1
15	736683	'USA'		1	0

Now, what I need to to is to merge together the rows with the same date. As you can see, this is the case for the rows in the intervals 1:3 and 13:14. I have to do this following a few simple criteria:

• if the country in the duplicate rows is always the same, the final row should still show that country, otherwise it must show "Multiple";
• value1 and value2 of the final row must be the sum of value1 and value2 of the duplicate rows. Following those criteria, the table in the above example should become:

1	736561	'USA'		2976	304
2	736572	'England'	1	0
3	736573	'USA'		1	0
4	736575	'USA'		1	0
5	736576	'England'	1	0
6	736577	'USA'		2	0
7	736580	'USA'		1	1
8	736581	'USA'		1	0
9	736582	'USA'		1	0
10	736599	'USA'		1	0
11	736619	'Multiple'	6	1
12	736683	'USA'		1	0

Thanks for your help!

Answer 1

[Kelly Kearney]

The accumarray function is designed for this sort of problem:

% Your data

data = {...
736561  'USA'    2752  251
736561  'USA'    184  53
736561  'USA'    40  0
736572  'England'  1  0
736573  'USA'    1  0
736575  'USA'    1  0
736576  'England'  1  0
736577  'USA'    2  0
736580  'USA'    1  1
736581  'USA'    1  0
736582  'USA'    1  0
736599  'USA'    1  0
736619  'USA'    5  0
736619  'France'        1  1
736683  'USA'    1  0};

data = cell2table(data, 'VariableNames', {'date', 'country', 'value1', 'value2'});

% Summing the values is the default of accumarray

[unqdate, ~, idx] = unique(data.date);

val1 = accumarray(idx, data.value1);
val2 = accumarray(idx, data.value2);

% Accumarray is very picky about its inputs, so analyzing a cell array
% requires a few extra steps...

[unqcountry, ~, cidx] = unique(data.country);
unqcountry = [unqcountry; 'Multiple'];

ctmp = accumarray(idx, cidx, [max(idx) 1], @(x) {unique(x)});
hasmult = cellfun(@(x) length(x) > 1, ctmp);
ctmp{hasmult} = max(cidx)+1;
ctmp = cat(1, ctmp{:});

newdata = table(unqdate, unqcountry(ctmp), val1, val2)

And the results:

newdata = 

       unqdate         Var2       val1    val2
      __________    __________    ____    ____

      7.3656e+05    'USA'         2976    304 
      7.3657e+05    'England'        1      0 
      7.3657e+05    'USA'            1      0 
      7.3658e+05    'USA'            1      0 
      7.3658e+05    'England'        1      0 
      7.3658e+05    'USA'            2      0 
      7.3658e+05    'USA'            1      1 
      7.3658e+05    'USA'            1      0 
      7.3658e+05    'USA'            1      0 
       7.366e+05    'USA'            1      0 
      7.3662e+05    'Multiple'       6      1 
      7.3668e+05    'USA'            1      0

Answer 2

[Peter Perkins]

varfun with grouping variables does this in one line:

>> c = { 1 736561 'USA'  2752 251
         ...
        15 736683 'USA'  1 0};
>> t = cell2table(c,'VariableNames',{'ID' 'Date' 'Country' 'X' 'Y'});
>> t.Date = datetime(t.Date,'ConvertFrom','datenum','Format','dd-MMM-yyyy')
t =
  15×5 table
    ID       Date         Country      X       Y 
    __    ___________    _________    ____    ___
     1    19-Aug-2016    'USA'        2752    251
     2    19-Aug-2016    'USA'         184     53
     3    19-Aug-2016    'USA'          40      0
     4    30-Aug-2016    'England'       1      0
     5    31-Aug-2016    'USA'           1      0
     6    02-Sep-2016    'USA'           1      0
     7    03-Sep-2016    'England'       1      0
     8    04-Sep-2016    'USA'           2      0
     9    07-Sep-2016    'USA'           1      1
    10    08-Sep-2016    'USA'           1      0
    11    09-Sep-2016    'USA'           1      0
    12    26-Sep-2016    'USA'           1      0
    13    16-Oct-2016    'USA'           5      0
    14    16-Oct-2016    'France'        1      1
    15    19-Dec-2016    'USA'           1      0

>> tSum = varfun(@sum,t,'GroupingVariables',{'Date' 'Country'},'InputVariables',{'X' 'Y'})
tSum =
  13×5 table
       Date         Country     GroupCount    sum_X    sum_Y
    ___________    _________    __________    _____    _____
    19-Aug-2016    'USA'        3             2976     304  
    30-Aug-2016    'England'    1                1       0  
    31-Aug-2016    'USA'        1                1       0  
    02-Sep-2016    'USA'        1                1       0  
    03-Sep-2016    'England'    1                1       0  
    04-Sep-2016    'USA'        1                2       0  
    07-Sep-2016    'USA'        1                1       1  
    08-Sep-2016    'USA'        1                1       0  
    09-Sep-2016    'USA'        1                1       0  
    26-Sep-2016    'USA'        1                1       0  
    16-Oct-2016    'France'     1                1       1  
    16-Oct-2016    'USA'        1                5       0  
    19-Dec-2016    'USA'        1                1       0

That uses datetimes, not datenums, which is not crucial, but if you have R2014b or later, you're better off with datetimes. If you have R2016b or later, you could also use timetables. Actually, in 16b, there's a limitation where you cannot use both Date and Country as grouping variables, so you'd have to convert to a table. But in the recently-released R2017a, you can:

>> tt = table2timetable(t(:,2:end));
>> tSum = varfun(@sum,tt,'GroupingVariables',{'Date' 'Country'},'InputVariables',{'X' 'Y'})
tSum =
  13×4 timetable
        Date        Country     GroupCount    sum_X    sum_Y
    ___________    _________    __________    _____    _____
    19-Aug-2016    'USA'        3             2976     304  
    30-Aug-2016    'England'    1                1       0  
    31-Aug-2016    'USA'        1                1       0  
    02-Sep-2016    'USA'        1                1       0  
    03-Sep-2016    'England'    1                1       0  
    04-Sep-2016    'USA'        1                2       0  
    07-Sep-2016    'USA'        1                1       1  
    08-Sep-2016    'USA'        1                1       0  
    09-Sep-2016    'USA'        1                1       0  
    26-Sep-2016    'USA'        1                1       0  
    16-Oct-2016    'France'     1                1       1  
    16-Oct-2016    'USA'        1                5       0  
    19-Dec-2016    'USA'        1                1       0

You can also aggregate data in a timetable using retime, but varfun is the place to go to aggregate with respect to both time and other grouping variables.

Hope this helps.

[dpb]

Comes pretty close but doesn't quite fulfill OP's spec for the desired answer...there are two entries for 16-Oct that aren't combined above because Country values don't match; in that case is supposed to be 'Multiple' instead, but still combine the entries.

Can get the correct sums if only use 'Date' as the grouping variable, but then the result returned doesn't include the Country I discovered; probably because varnum can't handle the fact there is a mix of values for a group so it just discards the variable.

But, the example use of varnum is beneficial even if it doesn't quite solve OP's problem in its entirety. Perhaps the problem would serve as inspiration for further enhancements??? In this case the ability to define a default or substiture value for the Country variable; in other cases able to link to a callback function would be useful no doubt.

[Peter Perkins]

Thanks, I hadn't noticed that. I think I'd probably still call varfun but group only on time, and also apply another function to the country names.

t = cell2table(c,'VariableNames',{'ID' 'Date' 'Country' 'X' 'Y'});
t.Date = datetime(t.Date,'Format','dd-MMM-yyyy');
t.Country = categorical(t.Country);

tSum1 = varfun(@sum,t,'GroupingVariables','Date','InputVariables',{'X' 'Y'});
tSum2 = varfun(@countryOrMultiple,t,'GroupingVariables','Date','InputVariables','Country');
tsum = [tSum2 tSum1(:,3:end)]
tsum =
  12×5 table
       Date        GroupCount    countryOrMultiple_Country    sum_X    sum_Y
    ___________    __________    _________________________    _____    _____
    19-Aug-2016    3             USA                          2976     304  
    30-Aug-2016    1             England                         1       0  
    31-Aug-2016    1             USA                             1       0  
    02-Sep-2016    1             USA                             1       0  
    03-Sep-2016    1             England                         1       0  
    04-Sep-2016    1             USA                             2       0  
    07-Sep-2016    1             USA                             1       1  
    08-Sep-2016    1             USA                             1       0  
    09-Sep-2016    1             USA                             1       0  
    26-Sep-2016    1             USA                             1       0  
    16-Oct-2016    2             Multiple                        6       1  
    19-Dec-2016    1             USA                             1       0

Given that I've made Country a categorical, here's a function to deal with 'Multiple':

function uc = countryOrMultiple(c)
% return either the scalar input categorical value, or the scalar
% categorical value 'Multiple'
uc = unique(c);
if ~isscalar(uc)
    uc = uc(1); % keep all the existing categories
    uc(1) = 'Multiple';
end
end

And because the grouping is on time alone, you could use either varfun (or retime, for that matter) on a timetable, in R2016b or later.

[dpb]

That's an alternative, Peter; and good reminder that the function handle can be anything w/ the proper input/output arguments. I was thinking of an 'onerror' alternate function or value that could possibly cope with the case in one go as an enhancement.

Good practice for table, unfortunately to date I'm limited to R2014b owing to hardware limitations (32-bit) and haven't had opportunity to work with the class to any great extent. Converting to categorical is good; don't think of them too much yet, either...

Answer 3

[dpb]

Almost same solution as above...the only real significant difference is that strcmp is cell-aware so don't need cellfun to process the country names.

d=readtable('tomm.dat','readvariablenames',0,'delimiter',' ');   % get the data
[u,ia,ib]=unique(d.Var1);       % unique dates, locations in both length arrays
[n,ix]=histc(ib,1:length(ia));  % count the number of each group to see who's duplicated
for i=find(n>1).'               % for those that are duplicates
  if ~all(strcmp(d.Var2(i==ix),d.Var2(i)))   % if not all same country
    d.Var2(i==ix)={'Multiple'};              % set country value to 'Multiple'
  end
end

Now with preparatory work out of way, build the output table. Rows wanted from existing are the unique from the ia index vector for the date/country as modified for country plus the same accumarray results for the data--

d=[d(ia,1:2) array2table(accumarray(ib,d.Var3),'variablenames',{'Var3'}) ...
             array2table(accumarray(ib,d.Var4),'variablenames',{'Var4'})];

>> d
d = 
     Var1          Var2       Var3    Var4
  __________    __________    ____    ____
  7.3656e+05    'USA'         2976    304 
  7.3657e+05    'England'        1      0 
  7.3657e+05    'USA'            1      0 
  7.3658e+05    'USA'            1      0 
  7.3658e+05    'England'        1      0 
  7.3658e+05    'USA'            2      0 
  7.3658e+05    'USA'            1      1 
  7.3658e+05    'USA'            1      0 
  7.3658e+05    'USA'            1      0 
   7.366e+05    'USA'            1      0 
  7.3662e+05    'Multiple'       6      1 
  7.3668e+05    'USA'            1      0 
>>

Seems like ought to be able to just append the arrays to the selected subsets of the table, but the casting function was only way seemed to work; seems like more work than what should be necessary; maybe that's an enhancement request for silent conversion, who knows...

ADDENDUM

I'm still pretty green with the table class, it is somewhat easier following Kelly's lead using table on the arrays jointly rather than individually--still have to force names though, it seems to not clash.

d=[d(ia,1:2) table(accumarray(ib,d.Var3),accumarray(ib,d.Var4),'variablenames',{'Var3','Var4'})]