Transform table format

Question

I am facing a problem with my data and would like to have some help here.

From this following table

    Diam    Conc        Label
1   187.5   5198444     CP1
2   202.5   17674710    CP1
3   217.5   59262263    CP1
4   232.5   129961103   CP1
5   247.5   108127637   CP1
6   262.5   99810127    CP2
7   277.5   68619462    CP2
8   292.5   45746308    CP2
9   307.5   31190665    CP2
10  322.5   24952532    CP3
11  337.5   16635021    CP3
12  352.5   12476266    CP3
13  367.5   16635021    CP4
14  382.5   13515955    CP4
15  397.5   14555643    CP4
16  412.5   11436577    CP4

I am trying to crate a table as shown below:

length (Diam) = 15000; (from 0.1 to 1500, by 0.1)

        Diam  CP1   CP2  CP3    CP4   
1       0.0   ...   ...  ...    ...
2       0.1   ...   ...  ...    ...
3       0.2   ...   ...  ...    ...
4       0.3   ...   ...  ...    ...
5       0.4   ...   ...  ...    ...
3       0.2   ...   ...  ...    ...
4       0.3   ...   ...  ...    ...
5       0.4   ...   ...  ...    ...

What I am aiming to do is : for each value of Conc in table 1, put this value in table 2 in the corresponding Label column and the corresponding Diam value.

Are you looking for this http://stackoverflow.com/questions/5890584/reshape-data-from-long-to-wide-format-r ? — David Arenburg, Jan 14 '16 at 13:52
what @DavidArenburg said, plus this is not really a dplyr question. You can use `tidyr::spread` which plays nicely with dplyr though. — C8H10N4O2, Jan 14 '16 at 14:02
When you say “file” you seem to actually mean “table”. Whether these come from / are going into files is really irrelevant for the problem at hand. — Konrad Rudolph, Jan 14 '16 at 14:42
I see this question marked as dup, but when I read "for each value of Conc in table 1, put this value in table 2", I don't interpret that as a long to wide reshape. OP **also** needs a reshape, but he's looking for a join, which he won't find in any of the reshape answers (agreed, there should be a pile of answers for joins too, but as the question was stated, I don't find it as duplicated). — PavoDive, Jan 16 '16 at 09:19

score 0 · Answer 1 · answered Jan 14 '16 at 14:55

First, you'll need to reshape your data. I read in your first table as o (original):

o <- reshape(o,idvar="Diam",timevar="Label",direction="wide")

Then you can use package data.table to produce the inner joint:

library(data.table)
setDT(o,Diam)

Then we'll need to define a destination table, dest (notice that I used the lower and higher values of Diam of your original table. If you want the 15000+ steps in the sequence, you'll need to adjust the code accordingly):

 dest <- data.frame(diam=seq(min(o$Diam),max(o$Diam),1))

and make it a data table:

setDT(dest,diam)

and then perform the joint:

dest[o,]

Transform table format

1 Answers1