How to sort array by months/weeks from given dataset in PHP-CodePudding

Given a dataset from a database I want to get new arrays in a custom order.

The dataset would look like this:

 $dataset = [
'val1' => ['202201' => '1', '202202' => '2', '202203' => '3', '202206' => '5', '202207' => '4'], 
'val2' => ['202201' => '54', '202202' => '43', '202203' => '12']
]

As you can see val1, val2, etc. are ordered by year and weeknumber which are technically DateTime objects. I am able to output this dataset now to an array ordered by weeks.

But I want to have a second array now, which aggregates these values by month like the following:

$perMonth = [
'2022-01' => ['val1' => '6'], //Month 1 related week values added together
'2022-02' => ['val1' => '9'] //Month 2 related week values added together
]

Do you have any ideas how can I approach this problem? I can't edit the database. Thanks for your help!

CodePudding user response：

You will have to create actual DateTime instance from those strings, so that you can determine what month they actually fall into. Then you are able to create the YYYY-MM key for your $perMonth array from that.

So you loop over your input data, determine the YYYY-MM value for the current item - and sum the values up under that key in your result array.

CodePudding user response：

Based on CBroe's answer here is a possible solution. The string '202201' is converted to '2022W01' with substr_replace, which can be interpreted directly by DateTime.

$dataset = [
'val1' => ['202201' => '1', '202202' => '2', '202203' => '3', '202206' => '5', '202207' => '4'], 
'val2' => ['202201' => '54', '202202' => '43', '202203' => '12']
];

$perMonth = [];
foreach($dataset as $val => $array){
  foreach($array as $yearWeek => $number){
    $keyYearMonth = date_create(substr_replace($yearWeek,'W',4,0))->format('Y-m');
    $perMonth[$keyYearMonth][$val] ??= 0;
    $perMonth[$keyYearMonth][$val]  = $number;
  }
}

var_export($perMonth);

Output:

array (
  '2022-01' => 
  array (
    'val1' => 6,
    'val2' => 109,
  ),
  '2022-02' => 
  array (
    'val1' => 9,
  ),
)