problem in using left_join with 2 dataframes

General
1

--Hi all,

i can't find a soluce to create 2 new columns using 2 dataframes, i have to join them them using common keys.

my first dataframe:

   targets genes_ref1 genes_ref2  MPX
1      18q      H2BC8      RPP30 MPX1
2       9q      H2BC8      RPP30 MPX1
3       Xp      H2BC8      RPP30 MPX1
4      20q    NECTIN2      RPPH1 MPX1
5      17q    NECTIN2      RPPH1 MPX1
6       Yp     SLAIN2      RPPH1 MPX1
7      11p     SLAIN2      RPPH1 MPX1
8      12q      H2BC8      RPP30 MPX2
9       5q     SLAIN2      RPPH1 MPX2
10      7q     SLAIN2      RPPH1 MPX2
11     13q     SLAIN2      RPP30 MPX2
12     12p     SLAIN2      RPP30 MPX2
13      1q    NECTIN2      RPPH1 MPX2
14     17p    NECTIN2      RPPH1 MPX2
15      8q      H2BC8      RPP30 MPX3
16      4q      H2BC8      RPP30 MPX3
17      1p    NECTIN2      RPPH1 MPX3
18     20p    NECTIN2      RPPH1 MPX3
19     14q    NECTIN2      RPPH1 MPX3
20     19p    NECTIN2      RPPH1 MPX3
21      6q     SLAIN2      RPP30 MPX3
22      2q     SLAIN2      RPPH1 MPX4
23      9p      H2BC8      RPP30 MPX4
24     16q      H2BC8      RPP30 MPX4
25      7p    NECTIN2      RPPH1 MPX4
26     15q    NECTIN2      RPPH1 MPX4
27      3p    NECTIN2      RPPH1 MPX4
28     22q    NECTIN2      RPPH1 MPX4

my second dataframe:

   sample samplemix  target targetype concent PoissonConfMax PoissonConfMin    DyeName
