MATLAB: Merging Table with Duplicate Dates

MATLABtable

Best Answer

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

Related Solutions

MATLAB: Time series data with missing dates to remap to calendar days vector

If you have R2014b or later, use tables and datetimes. For the specific question you've asked, simplest to use ismember and indexing:

>> Date = datetime({'01/01/2015';'01/14/2015';'05/30/2015'});
>> Value = [10; 5; 10];
>> TSomeDays = table(Date,Value)
TSomeDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    14-Jan-2015     5   
    30-May-2015    10   
>> Date = datetime(2015,1,(1:180)');
>> Value = zeros(size(Date));
>> TAllDays = table(Date,Value);
>> [~,i] = ismember(TSomeDays.Date,TAllDays.Date)
i =
     1
    14
   150
>> TAllDays.Value(i) = TSomeDays.Value
TAllDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]
>> Date = datetime(2015,1,(1:365)');
>> TAllDays = table(Date);

Another possibility is to use an outer join, which may be too heavy of a hammer for this specific question, but which provides capabilities that you may yet need. outerjoin fills missing values with NaN, but you can put zeros in their place:

>> TAllDays = outerjoin(TAllDays,TSomeDays,'Key','Date','MergeKeys',true)
TAllDays =
  365×2 table
       Date        Value
    ___________    _____
    01-Jan-2015     10  
    02-Jan-2015    NaN  
    03-Jan-2015    NaN  
[snip]
    13-Jan-2015    NaN  
    14-Jan-2015      5  
    15-Jan-2015    NaN  
[snip]
    29-May-2015    NaN  
    30-May-2015     10  
    31-May-2015    NaN  
[snip]
>> TAllDays.Value(isnan(TAllDays.Value)) = 0
TAllDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]

If you have R2016b, this is trivial using a timetable:

>> Date = datetime(2015,1,(1:365)');
>> TTAllDays = retime(TTSomeDays,Date,'FillWithConstant','Constant',0)
TTAllDays =
        Date       Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]

MATLAB: How do i eliminate such rows are not common between two tables

This is called an inner join

newtable = innerjoin(table1, table2);

Best Answer

Related Solutions

MATLAB: Time series data with missing dates to remap to calendar days vector

MATLAB: How do i eliminate such rows are not common between two tables

Related Question