I would like to divide certain cells in one dataframe by certain cells in another dataframe.
Dataframe1 -- The column names in the dataset are: Compound_Number, Compound_Concentration, Time, Technical_Replicate, and Colony_Count. In total, there are 12 compounds, 3 concentrations, 3 time points, and 6 technical replicates.
Dataframe2 -- The column names are Compound_Number, Technical_Replicate, Time, and Replicate_Mean. In total, there are 12 compounds, 3 time points, and 1 replicate mean.
I need to divide the Colony_Count in Dataframe1 by the Replicate_Mean in Dataframe2 -- but I need to make sure that the division occurs in a paired-fashion (e.g., the compound number must be the same, as well as the time and technical replicate).
I know that I can do all of this by hand...
#Dataframe1 (corpus)
C1_T1_TR1 <- corpus[ which(corpus$Compound_Number==1 & corpus$Technical_Replicate==1 & corpus$Time==1),]
#Dataframe2 (normalizing_means)
NC1_T1_TR1 <- normalizing_means[ which(normalizing_means$Compound_Number==1 & normalizing_means$Time==1 & normalizing_means$Technical_Replicate==1),]$Replicate_Mean
Then I can do...
C1_T1_TR1$Colony_Count/N1_T1
However, that means writing out those lines multiple times in order to catch all the compounds, replicates, and times -- and then merging the results from all those selections and operations back into a single dataframe. This is cumbersome and I am certain there is a better R way of doing it. I did see this: https://stackoverflow.com/questions/33150534/divide-multiple-columns-of-one-data-frame-by-row-names-value-of-another-datafram, but it is not quite what I need -- any assistance is greatly appreciated.
Here is a bit of data.
Dataframe1
Compound_Number Compound_Concentration Time Technical_Replicate Colony_Count
1 0.1 mM 5 4 46000000 #This is the example line
1 0.05 mM 5 4 109000000
1 0.02 mM 5 4 220000000
1 0.1 mM 25 4 30000
1 0.05 mM 25 4 16000000
1 0.02 mM 25 4 340000000
1 0.1 mM 1 1 5000000
1 0.05 mM 1 1 220000000
1 0.02 mM 1 1 210000000
1 0.1 mM 5 1 9000000
1 0.05 mM 5 1 70000000
1 0.02 mM 5 1 57000000
1 0.1 mM 5 2 560000
1 0.05 mM 5 2 34000000
1 0.02 mM 5 2 300000000
1 0.1 mM 25 2 10000
2 0.05 mM 1 3 120000000
2 0.02 mM 1 3 210000000
2 0.1 mM 5 3 280000000
2 0.05 mM 5 3 240000000
2 0.02 mM 5 3 80000000
2 0.1 mM 25 3 110000000
2 0.05 mM 25 3 250000000
2 0.02 mM 25 3 350000000
2 0.1 mM 1 4 290000000
2 0.05 mM 1 4 340000000
2 0.05 mM 1 1 300000000
2 0.02 mM 1 1 110000000
2 0.1 mM 5 1 510000000
2 0.05 mM 5 1 420000000
Dataframe2
Compound_Number Technical_Replicate Time Replicate_Mean
1 1 1 288000000
1 1 5 232000000
1 1 25 230000000
1 2 1 351666666.666667
1 2 5 320000000
1 2 25 291666666.666667
1 3 1 570000000
1 3 5 493333333.333333
1 3 25 701666666.666667
1 4 1 425000000
1 4 5 630000000 #This is the example line
1 4 25 380000000
1 5 1 473333333.333333
1 5 5 463333333.333333
1 5 25 433333333.333333
1 6 1 478333333.333333
1 6 5 453333333.333333
1 6 25 520000000
2 1 1 391666666.666667
2 1 5 356666666.666667
2 1 25 373333333.333333
2 2 1 445000000
2 2 5 423333333.333333
2 2 25 353333333.333333
2 3 1 248333333.333333
2 3 5 281666666.666667
2 3 25 151666666.666667
2 4 1 325000000
2 4 5 360000000
2 4 25 420000000
2 5 1 156666666.666667
2 5 5 298333333.333333
2 5 25 338333333.333333
2 6 1 313333333.333333
2 6 5 318333333.333333
2 6 25 276666666.666667
For clarity this is an example row from Dataframe1:
Compound_Number Compound_Concentration Time Technical_Replicate Colony_Count
1 0.1 mM 5 4 46000000
I need to find the corresponding row in Dataframe2.
Compound_Number Technical_Replicate Time Replicate_Mean
1 4 5 630000000
And I would like to divide 46000000 by 630000000.
Thank you in advance.