1   CTRLM      MPX1     18q   Unknown     335         341.29            329     FAM Lo
2   CTRLM      MPX1     20q   Unknown     393         399.39            386     FAM Hi
3   CTRLM      MPX1   RPP30 Reference     356         362.03            349     HEX Lo
4   CTRLM      MPX1      Yp   Unknown       0           0.13              0     HEX Hi
5   CTRLM      MPX1     17q   Unknown     353         359.67            347     Cy5 Lo
6   CTRLM      MPX1      9q   Unknown     333         338.97            327     Cy5 Hi
7   CTRLM      MPX1   H2BC8 Reference     340         346.09            334   Cy5.5 Lo
8   CTRLM      MPX1   RPPH1 Reference     379         386.06            373   Cy5.5 Hi
9   CTRLM      MPX1      Xp   Unknown     329         334.84            323     ROX Lo
10  CTRLM      MPX1     11p   Unknown     344         350.38            338     ROX Hi
11  CTRLM      MPX1  SLAIN2 Reference     372         378.44            365 ATTO590 Lo
12  CTRLM      MPX1 NECTIN2 Reference     375         381.91            369 ATTO590 Hi
13  CTRLM      MPX2      5q   Unknown     337         343.80            331     FAM Lo
14  CTRLM      MPX2      1q   Unknown     347         353.30            340     FAM Hi
15  CTRLM      MPX2   RPP30 Reference     344         350.32            337     HEX Lo
16  CTRLM      MPX2     12q   Unknown     313         319.20            307     HEX Hi
17  CTRLM      MPX2      7q   Unknown     355         361.67            348     Cy5 Lo
18  CTRLM      MPX2     13q   Unknown     336         342.47            329     Cy5 Hi
19  CTRLM      MPX2   H2BC8 Reference     322         328.08            315   Cy5.5 Lo
20  CTRLM      MPX2   RPPH1 Reference     342         348.54            335   Cy5.5 Hi
21  CTRLM      MPX2     12p   Unknown     316         322.39            310     ROX Lo
22  CTRLM      MPX2     17p   Unknown     343         349.88            337     ROX Hi
23  CTRLM      MPX2  SLAIN2 Reference     339         345.72            333 ATTO590 Lo
24  CTRLM      MPX2 NECTIN2 Reference     337         343.36            330 ATTO590 Hi
25  CTRLM      MPX3      8q   Unknown     318         324.27            312     FAM Lo
26  CTRLM      MPX3      1p   Unknown     343         349.47            337     FAM Hi
27  CTRLM      MPX3   RPP30 Reference     336         342.38            330     HEX Lo
28  CTRLM      MPX3     20p   Unknown     334         339.93            327     HEX Hi
29  CTRLM      MPX3      4q   Unknown     328         334.10            322     Cy5 Lo
30  CTRLM      MPX3     14q   Unknown     351         357.15            344     Cy5 Hi
31  CTRLM      MPX3   H2BC8 Reference     329         335.59            323   Cy5.5 Lo
32  CTRLM      MPX3   RPPH1 Reference     348         354.68            342   Cy5.5 Hi
33  CTRLM      MPX3     19p   Unknown     357         363.22            350     ROX Lo
34  CTRLM      MPX3      6q   Unknown     338         344.15            332     ROX Hi
35  CTRLM      MPX3  SLAIN2 Reference     345         351.11            338 ATTO590 Lo
36  CTRLM      MPX3 NECTIN2 Reference     334         340.07            328 ATTO590 Hi
37  CTRLM      MPX4      2q   Unknown     237         241.91            232     FAM Lo
38  CTRLM      MPX4      7p   Unknown     251         256.65            246     FAM Hi
39  CTRLM      MPX4   RPP30 Reference       0           0.00              0     HEX Lo
40  CTRLM      MPX4      9p   Unknown     224         229.08            219     HEX Hi
41  CTRLM      MPX4     16q   Unknown     222         227.11            217     Cy5 Lo
42  CTRLM      MPX4     15q   Unknown     241         245.79            236     Cy5 Hi
43  CTRLM      MPX4   H2BC8 Reference       0           0.00              0   Cy5.5 Lo
44  CTRLM      MPX4   RPPH1 Reference     258         262.83            252   Cy5.5 Hi
45  CTRLM      MPX4      3p   Unknown     251         255.82            245     ROX Lo
46  CTRLM      MPX4     22q   Unknown     252         257.48            247     ROX Hi
47  CTRLM      MPX4  SLAIN2 Reference     260         265.45            255 ATTO590 Lo
48  CTRLM      MPX4 NECTIN2 Reference     254         259.61            249 ATTO590 Hi

and i need to obtain that:

	sample	samplemix	target	targetype	concent	Mean
1	CTRLM	MPX1	18q	Unknown	335	335/(356+340)
2	CTRLM	MPX1	20q	Unknown	393	339/(375+379)
3	CTRLM	MPX1	RPP30	Reference	356	NA
4	CTRLM	MPX1	Yp	Unknown	0	0/(372+379)
5	CTRLM	MPX1	17q	Unknown	353	353/(372+379)
6	CTRLM	MPX1	9q	Unknown	333	333/(375+379)
7	CTRLM	MPX1	H2BC8	Reference	340	NA
8	CTRLM	MPX1	RPPH1	Reference	379	NA
9	CTRLM	MPX1	Xp	Unknown	329	329/(340+356)
10	CTRLM	MPX1	11p	Unknown	344	344/(372+379)
11	CTRLM	MPX1	SLAIN2	Reference	372	NA
12	CTRLM	MPX1	NECTIN2	Reference	375	NA
13	CTRLM	MPX2	5q	Unknown	337	…
14	CTRLM	MPX2	1q	Unknown	347	…
15	CTRLM	MPX2	RPP30	Reference	344	…
16	CTRLM	MPX2	12q	Unknown	313	…
17	CTRLM	MPX2	7q	Unknown	355	
18	CTRLM	MPX2	13q	Unknown	336	
19	CTRLM	MPX2	H2BC8	Reference	322	
20	CTRLM	MPX2	RPPH1	Reference	342	
21	CTRLM	MPX2	12p	Unknown	316	
22	CTRLM	MPX2	17p	Unknown	343	
23	CTRLM	MPX2	SLAIN2	Reference	339	
24	CTRLM	MPX2	NECTIN2	Reference	337	
25	CTRLM	MPX3	8q	Unknown	318	
26	CTRLM	MPX3	1p	Unknown	343	
27	CTRLM	MPX3	RPP30	Reference	336	
28	CTRLM	MPX3	20p	Unknown	334	
29	CTRLM	MPX3	4q	Unknown	328	
30	CTRLM	MPX3	14q	Unknown	351	
31	CTRLM	MPX3	H2BC8	Reference	329	
32	CTRLM	MPX3	RPPH1	Reference	348	
33	CTRLM	MPX3	19p	Unknown	357	
34	CTRLM	MPX3	6q	Unknown	338	
35	CTRLM	MPX3	SLAIN2	Reference	345	
36	CTRLM	MPX3	NECTIN2	Reference	334	
37	CTRLM	MPX4	2q	Unknown	237	
38	CTRLM	MPX4	7p	Unknown	251	
39	CTRLM	MPX4	RPP30	Reference	0	
40	CTRLM	MPX4	9p	Unknown	224	
41	CTRLM	MPX4	16q	Unknown	222	
42	CTRLM	MPX4	15q	Unknown	241	
43	CTRLM	MPX4	H2BC8	Reference	0	
44	CTRLM	MPX4	RPPH1	Reference	258	
45	CTRLM	MPX4	3p	Unknown	251	
46	CTRLM	MPX4	22q	Unknown	252	
47	CTRLM	MPX4	SLAIN2	Reference	260	
48	CTRLM	MPX4	NECTIN2	Reference	254

