Sum of duplicate dates in an array

Hello Guys,

I am struggling to sum the duplicate values in the following array

733943  -0.269926178145507
733943  1.01026388377933
733943  1.14852185563509
733949  3.00248661059463
733967  0.258922118015752
733988  1.52255831783950
733998  1.83156763219728
734005  2.78212297367682
734005  1.77266990099976
734005  1.08892548819182
734020  -0.414331330774944
734020  1.82288957774126
734020  2.15308021389711
734020  1.30322099539427
734044  1.15435389768749
734044  1.76528765989410
734052  -0.780095564850535
734052  -0.556888544288923
734052  -0.223506106870708
734052  2.38621682905110
734052  2.83778592043497
734052  1.36124058024847
734053  0.270795033631287
734082  -0.0299965318759457
734082  0.302341970371696
734082  2.27577783905383
734082  0.0487635633320927
734082  2.89536208413476
734082  2.25180288796776
734082  2.00949925793319
734082  1.90528826428200
734108  1.57887252817668
734108  1.20029202553859
734117  0.948833390261932
734128  1.62618452385620
734142  1.59160688924087
734171  -1.18570629605747
734171  1.05327431079761
734171  0.594520182104905
734171  1.01572731938152
734171  0.528895008850812
734171  1.71771026362169
734171  1.28895153306883

I tried the following

A = ReturnGlobal ( above Array)
[n,bin] = histc(A(:,1),unique(A(:,1)))
multiple = find(n > 1);
index    = find(ismember(bin, multiple))

But I am not able to do that correctly.

Normally, I expect a unique date on the right hand side and the sum of values under this date.

Could you please help me?

Thank you

D

Answer 1

[Mohammad Abouali]

I would use grpstat() as follow:

if you just want to sum by the group here is how you do it

sumA = grpstats(A(:,2),A(:,1),{@sum})

If later you want to calculate more statistic for each group determined in first column you can still use grpstat. for example:

[sumA,meanA,stdA,count]=grpstats(A(:,2),A(:,1),{@sum, 'mean','std', @numel})

where sumA stores the sum and meanA stores the mean and stdA stores the standard deviation for each group.

By group, I mean those that have 733943 in first column are considered one group and those having 734005 in their first column are considered another group.

actually by passing the function handle you can do any arbitrary operation that you want, based on each group separately.

Here are the results

results=table(num2str(unique(A(:,1))),count,sumA,meanA,stdA, ...
              'VariableNames',{'group','count','sum','mean','std'})

results = 

    group     count      sum       mean        std  
    ______    _____    _______    _______    _______

    733943    3         1.8889    0.62962    0.78209
    733949    1         3.0025     3.0025          0
    733967    1        0.25892    0.25892          0
    733988    1         1.5226     1.5226          0
    733998    1         1.8316     1.8316          0
    734005    3         5.6437     1.8812     0.8518
    734020    4         4.8649     1.2162     1.1419
    734044    2         2.9196     1.4598      0.432
    734052    6         5.0248    0.83746     1.5723
    734053    1         0.2708     0.2708          0
    734082    8         11.659     1.4574     1.1591
    734108    2         2.7792     1.3896     0.2677
    734117    1        0.94883    0.94883          0
    734128    1         1.6262     1.6262          0
    734142    1         1.5916     1.5916          0
    734171    7         5.0134     0.7162    0.93109

[Davin]

Thanks Mohammad.

Answer 2

[Star Strider]

I would use the accumarray function:

M = [733943  -0.269926178145507
733943  1.01026388377933
733943  1.14852185563509
733949  3.00248661059463
733967  0.258922118015752
733988  1.52255831783950
733998  1.83156763219728
          ...
734171  1.01572731938152
734171  0.528895008850812
734171  1.71771026362169
734171  1.28895153306883];

M11 = M(1,1);
M(:,1) = M(:,1)-M(1,1)+1;               % Create Tractable Indices
MSum = accumarray(M(:,1), M(:,2));      % Sum Values For Each Value In M(:,1)

The sums in ‘Msum’ will be the accumulated sums in ‘M(:,2)’ for each unique value in ‘M(:,1)’. To regenerate the original values for ‘M(:,1)’ in ‘Msum’, add to those indices M(1,1)-1.

ADDED:

Result = [find(MSum)+M11-1 MSum(MSum>0)]

produces:

Result =

                      733943          1.88885956126891
                      733949          3.00248661059463
                      733967         0.258922118015752
                      733988           1.5225583178395
                      733998          1.83156763219728
                      734005           5.6437183628684
                      734020           4.8648594562577
                      734044          2.91964155758159
                      734052          5.02475311372437
                      734053         0.270795033631287
                      734082          11.6588393351994
                      734108          2.77916455371527
                      734117         0.948833390261932
                      734128           1.6261845238562
                      734142          1.59160688924087
                      734171           5.0133723217679

[Davin]

Thanks Stan. Great Answer