CODEWITHSUNDEEP
phparraysmultidimensional-arraypivotgrouping
kroseva
kroseva

Reputation: 311

Pivot 2d array data by two columns and use zero default values for any gaps in data

This is the result of array after I build it using the array_push() function from mssql result.

Array
(
    [0] => Array
        (
            [STICKER] => FALCON
            [MONTH] => 1
            [JUM] => 65826210.00
        )

    [1] => Array
        (
            [STICKER] => FALCON
            [MONTH] => 2
            [JUM] => 68070573.00
        )

    [2] => Array
        (
            [STICKER] => FALCON
            [MONTH] => 3
            [JUM] => 99053067.60
        )

    [3] => 
    [4] => Array
        (
            [STICKER] => HRD
            [MONTH] => 2
            [JUM] => 1521400.00
        )

    [5] => Array
        (
            [STICKER] => HRD
            [MONTH] => 3
            [JUM] => 2093200.00
        )
)

I need to convert array above into this structure:

Array
(
    [0] => Array
        (
            [0] => 
            [1] => 1
            [2] => 2
            [3] => 3
        )
    [1] => Array
        (
            [0] => FALCON
            [1] => 65826210.00
            [2] => 68070573.00
            [3] => 99053067.60
        )
    [2] => Array
        (
            [0] => HRD
            [1] => 0
            [2] => 1521400.00
            [3] => 2093200.00
        )
)

Note: Array[0] values would be 1,2,3 (this is actualy month, i just input up to 3 in order to get the code not too long. but it will be up to 12 (Jan - Dec)).

If from original array, there is none value (example from array[3]), then it will be convert to new array[2]->[1] with value 0.

Upvotes: 1

Views: 82

Answers (3)

mickmackusa
mickmackusa

Reputation: 48071

Aside from using array_column() to source the unique month numbers from the entire payload, this pivot task can be performed with a single, non-nested loop.

The empty subarrays are conditionally discarded. Reference arrays are used to directly fill the result array with without temporary keys. When a sticker is encountered for the first time, it is pre-filled with zero default values for all months which are potentially overwritten later. The below is designed to "add" the encountered JUM value to the pre-filled zero, but += can be replaced with = if strict overwriting is desired. Demo

$months = array_column($array, 'MONTH', 'MONTH');
ksort($months);
$pivot = [[''] + $months];
foreach ($array as $row) {
    if (!$row) {
        continue;
    }
    ['STICKER' => $s, 'MONTH' => $m, 'JUM' => $j] = $row;
    if (!isset($ref[$s])) {
        $ref[$s] = [$s] + array_fill_keys($months, 0);
        $pivot[] =& $ref[$s];
    }
    $ref[$s][$m] += $j;
}
var_export($pivot);

Upvotes: 0

Sathish
Sathish

Reputation: 2460

Try this,

If any month is not mentioned from stickers the jum considered as 0,

$array = array(
    array('STICKER' => 'FALCON', 'MONTH' => 1, 'JUM' => 65826210.00),
    array('STICKER' => 'FALCON', 'MONTH' => 2, 'JUM' => 68070573.00),
    array('STICKER' => 'FALCON', 'MONTH' => 3, 'JUM' => 99053067.60),
    array(),
    array('STICKER' => 'HRD', 'MONTH' => 2, 'JUM' => 1521400.00),
    array('STICKER' => 'HRD', 'MONTH' => 3, 'JUM' => 2093200.00),
);

$result[0][] = '';

foreach ($array as $key => $res) {
    if (!empty($res)) {
        $result[0][$res['MONTH']] = $res['MONTH'];
        $result1[$res['STICKER']][$res['MONTH']] = $res['JUM'];
    }
}
foreach ($result1 as $key => $res) {
    $fin = array();
    foreach ($res as $key1 => $re) {
        foreach ($result[0] as $key2 => $month) {
            if ($month != '') {
                if (array_key_exists($month, $res)) {
                    if (!array_key_exists($month, $fin) && $month == $key1) {
                        $fin[$month] = $re;
                    }
                } else {
                    $fin[$month] = 0;
                }
            }
        }
    }
    $result[] = array_merge(array($key), $fin);
}
echo'<pre>';
print_r($result);
echo'<pre>';

Result:

Array
(
    [0] => Array
        (
            [0] => 
            [1] => 1
            [2] => 2
            [3] => 3
        )

    [1] => Array
        (
            [0] => FALCON
            [1] => 65826210
            [2] => 68070573
            [3] => 99053067.6
        )

    [2] => Array
        (
            [0] => HRD
            [1] => 0
            [2] => 1521400
            [3] => 2093200
        )

)

I hope this is used to achieve your output(you mentioned in above question)!!

Upvotes: 1

knetsi
knetsi

Reputation: 1631

try something like this not the issue with the empty array i could not understand how the program would know that it is STICKER HRD so it will be filled with zero.But you could later check if every month "isset" and if it is not act as it is zero

$arr1=array(
 array('STICKER'=>'FALCON','MONTH'=>1,'JUM'=>65826210.00),
 array('STICKER'=>'FALCON','MONTH'=>2,'JUM'=>68070573.00),
 array('STICKER'=>'FALCON','MONTH'=>3,'JUM'=>99053067.60),
 array(),
 array('STICKER'=>'HRD','MONTH'=>2,'JUM'=>1521400.00),
 array('STICKER'=>'HRD','MONTH'=>3,'JUM'=>2093200.00),
);

$arr2=array();
$arr3=array();

$arr2[0][0]="";
foreach($arr1 as $key => $val){
 if(!empty($val)){
    $arr2[0][$val['MONTH']]=$val['MONTH'];

    //group each STICKER
    $arr3[$val['STICKER']][]=$val['JUM'];
 }
}

//transfer the grouped data to arr2
foreach($arr3 as $key => $val){

 $tmp_arr=array($key);

  $arr2[]=array_merge($tmp_arr,$val);
}


print_r($arr2);

Upvotes: 0

Related Questions