Group rows of data and in each group sum one column and concatenate another column

Question

In php, I have an indexed array of associative rows like this:

$the_array = [
   ['id' => 1, 'value' => 10, 'name' => 'apple'],
   ['id' => 1, 'value' => 20, 'name' => 'orange'],
   ['id' => 1, 'value' => 30, 'name' => 'banana'],
   ['id' => 2, 'value' => 100, 'name' => 'car'], 
   ['id' => 2, 'value' => 200, 'name' => 'bicycle'],
];

and I would like to restructure it to by grouping on id values and in each group I'd like to sum the value values and make a comma-separated string of the name values.

[
    ['id' => 1, 'value' => 60,  'name' => 'apple,orange,banana'],
    ['id' => 2, 'value' => 300, 'name' => 'car,bicycle']
]

This is what I tried:

function group_by($key, $data) {
    $result = array();
    foreach($data as $val) {
        if(array_key_exists($key, $val)){
            $result[$val[$key]][] = $val;
        }else{
            $result[""][] = $val;
        }
    }
    return $result;
}

It's not working and the result is wrong/incomplete.

Answer 1

I would create an intermediary array which groups into array keys by id first, then use that to call combinations of array_column() with array_sum() and implode() to produce your sum value and combined name string.

$temp_array = [];
foreach ($the_array as $init) {
  // Initially, group them on the id key as sub-arrays
  $temp_array[$init['id']][] = $init;
}

$result = [];
foreach ($temp_array as $id => $arr) {
  // Loop once for each id ($temp_array has 2 elements from your sample)
  // And add an element to $result
  $result[] = [
    'id' => $id,
    // Sum the value subkeys
    // array_column() returns just the key 'value' as an array
    // array_sum() adds them
    'value' => array_sum(array_column($arr, 'value')),
    // implode the name subkeys into a string
    'name' => implode(',', array_column($arr, 'name'))
  ];
}

print_r($result);
Array
(
    [0] => Array
        (
            [id] => 1
            [value] => 60
            [name] => apple,orange,banana
        )

    [1] => Array
        (
            [id] => 2
            [value] => 300
            [name] => car,bicycle
        )

)

Answer 2

It is not necessary to write multiple loops or to keep a map of indexes. The most lean, directly way is to use temporary first-level keys based on the value being grouped by.

Both of the snippets below generate the same output and have the same internal processing. The only difference between the two snippets is that one iterates the input data using a language construct, the other uses functional iteration.

As you loop through the data, apply temporary keys to the result array based on the id value of each respective row. If the first encounter of a given id, then store the full row of data into the group. If not the first encounter of a given id, then add the value value to the previous value value AND concatenate the new name value to the previously stored name value in the group.

When finished, you may wish to re-index the first level by calling array_values().

Classic foreach(): (Demo)

$result = [];
foreach ($the_array as $row) {
    if (!isset($result[$row['id']])) {
        $result[$row['id']] = $row;
    } else {
        $result[$row['id']]['value'] += $row['value'];
        $result[$row['id']]['name'] .= ",{$row['name']}";
    }
}
var_export(array_values($result));

---

Functional-style with array_reduce(): (Demo)

var_export(
    array_values(
        array_reduce(
            $the_array,
            function ($carry, $row) {
                if (!isset($carry[$row['id']])) {
                    $carry[$row['id']] = $row;
                } else {
                    $carry[$row['id']]['value'] += $row['value'];
                    $carry[$row['id']]['name'] .= ",{$row['name']}";
                }
                return $carry;
            }
        )
    )
);

Answer 3

Although this one is already answered, here is an alternative way to do all this in one loop.

If you keep track of a small map that maps the original IDs into their respective array indices.

$result = [];
$map = [];

foreach ($the_array as $subarray) {
    $id = $subarray['id'];

    // First time we encounter the id thus we can safely push it into result
    if (!key_exists($id, $map)) {
        // array_push returns the number of elements
        // since we push one at a time we can directly get the index.
        $index = array_push($result, $subarray) - 1;
        $map[$id] = $index;

        continue;
    }

    // If the id is already present in our map we can simply
    // update the running sum for the values and concat the
    // product names.
    $index = $map[$id];
    $result[$index]['value'] += $subarray['value'];
    $result[$index]['name'] .= ",{$subarray['name']}";
}

echo '<pre>';
print_r($result);
echo '</pre>';

Result:

Array
(
    [0] => Array
        (
            [id] => 1
            [value] => 60
            [name] => apple,orange,banana
        )
    [1] => Array
        (
            [id] => 2
            [value] => 300
            [name] => car,bicycle
        )
)