Computation on 2 data frames using factors

Question

I have two dataframes I need to join the Ct columns together from:

> Df1

  Gene Treatment Replic    Ct
1 ACTA2       CON      1 1.900
2 ACTA2       CON      2 2.780
3 ACTA2       TGF      1 3.001
4 ACTA2       TGF      2 4.010
5  CAV1       CON      1 1.330
6  CAV1       CON      2 2.330
7  CAV1       TGF      1 6.300
8  CAV1       TGF      2 4.200

> Df2

  Gene Treatment Replic    Ct
1 PPIA       CON      1 3.300
2 PPIA       CON      2 1.700
3 PPIA       TGF      1 1.001
4 PPIA       TGF      2 3.700

Wherever both the Treatment and Replic values match in Df1 and Df2, I want to add the Ct value from Df2 to a new column in Df1. ie, I am looking to create this:

 Gene Treatment Replic    Ct  Ct_y
1 ACTA2       CON      1 1.900 3.300
2 ACTA2       CON      2 2.780 1.700
3 ACTA2       TGF      1 3.001 1.001
4 ACTA2       TGF      2 4.010 3.700
5  CAV1       CON      1 1.330 3.300
6  CAV1       CON      2 2.330 1.700
7  CAV1       TGF      1 6.300 1.001
8  CAV1       TGF      2 4.200 3.700

The dataset is large and there are some points where data is missing, so I cannot rely on replicating Df2 and then cbind. Equally, join_left won't work as the keys are not unique. I am lost.

Thanks a lot for any help with this.

Did you try merging? `merge(df1, df2, by = c('Treatment', 'Replic'))` — Sotos, Apr 11 '19 at 12:56

score 0 · Accepted Answer · answered Apr 11 '19 at 12:59

You could something like this-

> data.table::setDT(dt1)
> data.table::setDT(dt2)
> setnames(dt2,"Ct","Ct_y")
> dt1[dt2[,!c("Gene")],on=.(Treatment,Replic)]

    Gene Treatment Replic    Ct  Ct_y
1: ACTA2       CON      1 1.900 3.300
2:  CAV1       CON      1 1.330 3.300
3: ACTA2       CON      2 2.780 1.700
4:  CAV1       CON      2 2.330 1.700
5: ACTA2       TGF      1 3.001 1.001
6:  CAV1       TGF      1 6.300 1.001
7: ACTA2       TGF      2 4.010 3.700
8:  CAV1       TGF      2 4.200 3.700

Awesome! Thanks a lot. Worked like a charm :) – James W Apr 11 '19 at 14:37 — James W, Apr 11 '19 at 14:37

Computation on 2 data frames using factors

1 Answers1