Linking long and wide tables through column label

dscho

Hi All, 

 

I am trying to link two data tables for an experiment involving particle size measurements; one table has information measured during an experiment and is stored in a "wide" format, the other contains the particle size measurement in a "long" format:

 

Table 1 - measurements: 

 

Sample ID    RPM             Flocculant               Activity ...etc

S1                100               A1                          10

S2                1000             A1                          5

S3                500               B1                          20

S4                1000             B1                          50

…etc

 

Table 2 – particle size (data is reported as % volume):

 

Size (µm)                S1                S2                S3                S4  ...etc

0.106                      0.1               1.0               0.1               0.1

0.1205                    0.2               2.0               0.1               0.1

0.137                      0.2               3.0               0.1               0.1

0.156                      0.3               6.0               0.1               0.1

… etc               

Is there any way to form a join between “Sample ID” in table 1 and the column headers in Table 2? If I transpose Table 2, I can form the join, but then cannot make the graph that I want from the data –e.g. below (I would then use a local data filter to filter by RPM, flocculant, etc for presentation use)

 

 

Any help much appreciated!

txnelson

I believe all you need to do, is to stack the S1,S2,S3 and S4 column from the second data table and then join your first data table to the stacked data table.