PhP Convert a multidimensional array column by column?

I'm trying to build a google chart column chart where each column represents a property (house), and the income, per year of that property. The year would be represented on the x-axis, and the y-axis represents the income amount.

End result in JS would need to look like this:

    var columnChartData = google.visualization.arrayToDataTable([
        ['Year', 'Property 1', 'Property 2'],
        ['2022', 300, 4000],
        ['2023', 6000, 8000]
      ]);

I'm currently struggling with converting my PHP array below, to the required google format. How can I build an array column by column?


    array([Property 1] => Array ( [2021] => 353.93 [2022] => 12628.65 [2023] => 12841.57 ) 
    [Property 2] => Array ( [2022] => 370.78 [2023] => 12841.57 ))

required JS/GoogleChart:

    ['Year', 'Property 1', 'Property 2'],
    ['2022', 300, 4000],
    ['2023', 6000, 8000]

Solution

Here is one approach. This heavily relies on the assumption that for each property, a record for the Year is included. For example, we are assuming that the nth element of Property1 has the same year as the nth element of Property2.

Note, if OP controls the source with SQL or some other data source, it would be much easier to use PIVOT which is available in many RDBMS (but notably not mySQL). Likewise, using a charting framework like plotly might be helpful as well - I like the examples from this website which shows us using two separate data sources on the same plot area. We could imagine applying this by having a dataset for Property1 and a separate one for Property2.

https://plotly.com/javascript/line-charts/

<?php
$data = ["Property1" => [2022=>300, 2023=>6000], "Property2" => [2022=>4000, 2023=>8000]];

// Reshape so that each row consists of [Year, Property1, Property2]
$formattedArray = array_map(function ($year, $prop1, $prop2) {
    return [$year, $prop1, $prop2];
},
    array_keys($data["Property1"]),
    array_values($data["Property1"]),
    array_values($data["Property2"]));

// Add a header
$res = array_merge([["Year", "Property1", "Property2"]], $formattedArray); 

// Method 2
$keys = array_keys($data);
$res2 = [array_merge(["Year"], $keys)];

// assume each property has the same years
$years = array_keys($data[$keys[0]]);

for ($i = 0; $i<count($years); $i++) {
    $thisRow = [$years[$i]];
    foreach($data as $prop) {
        array_push($thisRow, array_values($prop)[$i]);
    }
    array_push($res2, $thisRow);
}
print_r($res2);
?> 
<html> 
    <head> <!--Load the AJAX API--> 
        <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
        <script type="text/javascript">
          let thisData = <?php echo json_encode($res);?>; 
          // Load the Visualization API and the corechart package.
          google.charts.load('current', {'packages':['corechart']}); 
          // Set a callback to run when the Google Visualization API is loaded.
          google.charts.setOnLoadCallback(drawChart);

          // Callback that creates and populates a data table,
          // instantiates the scatter chart, passes in the data and
          // draws it.
          function drawChart() {

            // Create the data table.
              <?php echo "var chartData = google.visualization.arrayToDataTable(" . json_encode($res2) . ");"?>

            // Set chart options
            var options = {'title':'Different Properties By Year',
                           'width':600,
                           'height':400};

            // Instantiate and draw our chart, passing in some options.
            var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
            chart.draw(chartData, options);
          }
        </script>
      </head>

      <body>
        <!--Div that will hold the pie chart-->
        <div id="chart_div"></div>
      </body>
</html>