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

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, Aug 31 '17 at 08: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, Aug 31 '17 at 08:42
Possible duplicate of [PHP custom array merge on bases of same value and sum of value](https://stackoverflow.com/questions/34945377/php-custom-array-merge-on-bases-of-same-value-and-sum-of-value) and [Put multiple arrays in one large associative array](https://stackoverflow.com/questions/30629340/put-multiple-arrays-in-one-large-associative-array) and [Merge 2 multi-dimension arrays and sum value](https://stackoverflow.com/questions/10354154/merge-2-multi-dimension-arrays-and-sum-value) — mickmackusa, Aug 31 '17 at 13:53

mickmackusa · Answer 1 · 2017-08-31T11:29:52.160

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.

@mickmackusa If you "believe" your code is the most efficient, please provide a benchtest. [Let me help you with that](https://pastebin.com/B2MnfCyx) and [results](https://pastebin.com/88FdfDbV). 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. — Jeffrey, Aug 31 '17 at 12:52
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, Aug 31 '17 at 13:02
(when I first posted my claim on performance, Jeffrey's answer was flawed so I discounted it) — mickmackusa, Mar 29 '18 at 21:53

Jeffrey · Answer 2 · 2017-08-31T12:55:31.480

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
        )

)

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, Aug 31 '17 at 08:50
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. — Jeffrey, Aug 31 '17 at 12:56

Bart · Answer 3 · 2017-08-31T09:11:54.370

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 · Answer 4 · 2017-08-31T10:37:35.750

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

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

4 Answers4

Related