Home > Software engineering >  Calculate the sum of value if same key date
Calculate the sum of value if same key date

Time:10-26

I have an array with date and some total. I would like to sum all A_Total ; B_Total ; C_Total that have the same Date.

How can I do that ? Thanks for the help

Source :

Array
(
    [0] => Array
        (
            [Date] => 2022-10-23
            [A_Total] => 547
            [B_Total] => 70
            [C_Total] => 83
        )

    [1] => Array
        (
            [Date] => 2022-10-24
            [A_Total] => 547
            [B_Total] => 70
            [C_Total] => 83
        )

    [2] => Array
        (
            [Date] => 2022-10-25
            [A_Total] => 552
            [B_Total] => 70
            [C_Total] => 83
        )

    [3] => Array
        (
            [Date] => 2022-10-23
            [A_Total] => 5
            [B_Total] => 1
            [C_Total] => 19
        )

    [4] => Array
        (
            [Date] => 2022-10-24
            [A_Total] => 5
            [B_Total] => 1
            [C_Total] => 19
        )

    [5] => Array
        (
            [Date] => 2022-10-25
            [A_Total] => 5
            [B_Total] => 1
            [C_Total] => 19
        )

    [6] => Array
        (
            [Date] => 2022-10-23
            [A_Total] => 557
            [B_Total] => 134
            [C_Total] => 51
        )

    [7] => Array
        (
            [Date] => 2022-10-24
            [A_Total] => 557
            [B_Total] => 134
            [C_Total] => 51
        )

    [8] => Array
        (
            [Date] => 2022-10-25
            [A_Total] => 557
            [B_Total] => 134
            [C_Total] => 51
        )

)

Expected :

Array
(
    [0] => Array
        (
            [Date] => 2022-10-23
            [A_Total] => 1019
            [B_Total] => 205
            [C_Total] => 153
        )

    [1] => Array
        (
            [Date] => 2022-10-24
            [A_Total] => 1019
            [B_Total] => 205
            [C_Total] => 153
        )

    [2] => Array
        (
            [Date] => 2022-10-25
            [A_Total] => 1114
            [B_Total] => 205
            [C_Total] => 153
        )

)

CodePudding user response:

First get all values for each column

$columnA = array_column($arr, 'A_Total');
$columnB = array_column($arr, 'B_Total');
//etc...

Then, you could use array_sum:

$totalA = array_sum($columnA);
//same for b, c. etc...

In short:

$totalA = array_sum(array_column($arr, 'A_Total'));

References:

https://www.php.net/manual/en/function.array-sum.php https://www.php.net/manual/en/function.array-column.php

Edit~

Grouping columns by date:

$columnsByDate = [];