do you have a clue to help me ?

2

Show us the code you've tried with left_join().

3 
RefGeneName <- c("RPP30","H2BC8","RPPH1","SLAIN2","NECTIN2")
left_join(data_clean, df_Target_Ref_MPX, by= c('target' = 'targets', 'samplemix'='MPX')) %>% mutate_at(vars(concent,PoissonConfMax,PoissonConfMin), ~ replace(., is.na(.), 0)) %>%
    mutate(mean=sum((target %in% RefGeneName)/concent),
    max_ref=sum((target %in% RefGeneName)*PoissonConfMax),
    min_ref=sum((target %in% RefGeneName)*PoissonConfMin)
  )
4

I am finding impossible to read in your data.

Would it be possible for you to provide the data in dput() format?

Do dput(mydata) where "mydata" is the name of your dataset. Paste it here between

```

```

Thanks

5

I think I see one problem. Your first file has the variable name targets and the second has the variable name targets.

A second problem looks to be that

by= c('target' = 'targets', 'samplemix'='MPX')

seems illegal. I don't normally use the joins in {dplyr} but i do not think that you can subset a data.frame as this seems to be trying to do. Also there in no value "MPX" in that variable


|samplemix| N|
|---------|--|
|MPX1|	11|
|MPX2|	12|
|MPX3|	12|
|MPX4|	12|
6

all my code here:

# function to compute geometric mean removing 0 values if exist
geomean <- function(s){
         s <- s[!is.na(s)]
         if(length(s)>0){
                         gmean=exp(mean(log(s[s>0])))
                         return(gmean)
         }else{
          return(NA)
         }
}

# function to compute STDdev
computeSTDCNV <- function(concT, concRef, confMaxT, confMinT, confMaxRef, confMinRef){
        STDCNV=2*(concT/concRef)*(sqrt(((confMaxT-confMinT)^2/(concT^2))+((confMaxRef-confMinRef)^2/(concRef^2))))
        return(STDCNV)
}

