1

I'm trying to plot some data (standard curves for analytical chemistry) where the x axis is the mass of a compound I added to a solution, and the y axis is the signal recorded from an instrument (peak height on a mass spectrometer). I'd like Tableau to color code the data by compound (compound A, compound B, compound C, etc.), so that I'd wind up with a graph that looks something like this: enter image description here

The original structure of my data was like this:

 SampleID   | Mass A | Mass B | ... | Signal A | Signal B | ...  
 standard 0 |       0|       0| ... |         0|         0| ...
 standard 5 |   2.535|   2.555| ... |     0.494|     1.240| ... 
 standard 25|  12.675|  12.775| ... |     2.426|     7.235| ...

I know how to make graphs one compound at a time with these original data, but for the purposes of other analyses I'm doing with these data and because I want multiple compounds on the same graph, I've pivotted them so that the structure is now like this:

 SampleID   | Compound | Parameter | Value 
 standard 0 | A        | Mass      |     0
 standard 0 | A        | Signal    |     0
 standard 5 | A        | Mass      | 2.535

etc.

How do I make a graph where the mass is on the x axis, the signal is on the y axis, and the points are colored by compound? I don't see a good way to do it when my data are in this format. I've tried making new calculated variables where the value = NULL if the parameter is not equal to "Mass" and another calculated variable where the value = NULL if the parameter is not equal to "Signal" and then putting those pills on the columns and rows, but that's not working. Is there a way to do this in Tableau with data structured like this pivotted form?

Alternatively, is there a way to spread my pivotted data so that the new structure is like this: 

 SampleID   | Compound | Mass  |  Signal 
 standard 0 | A        |      0|       0
 standard 5 | A        |  2.535|   0.494
 standard 25| A        | 12.675|   2.426
 standard 0 | B        |      0|       0

etc.

and would that work better?

(For R users, that last bit would be the equivalent of the tidyr package gather and spread functions.)

shirewoman2
  • 1,842
  • 4
  • 19
  • 31

2 Answers2

1

To make the second structure appear like the third, add a calculated field called Mass defined as if Parameter = "Mass" then Value end. Do the same for Signal.

You can then hide the fields Parameter and Value if you like, and work with Mass and Value instead.

Put AVG(Mass) on the Columns Shelf and AVG(Signal) on the Rows shelf -- AVG, not ATTR. Then finally, put [Sample Id] on detail.

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • Thanks, but that doesn't work. When I do that and look at my data, the rows with a value for mass don't have any values for signal and vice versa. – shirewoman2 Nov 16 '17 at 19:14
  • I edited my answer to be explicit about what to do with the new fields I created. The reason you only see mass or signal in a data row is that your data is shaped that way. By placing Compound and Sample Id on the viz (in detail), the fields will be computed on a block of rows that have the same value for Compound and Sample Id. In that context, each mark will have both a mass and Signal value. – Alex Blakemore Nov 17 '17 at 22:34
  • Ah, I see how you're doing that. Yes, that works, too! I didn't realize I needed to add SampleID in the detail. Thanks! – shirewoman2 Nov 17 '17 at 22:53
0

If I had to deal with this, I'd prefer to pre-process the data so that it has the format "SampleID | Compound | Mass | Signal", that would make Tableau chart straightforward.

I think there's a way to achieve the same with the data structure you have, but it's more tricky. So, if I understand correctly, you have the data it this form:

SampleId    Compound    Parameter   Value
standard 5  A           Mass        2.535
standard 5  A           Signal      0.494
standard 5  B           Mass        2.555
standard 5  B           Signal      1.24
standard 25 A           Mass        12.675
standard 25 A           Signal      2.426
standard 25 B           Mass        12.775
standard 25 B           Signal      7.235

1) You can create calculated fields for Mass and Signal using level of detail expressions, that exclude the Parameter granularity:

Mass
{exclude [Parameter] : min(if [Parameter] = 'Mass' then [Value] else NULL end)}

Signal
{exclude [Parameter] : min(if [Parameter] = 'Signal' then [Value] else NULL end)}

That will "collapse" nulls in case Parameter is not included in the view.

2) Using the Scatter Plot visualization, you can pull Mass to columns and Signal to rows, add Compound to Color pane and SampleId to Detail pane. The plot will look like this:

enter image description here

xkavator
  • 376
  • 2
  • 4
  • Yes! That works! THANK YOU! Incidentally, I do agree that it would be better to have the source data structured differently, but for arcane reasons, it would be better if I could restructure the data in Tableau instead. I wanted to know whether Tableau could even do this for the purpose of future experiments. THANKS! – shirewoman2 Nov 17 '17 at 21:56
  • FYI - the "else NULL" is redundant. By default, if there is no else clause specified, null is the result. – Alex Blakemore Nov 17 '17 at 22:36