CODEWITHSUNDEEP
phparraysmultidimensional-arraygroupingdefault-value
fightstarr20
fightstarr20

Reputation: 12568

Group 2d array data by a column and apply default values where a date key is missing

I have an array that looks like this...

[
    ['code' => 'AS34', '2014-12-10' => 32],
    ['code' => 'AS34', '2014-12-11' => 42],
    ['code' => 'AS34', '2014-12-12' => 40],
    ['code' => 'AS34', '2014-12-15' => 44],
    ['code' => 'AH98', '2014-12-10' => 1],
    ['code' => 'AT78', '2014-12-12' => 1],
    ['code' => 'AL44', '2014-12-10' => 23],
    ['code' => 'AL44', '2014-12-11' => 27],
    ['code' => 'AL44', '2014-12-13' => 25],
    ['code' => 'AL44', '2014-12-15' => 26],
]

I am trying to turn it into an array that looks like this...

$example_data = array(
    array(
        'ID'       => 1,
        'code'     => 'AS34',
        '09/12/14' => '0',
        '10/12/14' => '32',
        '11/12/14' => '42',
        '12/12/14' => '40',
        '13/12/14' => '0',
        '14/12/14' => '0',
        '15/12/14' => '44',
    ),
    array(
        'ID'       => 2,
        'code'     => 'AH98',
        '09/12/14' => '0',
        '10/12/14' => '1',
        '11/12/14' => '0',
        '12/12/14' => '0',
        '13/12/14' => '0',
        '14/12/14' => '0',
        '15/12/14' => '0',
    ),
    array(
        'ID'       => 3,
        'code'     => 'AT78',
        '09/12/14' => '0',
        '10/12/14' => '0',
        '11/12/14' => '0',
        '12/12/14' => '1',
        '13/12/14' => '0',
        '14/12/14' => '0',
        '15/12/14' => '0',
    ),
    array(
        'ID'       => 4,
        'code'     => 'AL44',
        '09/12/14' => '0',
        '10/12/14' => '23',
        '11/12/14' => '27',
        '12/12/14' => '0',
        '13/12/14' => '25',
        '14/12/14' => '0',
        '15/12/14' => '26',
    ),
);

So basically it sets up an array for each 'code' and then the previous 7 days.

Upvotes: 0

Views: 97

Answers (4)

mickmackusa
mickmackusa

Reputation: 47864

Because there is no way to know what range of dates should be included in the result, it will require two loops to isolate all of the unique dates AND apply them to all groups.

  • Create an array of dates with default values.
  • Sort that array.
  • Then iterate the rows to group them into reference variables to be pushed into the result array.
  • when a code is re-encountered, you only need to update the date's value in the reference variable.

Code: (Demo)

$defaults = array_reduce(
    $array,
    fn($acc, $row) => $acc + [array_key_last($row) => 0],
    []
);
ksort($defaults);

$result = [];
foreach ($array as $row) {
    if (!isset($ref[$row['code']])) {
        $ref[$row['code']] = [
            'ID' => count($result) + 1,
            'code' => $row['code'],
        ] + $defaults;
        $result[] =& $ref[$row['code']];
    }
    $ref[$row['code']] = array_merge($ref[$row['code']], $row);
}
var_export($result);

Output:

array (
  0 => 
  array (
    'ID' => 1,
    'code' => 'AS34',
    '2014-12-10' => 32,
    '2014-12-11' => 42,
    '2014-12-12' => 40,
    '2014-12-13' => 0,
    '2014-12-15' => 44,
  ),
  1 => 
  array (
    'ID' => 2,
    'code' => 'AH98',
    '2014-12-10' => 1,
    '2014-12-11' => 0,
    '2014-12-12' => 0,
    '2014-12-13' => 0,
    '2014-12-15' => 0,
  ),
  2 => 
  array (
    'ID' => 3,
    'code' => 'AT78',
    '2014-12-10' => 0,
    '2014-12-11' => 0,
    '2014-12-12' => 1,
    '2014-12-13' => 0,
    '2014-12-15' => 0,
  ),
  3 => 
  array (
    'ID' => 4,
    'code' => 'AL44',
    '2014-12-10' => 23,
    '2014-12-11' => 27,
    '2014-12-12' => 0,
    '2014-12-13' => 25,
    '2014-12-15' => 26,
  ),
)

Upvotes: 0

Vladimir Vukanac
Vladimir Vukanac

Reputation: 984

Something like (fixed):

$example_data = array();
foreach($data as $id => $row) {
    $code = $row['code'];
    unset($row['code']);

    $karr = array_keys($row);
    $date = current($karr);

    $example_data[$code]['ID'] = $id;
    $example_data[$code]['code'] = $code;
    $example_data[$code][$date] = $row[$date];
}
print_r($example_data);

Test online

Upvotes: 1

weinerk
weinerk

Reputation: 522

I got this working:

<?php
header ('Content-type: text/plain; charset=utf-8');

//source data from the question
$sourceArr = array(
  array('id'            => 1
        ,'code'         => 'AS34'
        ,'2014-12-10'   => '32'
  )
  ,array('id'           => 1
         ,'code'        => 'AS34'
         ,'2014-12-11'  => '42'
  )
  ,array('id'           => 1
         ,'code'        => 'AS34'
         ,'2014-12-12'  => '40'
  )
  ,array('id'           => 1
         ,'code'        => 'AS34'
         ,'2014-12-15'  => '44'
  )
  ,array('id'           => 1
         ,'code'        => 'AH98'
         ,'2014-12-10'  => '1'
  )
  ,array('id'           => 1
         ,'code'        => 'AT78'
         ,'2014-12-12'  => '1'
  )
  ,array('id'           => 1
         ,'code'        => 'AL44'
         ,'2014-12-10'  => '23'
  )
  ,array('id'           => 1
         ,'code'        => 'AL44'
         ,'2014-12-11'  => '27'
  )
  ,array('id'           => 1
         ,'code'        => 'AL44'
         ,'2014-12-13'  => '25'
  )
  ,array('id'           => 1
         ,'code'        => 'AL44'
         ,'2014-12-15'  => '26'
  )
);

