Format values from one array into another

Question

I have a CSV that returns me the following array:

Array
(
    [0] => Array
        (
            [0] => ANO
            [1] => Sciences
            [2] => Mechanics
            [3] => Telecom
        )

    [1] => Array
        (
            [0] => 2001
            [1] => 226
            [2] => 27
            [3] => 81
        )

    [2] => Array
        (
            [0] => 2002
            [1] => 433
            [2] => 59
            [3] => 162
        )

    [3] => Array
        (
            [0] => 2003
            [1] => 816
            [2] => 165
            [3] => 647
        )

    [4] => Array
        (
            [0] => 2004
            [1] => 1098
            [2] => 421
            [3] => 864
        )

) 

What I need to turn this array into a json with the following format:

"categories": [
  {
    "category": [{"label": "2001"}, {"label": "2002"}, {"label": "2003"}, {"label": "2004"}]
  }
],
"dataset": [
  {
    "seriesname": "Sciences",
    "data": [{"value": "226"}, {"value": "433"}, {"value": "816"}, {"value": "1098"}]
  }, 
  {
    "seriesname": "Mechanics",
    "data": [{"value": "27"}, {"value": "59"}, {"value": "165"}, {"value": "421"}]
  }, 
  {
    "seriesname": "Telecom",
    "data": [{"value": "81"}, {"value": "162"}, {"value": "647"}, {"value": "864"}]
  }
]

I’m cracking my head on this, even though I know it’s not so complex, but I’m really stuck =/

EDIT 1

Follow what I’m doing:

for ($i=0; $i < count($spreadsheet_data); $i++) {

        $chart['categories']['category'][]['label'] = array_shift($spreadsheet_data[$i]);

        for ($d=0; $d < count($spreadsheet_data[$i]); $d++) {
            $chart['dataset']['data'][]['value'] = $spreadsheet_data[$i][$d];
        }

    }

Answer 1

To obtain the desired array, given the initial array, one of the first steps would be to do, which in mathematical language we call transposição da matriz

Given a matrix A of type m x n, it is called transpose of A and is indicated by A t the matrix obtained by exchanging the rows in an orderly manner by columns of A. The operation of obtaining a transposed matrix of A is so-called transposition of the matrix. [2]

Exp:

A = [ [A, B, C, D],
      [E, F, G, H],
      [I, J, L, M] ]

A^t = [ [A, E, I],
        [B, F, J],
        [C, G, L],
        [D, H, M] ]

And in PHP it can be obtained as follows:

$array = json_decode('[["A","B","C","D"],["E","F","G","H"],["I","J","L","M"]]');
// input => [["A","B","C","D"],["E","F","G","H"],["I","J","L","M"]]

$array = transpor_matriz($array);

echo json_encode($array);
// output => [["A","E","I"],["B","F","J"],["C","G","L"],["D","H","M"]]

function transpor_matriz($array) {
    $out = array();
    foreach ($array as  $rowkey => $row) {
        foreach($row as $colkey => $col){
            $out[$colkey][$rowkey]=$col;
        }
    }
    return $out;
}

Input x Output [3]:

---

The rest of the algorithm ME would thus implement:

$array = json_decode('[["ANO","Sciences","Mechanics","Telecom"],[2001,226,27,81],[2002,433,59,162],[2003,816,165,647],[2004,1098,421,864]]');
// Input => [["ANO","Sciences","Mechanics","Telecom"],[2001,226,27,81],[2002,433,59,162],[2003,816,165,647],[2004,1098,421,864]]
$array = transpor_matriz($array);
// Apos a transposição => [["ANO",2001,2002,2003,2004],["Sciences",226,433,816,1098],["Mechanics",27,59,165,421],["Telecom",81,162,647,864]]

$categories = array();
for($i = 0; $i <= count($array); $i++){
    if($i != 0){
        $categories["category"]["label".$i] = $array[0][$i];
    }
}
// Não preciso mais da primeira linha de anos, então apago ela
unset($array[0]);
$array = array_filter($array);

$dataset = array();
$i = 0;
foreach ($array as  $rowkey => $inner) {
    $j = 0;
    foreach($inner as $colkey => $col){
        if($j == 0){
            $dataset[$i]["seriesname"] = $col;
        }else{
            $dataset[$i]["data"][$j]["value"] = $col;
        }
        $j++;
    }
    $i++;
}
// Output terá dois arrays, categorias e dados
$output = array(array("categories" => $categories),array("dataset" => $dataset));
echo json_encode($output);
// Output => [{"categories":{"category":{"label1":2001,"label2":2002,"label3":2003,"label4":2004}}},{"dataset":[{"seriesname":"Sciences","data":{"1":{"value":226},"2":{"value":433},"3":{"value":816},"4":{"value":1098}}},{"seriesname":"Mechanics","data":{"1":{"value":27},"2":{"value":59},"3":{"value":165},"4":{"value":421}}},{"seriesname":"Telecom","data":{"1":{"value":81},"2":{"value":162},"3":{"value":647},"4":{"value":864}}}]}]

function transpor_matriz($array) {
    $out = array();
    foreach ($array as  $rowkey => $row) {
        foreach($row as $colkey => $col){
            $out[$colkey][$rowkey]=$col;
        }
    }
    return $out;
}

That way we have the following Input x Output:

---

[Obs]: Like the @rray commented we have to keep a numeric key, otherwise the data could simply be overwritten. See the examples:

1

$teste["oi"] = 5;
$teste["oi"] = 10;
echo json_encode($teste);
// talvez seja esse output "esperado" = {"oi"{5,10}}
// output obtida = {"oi":10}

2

$teste["oi"][0] = 5;
$teste["oi"][1] = 10;
echo json_encode($teste);
// output esperada = {"oi"{5,10}}
// output obtida = {"oi":[5,10]}

Thus the result obtained was not exactly identical to the expected, but very close