### input data:
structure(list(sample = c("CTRLM", "CTRLM", "CTRLM", "CTRLM", 
"CTRLM", "CTRLM", "CTRLM", "CTRLM", "CTRLM", "CTRLM", "CTRLM", 
"CTRLM", "CTRLM", "CTRLM", "CTRLM", "CTRLM", "CTRLM", "CTRLM", 
"CTRLM", "CTRLM", "CTRLM", "CTRLM", "CTRLM", "CTRLM", "SC1643", 
"SC1643", "SC1643", "SC1643", "SC1643", "SC1643", "SC1643", "SC1643", 
"SC1643", "SC1643", "SC1643", "SC1643", "SC1643", "SC1643", "SC1643", 
"SC1643", "SC1643", "SC1643", "SC1643", "SC1643", "SC1643", "SC1643", 
"SC1643", "SC1643"), samplemix = c("MPX1", "MPX1", "MPX1", "MPX1", 
"MPX1", "MPX1", "MPX2", "MPX2", "MPX2", "MPX2", "MPX2", "MPX2", 
"MPX3", "MPX3", "MPX3", "MPX3", "MPX3", "MPX3", "MPX4", "MPX4", 
"MPX4", "MPX4", "MPX4", "MPX4", "MPX1", "MPX1", "MPX1", "MPX1", 
"MPX1", "MPX1", "MPX2", "MPX2", "MPX2", "MPX2", "MPX2", "MPX2", 
"MPX3", "MPX3", "MPX3", "MPX3", "MPX3", "MPX3", "MPX4", "MPX4", 
"MPX4", "MPX4", "MPX4", "MPX4"), target = c("18q", "RPP30", "17q", 
"H2BC8", "Xp", "SLAIN2", "5q", "RPP30", "7q", "H2BC8", "12p", 
"SLAIN2", "8q", "RPP30", "4q", "H2BC8", "19p", "SLAIN2", "2q", 
"RPP30", "16q", "H2BC8", "3p", "SLAIN2", "20q", "Yp", "9q", "RPPH1", 
"11p", "NECTIN2", "1q", "12q", "13q", "RPPH1", "17p", "NECTIN2", 
"1p", "20p", "14q", "RPPH1", "6q", "NECTIN2", "7p", "9p", "15q", 
"RPPH1", "22q", "NECTIN2"), targetype = c("Unknown", "Reference", 
"Unknown", "Reference", "Unknown", "Reference", "Unknown", "Reference", 
"Unknown", "Reference", "Unknown", "Reference", "Unknown", "Reference", 
"Unknown", "Reference", "Unknown", "Reference", "Unknown", "Reference", 
"Unknown", "Reference", "Unknown", "Reference", "Reference", 
"Unknown", "Unknown", "Reference", "Unknown", "Reference", "Unknown", 
"Unknown", "Unknown", "Reference", "Unknown", "Reference", "Unknown", 
"Unknown", "Unknown", "Reference", "Unknown", "Reference", "Unknown", 
"Unknown", "Unknown", "Reference", "Unknown", "Reference"), concent = c(335.139434814453, 
355.666412353516, 353.325714111328, 339.886322021484, 328.753051757812, 
371.91064453125, 337.206756591797, 343.656555175781, 354.875701904297, 
321.658569335938, 316.038879394531, 339.108917236328, 318.15185546875, 
336.060516357422, 327.876434326172, 329.348358154297, 356.688323974609, 
344.704711914062, 236.901123046875, 243.056610107422, 222.272674560547, 
237.132873535156, 250.647125244141, 260.170623779297, 392.649444580078, 
0, 332.836608886719, 379.448699951172, 344.13525390625, 375.343444824219, 
346.599273681641, 312.8798828125, 335.891510009766, 341.894226074219, 
343.215759277344, 336.768188476562, 343.079406738281, 333.639984130859, 
350.682281494141, 348.233459472656, 337.811553955078, 333.774353027344, 
251.467300415039, 224.220977783203, 240.730239868164, 257.576843261719, 
252.288009643555, 254.40087890625), PoissonConfMax = c(341.292388916016, 
362.032653808594, 359.667816162109, 346.088928222656, 334.8388671875, 
378.443115234375, 343.80029296875, 350.322021484375, 361.665191650391, 
328.077117919922, 322.393615722656, 345.723724365234, 324.274291992188, 
342.376831054688, 334.104583740234, 335.592376708984, 363.224151611328, 
351.113494873047, 241.912811279297, 248.139511108398, 227.112289428711, 
242.147232055664, 255.817016601562, 265.448394775391, 399.391296386719, 
0.134308561682701, 338.965393066406, 386.0576171875, 350.382110595703, 
381.910797119141, 353.29736328125, 319.198547363281, 342.470367431641, 
348.540100097656, 349.876281738281, 343.356872558594, 349.470825195312, 
339.930297851562, 357.154541015625, 354.679748535156, 344.146667480469, 
340.066070556641, 256.646545410156, 229.083740234375, 245.78630065918, 
262.825378417969, 257.476593017578, 259.613464355469), PoissonConfMin = c(329.016479492188, 
349.332244873047, 347.015472412109, 333.714172363281, 322.696533203125, 
365.411926269531, 330.647552490234, 337.026245117188, 348.122619628906, 
315.272552490234, 309.716125488281, 332.528717041016, 312.059051513672, 
329.775787353516, 321.678985595703, 323.135131835938, 350.186309814453, 
338.328430175781, 231.90934753418, 237.994140625, 217.451583862305, 
232.138412475586, 245.498382568359, 254.914932250977, 385.943603515625, 
0, 326.737548828125, 372.874328613281, 337.919219970703, 368.810211181641, 
339.936645507812, 306.592803955078, 329.346893310547, 335.283325195312, 
336.590270996094, 330.213806152344, 336.720275878906, 327.381011962891, 
344.243103027344, 341.820037841797, 331.508209228516, 327.513916015625, 
246.309295654297, 219.376937866211, 235.694412231445, 252.350143432617, 
247.120742797852, 249.209823608398), DyeName = c("FAM Lo", "HEX Lo", 
"Cy5 Lo", "Cy5.5 Lo", "ROX Lo", "ATTO590 Lo", "FAM Lo", "HEX Lo", 
"Cy5 Lo", "Cy5.5 Lo", "ROX Lo", "ATTO590 Lo", "FAM Lo", "HEX Lo", 
"Cy5 Lo", "Cy5.5 Lo", "ROX Lo", "ATTO590 Lo", "FAM Lo", "HEX Lo", 
"Cy5 Lo", "Cy5.5 Lo", "ROX Lo", "ATTO590 Lo", "FAM Hi", "HEX Hi", 
"Cy5 Hi", "Cy5.5 Hi", "ROX Hi", "ATTO590 Hi", "FAM Hi", "HEX Hi", 
"Cy5 Hi", "Cy5.5 Hi", "ROX Hi", "ATTO590 Hi", "FAM Hi", "HEX Hi", 
"Cy5 Hi", "Cy5.5 Hi", "ROX Hi", "ATTO590 Hi", "FAM Hi", "HEX Hi", 
"Cy5 Hi", "Cy5.5 Hi", "ROX Hi", "ATTO590 Hi")), row.names = c(NA, 
-48L), class = c("tbl_df", "tbl", "data.frame"))


