Pivot data in an array of associative arrays to group by values in one column

Question

I have a large multidimensional array structured like the sample below.

[
    ['name' => 'SMITH', 'status' => 'Incomplete', 'count' => 2],
    ['name' => 'SMITH', 'status' => 'Complete', 'count' => 2],
    ['name' => 'HUGHES', 'status' => 'Incomplete', 'count' => 3],
    ['name' => 'HUGHES', 'status' => 'Complete', 'count' => 1],
];

What I would like is a new array that looks like this -

[
    ['name' => 'SMITH', 'Incomplete' => 2, 'Complete' => 2],
    ['name' => 'HUGHES', 'Incomplete' => 3, 'Complete' => 1]
]

I'm pretty new to PHP and I do realize I need to loop through the original array but any help would be appreciated.

My experience in asking questions like this is that you should provide some context for the question, after the question. That way people can answer your specific question and probably suggest a better way to do things. — andrew, Feb 10 '11 at 17:59

score 3 · Accepted Answer · answered Feb 10 '11 at 17:41

$original = array( array ( 'name' => 'SMITH',
                           'status' => 'Incomplete',
                           'count' => 2
                         ),
                   array ( 'name' => 'SMITH',
                           'status' => 'Complete',
                           'count' => 2
                         ),

                   array ( 'name' => 'HUGHES',
                           'status' => 'Incomplete',
                           'count' => 3
                         ),
                   array ( 'name' => 'HUGHES',
                           'status' => 'Complete',
                           'count' => 1
                         ),
                 );

// initialise our new array
$newArray = array();
// loop through each entry from the original array in turn
foreach($original as $entry) {
    // temporarily use the name as the key for our new array (we'll reset to a numeric later)
    $name = $entry['name'];
    $newArray[$name]['name'] = $name;
    //  test if we have multiple entries for the same name/status
    if (isset($newArray[$name][$entry['status']])) {
        $newArray[$name][$entry['status']] += $entry['count'];
    } else {
        $newArray[$name][$entry['status']] = $entry['count'];
    }
}

//  Reset top-level keys to numerics
$newArray = array_values($newArray);

echo '<pre>';
var_dump($newArray);
echo '</pre>';

I would invert your `isset` check so that you can remove the else statement, setting the value of `status` to `0` and then just using the `+=` operator to reduce the code by 2 lines. — RobertPitt, Feb 10 '11 at 17:59
@RobertPitt - fewer lines of code, but I don't know if it would be any faster (even for large arrays)... of course, if there's only ever a single entry for each name:complete/incomplete combination, then the if test isn't even necessary — Mark Baker, Feb 10 '11 at 19:50

score 2 · Answer 2 · answered Feb 10 '11 at 18:39

I would break this into three steps.

Step One: transform this

[0] => Array
    (
        [name] => SMITH
        [status] => Incomplete
        [count] => 2
    )

[1] => Array
    (
        [name] => SMITH
        [status] => Complete
        [count] => 2
    )

into this

[0] => Array
    (
        [name] => SMITH
        [Incomplete] => 2
    )

[1] => Array
    (
        [name] => SMITH
        [Complete] => 2
    )

which can be done by the following

$data = array_map($data, function($d) {
    return array('name' => $d['name'], $d['status'] => $d['count']);
});

Step Two:

I have seen many people ask about this stage of problem, typically when dealing with database query result sets. The basic problem is that instead of indexing the records by row number, you want to index the records based on some data each record contains. So, here is a generic function...

function make_index($array, $key) {
    foreach ($array as $a) {
        $r = &$reindexed[$a[$key]];
        $r = array_merge((array)$r, $a);
    }
    return $reindexed;
}

acting on our result from Step One, presuming we call it as $data = make_index($data, "name");, we will arrive at

Array
(
    [SMITH] => Array
        (
            [name] => SMITH
            [Incomplete] => 2
            [Complete] => 2
        )

)

Step Three:

now you want to go back to numerical indexes, and that is easy achieved.

$data = array_values($data);

gives

Array
(
    [0] => Array
        (
            [name] => SMITH
            [Incomplete] => 2
            [Complete] => 2
        )

)

Conclusion:

It is useful to break data set transformations into baby steps. By doing so, you can reuse individual steps in different problems. Also, for complex changes, it becomes easier to arrive at a solution, or understand the code once it is written.

A Note about Step Two:

In step two I provided a "generic" function, but really, the merge done by array_merge will not accommodate most situations. Specifically, query results have records containing all the same keys, and a simple overwrite is not likely the aggregation method desired. Here is another variant to overcome this limitation.

function make_index($array, $key, $M) {
    foreach ($array as $a) {
        $r = &$reindexed[$a[$key]];
        $r = $M((array)$r, $a);
    }
    return $reindexed;
}

// ex: $data = make_index($data, "name", function($e1, $e2){
//    return array_merge($e1, $e2);
// });
// equiv to: $data = make_index($data, "name", 'array_merge');

I hate to down vote such wealthy post, but you totally missed the point, he never wanted to re index alone, he also wanted to sum the data. — RobertPitt, Feb 10 '11 at 19:14
You totally missed what I gave in my answer. I didn't just re-index alone; I continue on to take the re-indexed array and transform it into his final, desired structure. — erisco, Feb 10 '11 at 21:43

score 1 · Answer 3 · answered Feb 10 '11 at 17:56

Something like this would be the way i would go:

$new = array();
foreach($original as $entity)
{
    if(!isset($new[$entity["name"]]))
    {
        $new[$entity["name"]] = array(
            "name" => $entity["name"],
            "Complete" => 0,
            "Incomplete" => 0
        );
    }
    $new[$entity["name"]][$entity["status"]] += $entity["count"];
}

print_r($new);

using the array sample above, please note the case-sensitivity in the arrays, this has an effect.

The outpout would be like so:

Array
(
    [SMITH] => Array
        (
            [name] => SMITH
            [Complete] => 2
            [Incomplete] => 2
        )

    [HUGHES] => Array
        (
            [name] => HUGHES
            [Complete] => 1
            [Incomplete] => 3
        )

)

and there's a live code demo @ http://codepad.org/H099lulS

mickmackusa · Answer 4 · 2022-08-31T08:06:08.733

Because the question doesn't make any indication that there might be "gaps" in the data to be pivot and there is no mention of needing "summing", the task is essentially a pivot.

Here's a fun snippet that uses a body-less loop to pivot the data.

Code: (Demo)

$result = [];
foreach ($array as [
    'name' => $name,
    'name' => $result[$name]['name'],
    'status' => $status,
    'count' => $result[$name][$status]
]);
var_export(array_values($result));

Output:

array (
  0 => 
  array (
    'name' => 'SMITH',
    'Incomplete' => 2,
    'Complete' => 2,
  ),
  1 => 
  array (
    'name' => 'HUGHES',
    'Incomplete' => 3,
    'Complete' => 1,
  ),
)

Discovery post: While destructuring an array, can the same element value be accessed more than once?

The more classic design would look this way (which is admittedly more concise and easier to read): (Demo)

$result = [];
foreach ($array as $row) {
    $result[$row['name']][$row['status']] = $row['count'];
}
var_export(array_values($result));

Pivot data in an array of associative arrays to group by values in one column

4 Answers4