How to Aggregate Values from CSV file

[RobertAmsterdam]

Hello,

 

I have a CSV file that looks like this:

"Account","State","Zip","Email","Birth Date","Favorites","Balance"

with data rows in them. I need to calculate total balances aggregated by state, need to get output in .txt flle like:

State, Balance
value, value

this is how I started :

$inputfile = 'input.csv';
$inputHandle = fopen($inputfile, "r");
$output = array();
while (($data = fgetcsv($inputHandle, 1024, ",")) !== FALSE){

foreach($data as $line) {
    list($state, $balance) =  $data; 
    
    $state = trim($state);
    
    $balance = trim($balance);
    
    $output[$state] += (int)$balance;
    
    file_put_contents('summary.txt', var_export($output, TRUE));
    
 }
}

I get unidentified inxex errors on

 

$output[$state] += (int)$count;

 

May be i make some mistake in line:  

 

list($state, $balance) =  $data; 

    

 

 

Could someone maybe propose a better, more convinient solution here?

 

 

 

 

 

[ginerjm]

You can't do a '+=' operation on something that hasn't been created yet.

You should also check that balance is not null before trying to  include it.

 

You should re-think your algorithm.  You are outputting your file on each and every pass thru the loop.  Wasteful.  Do it after your loop completes.

[Barand]

With that csv file

 

list($state, $balance) =  $data;

 

will put Account and State into $state and $balance.

 

try

list(,$state,,,,,$balance) = $data;

[RobertAmsterdam]

 

With that csv file

 

list($state, $balance) =  $data;

 

will put Account and State into $state and $balance.

 

try

list(,$state,,,,,$balance) = $data;

 

There is like 12 rows, should I list each and every one of them, when I need only 2?

[RobertAmsterdam]

There is like 12 rows, should I list each and every one of them, when I need only 2?

 

I could do like this:

$inputfile = 'input.csv';
$inputHandle = fopen($inputfile, "r");
$output = array();
while (($data = fgetcsv($inputHandle, 1024, ",")) !== FALSE){
    
   unset($data[0],$data[1],$data[2], $data[3], $data[4], $data[6], $data[7], $data[8], $data[9], $data[10], $data[11] );  // remove redundant fields
    
   $data = array_values($data);  // restore keys
}
foreach($data as $key => $line) {
    
    list($state, $balance) =  $line;
    $state = trim($state);
    $balance = trim($balance);
    $output[$state] += (int)$balance;
    
    //file_put_contents('summary.txt', var_export($output, TRUE));
  }


  file_put_contents('summary.txt', var_export($output, TRUE));

but i get  Invalid argument supplied for foreach() 

for some reason

[RobertAmsterdam]

$state = $dataRow[5];
    $balance =  $dataRow[12];
    if (!isset($sumArray[$state])) {
        $sumArray[$state] = 0; 
    }
    $sumArray[$state] += $balance; // add balance for state

was solution

Edited July 3, 2013 by RobertAmsterdam