Using Mathcad Prime for Matrix Row Substitution Based on Matching Criteria

[Perez]

Hello Everyone,

I am attempting to write a 'RowSwapper' function in Prime 8. Essentially, I have a matrix 'A' in which I need to replace certain rows that are present in another matrix, 'B'. The function should take the following arguments: RowSwapper(A, B, "label"). It should generate a new matrix, called 'A_Swapped', where rows are swapped only in instances where items have the same ID (column 2) and where label="LoadCase4" (column 5).

However, the function I've developed isn't ideal because it relies on matrix 'A' having a well-defined order, and that matrix 'B' maintains the same order per unique ID as they initially appear in Matrix 'A'. Furthermore, it only includes load cases equivalent to "LoadCase4". Additionally, it's time-consuming in terms of computation.

Screen shot of Matrix A and B:

Desired Output

Please note that I don't currently have access to Prime 9. Feel free to send me screenshots of the Prime 9 solution along with the file, which I can open when I eventually install Prime 9.

Here are the known conditions or specifications:

1. Both matrix 'A' and 'B' will have the same number of columns.
2. Matrix 'A' and 'B' can have any number of rows.
3. If Matrix 'B' contains multiple rows with the same ID and the same load case being searched for, then it should select the row with the highest value in column 10.
4. All IDs in Matrix 'A' will be present in Matrix 'B'.
5. The original order of Matrix 'A' should be preserved.
6. An attached Excel file contains tabs for the content of matrix 'A' and 'B', as well as a tab with the desired output.

[ttokoro]

Without condition 3.

[Werner_E]

Obviously your RowSwapper() function already does what you demand!?
You seem to are using additional informations which you didn't provide. Such as that ever 9th row in A (starting with #3) should be swapped and that the corresponding lines are in consecutive order in matrix B. So your function seems to assume some special kind of sorting both in A and B.

I don't understand the introduction of variable "Ans" in your function - its not used for creating the output and so the time and memory consuming stack command which creates a huge matrix of more than 750000 rows can be deleted!

EDIT: I overlooked that error! Because you have ORIGIN set to 0, the i-loop must run just up to rows(A)-1.

Just noticed what you wrote:

> However, the function I've developed isn't ideal because it relies on matrix 'A' having a well-defined order, and that matrix 'B' maintains the same order per unique ID as they initially appear in Matrix 'A'.

OK, does this mean that you are looking for a function which returns the same result if we shuffle the rows of both matrices, A and B?
Actually I guess it could not be the same order because you want the result matrix in the order the ID turn out the first time in A, correct?

So shuffling of A would also change the resulting matrix.

But shuffling of B should not matter, correct?

So if we should not shuffle A, can we rely that there are always nine load cases for each element and that the rows are sorted so that all load cases are in consecutive order? This would mean that we could use your i-loop 3,12,..rows(A) and only would have to deal with finding the correct row in the (eventually shuffled) matrix B.

> Furthermore, it only includes load cases equivalent to "LoadCase4".

You are talking about matrix B? Does this mean that the looked for routine should also skip lines in B which do not contain "LoadCase4"?
EDIT: Ah, guess you are referring to the third function argument you called "label".

> Additionally, it's time-consuming in terms of computation.

No, its not. Its pretty fast if you delete the superfluous "stack"-line as explained above 😉

I fear that @ttokoro  would have to modify his function significantly 😞

[Perez]

Hello @Werner_E ,

Yes, I'm looking for a better row swapping function that can look for the target load case in Matrix B.

Let me clarify as follows:

1. Yes, we can rely that in Matrix A there will be always nine load cases (or n number of load cases) for each element and that the rows are sorted so that all load cases are in consecutive order. 
2. Matrix B could have several load cases per ID. This means that the looked for routine should also skip lines in B which do not contain "LoadCase4".
3. The order of Matrix B does not matter.
4. The target load case to look for in Matrix B is LoadCase4, but this can be an argument of the function so that the user can apply the function for another load case so I wouldn't relay solely on the i-loop 3,12,..rows(A). Matrix B could have several load cases per ID. This means that the looked for routine should also skip lines in B which do not contain the target "LoadCase4".
5. Also, we can rely that Matrix B will have only one row per load case per ID. For example, you wont find two rows with LoadCase4 for the same ID. In other words, no need to apply point 3 of the original specification. I checked the scenario and we can have Matrix B as a collection of maximums before running the RowSwapper function.

I corrected the original RowSwapper function as you pointed it out and it runs super fast but unfortunately it relays in an ideal scenario of having Matrix B perfectly in a specific order. 

Also I tried the function from @ttokoro but is giving an error.

[Werner_E · Accepted solution]

This seems to be what you are looking for

Check:

[Perez]

Hello @Werner_E , your function is working great and is fast. It is even picking the maximum row when there are more than one entry per ID for the same targeted load case.👍

[Werner_E]

---

@Perez wrote:

Hello @Werner_E , your function is working great and is fast. It is even picking the maximum row when there are more than one entry per ID for the same targeted load case.👍

---

Yes, the function is pretty straight forward without using any tricky shortcuts. Its simply running through your specifications - with the exception of #4 as its throwing an appropriate error if no suitable row could be found in matrix B.

Not sure why you experienced an that error with @ttokoro 's function. From your screenshot all looks OK to me.

His A-row i-col 5-element 0 is far too complicated in my opinion. it should work OK, but simply typing A[i,5 ([ is the keyboard short for the matrix index) would do the same much simpler. Also the parenthesis around B-row k aren't necessary, but they should not do any

harm. Hist function should work OK given a very well behaved matrix B. You would have to post the worksheet to be able to say why you experience the error. 

Actually his approach does not differ much from yours. Only difference is that he is looking in A for the rows with "LoadCase 4" instead of assuming that the are every 9th row starting with row #3 as your function does. His function still relies on matrix B to be sorted the correct way and including exactly one row for each element.

As you wrote that we can rely on the matrix A to be sorted with the 9 load cases for each element in consecutive order, my function could probably be made slightly faster as we could first detect the first row to use depending on "label" and then just jump from there to every 9th row instead of reading every row and checking if the load case is the desired one.

But I am not sure if the slight speed improvement would justify the loss of generality.

EDIT: Edited my previous post and slightly changed the the function as I see no benefit in defining the variables ID and LC.
Also attached the file (format P9) which of course also includes the simple "Shuffle( )" function.

[Perez]

Hello @Werner_E , attached is my worksheet to investigate the error with @ttokoro 's function.

I agree to keep your function general because matrix A could have n number of load cases per ID. Not always there will be 9 load cases.

[Werner_E]

In your definition of RowSwapper_tt there is a typo. You typed rows*(A) with an implicit multiplication in-between instead of a function call rows(A).