counting row values with different ids in multidimensional array-CodePudding

I know there are a lot of topics about this, but just couldn't find any that would actually help me solve the problem. Ok so i am having a problem with counting rows and values in multidimensional array (imported via .csv)

I am trying to achieve something like...

Array ( [0] => Array ( [0] => 1 [1] => 6278,31 [2] => 4) 
        [1] => Array ( [0] => 2 [1] => 2,0 [2] => 2) 
        [2] => Array ( [0] => 3 [1] => 3,01 [2] => 3) 
         )

where [0] would be buyer id, 1 is total number of buys and [2] total cost.

My current code:

if(($s_open = fopen("sales.csv", "r")) !== FALSE) 
    {
        $s_count = count(file("sales.csv"));
        while(($sales = fgetcsv($s_open, 100, ";")) !== FALSE) 
        {
            ...
        }
        fclose($s_open);
    }

Thanks in advance.

CodePudding user response：

You could group your data by creating an array with the buyer_id as index.

Each time you find a new buyer, you could create a small array ('id','count','amount').

Then, sum the values of the current row into that array.

const KEY_ID     = 0; // shortcut for array indexes
const KEY_AMOUNT = 1;
const KEY_COUNT  = 2;


$buyers = []; // final array

if (($s_open = fopen("sales.csv", "r")) !== FALSE) 
{
    $s_count = count(file("sales.csv"));
    while(($sales = fgetcsv($s_open, 100, ";")) !== FALSE) 
    {
        // Extract data from row:
        [$id, $buyer_id, , $amount] = $sales;
        
        // If the buyer doesn't exists in the array,
        // create a new array with required format:
        if (!isset($buyers[$buyer_id])) {
            $buyers[$buyer_id] = [
                KEY_ID     => $buyer_id, 
                KEY_COUNT  => 0, 
                KEY_AMOUNT => 0,
            ];
        }
        
        // Update values:
        $buyers[$buyer_id][KEY_COUNT]  ;
        $buyers[$buyer_id][KEY_AMOUNT]  = $amount;
    }
    fclose($s_open);
}

// Re-index values:
$buyers = array_values($buyers);

CodePudding user response：

Using this mock data in.csv:

id, buyer_id, amount
1, 1, 100.55
2, 1, 500.1
3, 2, 50.55
4, 3, 1.0

I wrote the following PHP to parse it into a nicely formatted array (if you don't want to have named keys, you can just remove the header logic):

<?php

// Each line into an array
$csv = file(__DIR__ . DIRECTORY_SEPARATOR . 'in.csv');

// First line is the header
$header = str_getcsv(array_shift($csv));

// When the CSV header is `id, buyer_id, amount`, remove the whitespace
$header = array_map(function ($item) {
    return trim($item);
}, $header);

// Get the CSV data as an array of arrays
$out = array_map(function ($line) use ($header) {
    $data = str_getcsv($line);
    return array_combine($header, $data);
}, $csv);

print_r($out);

Which prints the following to your screen:

Array
(
    [0] => Array
        (
            [id] => 1
            [buyer_id] =>  1
            [amount] =>  100.55
        )

    [1] => Array
        (
            [id] => 2
            [buyer_id] =>  1
            [amount] =>  500.1
        )

    [2] => Array
        (
            [id] => 3
            [buyer_id] =>  2
            [amount] =>  50.55
        )

    [3] => Array
        (
            [id] => 4
            [buyer_id] =>  3
            [amount] =>  1.0
        )

)

CodePudding user response：

Try this

$data = [];
if(($s_open = fopen("sales.csv", "r")) !== FALSE) {
    $s_count = count(file("sales.csv"));
    while( ([$id, $buyer_id, $amount] = fgetcsv($s_open, 100, ";")) !== FALSE ){
        if( $id !== 'id' ){
            $data[$buyer_id] = [
                $buyer_id,
                ($data[$buyer_id][1] ?? 0)   1,
                ($data[$buyer_id][2] ?? 0)   $amount
            ];
        }
    }
    fclose($s_open);
    $data = array_values($data);
}
// print_r($data);