structure(list(targets = c("18q", "9q", "Xp", "20q", "17q", "Yp", 
"11p", "12q", "5q", "7q", "13q", "12p", "1q", "17p", "8q", "4q", 
"1p", "20p", "14q", "19p", "6q", "2q", "9p", "16q", "7p", "15q", 
"3p", "22q"), genes_ref1 = c("H2BC8", "H2BC8", "H2BC8", "NECTIN2", 
"NECTIN2", "SLAIN2", "SLAIN2", "H2BC8", "SLAIN2", "SLAIN2", "SLAIN2", 
"SLAIN2", "NECTIN2", "NECTIN2", "H2BC8", "H2BC8", "NECTIN2", 
"NECTIN2", "NECTIN2", "NECTIN2", "SLAIN2", "SLAIN2", "H2BC8", 
"H2BC8", "NECTIN2", "NECTIN2", "NECTIN2", "NECTIN2"), genes_ref2 = c("RPP30", 
"RPP30", "RPP30", "RPPH1", "RPPH1", "RPPH1", "RPPH1", "RPP30", 
"RPPH1", "RPPH1", "RPP30", "RPP30", "RPPH1", "RPPH1", "RPP30", 
"RPP30", "RPPH1", "RPPH1", "RPPH1", "RPPH1", "RPP30", "RPPH1", 
"RPP30", "RPP30", "RPPH1", "RPPH1", "RPPH1", "RPPH1"), MPX = c("MPX1", 
"MPX1", "MPX1", "MPX1", "MPX1", "MPX1", "MPX1", "MPX2", "MPX2", 
"MPX2", "MPX2", "MPX2", "MPX2", "MPX2", "MPX3", "MPX3", "MPX3", 
"MPX3", "MPX3", "MPX3", "MPX3", "MPX4", "MPX4", "MPX4", "MPX4", 
"MPX4", "MPX4", "MPX4")), row.names = c(NA, -28L), class = "data.frame")


