encode CSV to JSON according to certain scheme using PHP

Question

I have a CSV file:

name;categories;data
019;07:50:00;0,0
017;07:50:00;0,8
019;07:55:00;0,4
017;07:55:00;1,3
019;08:00:00;0,8
017;08:00:00;1,9

I would like to convert it using PHP into a JSON file according to the following scheme (for a Highcharts diagram):

{
    "xAxis": 
    {
        "categories": ["07:50:00","07:55:00","08:00:00"]
    },
    "series": 
    [
        {
        "name": "019",
        "data": [0.0,0.4,0.8]
        }, 
        {
        "name": "017",
        "data": [0.8,1.3,1.9]
        }
    ]
}

Explanation:

The rows of the CSV file alternate with data records, here: 019 and 017 (column: 'name'). There can also be more than two data records, this is not fixed. Every five minutes (column: 'categories') the value changes (column: 'data').

I think this is done with the programming method called 'control break'. Can anyone show me an approach to how it could work?

score 0 · Answer 1 · answered Oct 25 '18 at 08:18

Less of a control break implementation actually, because working with arrays makes the usual checks and looking back at the “previous record” key value superfluous.

Here’s how I would do this,

<?php

// using a data array here for illustration purposes,
// replace with reading from CSV on your own
$data = array (
  0 => 
  array (
    0 => '019',
    1 => '07:50:00',
    2 => '0,0',
  ),
  1 => 
  array (
    0 => '017',
    1 => '07:50:00',
    2 => '0,8',
  ),
  2 => 
  array (
    0 => '019',
    1 => '07:55:00',
    2 => '0,4',
  ),
  3 => 
  array (
    0 => '017',
    1 => '07:55:00',
    2 => '1,3',
  ),
  4 => 
  array (
    0 => '019',
    1 => '08:00:00',
    2 => '0,8',
  ),
  5 => 
  array (
    0 => '017',
    1 => '08:00:00',
    2 => '1,9',
  ),
);

// initialize object and properties to hold final result
$result = new stdClass();
$result->xAxis = new stdClass();
$result->series = [];
$categories = $series = [];

// accumulate data for categories and series
// using the name as key for the series array, makes it easier - otherwise you’d
// have to check if an array entry with specific name property value already exists
foreach($data as $row) {
  $categories[] = $row[1];
  $series[$row[0]][] = $row[2];
}

// make categories unique to filter out duplicates, and re-index numerically
$result->xAxis->categories = array_values(array_unique($categories));

// transform temporary series data into final structure, putting the key into the
// name property now
foreach($series as $key => $item) {
  $temp = new stdClass();
  $temp->name = $key;
  $temp->data = $item;
  $result->series[] = $temp;
}

echo json_encode($result);

(Transformation of the x,y values in the last CSV column to x.y integer format I’ll also leave up to you.)

After figuring out how to load the CSV into an array, I find that this approach works just as well. Unfortunately, you can't mark two answers as good. CSV to Associative Array by user Daerik: https://stackoverflow.com/questions/4801895/csv-to-associative-array/41942299#41942299 — Zurreal, Oct 26 '18 at 07:19

score 0 · Accepted Answer · answered Oct 25 '18 at 08:55

This can be done with a single loop, ergo it should be done with a single loop.

Code: (Tested to be successful on localhost)

if (($handle = fopen("Zurreal.csv", "r")) !== false) {
    fgetcsv($handle, 1000, ';');                              // disregard column heading row
    while (($row = fgetcsv($handle, 1000, ';')) !== false) {  // notice the semicolon delimiter
        if (!isset($firstname)) {                             // if the first row of data
            $firstname = $row[0];                             // cache the name value
            $xAxis['xAxis']['categories'] = [$row[1]];        // store this row's time value
        } elseif ($firstname == $row[0]) {
            $xAxis['xAxis']['categories'][] = $row[1];        // store only time values from rows with first row's name
        }
        $series[$row[0]]['name'] = $row[0];                   // store (and overwrite after first occurrence) the name value
        $series[$row[0]]['data'][] = (float)str_replace(',', '.', $row[2]);  // push the prepared data values into the data subarray
    }
    fclose($handle);
    $result = array_merge($xAxis, ['series' => array_values($series)]);  // remove the temp keys from series and build the final array structure
    echo json_encode($result, JSON_PRETTY_PRINT);              // convert final array to json (pretty print for demo only)
}

Output:

{
    "xAxis": {
        "categories": [
            "07:50:00",
            "07:55:00",
            "08:00:00"
        ]
    },
    "series": [
        {
            "name": "019",
            "data": [
                0,
                0.4,
                0.8
            ]
        },
        {
            "name": "017",
            "data": [
                0.8,
                1.3,
                1.9
            ]
        }
    ]
}

Thanks for your solution. How is this notation to be understood: [$row[1]] ? Why the outer square brackets? — Zurreal, Oct 26 '18 at 10:57

encode CSV to JSON according to certain scheme using PHP

2 Answers2