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.). The original structure of my data (Public/Standard curve example.csv at master · shirewoman2/Public · GitHub ) 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
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
and would that work better?
(For R users, that last bit would be the equivalent of the tidyr package
I'm attaching my actual Tableau workbook.
Nicotine and metabolites.twbx 264.3 KB