# my code:

combine_df<-left_join(data_clean, df_Target_Ref_MPX, by= c('target' = 'targets', 'samplemix'='MPX'))

ref_values <- filter(data_clean,targetype=='Reference') %>%
  select(
    sample,
    samplemix,
    targetref=target,
    ref_concent=concent,
    refConfMax=PoissonConfMax,
    refConfMin=PoissonConfMin
  )

df_cnv <- combine_df %>% left_join(ref_values,by = c("sample","samplemix","genes_ref1" = "targetref")) %>% rename(Ref1_Value = ref_concent, Ref1_ConfMax=refConfMax, Ref1_ConfMin=refConfMin) %>%
                    left_join(ref_values,by = c("sample","samplemix","genes_ref2" = "targetref")) %>% rename(Ref2_Value = ref_concent, Ref2_ConfMax=refConfMax, Ref2_ConfMin=refConfMin) %>%
                     mutate(
    Ref_Mean=mapply(function(x, y) geomean(c(x,y)), Ref1_Value, Ref2_Value),
    Ref_Mean_Max=mapply(function(x, y) geomean(c(x,y)), Ref1_ConfMax, Ref2_ConfMax),
    Ref_Mean_Min=mapply(function(x, y) geomean(c(x,y)), Ref1_ConfMin, Ref2_ConfMin),
    STD=base::ifelse(is.na(Ref_Mean), NA_real_, computeSTDCNV(concent, Ref_Mean, PoissonConfMax, PoissonConfMin, Ref_Mean_Max, Ref_Mean_Min)),
    cnv=base::ifelse(is.na(Ref_Mean), NA_real_, 2*concent/Ref_Mean),
    PoissonCNVMax=cnv+STD,
    PoissonCNVMin=cnv-STD
  )

the last part with the use of multiple left_join, I think it can be simplified

7

Ah, thanks for the data and the code. I'll poke around a bit. Having the original data really helps.

I seldom use {dplyr} so it may take a bit of time and you may get back some weird–looking code to start with.

8

I'm not seeing where data_clean is defined.

9

I am assuming that data_clean corresponds to @ mslider's first data.frame in the first post. I am also interpreting df_Target_Ref_MPX as the second data.frame.

So far it seems reasonable but other things get confusing as one goes on.

1 Like
10

Following @jrkrideau's guidance I think you may have just switched the order of the by arguments. Try

combine_df<-left_join(data_clean, df_Target_Ref_MPX, by= c('targets' = 'target', 'MPX' = 'samplemix'))

11

I never thought of anything that obvious! I changed the varname target changed to targets in df_Target_Ref_MPX , but totally missed the reversals.

I think there are some more problems in the code but I may well be missing the obvious.

12

As I mentioned before, I don't usually use {dplyr} so I converted the files you gave us in dput()format to data.table format. I do not think this has any effect on your code bet it makes it easier to do checks on thing as I go along

So FST is the data.table with the variables "targets", "genes_ref1", "genes_ref2" ,"MPX" and the data.table SEC contains "sample", "samplemix", "targets" , "targetype", "concent", "PoissonConfMax", "PoissonConfMin" "DyeName"

I cannot find anything like "targetype" as an option in the filter command in {dplyr} Am I missing something?

ref_values <- filter(FST, targetype == 'Reference')

Also if ref_values is filtering variables from FST, I do not understand this since I do not see where variables like PoissonConfMax are in SEC.

select(
    sample,
    samplemix,
    targetref=target,
    ref_concent=concent,
    refConfMax=PoissonConfMax,
    refConfMin=PoissonConfMin
  )

The error message is not terrible informative.

13

absolutly, data_clean is the first dataframe whereas df_Target_Ref_MPX is the second

14

yes, the right code was:

combine_df<-left_join(data_clean, df_Target_Ref_MPX, by= c('target' = 'targets', 'samplemix'='MPX'))