"Percent of Total" Formula Column Utility

[#1]

This Add-in generates formula columns calculating the percent of total (row-wise) for the selected numeric columns. These columns are especially useful when creating stacked bar or column chart of the columns of interest and you wish for your scale to be percent of total rather than a sum.  

Usage: After installing this Add-in, select the set of columns in the data table, then choose "Percent of Total Formula Columns" from the Add-ins menu. 

Note: The formulas generated by this Add-in will tolerate missing values row-wise for all but one of the selected columns.

 

 When creating a stacked bar or column chart, the resulting plot is of the composition, rather than the total:

Total Sales

Composition of Sales

[chrisATX1]

Very helpful!

[marxx]

Hi Julian, Many thanks for this add-in. It's enormously helpful for analysis of compositional data. Much appreciated!

However, to analyze compositional data in a multivariate manner we need to convert row-wise "counts" to Aitchison's 'centered log ratio' (CLR) for all the columns that are components of the composition (e.g. all the columns that are included in the "percent of total" calculation).

It's very laborious to do this manually in JMP (although we do it for small compositions), and for work with gene transcript or microbiome sequencing data it is not even feasible.

The equation is simple, its just the natural log of the ratio of a given component over the *geometric* mean of all the components of the composition. Thus, for a 3 part composition (A, B, C) A is transformed as: LN(A/((A*B*C)^0.33)); B as LN(B/((A*B*C)^0.33))  and so on for C.

Unfortunately I am not fluent in the scripting (yet)... Would you be able to create a version of the 'Percent of Total' Add In that could instead produce columns of CLR transformed values for the composition?  

Particularly, with subcompositional coherence, we are constantly having to make several new different subcompositions on an ongoing basis. If we have a 100 component microbe composition, we might produce 10 or so different subcompositions each having a different set of columns included in the subcomposition... It would be extraordinarily helpful to multi-select columns and produce the CLR transformations.

Thanks much!

[julian]

Hi @marxx,

I'm not familiar with the centered log ratio but from your description the calculation seems straightforward enough. I put together a version of this add-in that calculates the CLR and posted it here: 

Download CLR Formula Columns Add-in

Would you check to see if this add-in produces the columns you expect?  If this is functioning properly I'll post this as a separate add-in in the user community for others to use. 

I hope this helps!

@julian

[marxx]

Hi Julian,

I tested the JMP CLR Add In versus a 6 component composition that I had
created manually... And the add-in you produced matched all the columns
that had been manually produced. I really appreciate it! 

Thanks so much!
Marxx

[Edit: Looks like screenshot image can't be included, but it works].
[image: Inline image 1]

[marxx]

Hi Julian, 

Would you please repost your CLR Formula Columns Add-in? I was assigned a new computer at work and lost all my previous addins.

PS- I couldn't find it posted in it's own post... just the link here that you posted previously.

Thanks, marxx

[julian]

Hi @marxx,

Sorry to hear you lost all your Add-ins! Yes, I just linked to it here since it's really just a variant of this. Here's an updated link: Download CLR Formula Columns Add-In

Let me know if you have any trouble downloading.

julian

[DVB]

Hi Julian

The add in "CLR formula columns" is super helpful. Thanks for developing and posting here. This add in works good up to 110 columns and the dataset has more than 110 columns, the transformation values are missing randomly for some rows (samples). Would you be able to check and fix it. thanks!      

[julian]

Hi @dvp,

I looked into this and there were some efficiencies I could make to the Add-In (which is attached here) but the issue is not actually the Add-In. Rather, it's that you're hitting a numeric capacity issue with a product of a very large number of columns (which is a piece of the formula). Double-precision floating-point numbers are the biggest values most programs can store, which is approximately 1.8 × 10^308. Anything larger than that physically can't be represented in the way the software is compiled. So, the missing values you're seeing are those rows where the product of the columns wasn't calculable and the formula failed.

If you're interested, you can read more on Wikipedia about double-precision floating-point numbers. 

I hope this helps!

@julian 

[DVB]

Thanks Julian for the explanation and the updated one. I will look into it and let you know the outcome. I will read more about double-precision floating point numbers. Thanks