Accessing nested values in a table

Processing Coding Questions
1

I have a CSV file with no headers and where each row contains multiple lists of floats.

[(40.65, 20.30, 120.92, 86.12, 99.33), (10.35, 90.10, 10.52, 1.12, 59.3), (10.65, 62.10, 12.12, 96.23, 19.3)]
[(96.37, 67.13, 312.62, 87.11, 87.18), (90.94, 41.04, 63.01, 9.10, 30.3), (11.98, 99.01, 41.30, 16.04, 10.4)]
[(11.88, 12.98, 182.22, 13.84, 81.09), (50.35, 10.10, 10.52, 8.12, 91.3), (88.51, 21.66, 81.13, 36.13, 97.3)]
  • For each row, how can I access the list objects ?

Let’s say I want to retrieve the first list of floats on the second row. What method do I need to call to get (96.37, 67.13, 312.62, 87.11, 87.18) as an output ?

table.getRow(1) is unsubscriptable and table.getRow(1).getFloat(0) will return the first value, not the first list object.

  • How can I isolate the first and last value of each list from the brackets ?

Calling table.getRow(1).getFloat(0) will actually return a NaN in this case because table.getRow(1).getString(0) is returning [(96.37, not 96.37. Same thing for the last value of each list.

1 Like
2

You would need String commands

Get a String with table.getRow(1)

Then Look at split with ”(”

For loop over the resulting array

Remove ) using some form of replace

Then split the result with ”,”

3

It would be easier if it were an json object

2 Likes
4

Thanks for the suggestion. I converted the file to JSon format and loaded it with loadJSONObject() but am unable to retrieve the data with the getFloat() method.

{
 "0":{
    "0":[40.65, 20.30, 120.92, 86.12, 99.33],
    "1":[10.35, 90.10, 10.52, 1.12, 59.3],
    "2":[10.65, 62.10, 12.12, 96.23, 19.3],

  }
}

How would you access the second list of floats ("1") of the first row ("0") in this example ?

5

It’s something like jsonarray=…getjsonarray…

See reference

6

Tried already but got a TypeError: getJSONArray(): 1st arg can't be coerced to String.
Hence my question.

This thing is a nighmare.

1 Like
7

? i might not understand the issue, but just reading and display
that data from CSV is not the problem,
after you converted the data syntax to valid CSV file ( as spreadsheet would read )

after that you would need to use the data in a more MATRIX thinking, but table might also do that without restoring it to array, like a SQL VIEW?

// accessing nested values in a table
// https://discourse.processing.org/t/accessing-nested-values-in-a-table/9761
/*
/data/data.csv
_______________________
major, minor,A,B,C,D,E
0,0,40.65, 20.30, 120.92, 86.12, 99.33
0,1,10.35, 90.10, 10.52, 1.12, 59.3
0,2,10.65, 62.10, 12.12, 96.23, 19.3
1,0,96.37, 67.13, 312.62, 87.11, 87.18
1,1,90.94, 41.04, 63.01, 9.10, 30.3
1,2,11.98, 99.01, 41.30, 16.04, 10.4
2,0,11.88, 12.98, 182.22, 13.84, 81.09
2,1,50.35, 10.10, 10.52, 8.12, 91.3
2,2,88.51, 21.66, 81.13, 36.13, 97.3
________________________
*/

Table data;
String data_fn = "data/data.csv";
int dc = 60, dx = 10, dr =20, posx, posy;

void setup() {
  size(500, 500);
  data      = loadTable(data_fn, "header");
}

void draw() {
  background(80, 80, 0);
  for (int c =0; c < data.getColumnCount(); c++)     text(data.getColumnTitle(c), dx+c*dc, dr);     // data Column Header
  for (int r = 0; r < data.getRowCount(); r++)   for (int c =0; c < data.getColumnCount(); c++) {   // data
    posx = dx+c*dc;      
    posy = (r+1)*dr;
    text(data.getString(r, c), posx, dr + posy);                                                    // data cell content
  }
}

2019-03-30_00-20-45_snap

in editgrid looks like: ( sorry does a resort )
SNAG-0033

1 Like
8

Hi @kll, thanks for the suggestion and example code.

Iterating through the columns and rows of the table like you did is the first thing I tried. But the problem stays the same:

  • values next to a bracket are not taken into account
  • it’s returning single float values, not sublists

I also tried the loadSpreadSheet() function from the HiVis library but got an error (1st arg can't be coerced to java.io.File) with both csv and xlsx files.

Spent most of the day on this and still can’t figure out a viable solution to manipulate nested tabular data with Processing.

9

here

I know it sucks…

I think it’s floats inside an JSONArray inside a JSONObject inside a JSONObject…

// https://discourse.processing.org/t/accessing-nested-values-in-a-table/9761/2

// data (The \" means " in String)
String data =
  "{"+
  " \"0\":{"+
  "     \"0\":[40.65, 20.30, 120.92, 86.12, 99.33],"+
  "     \"1\":[10.35, 90.10, 10.52,  1.12,  59.3],"+
  "     \"2\":[10.65, 62.10, 12.12,  96.23, 19.3],"+
  " }"+
  "}";

// class with tools 
Tools tools = new Tools(); 

// the table
Table tableValues=null; 

void setup() {
  size(660, 660);
  // parse the above String 
  JSONObject json = parseJSONObject(data);

  // Success? 
  if (json == null) {
    // Fail 
    println("JSONObject could not be parsed +++++++++++++++++++++++++");
    exit(); 
    return;
  } else {
    // Success 

    JSONObject myJSONObject = json.getJSONObject("0"); 
    println(myJSONObject);

    // setting up table (column names / headlines)
    tableValues=tools.setUpTable(myJSONObject); 

    // adding data (must match headlines)
    tableValues=tools.addJSONArrayToTable(tableValues, myJSONObject, "0");
    tableValues=tools.addJSONArrayToTable(tableValues, myJSONObject, "1");
    tableValues=tools.addJSONArrayToTable(tableValues, myJSONObject, "2");
  }//else
}// setup

void draw() {
  // 
  background(0); 
  tools.showTable(tableValues, 
    22, 22);

  fill(tools.WHITE); 
  text("use mouse over to show full text", 
    22, height/2-110);
}// draw 

//===============================================================================
// Tools collection 

class Tools {

  // class not like a car class Car for an object but a collection of tools. 

  final color RED   = color(255, 0, 0); 
  final color GREEN = color(0, 255, 0); 
  final color BLUE  = color(0, 0, 255); 

  final color WHITE = color(255); 
  final color BLACK = color(0); 
  final color GRAY  = color(255/2); 

  boolean cursorSignShowFlag=false; 

  // ----------------------------------------------------------------

  Table setUpTable(JSONObject myJSONObject_) {
    // expects a JSONObject with a JSONArray
    // make a new table from scratch 
    Table newT = new Table();
    JSONArray values = myJSONObject_.getJSONArray( "0" );    // https://www.processing.org/reference/JSONObject_getJSONArray_.html
    String[] headers1 = new String[values.size()]; 
    for (int i = 0; i < values.size(); i++) {
      headers1[i] = str(i);
    }//for
    newT = newTable(headers1);
    return newT;
  }

  Table addJSONArrayToTable ( Table table1, JSONObject myJSONObject_, String line_ ) {
    // expects a table of same size 
    // expects a JSONObject with a JSONArray
    // expects a line number for the JSONArray  
    JSONArray values = myJSONObject_.getJSONArray( line_ );    // https://www.processing.org/reference/JSONObject_getJSONArray_.html
    String[] values1 = new String[values.size()]; 
    for (int i = 0; i < values.size(); i++) {
      values1[i] = str(values.getFloat(i));
    }//for
    table1 = tableAddData ( table1, values1 );
    return table1;
  }

  // ----------------------------------------------------------------

  void showTable(Table tableBtn, 
    int x, int y) { 

    int factorX=78; // column width 

    // rect
    stroke(WHITE);
    noFill();
    rect( x, y, 
      tableBtn.getColumnCount()*78-6, (tableBtn.getRowCount()+1) * 22 + 10 );

    // headline 
    showTableHeadline(tableBtn, x+6, y+19, factorX);

    // horizontal line 
    stroke(WHITE);
    line( x+2, y+5+19, 
      6+x+(tableBtn.getColumnCount())*factorX-13, y+5+19);

    // grid 
    // loop over rows (y)
    for (int i=0; i<tableBtn.getRowCount(); i++) {

      // current data row
      TableRow row = tableBtn.getRow(i);

      // loop over columns in that row (i2 is for x) 
      for (int i2=0; i2<tableBtn.getColumnCount(); i2++) {

        fill(WHITE);
        text(row.getString(i2), 
          i2*factorX+x+6, 25+ i * 22 +y+8, 
          factorX-8, 15);

        if (mouseInside(i2*factorX+x+6, 25+ i * 22 +y+8, 
          factorX-8, 15)) {
          text (row.getString(i2), 
            20, height-22);
        }//if

        // vertical line 
        line( i2*factorX+x, +y, 
          i2*factorX+x, tableBtn.getRowCount() * 22 + y + 31);
      }//for
    }//for
  }// method 

  boolean mouseInside( float x_, float y_, 
    float w_, float h_) {
    return mouseX>x_ &&
      mouseX<x_+w_ &&
      mouseY>y_ &&
      mouseY<y_+h_;
  }

  void showTableHeadline(Table tableBtn, 
    int x, int y, 
    int factorX) { 
    // headline for table 
    TableRow row0 = tableBtn.getRow(0);
    for (int i=0; i<tableBtn.getColumnCount(); i++) {
      // headline 
      fill(GREEN);
      text(row0.getColumnTitle(i), 
        i*factorX+x, y-2);
    }
  }//method 

  // ---
  // make table 

  Table newTable (String... listColumnNames) { 

    Table newT = new Table();

    // make columns
    for (String s1 : listColumnNames) {
      newT.addColumn(s1);
    }

    return newT;
  }//method

  Table tableAddData( Table table1, String... data1  ) {

    // add rows with data 
    TableRow newRow = table1.addRow();

    // add rows with data 
    int i=0; 
    for (String s1 : data1) {
      newRow.setString(newRow.getColumnTitle(i), s1);
      i++;
    }
    return table1;
  }//method

  // --- 

  void printlnTable(Table tableBtn) { 

    // rect
    stroke(WHITE);
    noFill();

    println("------------------------------------");

    // headline 
    printlnTableHeadline(tableBtn);

    // grid 
    // loop over rows (y)
    for (int i=0; i<tableBtn.getRowCount(); i++) {

      // current data row
      TableRow row = tableBtn.getRow(i);

      // loop over columns in that row (i2 is for x)
      String s1="";
      for (int i2=0; i2<tableBtn.getColumnCount(); i2++) {
        s1+="   "+row.getString(i2);
        //
      }//for
      println(s1);
    }//for
    println("===========================================");
    //
  } // method 

  void printlnTableHeadline(Table tableBtn ) { 
    // headline for table 
    TableRow row0 = tableBtn.getRow(0);
    for (int i=0; i<tableBtn.getColumnCount(); i++) {
      // headline 
      print("   "+row0.getColumnTitle(i));
    }
    println("");
  }//method 

  // ------------------------------------------------------------------

  String cursorSign() {
    // blinking cursor sign | for Input Box 
    if (frameCount % 13 == 0)
      cursorSignShowFlag= ! cursorSignShowFlag;
    if (cursorSignShowFlag)
      return"|";
    else return"";
  }//method

  //
}//class
//
3 Likes
10

there are ( like i show you ) nested datasets,
but in CSV and EXCEL and SQL it has to done by INDEXing
as there can not be a syntax [a,b,c],[d,e,f] in a CSV file!
or did you try to load it into EXCEL?

now that syntax ( what you might see for 2 dim arrays )
is replaced by the index columns what makes you free
for even more dimensions, BUT basic rule:
the number of [f1,f2,f3,f4,f5] here 5
must stay same throughout the database.
in SQL the index columns would actually be in separate tables
data: ( indexed main table: idx,f1,f2,f3,f4,f5 )
1,a,b,c,d,e
2,f,g,h,i,j
3
4
5
6

table dimension: idx, major, minor, main_idx
1,0,0,0
2,0,1,1
3,0,2,2
4,1,0,3
5,1,1,4
6,1,2,5
7,2,0,6

in a ( for spreadsheet valid ) CSV file
( and like you have started it for JSON also )
you would do like i show

major, minor, f1,f2,f3,f4,f5

and then there is no reading / loading problem ( as there or no “” )
the CSV and code i show is functioning,
and as mentioned your thinking in multi dim array needs a little
more code like

float get_val(int major, int minor) {}

but that is just using the already loaded table.
like

click 
// accessing nested values in a table
// https://discourse.processing.org/t/accessing-nested-values-in-a-table/9761
// v0.2 function (major,minor,fx)

/*
/data/data.csv
 _______________________
 major, minor,A,B,C,D,E
 0,0,40.65, 20.30, 120.92, 86.12, 99.33
 0,1,10.35, 90.10, 10.52, 1.12, 59.3
 0,2,10.65, 62.10, 12.12, 96.23, 19.3
 1,0,96.37, 67.13, 312.62, 87.11, 87.18
 1,1,90.94, 41.04, 63.01, 9.10, 30.3
 1,2,11.98, 99.01, 41.30, 16.04, 10.4
 2,0,11.88, 12.98, 182.22, 13.84, 81.09
 2,1,50.35, 10.10, 10.52, 8.12, 91.3
 2,2,88.51, 21.66, 81.13, 36.13, 97.3
 ________________________
 */

Table data;
String data_fn = "data/data.csv";
int dc = 60, dx = 10, dr =20, posx, posy;

void setup() {
  size(500, 500);
  data      = loadTable(data_fn, "header");
}

void draw() {
  background(80, 80, 0);
  for (int c =0; c < data.getColumnCount(); c++)     text(data.getColumnTitle(c), dx+c*dc, dr);     // data Column Header
  for (int r = 0; r < data.getRowCount(); r++)   for (int c =0; c < data.getColumnCount(); c++) {   // data
    posx = dx+c*dc;      
    posy = (r+1)*dr;
    text(data.getString(r, c), posx, dr + posy);                                                    // data cell content
  }
}

float get_matrix(int major, int minor, int fcol) { 
  String ma = str(major);                           // findRows uses string??
  float result=-9999;
  for ( TableRow row : data.findRows(ma, 0) ) {
    if ( row.getInt(1) == minor )    result = row.getFloat(2+fcol);  // column like "A" as 0 in col 2
  }
  return result;
}

void print_matrix() {
  for ( int i = 0; i < 3; i++ ) for ( int j = 0; j < 3; j++ ) {
    for ( int k = 0; k < 5; k++ ) print(" ["+i+","+j+","+k+"]: "+ get_matrix(i, j, k) );
    println();
  }
}

void keyPressed() {
  if ( key == 'p' ) print_matrix();
}

SNAG-0034
SNAG-00341094×720 120 KB

3 Likes
11

kll, I don’t follow you

Of course you won’t have that complexity in normal csv

But in json nearly every degree of complexity is usual. Have you looked at json databases? They are huge. And they are written by programs not by humans anyway.

But I think it’s easier to parse the json instead of converting it by hand.

Chrisir

12

sorry, i by no way argue against the use of JSON,
still i wanted to show a way to use CSV / EXCEL&co / SQL /
and processing ‘table’

and actually

String data =
  "{"+
  " \"0\":{"+
  "     \"0\":[40.65, 20.30, 120.92, 86.12, 99.33],"+
  "     \"1\":[10.35, 90.10, 10.52,  1.12,  59.3],"+
  "     \"2\":[10.65, 62.10, 12.12,  96.23, 19.3],"+
  " }"+
  "}";

and this

 0,0,40.65, 20.30, 120.92, 86.12, 99.33
 0,1,10.35, 90.10, 10.52, 1.12, 59.3
 0,2,10.65, 62.10, 12.12, 96.23, 19.3

is both valid and not too much different,
but this

[(40.65, 20.30, 120.92, 86.12, 99.33), (10.35, 90.10, 10.52, 1.12, 59.3), (10.65, 62.10, 12.12, 96.23, 19.3)]

would require a new importer,
yes, loadStrings() and some split coding might do it too.

they https://www.w3.org/2013/csvw/wiki/Main_Page work on this?

2 Likes
13

Yep. But you don’t have to store objects for each row with a row number name. That is needlessly complex. JSON already has an ordered element: array. So you can just do an array of arrays like this for 2D (normal spreadsheet):

[
  [
      50,
      10,
      5,
      50
  ],
  [
      51,
      9,
      6.5,
      58.5
  ]
]

…and for 3D, just do an array of arrays of arrays. Here is your data:

[
  [
    [
      40.65,
      20.3,
      120.92,
      86.12,
      99.33
    ],
    [
      10.35,
      90.1,
      10.52,
      1.12,
      59.3
    ],
    [
      10.65,
      62.1,
      12.12,
      96.23,
      19.3
    ]
  ],
  [
    [
      96.37,
      67.13,
      312.62,
      87.11,
      87.18
    ],
    [
      90.94,
      41.04,
      63.01,
      9.1,
      30.3
    ],
    [
      11.98,
      99.01,
      41.3,
      16.04,
      10.4
    ]
  ],
  [
    [
      11.88,
      12.98,
      182.22,
      13.84,
      81.09
    ],
    [
      50.35,
      10.1,
      10.52,
      8.12,
      91.3
    ],
    [
      88.51,
      21.66,
      81.13,
      36.13,
      97.3
    ]
  ]
]

…or, minified:

[[[40.65,20.3,120.92,86.12,99.33],[10.35,90.1,10.52,1.12,59.3],[10.65,62.1,12.12,96.23,19.3]],[[96.37,67.13,312.62,87.11,87.18],[90.94,41.04,63.01,9.1,30.3],[11.98,99.01,41.3,16.04,10.4]],[[11.88,12.98,182.22,13.84,81.09],[50.35,10.1,10.52,8.12,91.3],[88.51,21.66,81.13,36.13,97.3]]]

You can access it with JSONArray.

String jsondata = "[[[40.65,20.3,120.92,86.12,99.33],[10.35,90.1,10.52,1.12,59.3],[10.65,62.1,12.12,96.23,19.3]],[[96.37,67.13,312.62,87.11,87.18],[90.94,41.04,63.01,9.1,30.3],[11.98,99.01,41.3,16.04,10.4]],[[11.88,12.98,182.22,13.84,81.09],[50.35,10.1,10.52,8.12,91.3],[88.51,21.66,81.13,36.13,97.3]]]";

JSONArray ja = parseJSONArray(jsondata);

int row = 2;
int col = 0;
int val = 1;

float val = ja.getJSONArray(row).getJSONArray(col).getFloat(val);
println(val);  // 182.22

…and you can wrap that code up in a function to make it shorter:

float ja3Dfloat(JSONArray ja, int row, int col, int item){
  return ja.getJSONArray(row).getJSONArray(col).getFloat(val)
}
//...
ja3Dfloat(ja, 2, 0, 1);  // 182.22
4 Likes
14

Thanks to all of you for your time and suggestions.

I’m sticking to Chrisis/JeremyDouglass workarounds for now and will probably look for a more viable/efficient solution later, like using Tablesaw or Morpheus (provided I’m able to install Maven on my system).

1 Like
15

If you want I could write the version I wrote about in my first post using split()

If so, Can you post a longer data set

1 Like
16

Great. If you can’t change your “CSV”-esque format and need to convert it on the fly to 3D JSONArray, just:

  1. ( --> [
  2. ) --> ]
  3. \n --> , (assuming no blank last line)
  4. add a single [ ] pair around the beginning/end of the whole thing

so this (your file):

[(40.65, 20.30, 120.92, 86.12, 99.33), (10.35, 90.10, 10.52, 1.12, 59.3), (10.65, 62.10, 12.12, 96.23, 19.3)]
[(96.37, 67.13, 312.62, 87.11, 87.18), (90.94, 41.04, 63.01, 9.10, 30.3), (11.98, 99.01, 41.30, 16.04, 10.4)]
[(11.88, 12.98, 182.22, 13.84, 81.09), (50.35, 10.10, 10.52, 8.12, 91.3), (88.51, 21.66, 81.13, 36.13, 97.3)]

becomes this (valid JSON):

[[[40.65, 20.30, 120.92, 86.12, 99.33], [10.35, 90.1, 10.52, 1.12, 59.3], [10.65, 62.1, 12.12, 96.23, 19.3]],
[[96.37, 67.13, 312.62, 87.11, 87.18], [90.94, 41.04, 63.01, 9.10, 30.3], [11.98, 99.01, 41.30, 16.04, 10.4]],
[[11.88, 12.98, 182.22, 13.84, 81.09], [50.35, 10.10, 10.52, 8.12, 91.3], [88.51, 21.66, 81.13, 36.13, 97.3]]]
2 Likes
17

That is very kind of you but it’s all working good now. Thank you!

1 Like