How to merge two arrays using column values and find sum of another column's values? [duplicate]

Question

I am having two arrays with same keys from two different queries.

First query result:

Array
(
  [0] => Array
  (
      [Contribution] => 1000.00
      [P_Name] => A
  )
  [1] => Array
  (
      [Contribution] => 1500.00
      [P_Name] => B
  )
)

Second query result:

Array
(
  [0] => Array
  (
      [Contribution] => 100.00
      [P_Name] => A
  )
  [1] => Array
  (
      [Contribution] => 200.00
      [P_Name] => B
  )
)

The first array may be empty and/or the second may be empty.

I want to get the create a new array that finds the sum of Contribution values where P_Name values match, like this:

Array
(
  [0] => Array
  (
      [Contribution] => 1100.00
      [P_Name] => A
  )
  [1] => Array
  (
      [Contribution] => 1700.00
      [P_Name] => B
  )
)

I have tried array_merge():

$result1= $this->model1->getOthersTotal($date);
$result2=$this->model1->getMiscTotal($date);
$merged_result = array_merge( $result1, $result2 );

$merged_result contains:

Array (
    [0] => Array (
        [Contribution] => 1000.00
        [P_Name] => A
    )
    [1] => Array (
        [Contribution] => 1001.00
        [P_Name] => A
    )
    [2] => Array (
        [Contribution] => 69.00
        [P_Name] => B
    )
)

If your both array has the same length then you can easily loop them and check with P_Name, if it matches then sum contribution and store it into another array as your result — Saroj
– Saroj, Commented Aug 31, 2017 at 8:38
I Have tried Array_merge , $result1= $this->model1->getOthersTotal($date); $result2=$this->model1->getMiscTotal($date); $merged_result = array_merge( $result1, $result2 ); Array ( [0] => Array ( [Contribution] => 1000.00 [P_Name] => A ) [1] => Array ( [Contribution] => 1001.00 [P_Name] => A ) [2] => Array ( [Contribution] => 69.00 [P_Name] => B ) ) — user3615287
– user3615287, Commented Aug 31, 2017 at 8:42
Possible duplicate of PHP custom array merge on bases of same value and sum of value and Put multiple arrays in one large associative array and Merge 2 multi-dimension arrays and sum value — mickmackusa
– mickmackusa ♦, Commented Aug 31, 2017 at 13:53

mickmackusa · Accepted Answer · 2017-08-31 11:29:52Z

2

Input:

$a=[['Contribution'=>1000,'P_Name'=>'A'],
    ['Contribution'=>1500,'P_Name'=>'B'],
    ['Contribution'=>2000,'P_Name'=>'C']];
$b=[['Contribution'=>100,'P_Name'=>'A'],
    ['Contribution'=>200,'P_Name'=>'B'],
    ['Contribution'=>300,'P_Name'=>'D']];

If you temporarily assign associative keys to the subarrays using array_column(), then you can leverage array_merge_recursive() to group on P_Name values, then call array_sum() to do the addition if there is more than one value to for a given P_Name.

Method #1: (Demo)

$keyed=array_merge_recursive(array_column($a,NULL,'P_Name'),array_column($b,NULL,'P_Name'));
foreach($keyed as $p_name=>$array){
    $result[]=['Contribution'=>(is_array($array['Contribution'])?array_sum($array['Contribution']):$array['Contribution']),'P_Name'=>$p_name];
}
var_export($result);

Or just do a standard merge to create one array, then loop and add as you go. Finalize the output array with array_values() to reindex the elements.

Method #2: (Demo)

foreach(array_merge($a,$b) as $array){
    if(isset($result[$array['P_Name']])){
        $result[$array['P_Name']]['Contribution']+=$array['Contribution'];
    }else{
        $result[$array['P_Name']]=$array;
    }
}
$result=array_values($result);
var_export($result);

Output: (from either method)

array (
  0 => 
  array (
    'Contribution' => 1100,
    'P_Name' => 'A',
  ),
  1 => 
  array (
    'Contribution' => 1700,
    'P_Name' => 'B',
  ),
  2 => 
  array (
    'Contribution' => 2000,
    'P_Name' => 'C',
  ),
  3 => 
  array (
    'Contribution' => 300,
    'P_Name' => 'D',
  ),
)

It is out of the scope of this question, but chances are the best approach would be to perform this grouping/addition via database query.

edited Aug 31, 2017 at 11:29

answered Aug 31, 2017 at 10:55

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeffrey Over a year ago

@mickmackusa If you "believe" your code is the most efficient, please provide a benchtest. Let me help you with that and results. Your method is more than twice as slow compared to mine. All methods on this page are included and provide the correct (and expected) response.

mickmackusa Over a year ago

Any benchmark done at this point will be speculative as we don't know the OP's potential array sizes nor the OP's php version. But good on you for providing the early returns, that's a good addition. The OP should definitely check for empty arrays as a shortcut. Perhaps I'll reword my claim and play it safe to say, most concise methods.