foreach($sourceArr as $k => $v)
{
  //get dates for last seven days
  $d0 = date('Y-m-d',time() - 60 * 60 * 24 * 0);
  $d1 = date('Y-m-d',time() - 60 * 60 * 24 * 1);
  $d2 = date('Y-m-d',time() - 60 * 60 * 24 * 2);
  $d3 = date('Y-m-d',time() - 60 * 60 * 24 * 3);
  $d4 = date('Y-m-d',time() - 60 * 60 * 24 * 4);
  $d5 = date('Y-m-d',time() - 60 * 60 * 24 * 5);
  $d6 = date('Y-m-d',time() - 60 * 60 * 24 * 6);

  //if we have a valid element - then save it in temp array
  if(array_key_exists($d0,$v)){ $tmpArr[$v['code']][$d0] = "'$v[$d0]'"; }
  if(array_key_exists($d1,$v)){ $tmpArr[$v['code']][$d1] = "'$v[$d1]'"; }
  if(array_key_exists($d2,$v)){ $tmpArr[$v['code']][$d2] = "'$v[$d2]'"; }
  if(array_key_exists($d3,$v)){ $tmpArr[$v['code']][$d3] = "'$v[$d3]'"; }
  if(array_key_exists($d4,$v)){ $tmpArr[$v['code']][$d4] = "'$v[$d4]'"; }
  if(array_key_exists($d5,$v)){ $tmpArr[$v['code']][$d5] = "'$v[$d5]'"; }
  if(array_key_exists($d6,$v)){ $tmpArr[$v['code']][$d6] = "'$v[$d6]'"; }

}

//create the result array
$cnt = 0;
foreach($tmpArr as $k => $v)
{
  $resultArr[$cnt]['ID'] = $cnt;
  $resultArr[$cnt]['code'] = "'$k'";
  asort($v);
  foreach($v as $k2 => $v2)
  {
    $resultArr[$cnt][$k2] = $v2;
  }
  $cnt++;
}

//output
echo "Old array: ".print_r($sourceArr,1);
echo "New array: ".print_r($resultArr,1);

?>

Upvotes: 0

Verhaeren
Verhaeren

Reputation: 1661

I figured this:

$data = Array
(
    0 => Array
        (
            "id" => 1,
            "code" => "AS34",
            "2014-12-10" => 32
        ),

    1 => Array
        (
            "id" => 1,
            "code" => "AS34",
            "2014-12-11" => 42
        ),

    2 => Array
        (
            "id" => 1,
            "code" => "AS34",
            "2014-12-12" => 40
        ),

    3 => Array
        (
            "id" => 1,
            "code" => "AS34",
            "2014-12-15" => 44
        ),

    4 => Array
        (
            "id" => 1,
            "code" => "AH98",
            "2014-12-10" => 1
        ),

    5 => Array
        (
            "id" => 1,
            "code" => "AT78",
            "2014-12-12" => 1
        ),

    6 => Array
        (
            "id" => 1,
            "code" => "AL44",
            "2014-12-10" => 23
        ),

    7 => Array
        (
            "id" => 1,
            "code" => "AL44",
            "2014-12-11" => 27
        ),

    8 => Array
        (
            "id" => 1,
            "code" => "AL44",
            "2014-12-13" => 25
        ),

    9 => Array
        (
            "id" => 1,
            "code" => "AL44",
            "2014-12-15" => 26
        )
);

function in_array_r($needle, $haystack, $strict = false) { //taken from http://stackoverflow.com/a/4128377/4263082

    foreach ($haystack as $item) {

        if (($strict ? $item === $needle : $item == $needle) || (is_array($item) && in_array_r($needle, $item, $strict))) {
            return true;
        }

    }

    return false;
}

$codes = Array();
$counter = -1;

foreach($data as $key => $value) {

    if(!in_array_r($data[$key]["code"], $codes, true)) {

        $codes[++$counter] = Array();

        foreach($data[$key] as $subkey => $subvalue) {$codes[$counter][$subkey] = $subvalue;}

    }
    else{

        foreach($data[$key] as $subkey => $subvalue) {

            if($subkey != "code" && $subkey != "id") {$codes[$counter][$subkey] = $subvalue;}

        }

    }

}

print_r($codes);

OUTPUT

Array
(
    [0] => Array
        (
            [id] => 1
            [code] => AS34
            [2014-12-10] => 32
            [2014-12-11] => 42
            [2014-12-12] => 40
            [2014-12-15] => 44
        )

    [1] => Array
        (
            [id] => 1
            [code] => AH98
            [2014-12-10] => 1
        )

    [2] => Array
        (
            [id] => 1
            [code] => AT78
            [2014-12-12] => 1
        )

    [3] => Array
        (
            [id] => 1
            [code] => AL44
            [2014-12-10] => 23
            [2014-12-11] => 27
            [2014-12-13] => 25
            [2014-12-15] => 26
        )

)

Upvotes: 1

Related Questions