Group By Variables for Pearson Correlation

I have a dataset with 5 columns: batch ID, product, parameter, predicted, and actual. I am trying to output a summary table with the Pearson correlation between predicted and actual grouped by product and parameter. I created a workflow that attempts to do this by deriving a list of unique products and parameters, then filtering the raw data to a particular combination of those two variables, then calculating the Pearson correlation and joining it all together into a single row at the end.

I would like to loop through all of the other possible product + parameter combinations and append the resulting correlation values to the table as well. I tried creating a macro to accomplish this but I'm having a hard time getting the desired result. I have attached the original workflow with sample data, any pointers on how to macro-ize it would be greatly appreciated!

Correlation Group By.yxmd

Macros

Accepted answers

mceleavey

@SWood ,

ok, so I simplified the process of creating unique values and creating unique combinations of Parameter and Product. I simplified it further by creating a compound key and using this as the filter in the in Control Parameter.

The workflow now looks like this:

with the macro doing the following:

This is generating the Pearson correlation per group of Parameter/Product combinations and appending them to each appropriate record.

You do get an error on five of the rows and I think this is simply data related, but it outputs accordingly.

Hope this helps,

Correlation Group By_mceleavey.yxzp

All comments

mceleavey

Hi @SWood ,

just so I understand, I see you are filtering to the combinations of Product and Parameter, but this will achieve the same as you already have. Are you attempting to return a Pearson correlation figure for an average of each group? What is it you're trying to achieve? Given that those combinations are not unique and will result in the same calculation as you are feeding it through the Pearson tool at the same level of granularity.

Let me know what it is you want I'll build it for you.

mceleavey

Sorry, no.

I think I understand.

mceleavey

@SWood ,

The workflow now looks like this:

with the macro doing the following:

This is generating the Pearson correlation per group of Parameter/Product combinations and appending them to each appropriate record.

You do get an error on five of the rows and I think this is simply data related, but it outputs accordingly.

Hope this helps,

Correlation Group By_mceleavey.yxzp

SWood

Thank you @mceleavey, this was very helpful. I tweaked it a little bit to get the correlation for each key grouping. The hardest part was figuring out how to get the control parameter to properly update the filter in the macro. I had to change the filter in the macro to be a custom filter instead of a basic filter, and then make sure the "replace a specific string" option was enabled in the update value action with the correct value entered in that field: just J-6, not "J-6", not [Key] = "J-6".

Correlation Group By v2.yxzp

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335