mickmackusa Over a year ago

(when I first posted my claim on performance, Jeffrey's answer was flawed so I discounted it)

Jeffrey · Accepted Answer · 2017-08-31 12:55:31Z

1

Here is a quick and dirty way to do it: Loop over both arrays, the outer loop fills the inner loop. If no match was fount, $x remails 0, and the value will be added to the inner loop. If a match is found, $x is 1 and the inner loop will break to continue the outer loop.

$a = [['a' => 10,'b' => 'g'], ['a' => 11,'b' => 'h']];
$b = [['a' => 1, 'b' => 'g'], ['a' => 2, 'b' => 'h'], ['a' => 3, 'b' => 'i']];

// now its fool proof.
function mergeData( $a, $b )
{
    if( empty( $a ) && empty( $b ) )
        return [];
    if( empty( $a ) )
        return $b;
    if( empty( $b ) )
        return $a;

    foreach( $b AS $i => $c ) {
        $x = 0;
        foreach( $a AS $ii => $d ) {
            if( $c['b'] == $d['b'] ) {
                $a[ $ii ]['a'] += $c['a'];
                $x             = 1;
                break;
            }
        }
        if( !$x )
            $a[] = $b[ $i ];
    }

    return $a;
}

Output

Array
(
    [0] => Array
        (
            [a] => 11
            [b] => g
        )

    [1] => Array
        (
            [a] => 13
            [b] => h
        )

    [2] => Array
        (
            [a] => 3
            [b] => i
        )

)

edited Aug 31, 2017 at 12:55

answered Aug 31, 2017 at 8:42

Jeffrey

1,8062 gold badges25 silver badges44 bronze badges

3 Comments

user3615287 Over a year ago

$a can be empty, in that case the output will be different.

Jeffrey Over a year ago

If $a is empty, you don't need to loop, because $b is the answer. As noted: it is a quick and dirty method, it works, but has no checks for emptyness etc in it.

Jeffrey Over a year ago

To the person who downvotes, provide a reason(!). This code is working, behaviours as requested and is the most optimised code I could think of.

Bart · Accepted Answer · 2017-08-31 09:11:54Z

Little bit different approach

$array1 = [
    [
        'Contribution' => 10,
        'P_Name' => 'A'
    ],  
    [
        'Contribution' => 1500,
        'P_Name' => 'B'
    ]     
];
$array2 = [
    [
        'Contribution' => 200,
        'P_Name' => 'B'
    ],   
    [
        'Contribution' => 100,
        'P_Name' => 'C'
    ],   
];

$array3 = array_map(function($elem) use (&$array2){
    foreach($array2 as $i => &$a2){
        if($a2['P_Name'] == $elem['P_Name']){
            $a2['Contribution'] += $elem['Contribution'];
            return;
        }            
    }
    return $elem;
},$array1);

$array3 = array_merge(array_filter($array3),$array2);

print_r($array3);

output:

Array
(
    [0] => Array
        (
            [Contribution] => 10
            [P_Name] => A
        )

    [1] => Array
        (
            [Contribution] => 1700
            [P_Name] => B
        )

    [2] => Array
        (
            [Contribution] => 100
            [P_Name] => C
        )
)

localheinz · Accepted Answer · 2017-08-31 10:37:35Z

You can use array_reduce(), array_map(), and array_sum():

<?php

function merge(array ...$sets)
{
    /**
     * group contributions by name
     */
    $contributions = array_reduce(
        $sets,
        function (array $contributions, array $set) {
            foreach ($set as $element) {
                $name = $element['P_Name'];
                $contribution = $element['Contribution'];

                if (!array_key_exists($name, $contributions)) {
                    $contributions[$name] = [];
                }

                $contributions[$name][] = $contribution;

            }

            return $contributions;
        },
        []
    );

    /**
     * normalize the array so we remove the name as key, and return a tuple of name and contribution, with the desired
     * structure
     */
    return array_values(array_map(function (array $contribution, $name) {
        return [
            'Contribution' => array_sum($contribution),
            'P_Name' => $name,
        ];
    }, $contributions, array_keys($contributions)));
}

$a = [
    [
        'Contribution' => 1000,
        'P_Name' => 'A',
    ],
    [
        'Contribution' => 1500,
        'P_Name' => 'B',
    ],
];

$b = [
    [
        'Contribution' => 100,
        'P_Name' => 'A',
    ],
    [
        'Contribution' => 200,
        'P_Name' => 'B',
    ],
];

$merged = merge($a, $b);

var_dump($merged);

Note Because of using variadics, any number of arrays can be passed to merge(). Requires at least PHP 5.6, though.

For reference, see:

For an example, see:

https://3v4l.org/EI4Tn

Collectives™ on Stack Overflow

How to merge two arrays using column values and find sum of another column's values? [duplicate]

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Linked

Related