foreach ($arr as $totals) {
    if (!isset($columnsByDate[$totals['Date']]) {
        $columnsByDate[$totals['Date']] = [];
    }

    $columnsByDate[$totals['Date']][] = $totals;
}

CodePudding user response:

It will be a lot easier if you just make your array use the dates for keys. I mean, you're trying to get the data grouped by dates anyway, so why not use the date as an identifier? Say you have this array:

$source = [
    ['Date' => '2022-10-23', 'A_Total' => 547, 'B_Total' => 70,  'C_Total' => 83],
    ['Date' => '2022-10-24', 'A_Total' => 547, 'B_Total' => 70,  'C_Total' => 83],
    ['Date' => '2022-10-25', 'A_Total' => 552, 'B_Total' => 70,  'C_Total' => 83],
    ['Date' => '2022-10-23', 'A_Total' => 5,   'B_Total' => 1,   'C_Total' => 19],
    ['Date' => '2022-10-24', 'A_Total' => 5,   'B_Total' => 1,   'C_Total' => 19],
    ['Date' => '2022-10-25', 'A_Total' => 5,   'B_Total' => 1,   'C_Total' => 19],
    ['Date' => '2022-10-23', 'A_Total' => 557, 'B_Total' => 134, 'C_Total' => 51],
    ['Date' => '2022-10-24', 'A_Total' => 557, 'B_Total' => 134, 'C_Total' => 51],
    ['Date' => '2022-10-25', 'A_Total' => 557, 'B_Total' => 134, 'C_Total' => 51],
];

Let's create a buffer array, then go through the source array and write the values from it to the new array:

$buffer = [];

foreach($source as $s) {
    
    $dt = $s['Date'];
    if(!isset($buffer[$dt])) { 
        $buffer[$dt] = [
            'A_Total' => 0,
            'B_Total' => 0,
            'C_Total' => 0
        ]; 
    }
    
    $buffer[$dt]['A_Total']  = $s['A_Total'];
    $buffer[$dt]['B_Total']  = $s['B_Total'];
    $buffer[$dt]['C_Total']  = $s['C_Total'];
    
}

echo "<pre>"; print_r($buffer); echo "</pre>"; exit;

Let's go through each bit. First, foreach will iterate over the array and return each element to whatever variable you specify, in this case, $s:

foreach($source as $s) {

and $s has the entire element. So let's get the date from that element:

    $dt = $s['Date'];

We'll need to make sure that the buffer array has an element that matches this date, so let's use isset to see, and if it doesn't, then we'll create an array that contains all of the keys we'll need ('A_Total', 'B_Total', 'C_Total') using the date as the key:

    if(!isset($buffer[$dt])) { 
        $buffer[$dt] = [
            'A_Total' => 0,
            'B_Total' => 0,
            'C_Total' => 0
        ]; 
    }

You didn't say which version of php you're using, and there are multiple ways to check for existence, and the later your version of php the easier they are to use, but isset is going to work for just about any version. ! is just the logical NOT operator, to invert the truth value coming from isset. In plain english, all we are saying is "If not is set" and we specify the buffer element we are checking, and the action to take if the condition evaluates to true is inside the curly braces. If the element IS present (set), then we don't take any action, because all we're doing with this buffer is holding the old values and adding each new value to to old sum.

Now we have a buffer array that has an element that matches our date. All we need to do is write the data from the current element of the source array ($s) to the buffer array, where = is just shorthand for "add this value to whatever is there already". It's why we set the values to 0 in the array initialization:

    $buffer[$dt]['A_Total']  = $s['A_Total'];
    $buffer[$dt]['B_Total']  = $s['B_Total'];
    $buffer[$dt]['C_Total']  = $s['C_Total'];

and after the foreach finishes looping, you'll end up with this:

Array
(
    [2022-10-23] => Array
        (
            [A_Total] => 1109
            [B_Total] => 205
            [C_Total] => 153
        )

    [2022-10-24] => Array
        (
            [A_Total] => 1109
            [B_Total] => 205
            [C_Total] => 153
        )

    [2022-10-25] => Array
        (
            [A_Total] => 1114
            [B_Total] => 205
            [C_Total] => 153
        )

)

But what if you didn't know the keys ahead of time, or later you added some, or you wanted to reuse the code for any set of keys, and you didn't want to have to hard code A_Total, B_Total, etc in every time you made a change? With some slight modifications, it's no problem:

$buffer = [];

foreach($source as $s) {
    
    $dt = $s['Date'];
    if(!isset($buffer[$dt])) { $buffer[$dt] = []; }
    unset($s['Date']);
    
    foreach($s as $key=>$val) {
        if(!isset($buffer[$dt][$key])) { $buffer[$dt][$key] = 0; }
        $buffer[$dt][$key]  = $val;
    }
    
}

echo "<pre>"; print_r($buffer); echo "</pre>"; exit;

So, again we have the creation of a buffer array, and then we use foreach to loop over the source array, with each element assigned to $s inside the loop. That's all the same. And extracting the date and checking for the existence of the key in the buffer array is also the same. But this time, when we initialize each element in the buffer array, instead of hardcoding the keys and values, we just assign an empty array [] to the element.

    if(!isset($buffer[$dt])) { $buffer[$dt] = []; }

and then we can use a variation of foreach to access both the key name and the value of each element by passing it two variable names instead of one:

    foreach($s as $key=>$val) {

Now, inside each loop I'll have both the key name (A_Total, for instance) and the value (547), and I can just add the value the same way, with =. However, in this case, since the buffer element was only an empty array, with no keys, we'll have to do a little work to make sure that the buffer element has a key that matches our current loop. It's just like we did before, with isset, except now we're checking to see if A_Total exists in the buffer for this date, and if not, we'll create it and set it to 0:

    if(!isset($buffer[$dt][$key])) { $buffer[$dt][$key] = 0; }

before adding the current value to it:

    $buffer[$dt][$key]  = $val;

Now, I did skip one thing I did. The element of the source array that we're working with ($s) also includes the date as one of its elements, so iterating it with foreach($s as $key=>$val) is going to include that element. To avoid that, I unset the date element after I extracted it:

    unset($s['Date']);

And that's it. It would work for any set of keys now, and any set of dates, whether you know them ahead of time or not. There's a few ways to get the same result, using array_map or array_reduce. PHP has a lot of array handling methods, so if you're going to spend any time working in it, you should check them out. For instance, I notice that all your keys are the same, and 'Date' is always the first one. So I could get an array of all the values EXCEPT that one using array_slice, where I'm passing the first element of the source array ($source[0]) and telling it to return everything starting at element 1 (arrays are 0 indexed, so I'm telling it to skip the first one):

$empties = array_slice($source[0], 1);
echo "<pre>"; print_r($empties); echo "</pre>"; exit;

which would give you this:

Array
(
    [A_Total] => 547
    [B_Total] => 70
    [C_Total] => 83
)

You could take that same thing, and extract the keys from it using array_keys and pass that to array_fill_keys:

$empties = array_fill_keys(array_keys(array_slice($source[0], 1)), 0);
echo "<pre>"; print_r($empties); echo "</pre>"; exit;

and end up with this:

Array
(
    [A_Total] => 0
    [B_Total] => 0
    [C_Total] => 0
)

which you'll notice is the same array that we hard-coded in the very first example. You could also extract the dates from the source data like so:

$buffer = array_column($source, 'Date');
echo "<pre>"; print_r($buffer); echo "</pre>"; exit;
Array
(
    [0] => 2022-10-23
    [1] => 2022-10-24
    [2] => 2022-10-25
    [3] => 2022-10-23
    [4] => 2022-10-24
    [5] => 2022-10-25
    [6] => 2022-10-23
    [7] => 2022-10-24
    [8] => 2022-10-25
)

These aren't unique, but you could either use array_flip so that the values and keys switch places, resulting in an array where the keys are the unique dates, OR you could take that array we constructed just a second ago ($empties):

Array
(
    [A_Total] => 0
    [B_Total] => 0
    [C_Total] => 0
)

and use the array_fill_keys trick again to construct the buffer array completely:

$buffer = array_fill_keys(
    array_column($source, 'Date'), 
    $empties
);
echo "<pre>"; print_r($buffer); echo "</pre>"; exit;
Array
(
    [2022-10-23] => Array
        (
            [A_Total] => 0
            [B_Total] => 0
            [C_Total] => 0
        )

    [2022-10-24] => Array
        (
            [A_Total] => 0
            [B_Total] => 0
            [C_Total] => 0
        )

    [2022-10-25] => Array
        (
            [A_Total] => 0
            [B_Total] => 0
            [C_Total] => 0
        )

)

Then you wouldn't need to perform the isset checks, since you know the buffer already has the necessary keys:

$empties = array_fill_keys(array_keys(array_slice($source[0], 1)), 0);

$buffer = array_fill_keys(
    array_column($source, 'Date'), 
    $empties
);

foreach($source as $s) {
    foreach(array_keys($empties) as $k) {
        $buffer[$s['Date']][$k]  = $s[$k];
    }
}

echo "<pre>"; print_r($buffer); echo "</pre>"; exit;

You could create the buffer array entirely in one pass, without needing to construct the intermediary $empties array, but it is useful to have, because $empties has the key names that you'll want to extract from the source array, which is why I iterated the array_keys in this line:

foreach(array_keys($empties) as $k) {

so that I could use $k (the key name, eg: A_Total) with $s to get each value, $s[$k].

Anyway, sorry for the long winded answer. It was probably more than you were asking for. Hope it makes a little sense, and helps. The main takeaway is that if you want to group them by date, just use an array that uses the dates as its keys. It makes everything a lot simpler.

  •  Tags:  
  • php
  • Related