Group rows of data by one column and sum several columns within each group

Question

I have a user upload a .csv file from the backend of Wordpress through a custom file upload field (Advanced custom fields). I then call this field to retrieve the data from that file to create an array of said data:

$upload_cq = get_field('report_current_quarter');
$report_cq = array();
if(($handle_cq = fopen($upload_cq, "r")) !== FALSE)
{
    while(($data_cq = fgetcsv($handle_cq, 1000, ",")) !== FALSE)
    {
        $report_cq[] = $data_cq;
    }
    $results_cq = array_slice($report_cq, 3); // remove unwanted row
    fclose($handle_cq);
}

Once I create the array, I then create another array with a key and associate value, and ensure that the "team_id" column is present & not empty, as so (there is a teamName function which I won't get into):

foreach($results_cq as $results)
{
    $team_id = $results[6];
    if(!empty($team_id))
    {
        $team_totals_cq[] = array(
            'id' => $team_id,
            'team' => teamName($team_id),
            'total_volume' => $results[41],
            'total_closed' => $results[24],
            'listings_closed' => $results[22],
            'buyers_closed' => $results[23],
            'total_agc' => $results[29],
            'rental_agc' => $results[30],
        );
    }
}
echo '<pre>'; print_r($team_totals_cq); echo '</pre>';

When printing the array, I get the following. My question now is; How do I group results with the same team "id" and add up their results (results = total_volume, total_closed, listings_closed, buyers_closed, total_agc, and rental_agc):

Array
(
    ...

    [6] => Array
        (
            [id] => 0011
            [team] => Williamson Team
            [total_volume] => $990,000
            [total_closed] => 4
            [listings_closed] => $0.00
            [buyers_closed] => 1
            [total_agc] => $20,812.50
            [rental_agc] => $23,812.50
        )

    ...

    [9] => Array
        (
            [id] => 0011
            [team] => Williamson Team
            [total_volume] => $415,000
            [total_closed] => 2
            [listings_closed] => $0.00
            [buyers_closed] => 0
            [total_agc] => $12,450.00
            [rental_agc] => $12,450.00
    )

    ...
)

I have tried everything within my abilities, and nothing has worked.

https://stackoverflow.com/a/51997986/2943403 and https://stackoverflow.com/a/45495722/2943403 and https://stackoverflow.com/a/13284149/2943403 — mickmackusa, Sep 12 '18 at 03:05
I feel this question is Too Broad because you have not shown any proof of research nor attempted to self-solve. I perceive this question as a task request; however, SO is not a free code writing service. Please do more before posting a question. — mickmackusa, Sep 12 '18 at 03:08
https://stackoverflow.com/q/24766301/2943403 and https://stackoverflow.com/a/45526667/2943403 — mickmackusa, Sep 12 '18 at 03:39

Jacob Mulquin · Accepted Answer · 2018-09-12T03:05:27.607

One method is to use team_id as keys in $team_totals_cq then simply add up the values as you go through the loop.

(Note: The preg_replace function strips anything that aren't numbers or .)

$team_totals_cq = [];

foreach ($results_cq as $results) {
    $team_id = $results[6];

    if (!empty($team_id)) {
        if (!isset($team_totals_cq[$team_id])) {
            $team_totals_cq[$team_id] = [
                'id' => $team_id,
                'team' => teamName($team_id),
                'total_volume' => preg_replace("/[^0-9\.]/", '', $results[41]),
                'total_closed' => $results[24],
                'listings_closed' => preg_replace("/[^0-9\.]/", '', $results[22]),
                'buyers_closed' => $results[23],
                'total_agc' => preg_replace("/[^0-9\.]/", '', $results[29]),
                'rental_agc' => preg_replace("/[^0-9\.]/", '', $results[30])
            ];
        } else {
            $team_totals_cq[$team_id]['total_volume'] += preg_replace("/[^0-9\.]/", '', $results[41]);
            $team_totals_cq[$team_id]['total_closed'] += $results[24];
            $team_totals_cq[$team_id]['listings_closed'] += preg_replace("/[^0-9\.]/", '', $results[22]); 
            $team_totals_cq[$team_id]['buyers_closed'] += $result[23];
            $team_totals_cq[$team_id]['total_agc'] += preg_replace("/[^0-9\.]/", '', $results[29]);
            $team_totals_cq[$team_id]['rental_agc'] += preg_replace("/[^0-9\.]/", '', $results[30]);
        }
    }
}

Ah ok, I'm not sure I was putting `+` after the character class. Yeah I'm not going to test code in answers like this, it's more to show the theory of how it works. — Jacob Mulquin, Sep 12 '18 at 03:06
`str_replace(['$', ','], '', '$formatted_string');` would be a lighter call for this case. The values have a predictable format so regex is overkill. — mickmackusa, Sep 12 '18 at 03:23

Group rows of data by one column and sum several columns within each group

1 Answers1