PHP 5.6 Trying to Sum assoc array columns by grouping one column

Question

I have a csv that I am bringing in as an array and trying to display grouped table data based on the ad type column.

My ad type column has 8,000+ entries, however only 3 - 5 different ad types.

The code below puts all 8,000+ records in the correct table format.

Can someone assist me in getting the impressions, clicks and cost to sum based on the unique list of ad types?

public function get_awdapi_adsType($mcc){
    ini_set("memory_limit","256M"); //increases the memory for large data processing
    global $tableView;

    $csv = array_map('str_getcsv', file('../../clients/client_reports/ads/' . $mcc . '_LAST_6_MONTHS___ad.csv'));
    array_walk($csv, function(&$a) use ($csv) {
      $a = array_combine($csv[0], $a);
    });
    array_shift($csv); # remove column header

        foreach($csv as $key){ 
            //trying to sum impressions based unique list of ad types
            $adType = $key['Ad type'];

            $impressions = $key['Impressions'];
            $clicks = $key['Clicks'];
            $cost = $key['Cost'];

            $adTypeData[] = '<tr><td>' . $adType . '</td><td>' . $impressions . '</td><td>' . $clicks . '</td><td>' . $cost . '</td></tr>';         

        }

        $tableView = implode('', $adTypeData);
        return $tableView;
}

ADDED array output (trying to sum [Impressions, Clicks, Cost] grouped by [Ad type])

Array ( [0] => Array ( [Month] => 2017-01-01 [Ad group] => Private Lenders [Ad group state] => ENABLED [Ad type] => TEXT_AD [Campaign] => RCN CT [Campaign state] => Status_Active [Final URL] => ["http://www.rcncapital.com"] [Criteria Type] => KEYWORD [Description] => -- [Description line 1] => Private Direct Lender. $50k Minimum [Description line 2] => Real Estate Loans. Apply Today! [Display URL] => www.RCNCapital.com [Ad] => CT Private Money Lenders [Headline 1] => -- [Headline 2] => -- [Ad ID] => 45005996629 [Ad state] => Status_Enabled [All conv. rate] => 0.00% [All conv.] => 0.00 [All conv. value] => 0.00 [Avg. Cost] => 0 [Avg. CPC] => 0 [Avg. CPM] => 0 [Avg. CPV] => 0 [Avg. position] => 2.0 [Clicks] => 0 [Conv. rate] => 0.00% [Conversions] => 0.00 [Total conv. value] => 0.00 [Cost] => 0 [Cost / conv.] => 0 [CTR] => 0.00% [Impressions] => 1 [Interaction Rate] => 0.00% [Interactions] => 0 [Interaction Types] => -- [View rate] => 0.00% [Views] => 0 ) [1] => Array ( [Month] => 2017-03-01 [Ad group] => Hard Money, Loan + Lender [Ad group state] => ENABLED [Ad type] => EXPANDED_AD [Campaign] => RCN - Hard Money, s - NY [Campaign state] => Status_Active [Final URL] => ["http://www.rcncapital.com/?utm_source=google&utm_medium=cpc&utm_campaign=RCN%20-%20Hard%20Money%2C%20s%20-%20NY"] [Criteria Type] => KEYWORD [Description] => -- [Description line 1] => Proven Direct Lender. $50k - $2.5M+ [Description line 2] => Quick Process. Apply Online Today. [Display URL] => rcncapital.com/hard-money-loans [Ad] => NY's #1 Hard Money Lender [Headline 1] => -- [Headline 2] => -- [Ad ID] => 48352191589 [Ad state] => Status_Paused [All conv. rate] => 0.00% [All conv.] => 0.00 [All conv. value] => 0.00 [Avg. Cost] => 0 [Avg. CPC] => 0 [Avg. CPM] => 0 [Avg. CPV] => 0 [Avg. position] => 1.5 [Clicks] => 0 [Conv. rate] => 0.00% [Conversions] => 0.00 [Total conv. value] => 0.00 [Cost] => 0 [Cost / conv.] => 0 [CTR] => 0.00% [Impressions] => 2 [Interaction Rate] => 0.00% [Interactions] => 0 [Interaction Types] => -- [View rate] => 0.00% [Views] => 0 ) [2] => Array...

Could I see a partial output of var dump on the $csv produced at line 5? — Altimus Prime, Jun 22 '17 at 14:07

Altimus Prime · Answer 1 · 2017-06-22T16:33:21.323

Usually a database is better for this kind of thing, but in php you could:

foreach($csv as $value){
   @$newarray[$value['adType']]['Impressions'] += $value['Impressions'];
   @$newarray[$value['adType']]['Clicks'] += $value['Clicks'];
   @$newarray[$value['adType']]['Cost'] += $value['Cost'];
}

$newarray will now be keyed by adType with the values as the sum of each of your other parameters

foreach($newarray as $key=>$value){
   $adTypeData[] = '<tr><td>' . $key. '</td><td>' . $value['Impressions'] . '</td><td>' . $value['Clicks']. '</td><td>' . $value['Cost']. '</td></tr>';         
}

I think the right way is to load the csv file into a databse which is made for this kind of query, especially if you may ever have to do this again. In mysql you do that by:

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES

mysql credit here

You would then simple write a query that looks like:

Select `adType`, sum(`Impressions`), sum(`clicks`), sum(`Costs`) from `your_ads_report_table` group by `adType`

PHP 5.6 Trying to Sum assoc array columns by grouping one column

1 Answers1