CODEWITHSUNDEEP
phparraysmultidimensional-arraysumgrouping
Noobie
Noobie

Reputation: 99

Group 2d array row data by two columns and keep a running total for each user

I have an array output as follows.

 array:7 [
      0 => array:5 [
        "count" => "6"
        "year" => "2019"
        "month" => "10"
        "name" => "P"
        "id" => 3
      ]
      1 => array:5 [
        "count" => "2"
        "year" => "2019"
        "month" => "10"
        "name" => "s.vimal"
        "id" => 1
      ]
      2 => array:5 [
        "count" => "4"
        "year" => "2019"
        "month" => "10"
        "name" => "MR"
        "id" => 2
      ]
      3 => array:5 [
        "count" => "4"
        "year" => "2019"
        "month" => "11"
        "name" => "vimaltest"
        "id" => 10
      ]
      4 => array:5 [
        "count" => "11"
        "year" => "2019"
        "month" => "11"
        "name" => "s.vimal"
        "id" => 1
      ]
      5 => array:5 [
        "count" => "1"
        "year" => "2019"
        "month" => "12"
        "name" => "MR"
        "id" => 2
      ]
      6 => array:5 [
        "count" => "1"
        "year" => "2019"
        "month" => "12"
        "name" => "vimaltest"
        "id" => 10
      ]
    ]

But I need to create an array which sums up the count of similar name,gradually increasing through each month.

Expected Output.

array:7 [
  0 => array:5 [
    "P"=>"6",
    "s.vimal"=>"2",
    "MR"=>"4"
    "year" => "2019"
    "month" => "10"
  ]
  1 => array:6 [
    "P"=>"6",
    "s.vimal"=>"13",
    "vimaltest"=>"4",
    "MR"=>"4"
    "year" => "2019"
    "month" => "11"

  ]
  2 => array:6 [
    "P"=>"6",
    "s.vimal"=>"13",
    "vimaltest"=>"5",
    "MR"=>"5"
    "year" => "2019"
    "month" => "12"
  ]
]

The Expected Output is a summary of input based on each month.You will get it when you look into the expected output.

Upvotes: 0

Views: 74

Answers (2)

mickmackusa
mickmackusa

Reputation: 47864

You do not need to prepopulate temporary arrays before processing the data. You will need to keep track of two data sets -- the date-grouped result array and also the user-specific totals.

Via one loop with array destructuring syntax to separate row data into convenient variables, identify unique date groups, push unique groups into the result array as references, keep tally of the user-specific amounts as they are encountered, and push the user data into the corresponding reference.

Code: (Demo)

$result = [];
foreach ($array as ['year' => $y, 'month' => $m, 'name' => $n, 'count' => $c]) {
    $group = "$y-$m";
    if (!isset($ref[$group])) {
        $ref[$group] = ['year' => $y, 'month'=> $m] + ($totals ?? []);
        $result[] =& $ref[$group];
    }
    $totals[$n] = ($totals[$n] ?? 0) + $c;
    $ref[$group][$n] = $totals[$n];
}
var_export($result);

Output:

array (
  0 => 
  array (
    'year' => '2019',
    'month' => '10',
    'P' => 6,
    's.vimal' => 2,
    'MR' => 4,
  ),
  1 => 
  array (
    'year' => '2019',
    'month' => '11',
    'P' => 6,
    's.vimal' => 13,
    'MR' => 4,
    'vimaltest' => 4,
  ),
  2 => 
  array (
    'year' => '2019',
    'month' => '12',
    'P' => 6,
    's.vimal' => 13,
    'MR' => 5,
    'vimaltest' => 5,
  ),
)

Upvotes: 0

Aksen P
Aksen P

Reputation: 4599

Firstly you can collect unique values such as year and months:

$mon_ar = array_unique(array_column($ar,'month'));
$year_ar = array_unique(array_column($ar,'year'));

Then you can loop your data array with next foreach loop:

foreach($ar as $rec){                       // each record
    foreach($year_ar as $year){             // each year
        foreach($mon_ar as $month){         // each month

            // if this is a new 'year.month' (201910,201911...)
            if (!isset($result[$year.$month])) 
            $result[$year.$month] = [ 'month' => $month, 'year' => $year];        

            // if month equal to current or it's previous month
            if ($rec['year'] == $year && $rec['month'] <= $month) { 
                // if this is a new 'name' value
                if (!isset($result[$year.$month][$rec['name']])) 
                $result[$year.$month][$rec['name']] = 0;
                // summing count to the existing 'name' value 
                $result[$year.$month][$rec['name']] += $rec['count']; 
            }
        }
    }
}

Outputs will be like:

Array
(
    [201910] => Array
        (
            [month] => 10
            [year] => 2019
            [P] => 6
            [s.vimal] => 2
            [MR] => 4
        )

    [201911] => Array
        (
            [month] => 11
            [year] => 2019
            [P] => 6
            [s.vimal] => 13
            [MR] => 4
            [vimaltest] => 4
        )

    [201912] => Array
        (
            [month] => 12
            [year] => 2019
            [P] => 6
            [s.vimal] => 13
            [MR] => 5
            [vimaltest] => 5
        )

)

Now you can use sort() for re-writing indexes to 0,1,2,...

sort($result);

Demo

Upvotes: 1

Related Questions