Table findRows() multiple criteria

Erif · June 17, 2022, 11:58am

Hello,

Say I’m importing a .csv table where headers are:
Column 2 - Data_value
Column 11 - Series_title_2
and I’m printing out the values from column 2 where column 11 contains “Mining”, as follows:

Table table = loadTable("data.csv","header");
  
  for(TableRow row : table.findRows("Mining","Series_title_2")) {
    println(row.getInt("Data_value"));
  }

Now, how can I concatenate more filters? I.e. by also adding “Actual” from column 12 - Series_title_3?
I’ve tried a for loop inside the for loop to no avail.
Thanks

glv · June 17, 2022, 12:40pm

Hello,

Tutorial here:

If you know the row that contains “Mining” you then check if column 2 with that row contains that value.

Try get.String()

References:
https://processing.org/reference/Table.html

You will need “equals” to compare strings:

https://processing.org/reference/String_equals_.html

:)

Erif · June 17, 2022, 1:19pm

Hi @glv, thanks for the references.
I checked the ‘Table’ ref page with all the methods inside out for hours yesterday, and the Data tutorial provided is identical to the Data chapter from the ‘Learning Processing Morgan Kaufmann Series’ by Daniel Shiffman book, which I’m using as a textbook to learn Processing.

As far as I know, getString() will give me one unique value when row/column is specified, but the result of my code so far is a list of 300 values, which I need to further reduce to 44 by introducing additional filters (similar to concatenating filters in Excel).

Right now, I’m able to apply the single filter of “Mining”, but I need to add the “Total earnings” and “Actual” ones to get the values from column C below.

Are you suggesting running that for loop 3 times with single filters each then use equals to compare the results? Sorry can’t seem to get the idea there

Chrisir · June 17, 2022, 5:47pm

When you put this in an separate line, what’s the return type please? Array of rows?

TableRow[] = .... does that work?

Erif · June 17, 2022, 6:00pm

@Chrisir Nope… or I’m doing it wrongly.
Do you mean like this?

TableRow[] row = table.findRows("Mining","Series_title_2");

Chrisir · June 17, 2022, 6:18pm

That’s what I meant. Then you can for lioop
over row (which is not a good name)

I was just thinking about whether you can apply the same command findRows on this array again? Or make it to a table and then use the command again?

Erif · June 17, 2022, 6:28pm

I was trying this… but doesn’t seem to work (below full code). It made sense to me to be restricting the output more and more by adding additional loops/conditions.

void setup() {
  size(500,500,P2D);
  loadData();
}

void draw() {
  background(255);
}

void loadData() {
  
  Table table = loadTable("data.csv","header");
  
  for(TableRow row : table.findRows("Mining","Series_title_2")) {
    for(TableRow row1 : table.findRows("Actual","Series_title_3")) {
      for(TableRow row2 : table.findRows("Total earnings","Series_title_1")) {
        println(row2.getInt("Data_value"));
        noLoop();
      }
     }
  }
}

The idea is to get the values I need first with println() then represent them in a graph.

Chrisir · June 17, 2022, 6:34pm

When you look here:

Maybe you can write a regexp with AND?

Erif · June 17, 2022, 6:47pm

Looked at it as well yesterday but don’t find anything that works… i.e:

concatenating .findRows, I get an error saying that findRows(String,String) does not exist

  for(TableRow row : table.findRows("Mining","Series_title_2").findRows("Actual","Series_title_3")) {
    println(row.getInt("Data_value"));
    noLoop();
  }

Using &&, no good either…

for(TableRow row : table.findRows("Mining","Series_title_2") && table.findRows("Actual","Series_title_3")) {
    println(row.getInt("Data_value"));
    noLoop();
  }

The problem is that I can’t find a method from the Table object that accepts multiple parameters

glv · June 17, 2022, 7:01pm

Hello @Erif,

Consider simplifying and iterate through each row of the table and perform any required tasks.

Consider using logical operators.

Example

Table table = loadTable("data.csv", "header");

for (int i = 0; i<table.getRowCount(); i++) 
  {
  TableRow row = table.getRow(i);
  String s1 = row.getString("String1");
  String s2 = row.getString("String2");
  String s3 = row.getString("String3");
  int i1 = row.getInt("Result");
  println (s1, s2, s3, i1);
  
  if (s1.equals(s2) || s2.equals(s3)) // Modify as required
    {
    println("Yay!");
    table.setInt(i, "Result", 1);
    }
  }

Modify as required.

You can always come back to a more complex or simple approach.

Have fun!

Update:

I also added a comment to code above.

:)

Erif · June 17, 2022, 8:45pm

mmm… I’m afraid this doesn’t work either… or I don’t know how to apply it
As per the image below with a simpler table, the output of println() should be just those 13 values in column 3, that is:
147.870064
148.915685
143.481452
145.689559
139.114093
143.738115
144.76762
140.216363
153.26737
145.279694
141.911001
154.31287
149.699332

There is not a single string value from columns Series_title_1,2 and 3 which is common between those columns, so I fail to see how s1.equals(s2) would work??

