Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Background subtraction based on variables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 15, 2017 8:22 AM
(2037 views)

Hi Everyone,

I am new to JMP and I need some help on selecting the specific rows from a column and subtract rest of the column based on the variable. Herewith, I have attached a data in which you can see Target, Replicates, Cell, Data order and Data. Basically, I wanted to subtract the background data from rest of the data with cell and data order specific. For instance, let's consider D in Target column is the background, I wanted the JMP to calculate the mean of D in every Cell and Data Order and subtract the rest of the data from the respective Cell and Data Order automatically.

Please let me know if you need more information! Thank you for helping me :)

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

For cases like this I often use *Summary* followed by *Update* (*Tables* menu).

Make a Summary table with mean of Data (grouped by Target, Cell & Data Order), delete rows except for Target = D, and updade the original with the summary table (match by columns Cell and Data Order).

If you need a dynamic solution it is possible with a Column Formula, however they can get quite complex. This formula should work for your example table:

```
:Data - If(:Target == "D",
Col Mean(:Data, :DataOrder, :Target == "D"),
(Col Sum(:Data, :Cell, :DataOrder) - Col Sum(:Data, :Cell, :DataOrder, :Target == "D")) / (
Col Number(:Data, :Cell, :DataOrder) - Col Number(:Data, :Cell, :DataOrder, :Target == "D"))
);
```

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Background subtraction based on variables

Hi, Subbu!

More information will be helpful!

For instance, do you need 25 (mean of Target D in Cell/DataOrder 1) subtracted from all the Cell/DataOrder 1's, and 22 (mean of Target D in Cell/DataOrder 2) subtracted from all the Cell/DataOrder 2's? Or something else?

Are Cell and DataOrder always identical?

Is there any specific reason why there are 7680 rows (with 7664 missing) in this datatable?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Background subtraction based on variables

Hi Kevin,

Thank you for the reply! Yes, you are right in the subtraction part! However, the Cell and Data order order are not identical. The Cell ranges from 1-4 and Data Order ranges from 0-14 or more. I apologize for the file! I did delete those information and put some random numbers for demonstration!

Thank you

Subbu

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

For cases like this I often use *Summary* followed by *Update* (*Tables* menu).

Make a Summary table with mean of Data (grouped by Target, Cell & Data Order), delete rows except for Target = D, and updade the original with the summary table (match by columns Cell and Data Order).

If you need a dynamic solution it is possible with a Column Formula, however they can get quite complex. This formula should work for your example table:

```
:Data - If(:Target == "D",
Col Mean(:Data, :DataOrder, :Target == "D"),
(Col Sum(:Data, :Cell, :DataOrder) - Col Sum(:Data, :Cell, :DataOrder, :Target == "D")) / (
Col Number(:Data, :Cell, :DataOrder) - Col Number(:Data, :Cell, :DataOrder, :Target == "D"))
);
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Background subtraction based on variables

Hi MS,

Thank you for the help! The column formula works great for the most part. However, in a large dataset I see the mean of Target D doesn't subtract the Target D. Any idea?

Thank you

Subbu