R - Replace selective rows data frame

Question

I have a dataframe "A" with the following shape

log P1  P2  P3  P4  P5  Method  Round   #TSamples   #Samples0   #Samples1   FP  TN  TP  FN  Time    Det_Time
data1   2   0   0   0   LOF     1   3   3   0   0   3   0   0   0.00800108909606934 1
data1   2   0   0   0   Mahalanobis 1   3   3   0   0   3   0   0   0.00100016593933105 1
data1   2   0   0   0   Cook    1   3   3   0   0   3   0   0   0.00900101661682129 1
     ...........

And another dataframe "B" with

log P1  P2  P3  P4  P5  Method  Round   #TSamples   #Samples0   #Samples1   FP  TN  TP  FN  Time    Det_Time
data1   2   0   0   0   Mahalanobis 1   3   3   0   0   3   0   0   0.00200080871582031 1
data1   3   0   0   0   Mahalanobis 1   3   3   0   0   3   0   0   0.000999927520751953    1

Basically, I would like to replace the rows "FP", "TN", "TP" and "FN" from the dataframe A from those from dataframe B when the columns "P1", "P2", "P3", "P4", "P5", "Method" and "Round" match in both A and B dataframe.

Raúl

The dataframe A has 7073 and the dataframe B 885, it should generate a dataframe of 7073 but it only generates 885. How can I fix it? It depends on the order in merge function? merge(A,B, ..) or merge(B,A,...)? — Ruser, Nov 26 '15 at 10:56
`merge(A, B, all.x = TRUE)`, you need to specify `by=` argument as well. — zx8754, Nov 26 '15 at 11:17

Berecht · Accepted Answer · 2015-11-26T12:46:58.663

0

Something like this:

  require(sqldf)

    C <- sqldf('select A.log, A.P1, A.P2, A.P3, A.P4, A.P5, A.Method, A.Round,  "A.#TSamples",   "A.#Samples0",   "A.#Samples1", B.FP,  B.TN,  B.TP,  B.FN, A.Time, A.Det_Time
                from A
                inner join B on (A.log = B.log and
                                A.P1 = B.P1 and
                                A.P2 = B.P2 and
                                A.P3 = B.P3 and
                                A.P4 = B.P4 and
                                A.P5 = B.P5 and and
                                A.Method  = B.Method and
                                A.Round = B.Round)              
                ') 

    C <- rbind(A,C)

    C <- C[!duplicated(C[c("log", "P1", "P2", "P3", "P4", "P5", "Method", "Round",  "#TSamples", "#Samples0", "#Samples1", "Time", "Det_Time")]),]

edited Nov 26 '15 at 12:46

answered Nov 26 '15 at 10:59

Berecht

1,085
9
23

It does. However, the rest of data from columns FP, TN, TP and FN, it inserts NA. Why? – Ruser Nov 26 '15 at 11:10
@Ruser - I edited my answer does it work now? – Berecht Nov 26 '15 at 11:16
Appears the following error Error in sqliteSendQuery(con, statement, bind.data) : error in statement: ambiguous column name: FP – Ruser Nov 26 '15 at 11:22
@Ruser - My mistake i will improve it soon – Berecht Nov 26 '15 at 11:26
let me know if you need extra information. Thank you – Ruser Nov 26 '15 at 12:23
Run the whole code again and you will get your desired ouput. – Berecht Nov 26 '15 at 12:47
I didn't check it well, it deletes the values from B not A. So, it leaves the same file than A. – Ruser Nov 26 '15 at 16:03
Does your data has and ID column (a column with unique records for all rows)? – Berecht Nov 27 '15 at 09:50
No, i doesn't. Don't worry, I've solved it using other method. Thank you – Ruser Nov 27 '15 at 12:52

R - Replace selective rows data frame

1 Answers1