topic joining tables while averaging across columns with the same name in Discussions

joining tables while averaging across columns with the same name

abmayfield — Sat, 10 Jun 2023 23:32:17 GMT

I have what would appear to be an easy-to-fix problem, but for the life of me, I can't figure out how to do it easily. I have three separate tables in which the rows are individual proteins. The "accession" refers to their names. Some proteins are found in multiple tables, whereas some are only found in one of the three. I know how to join the tables by matching the protein names (accession=accession), but when I do this it provides the data from only one of the tables. I would prefer to average (or sum) the values instead. For instance, if protein A is in tables 1 and 2, with values of 3 and 4, respectively, I would like the new, joined table to have protein A with a value of 3.4. I tried concatenate and JMP Query Builder with no luck, but I feel there must be an easy solution to this, right? The raw data I actually want to analyze are in the "abundances grouped" columns (7 data columns I'd like to average across tables). I bet a savvy JMP user could do what I'm trying to do with zero scripting in 5-10 seconds!

Re: joining tables while averaging across columns with the same name

jthi — Tue, 22 Jun 2021 17:19:58 GMT

Depending what you want to do with the data and how you will analyze it, maybe you could approach this first by concatenating (not joining) the datatables together, then using Summary table to calculate Sum/Average values of the columns of interest.

Edit:

Added bold to important part. This solution could work if you are only interested in specific columns in the data: abundances grouped and accession. You would end up with something like this:

Also if one of the tables is "master" from which you would take other information from, you could join back to that

Re: joining tables while averaging across columns with the same name

Dan_Obermiller — Tue, 22 Jun 2021 17:09:32 GMT

I agree with @jthi that joining the tables first and then summarizing may be a prudent course of action. When you are joining the tables, make sure you check the boxes to check the box to include non-matches

and not have the "Drop multiples" checked. This should give you all of the observations from both tables.