Table table = loadTable("data.csv","header");
  
  for(int i = 0; i < table.getRowCount(); i++) {
    
    TableRow row = table.getRow(i);
    
    String s1 = row.getString("Series_title_1");
    String s2 = row.getString("Series_title_2");
    String s3 = row.getString("Series_title_3");
    int i1 = row.getInt("Data_value");
    println(s1,s2,s3,i1);
    noLoop();
    
    if(s1.equals(s2) || s2.equals(s3)) {
      println("Yay!"); 
      table.setInt(i,"Data_value",1);
    }
  }```

glv · June 17, 2022, 9:55pm

I provided an example… not a direct answer to your question.
I added an update to my previous topic.

:)

Erif · June 20, 2022, 4:03pm

Aaaaaaaahhhhh, got it! I’m so used to the Excel rationale when it comes to filters that didn’t see the way of thinking in programming is different lol

Ok, so my code is correct till ‘println(income)’; now I get the target 13 values
Now, when I try to store those values in the table by using ‘table.setFloat’ then use those income values as the height for a bar chart is when there’s something missing .

Table table = loadTable("data.csv","header");

  for(int i = 0; i < table.getRowCount(); i++) {
    
    //iterate over all the table rows
    TableRow row = table.getRow(i);
    
    //define variables to retrieve the column headers I'm interested to filter by
    String s1 = row.getString("Series_title_1");
    String s2 = row.getString("Series_title_2");
    String s3 = row.getString("Series_title_3");
    
    //define the target variable from where I need to get my results      
     float income= row.getFloat("Data_value");
    
    //define filtering condition
    if(s1.equals("Total earnings") && s2.equals("Mining") && s3.equals("Actual")) {
      println(income);
      table.setFloat(i,"Data_value",income);
    }

    //draw rectangles whose height equals income
   //I think the problem is here when defining the bar widths?!?
    float w = width/income;// or... table.getRow(i).getFloat("Data_value");
    fill(0);
    stroke(255);
    rect(w*i,height-income,w,income); 
  }

glv · June 21, 2022, 12:08am

Hello @Erif,

Consider

using a counter in the if statement and increment each time you get a hit.
use a println() to watch this increment … 0, 1, 2, 3…
use this counter for the rectangle spacing
think about where you want to draw the bars… in the if or after?

I used a fixed width for w and was able to get a graph for 4 “hits”:

:)

Erif · June 21, 2022, 4:21pm

1 Cool! @glv I’m getting closer I followed those points and managed to get the counter working and printing the 1,2,3 …13 to account for all the hits.

3 My view is that you need to draw the bar inside the if in order to obtain just 13 bars, otherwise the filter would not apply.

2 What I’m struggling with now is the rectangle spacing, that is how to obtain the max(count) to divide the width always by 13. Right now as count is always increasing so is the denominator of ‘w’.

int count = 0; //var to count the final number of filtered values
  
  Table table = loadTable("data.csv","header");
  
  for(int i = 0; i < table.getRowCount(); i++) {
    
  //iterate over all the table rows
  TableRow row = table.getRow(i);
  
  //define variables to retrieve the column headers I'm interested to filter by
  String h1 = row.getString("Series_title_1");
  String h2 = row.getString("Series_title_2");
  String h3 = row.getString("Series_title_3");
    
  //define variables to retrive the text filters we're looking for
  String filter1 = "Total earnings";
  String filter2 = "Mining";
  String filter3 = "Actual";
    
    //define filtering condition
    if(h1.equals(filter1) && h2.equals(filter2) && h3.equals(filter3)) {

      //define the target variable from where I need to get my results      
      float income = row.getFloat("Data_value");
      
      //increment the counting
      count++;
      
      //draw rectangles whose height equals income
      float w = width/count;
      fill(0);
      stroke(255);
      rect(0,height,w,-income); //I know 0 is not correct but w*i doesn't work so one at a time :)
      
      println(income + " / " + count + " / " + w);
    }
  }

glv · June 21, 2022, 5:46pm

Keep thinking about it…

counter: 0, 1, 2, 3, 4…
x: 0, 50, 100, 150, 200…

Reference:

:)

Erif · June 21, 2022, 7:23pm

Ok, I think I got the rectX right with:

rect((count-1)*(i-count),height-income,5,income);

Now still struggling with the ‘w’… had to use a fixed one (5) temporarily for visibility.
This below doesn’t work as count increases therefore ‘w’ decreases. I need to pick the maximum value of ‘count’, which is 13, and divide width by it, am I right?
I tried max(count) but that’d mean turning count into an array and doesn’t take me anywhere.

float w = width/count;

Also tried this with no luck.

      IntList count_values = new IntList();
      count_values.append(count);
      
      int max_count = count_values.max();
      
      //draw rectangles whose height equals income
      float w = width/max_count;

glv · June 22, 2022, 12:53am

Hello,

This is a simple example of a bar graph:

size(600, 200);

int max = 70; // Try different values

for(int i = 0; i< max; i++)
  {  
  int x = i;  
  int y = 0;
  float w = float(width)/max; // I need floating point math here. Try with the float!
  float h = random(50, 170); // Random elements... this could have been from array or table
  stroke(0);
  rect(x*w, y, w, h);
  }

Take a look at the syntax and parameters in the reference:

Explore (play with different values) and understand this first and then integrate your version into your code.

:)

Erif · June 22, 2022, 9:56am

Thanks, yes, I understand the example provided, and in my case x,y and h variables are perfectly clear. The problem is the denominator of ‘w’ → max.

float w = float(width)/max //in the example it's a hardcoded number (70)

If I use max = 13, I get a perfectly valid result

What I’m trying is to not have to use a hardcoded value, but ‘max’ being the maximum value of my counter, as per below, so if I use another filter on the table and the max of counter is 7 don’t have to modify the code.
Does it make sense?

output2

Topic		Replies	Views
Java Processing find matching text in a table and get the value from next to it Coding Questions	2	235	April 11, 2022
Error when searching through a table Beginners	4	403	August 31, 2018
Column values from CSV arranged in a grid Coding Questions	9	315	February 13, 2021
Operations on TableRow object? Can't find sum Coding Questions	6	81	April 28, 2024
A quick note on nested TableRow looping Processing	7	308	August 2, 2018

Table findRows() multiple criteria

Related topics