Table manipulation

Clear all

# Table manipulation

Posts: 2
Customer
Topic starter
Active Member
Joined: 1 year ago

I would like to extract entries from a table based on values in a given column, how do I do this in Analytica? I am currently only able to do this with the indices for the table, but would like to make a subset of my data using a column value that is not an index.

Thanks!

Topic Tags
3 Replies
Posts: 17
Moderator
Member
Joined: 11 months ago

To represent the subset of data, you'll need a new index, one that contains only the columns of interest.

Let's say your original table (array) is named X, and that its indexes are Row and Col.  Which columns of X do you want to keep?  That's up to you, but it will be some expression involving X (the values of the table), which assigns a True or False to each Col.  Thus, whatever the criteria, it needs to be an "array reducing" criteria that eliminates the Row index.

As an example, let's say you want to keep only the columns that contain at least one negative number.  Define your new index as

Index SubCol := Subset( Max(X<0,Row) )

Now just re-index your original array and you have it:

X[Col=SubCol]

Posts: 20
Member
Joined: 12 years ago

The critical part of the above answer is that your criteria needs to reduce all indexes from X except for the one you want to subset (Col). As you get better at Analytica, you may want to ensure that your logic is robust to the possibility that another index, K, is added in the future. Consider what happens if a third index is added to X in the future. Now your criteria,

Max( X<0, Row )

is 2-D, with both K and Row. Each different K slice would recommend keeping a different subset of columns, which wouldn't work. To plan for that, you need to design your criteria to work no matter how many non-Col indexes there are. The example criteria, "keep only columns that contain at least one negative number", generalizes just fine, but you need to apply the Max( ) operation over all indexes of X except for Col. To do that, you would write it as

Max(X<0, ...indexesOf(X,except:Col) )

Now it will be fully array abstractable. You need to think through your criteria, because you can come up with a lot of possible criteria that work in 2-D, but don't make sense in more that 2 dimensions.  By the way, the ... in the previous expression is called repeated parameter forwarding. It takes the list of indexes from indexesOf() and forwards to the repeated index parameter of Max, as if you had listed multiple indexes.

Posts: 2
Customer
Topic starter
Active Member
Joined: 1 year ago

Thank you Dale and Lonnie! This was very helpful, I was able to create the new table with the rows of interest based on my criteria column 😀 It took more step than I was expecting, as I'm coming to Analytica from R.

Lonnie, interesting point on making sure the code can be generalized across multiple dimensions. I'm not quite sure how to apply this to my current project--but I will keep this in mind for the future!

Share:
Scroll to Top