Finding specific values from a big data list by position


#1

I’m trying to find specific values from a big excel file. It’s a list that’s also used to set parameter values for different types.
Schematic image:
image
(instead of A1, B1 and so forth there’d be actual values).
In practice the most left column will have names of the family types and the top row will have the names of the parameters.
I already disseccted the excel file to the point where there’s one list (with lists in it) that just contains the values (not actually values in the image):
image
a lists-in-lists that would overlap with these values to just give the parameter (no actual names in):
image
and a lists in lists that would just give the family type name (again: no actual names):
image

Now let’s say I wanted to read the parameter B value of Type 3, how would I do that?


#2

Is this what you are talking about? I couldn’t understand from the picture if you meant that the two extra lists came in as a list of lists like the main one. Personally, I think it is a little silly to try it this way. How is your main list structured? If it is in a list of list like [[A1,B1,C1],[A2,B2,C2],etc] then a better way to do this is to also bring in a list of column names and row names, find index for the row you want (1, 2, etc) and column you want (A, B, C) and get the element by mainList[row][col], which can be placed inside any codeblock.


#3

My main list still exist as a list within list with just some values, I’ve just created two other “lists within lists” that, Now, let’s say you’d give one index to these 3 lists, one of them would give the Type name, the other the parameter name, and the third the value. I made it like this to write parameter values using the Element. Set Parameter node and have 3 similar inputs. I thought it might be of use.

Your code might work, so I’ll check it out.


#4

Do all rows share the same column names? As in, do all of the 1’s, 2’s, and 3’s each have an A, B, C? If so, then you only need a list of rows (the 1, 2, 3, etc) and a list of columns (A, B, C) instead of nested lists.


#5

I’m not sure what you mean but the method you gave earlier works.


#6

Sorry to bother you any further, but could you maybe explain what’s happening in your code.

I might be willing to change this somewhat at some point (like giving a list of inputs instead of just one (which I already noticed insn’t a possiblity)). I’m specifically not certain what happens with the List.ContainsItem and the List.Map node.


#7

Sure. So the part labeled “#1 Filter Sublist 2 by 3” is the first step. The basic idea is using List.ContainsItem and the value you want (in this case 3), and going through the first sublist (123) to find where it is true (true = contains 3). Because your inputs are list of lists, I cannot use the list directly because each value is a list, not a number. So I have to use List.Map to go through each list within the lists. What it returns is a list of true or false depending on if the nested list contains the value 3.

From there, we have to back to the beginning code block to get to the second branch, starting with “Filter Main list by 3”. Using the bool mask list (true or false list), I filtered the second sublist (ABC) to find the correct list that I need to look at. In this case, that list would be the one that contains the value 3.

From there, we have to flatten it so it isn’t a nest list anymore and then I go to “#2 Filter sublist 1 by “B””. I just find the index of where B is and pass it on to the last part.

The last part uses the main list (A1B1C1, etc), filters by the bool mask from #1, flattens it so it is easier to search from, then uses the index found in #2 to get the correct element.

Note that this has tons of limitations. It is assuming that in this case the value of 3 is only within one list within the nested list. If there are multiple, it will behave differently. For example, if the second sublist was {A2,B2,C3} instead, the end result would likely be B2. There are better ways to do this if I can know how the excel sheet is actually set up.


#8

Thanks for the explanation, I find it a somewhat weird feature.

I nonetheless managed to get a lot done by trying to play around with “List.FilterByBoolMask” and the “List.GetItemAtIndex” nodes, even though that still very much has its practical limitations.


#10

Sorry, scratch that. There is still a problem with the new script I posted. Let me fix it first.


#11

image
awww…:blush:
But I understand quite well what’s going on here, this is what I made myself:


The “Param” input gives the name of the family parameter, the “Typ” input stands for the family type. The “n” for both of them are the lists of lists which in the example just had the digits and letter’s in them.
In this case I’m trying to read out the number of “Pipes” from a couple of different family types in this case. The reason the lower code gives a warning is because it reads out a couple of values from an array, but the input array can be shorter. The limitation here is that you have to add a little part to the code for each family type more in the array, in my case, it doesn’t go over 7, so it works fine.


#12

Hi @PauLtus and @kennyb6,

Although this is already marked as solved, can I suggest another approach? Something similar to this post.

It creates objects or dictionaries in a json like format from the excel data, so parameters can be retrieved by a given key (not more fiddling with the indexes!) :slight_smile:


#13

You’re welcome to join the discussion of course! :wink:
We’ve been going on for quite a while as well even though I’ve have it working already.
I think your method is looking very interesting.
I wonder: does it work when giving lists of inputs, even if I were to ask for the same value twice, would it still work?


#14

The package is quite flexible on what you can create and query from the objects.

Assuming this is still the end goal

I would do something like this:

  1. On the first group, the excel is read and deconstructed by taking the first header row (which will act as keys for the objects).
  2. Create the JsonObjects, one per row from the original data.
  3. Filtering the list of JsonObjects by providing a key and a value to compare.
  4. From the filtered objects, get the value/s associated with the key/s.

I find this method more straight forward than playing around with indexes and mapping lists. :slight_smile:

Data.xlsx (9.9 KB)
excelToJson.dyn (12.6 KB)