How to group an array by multiple keys and sum the result in php

Question

Sorry everyone I am new to this site I don't know how to properly format this question. I have an array() which contains about 6000 items. Each item has 3 properties for example.

Item[1]user=>'user1', acct_num => '1', value => 32,
Item[2]user=>'user1', acct_num => '1', value => 2,
Item[3]user=>'user1', acct_num => '2', value => 32,
Item[5]user=>'user1', acct_num => '2', value => 32,
Item[6]user=>'user2', acct_num => '1', value => 312,
Item[1]user=>'user2', acct_num => '1', value => 320,

So I want to get the total of user1 with account 1 which in this example is 34, and I need the total of user1 with account 2 which is 64 then sum both totals which is 98 then divide the acct totals by the whole total (94). so it will be 34/98 and 64 /98. I need to do that with all the items in the array

I am trying to avoid the database. because at the end i will insert the final array into the database and export it as xls — dianesis, Jul 23 '17 at 03:48
to be honest i havent write anything because i dont know how to proceed apparently php doesnt have something native to do the sorting. so far i am doing a bad work flow like inserting the whole csv file into database and do a select by user account and sum value group by user and account like select user, account, sum(value) from report group by user , account. But i want to ditch the databse and do array manipulation and finally insert into database — dianesis, Jul 23 '17 at 04:11
PHP *does* have native sorting. Look at [usort](http://php.net/manual/en/function.usort.php). You should also look at [array_keys](http://php.net/manual/en/function.array-keys.php). — Scott C Wilson, Jul 23 '17 at 04:22
so is possible to manipulate an array liek this one and group by username and account and sum the value by account? — dianesis, Jul 23 '17 at 04:26
Of course it is. Break the task down. Take a small slice of your data as a test set so you can iterate more quickly. Start by getting the totals of each user. Loop through the entire array totalling `value` for each user value. — Scott C Wilson, Jul 23 '17 at 04:32
but to do that i need to group the array by user and account and sum the value and that's what I dont know how to do and php doesnt offer a native function to group the items by keys — dianesis, Jul 23 '17 at 04:35
The data in your question is a bit wonky. Is the last entry supposed to be `Item[7]`? And what happened to `Item[4]`? — Scott C Wilson, Jul 23 '17 at 04:49

score 2 · Accepted Answer · answered Jul 23 '17 at 04:59

If you don't want to leverage the database you can just iterate over your data set, group and calculate averages

$csv = [
    ['user' => 'user1', 'acct_num' => '1', 'value' => 32],
    ['user' => 'user1', 'acct_num' => '1', 'value' => 2],
    ['user' => 'user1', 'acct_num' => '2', 'value' => 32],
    ['user' => 'user1', 'acct_num' => '2', 'value' => 32],
    ['user' => 'user2', 'acct_num' => '1', 'value' => 312],
    ['user' => 'user2', 'acct_num' => '1', 'value' => 320],
];

$subtotals = []; // totals per account
$totals = []; // totals per user
$results = [];

foreach($csv as $row) {
    list($user, $acct_num, $value) = array_values($row);

    $subtotal = isset($subtotals[$user][$acct_num]) ? $subtotals[$user][$acct_num] : 0;
    $subtotals[$user][$acct_num] = $subtotal + $value;

    $total = isset($totals[$user]) ? $totals[$user] : 0;
    $totals[$user] = $total + $value;
}

foreach ($subtotals as $user => $acct_nums) {
    foreach ($acct_nums as $acct_num => $value) {
        $total = $totals[$user];
        $avg = $value / $total;

        $results[] = compact('user', 'acct_num', 'value', 'total', 'avg');
    }
}

print_r($results);

Here is a demo

Output:

Array
(
    [0] => Array
        (
            [user] => user1
            [acct_num] => 1
            [value] => 34
            [total] => 98
            [avg] => 0.3469387755102
        )

    [1] => Array
        (
            [user] => user1
            [acct_num] => 2
            [value] => 64
            [total] => 98
            [avg] => 0.6530612244898
        )

    [2] => Array
        (
            [user] => user2
            [acct_num] => 1
            [value] => 632
            [total] => 632
            [avg] => 1
        )

)

this is great! I wonder how yo manage to know how to do this. — dianesis, Jul 23 '17 at 05:18
I think there is an unintended error here. The total is repeated twice for each item in an array. — dianesis, Aug 02 '17 at 01:52

How to group an array by multiple keys and sum the result in php

1 Answers1