Appropriate data structure for table that uses ranges

Question

I have a table that looks like this:

       <22  23-27   
8-10   1.3   1.8
11-13  2.2   2.8
14-16  3.2   3.8

and it goes on. So I'd like to lookup a value like this:

lookup(11,25)

and get the response, in this case 2.8. What is the best data structure to use for this? I have the data in CSV format.

I'm looking to program this in PHP.

Thank you.

Answer 1

I'm certainly not claiming this is the best or most efficient data structure, but this is how I'd map your data into a two-dimensional PHP array that very closely resembles your raw data:

$fp = fopen('data.csv', 'r');
$cols = fgetcsv($fp);
array_shift($cols); // remove empty first item
$data = array();
while ($row = fgetcsv($fp)) {
  list($min, $max) = explode('-', $row[0]);
  // TODO: Handle non-range values here (e.g. column header "<22")
  $data["$min-$max"] = array();
  for ($x = 0; $x < count($cols); $x++) {
    $data["$min-$max"][$cols[$x]] = $row[$x + 1];
  }
}

You'd then need to add some parsing logic in your lookup function:

function lookup($row, $col) {
  $return = null;
  // Loop through all rows
  foreach ($data as $row_name => $cols) {
    list($min, $max) = explode('-', $row_name);
    if ($min <= $row && $max >= $row) {
      // If row matches, loop through columns
      foreach ($cols as $col_name => $value) {
        // TODO: Add support for "<22"
        list($min, $max) = explode('-', $col_name);
        if ($min <= $col && $max >= $col) {
          $return = $value;
          break;
        }
      }
      break;
    }
  }
  return $return;
}

Answer 2

How about some kind of two dimensional data structure.

X "coordinates" being <22, 23-27
Y "coordinates" being ...

A two dimensional Array would probably work for this purpose.

You will then need some function to map the specific X and Y values to the ranges, but that should not be too hard.