PhP Convert a multidimensional array column by column?

Question

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]

It would be easier to advise if the numbers in your sample data matched the numbers in your expected output. — Markus AO
– Markus AO, Commented Jan 1, 2023 at 17:34
Could you confirm whether the data does not always have a matching year? For example, in the PHP array example, Property 1 has the years, 2021, 2022, and 2023 whereas Property 2 has the years 2022, and 2023. If the data is set up where all properties have matching years, this would be much more doable. — Cole
– Cole, Commented Jan 2, 2023 at 23:54
@Cole - I can make sure the data is 'complete' and would have 0 for empty years. — malteseKnight
– malteseKnight, Commented Jan 3, 2023 at 8:41

Cole · Accepted Answer · 2023-01-07 14:16:50Z

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>

Thanks for the above @Cole. Whilst in principle this work, the hardcoded limitation of having just two properties won't work for me. Apologies for not explaining myself better in the request - a user can have 1:n properties. Thanks however for the suggestion of using plotly. The way they have 2 datasets might make it easier for me to programatically extract the array looping on a year/property basis! I was in-so-far using tidy.js mixed with GoogleCharts but am reconsidering the plotly route. Will update here.
@malteseKnight see edit for method 2. I feel like there are probably more efficient ways to do it, but it does do what you ask for.

Collectives™ on Stack Overflow

PhP Convert a multidimensional array column by column?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related