CODEWITHSUNDEEP
phparraysmultidimensional-arraysumpivot
Happy
Happy

Reputation: 11

Pivot row data from a 2d array grouping by one column and summing another column respective to the pivoted column

I need to group the data in my 2d array by the SiteId column values and then use the TransactionType value within that group as the second level key which need to collect the total Amount for that transaction in the group.

Input:

[
    ['SiteID' => 147, 'Amount' => '500.00',  'TransactionType' => 'Deposit'],
    ['SiteID' => 147, 'Amount' => '500.00',  'TransactionType' => 'Redemption'],
    ['SiteID' => 147, 'Amount' => '1500.00', 'TransactionType' => 'Deposit'],
    ['SiteID' => 147, 'Amount' => '200.00',  'TransactionType' => 'Reload'],
    ['SiteID' => 150, 'Amount' => '100.00',  'TransactionType' => 'Deposit'],
    ['SiteID' => 3,   'Amount' => '500.00',  'TransactionType' => 'Redemption'],
    ['SiteID' => 150, 'Amount' => '200.00',  'TransactionType' => 'Redemption'],
    ['SiteID' => 3,   'Amount' => '500.00',  'TransactionType' => 'Deposit'],
    ['SiteID' => 3,   'Amount' => '200.00',  'TransactionType' => 'Deposit'],
    ['SiteID' => 3,   'Amount' => '200.00',  'TransactionType' => 'Reload'],
    ['SiteID' => 147, 'Amount' => '500.00',  'TransactionType' => 'Redemption']
]

Desired Result:

[
    147 => ['Deposit' => 2000.0, 'Redemption' => 1000.0, 'Reload' => 200.0],
    150 => ['Deposit' => 100.0, 'Redemption' => 200.0],
    3   => ['Redemption' => 500.0, 'Deposit' => 700.0, 'Reload' => 200.0]
]

Upvotes: -1

Views: 447

Answers (2)

mickmackusa
mickmackusa

Reputation: 47864

Use array destructuring in the foreach() to declare convenient variables for later use.

Push associative values into the result array using the id as the first level key, the transaction type as the second level key, and the amount as either the first encountered value or the value to add to a previously encountered element.

Code: (Demo)

$result = [];
foreach ($array as ['SiteID' => $id, 'Amount' => $amt, 'TransactionType' => $trn]) {
    $result[$id][$trn] = ($result[$id][$trn] ?? 0) + $amt;
}
var_export($result);

Upvotes: 0

Makita
Makita

Reputation: 1812

please provide a working array fragment in future.

$transactions = array (
    array( 'SiteID' => 147, 'Amount' => '500.00', 'TransactionType' => 'Deposit' ),
    array( 'SiteID' => 147, 'Amount' => '500.00', 'TransactionType' => 'Redemption'),
    array( 'SiteID' => 147, 'Amount' => '1500.00', 'TransactionType' => 'Deposit' ),
    array( 'SiteID' => 147, 'Amount' => '200.00', 'TransactionType' => 'Reload' ),
    array( 'SiteID' => 150, 'Amount' => '100.00', 'TransactionType' => 'Deposit' ),
    array( 'SiteID' => 3,   'Amount' => '500.00', 'TransactionType' => 'Redemption' ),
    array( 'SiteID' => 150, 'Amount' => '200.00', 'TransactionType' => 'Redemption' ),
    array( 'SiteID' => 3,   'Amount' => '500.00', 'TransactionType' => 'Deposit' ),
    array( 'SiteID' => 3,   'Amount' => '200.00', 'TransactionType' => 'Deposit' ),
    array( 'SiteID' => 3,   'Amount' => '200.00', 'TransactionType' => 'Reload' ),
    array( 'SiteID' =>147, 'Amount' => '500.00', 'TransactionType' => 'Redemption' )
);

$totals = null;

foreach ($transactions as $t){
    $amount = (float) $t['Amount'];
    if (isset($totals[ $t['SiteID'] ][ $t['TransactionType'] ])){
        $totals[ $t['SiteID'] ][ $t['TransactionType'] ] += (float) $amount;
    } else {
        $totals[ $t['SiteID'] ][ $t['TransactionType'] ] = (float) $amount;
    }
}

print_r ($totals);

This will produce a result like this:

Array
(
    [147] => Array
        (
            [Deposit] => 2000
            [Redemption] => 1000
            [Reload] => 200
        )

    [150] => Array
        (
            [Deposit] => 100
            [Redemption] => 200
        )

    [3] => Array
        (
            [Redemption] => 500
            [Deposit] => 700
            [Reload] => 200
        )

)

If you can deal with php notice warnings the loop can be shortened to:

foreach ($transactions as $t){
    $totals[ $t['SiteID'] ][ $t['TransactionType'] ] += (float) $t['Amount'];
}

Upvotes: 2

Related Questions