Table findRows() multiple criteria

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

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

:)

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 :frowning:

1 Like

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

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

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

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

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?

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.

When you look here:

Maybe you can write a regexp with AND?

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

  1. 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();
  }
  1. 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

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.

:)

mmm… I’m afraid this doesn’t work either… or I don’t know how to apply it :frowning:
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);
    }
  }```

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

:)

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 :metal::stuck_out_tongue_closed_eyes:
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 :thinking:.

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); 
  }
2 Likes

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”:

image

:)

1 Cool! @glv I’m getting closer :blush: 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);
    }
  }

1 Like

Keep thinking about it…

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

Reference:

:)

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? :thinking:
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;

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.

image

:)

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 :sunglasses:

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

1 Like