Hi All,
I merged two datasets:
ds_merge1 <- merge(x = ds_bw_customer_4_match, y =
ds_zww_customer_4_match,
by.x = "customer", by.y = "customer",
all.x = TRUE, all.y = FALSE)
R created a new dataset with the variables customer.x and customer.y. I
would like to merge these two variable back together. I wrote a little
function (code can be run) for it:
-- cut --
customer.x <- c("Miller", "Smith", NA,
"Bird", NA)
customer.y <- c("Miller", NA, "Doe",
"Fish", NA)
ds_test <- data.frame(customer.x, customer.y, stringsAsFactors = FALSE)
t_merge_variables <-
function(dataset,
var1,
var2,
merged_var) {
# Initialize
dataset[[merged_var]] = rep(NA, nrow(dataset))
dataset[["mismatch"]] = rep(NA, nrow(dataset))
for (i in 1:nrow(dataset)) {
# Check 1: var1 missing, var2 missing
if (is.na(dataset[[i, var1]]) &
is.na(dataset[[i, var2]])) {
dataset[["mismatch"]] <- 1 # var1 & var2 are missing
# Check 2: var1 filled, var2 missing
} else if (!is.na(dataset[[i, var1]]) &
is.na(dataset[[i, var2]])) {
dataset[[i, merged_var]] <- dataset[[i, var1]]
dataset[["mismatch"]] <- 0
# Check 3: var1 missing, var2 filled
} else if (is.na(dataset[[i, var1]]) &
!is.na(dataset[i, var2])) {
dataset[[i, merged_var]] <- dataset[[i, var2]]
dataset[["mismatch"]] <- 0
# Check 4: var1 == var2
} else if (dataset[[i, var1]] == dataset[[i, var2]]) {
dataset[[i, merged_var]] <- dataset[[i, var1]]
dataset[["mismatch"]] <- 0
# Leftover: var1 != var2
} else {
dataset[[i, merged_var]] <- NA
dataset[["mismatch"]] <- 2 # var1 != var2
} # end if
} # end for
return(dataset)
}
ds_var_merge1 <- t_merge_variables(dataset = ds_test,
var1 = "customer.x",
var2 = "customer.y",
merged_var = "customer")
ds_var_merge1
-- cut --
It is executed without error but delivers the wrong values in the variable
"mismatch". This variable is always 1 although it should be NA, 1 or 2
respectively.
Can you tell me why the variable is not correctly set?
Kind regards
Georg
You loop through each Sent from my iPhone> On Jun 6, 2016, at 8:29 AM, G.Maubach at weinwolf.de wrote: > > Hi All, > > I merged two datasets: > > ds_merge1 <- merge(x = ds_bw_customer_4_match, y = > ds_zww_customer_4_match, > by.x = "customer", by.y = "customer", > all.x = TRUE, all.y = FALSE) > > R created a new dataset with the variables customer.x and customer.y. I > would like to merge these two variable back together. I wrote a little > function (code can be run) for it: > > -- cut -- > > customer.x <- c("Miller", "Smith", NA, "Bird", NA) > customer.y <- c("Miller", NA, "Doe", "Fish", NA) > ds_test <- data.frame(customer.x, customer.y, stringsAsFactors = FALSE) > > t_merge_variables <- > function(dataset, > var1, > var2, > merged_var) { > > # Initialize > dataset[[merged_var]] = rep(NA, nrow(dataset)) > dataset[["mismatch"]] = rep(NA, nrow(dataset)) > > for (i in 1:nrow(dataset)) { > > # Check 1: var1 missing, var2 missing > if (is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[["mismatch"]] <- 1 # var1 & var2 are missing > > # Check 2: var1 filled, var2 missing > } else if (!is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Check 3: var1 missing, var2 filled > } else if (is.na(dataset[[i, var1]]) & > !is.na(dataset[i, var2])) { > dataset[[i, merged_var]] <- dataset[[i, var2]] > dataset[["mismatch"]] <- 0 > > # Check 4: var1 == var2 > } else if (dataset[[i, var1]] == dataset[[i, var2]]) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Leftover: var1 != var2 > } else { > dataset[[i, merged_var]] <- NA > dataset[["mismatch"]] <- 2 # var1 != var2 > } # end if > } # end for > return(dataset) > } > > ds_var_merge1 <- t_merge_variables(dataset = ds_test, > var1 = "customer.x", > var2 = "customer.y", > merged_var = "customer") > > ds_var_merge1 > > -- cut -- > > It is executed without error but delivers the wrong values in the variable > "mismatch". This variable is always 1 although it should be NA, 1 or 2 > respectively. > > Can you tell me why the variable is not correctly set? > > Kind regards > > Georg > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
You loop through each row but during each iteration you assign a value to the entire "mismatch" column. The last value assigned was 1. Sent from my iPhone> On Jun 6, 2016, at 8:29 AM, G.Maubach at weinwolf.de wrote: > > Hi All, > > I merged two datasets: > > ds_merge1 <- merge(x = ds_bw_customer_4_match, y = > ds_zww_customer_4_match, > by.x = "customer", by.y = "customer", > all.x = TRUE, all.y = FALSE) > > R created a new dataset with the variables customer.x and customer.y. I > would like to merge these two variable back together. I wrote a little > function (code can be run) for it: > > -- cut -- > > customer.x <- c("Miller", "Smith", NA, "Bird", NA) > customer.y <- c("Miller", NA, "Doe", "Fish", NA) > ds_test <- data.frame(customer.x, customer.y, stringsAsFactors = FALSE) > > t_merge_variables <- > function(dataset, > var1, > var2, > merged_var) { > > # Initialize > dataset[[merged_var]] = rep(NA, nrow(dataset)) > dataset[["mismatch"]] = rep(NA, nrow(dataset)) > > for (i in 1:nrow(dataset)) { > > # Check 1: var1 missing, var2 missing > if (is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[["mismatch"]] <- 1 # var1 & var2 are missing > > # Check 2: var1 filled, var2 missing > } else if (!is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Check 3: var1 missing, var2 filled > } else if (is.na(dataset[[i, var1]]) & > !is.na(dataset[i, var2])) { > dataset[[i, merged_var]] <- dataset[[i, var2]] > dataset[["mismatch"]] <- 0 > > # Check 4: var1 == var2 > } else if (dataset[[i, var1]] == dataset[[i, var2]]) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Leftover: var1 != var2 > } else { > dataset[[i, merged_var]] <- NA > dataset[["mismatch"]] <- 2 # var1 != var2 > } # end if > } # end for > return(dataset) > } > > ds_var_merge1 <- t_merge_variables(dataset = ds_test, > var1 = "customer.x", > var2 = "customer.y", > merged_var = "customer") > > ds_var_merge1 > > -- cut -- > > It is executed without error but delivers the wrong values in the variable > "mismatch". This variable is always 1 although it should be NA, 1 or 2 > respectively. > > Can you tell me why the variable is not correctly set? > > Kind regards > > Georg > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
Hi Not sure if this is the most effective or general solution but Here you get 2 if the value is same in both columns, 1 if it is only in one column and the other is NA and 0 if there is mismatch of values. temp <- (ds_test[,2] %in% ds_test[,1])+(ds_test[,1] %in% ds_test[,2]) here you get 0 if the value is same or if there is mismatch, 1 if NA is in first column, 2 if it is in second and 3 if in both. temp2 <- (is.na(ds_test[,2])+2*is.na(ds_test[,1])) and with combination you get 1 if you want value from first column, 2 if from second, 4 if they are both NA, and -1 if there is mismatch. temp2 + temp - 1 You could then construct ifelse command to select proper value. Regards Petr> ds_testcustomer.x customer.y 1 Miller Miller 2 Smith <NA> 3 <NA> Doe 4 Bird Fish 5 <NA> <NA>> ds_test+tempError in FUN(left, right) : non-numeric argument to binary operator> (is.na(ds_test[,1])+2*is.na(ds_test[,2]))+temp[1] 2 3 2 0 5> (is.na(ds_test[,1])+2*is.na(ds_test[,2]))+temp-2[1] 0 1 0 -2 3> (is.na(ds_test[,1])+2*is.na(ds_test[,2]))+temp-1[1] 1 2 1 -1 4> is.na(ds_test[,2])+2*is.na(ds_test[,1])[1] 0 1 2 0 3> (is.na(ds_test[,2])+2*is.na(ds_test[,1]))+temp-1[1] 1 1 2 -1 4> (is.na(ds_test[,2])+2*is.na(ds_test[,1]))+temp-1> -----Original Message----- > From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of > G.Maubach at weinwolf.de > Sent: Monday, June 6, 2016 2:30 PM > To: r-help at r-project.org > Subject: [R] Merging variables > > Hi All, > > I merged two datasets: > > ds_merge1 <- merge(x = ds_bw_customer_4_match, y > ds_zww_customer_4_match, > by.x = "customer", by.y = "customer", > all.x = TRUE, all.y = FALSE) > > R created a new dataset with the variables customer.x and customer.y. I > would like to merge these two variable back together. I wrote a little function > (code can be run) for it: > > -- cut -- > > customer.x <- c("Miller", "Smith", NA, "Bird", NA) > customer.y <- c("Miller", NA, "Doe", "Fish", NA) > ds_test <- data.frame(customer.x, customer.y, stringsAsFactors = FALSE) > > t_merge_variables <- > function(dataset, > var1, > var2, > merged_var) { > > # Initialize > dataset[[merged_var]] = rep(NA, nrow(dataset)) > dataset[["mismatch"]] = rep(NA, nrow(dataset)) > > for (i in 1:nrow(dataset)) { > > # Check 1: var1 missing, var2 missing > if (is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[["mismatch"]] <- 1 # var1 & var2 are missing > > # Check 2: var1 filled, var2 missing > } else if (!is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Check 3: var1 missing, var2 filled > } else if (is.na(dataset[[i, var1]]) & > !is.na(dataset[i, var2])) { > dataset[[i, merged_var]] <- dataset[[i, var2]] > dataset[["mismatch"]] <- 0 > > # Check 4: var1 == var2 > } else if (dataset[[i, var1]] == dataset[[i, var2]]) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Leftover: var1 != var2 > } else { > dataset[[i, merged_var]] <- NA > dataset[["mismatch"]] <- 2 # var1 != var2 > } # end if > } # end for > return(dataset) > } > > ds_var_merge1 <- t_merge_variables(dataset = ds_test, > var1 = "customer.x", > var2 = "customer.y", > merged_var = "customer") > > ds_var_merge1 > > -- cut -- > > It is executed without error but delivers the wrong values in the variable > "mismatch". This variable is always 1 although it should be NA, 1 or 2 > respectively. > > Can you tell me why the variable is not correctly set? > > Kind regards > > Georg > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html > and provide commented, minimal, self-contained, reproducible code.________________________________ Tento e-mail a jak?koliv k n?mu p?ipojen? dokumenty jsou d?v?rn? a jsou ur?eny pouze jeho adres?t?m. Jestli?e jste obdr?el(a) tento e-mail omylem, informujte laskav? neprodlen? jeho odes?latele. Obsah tohoto emailu i s p??lohami a jeho kopie vyma?te ze sv?ho syst?mu. Nejste-li zam??len?m adres?tem tohoto emailu, nejste opr?vn?ni tento email jakkoliv u??vat, roz?i?ovat, kop?rovat ?i zve?ej?ovat. Odes?latel e-mailu neodpov?d? za eventu?ln? ?kodu zp?sobenou modifikacemi ?i zpo?d?n?m p?enosu e-mailu. V p??pad?, ?e je tento e-mail sou??st? obchodn?ho jedn?n?: - vyhrazuje si odes?latel pr?vo ukon?it kdykoliv jedn?n? o uzav?en? smlouvy, a to z jak?hokoliv d?vodu i bez uveden? d?vodu. - a obsahuje-li nab?dku, je adres?t opr?vn?n nab?dku bezodkladn? p?ijmout; Odes?latel tohoto e-mailu (nab?dky) vylu?uje p?ijet? nab?dky ze strany p??jemce s dodatkem ?i odchylkou. - trv? odes?latel na tom, ?e p??slu?n? smlouva je uzav?ena teprve v?slovn?m dosa?en?m shody na v?ech jej?ch n?le?itostech. - odes?latel tohoto emailu informuje, ?e nen? opr?vn?n uzav?rat za spole?nost ??dn? smlouvy s v?jimkou p??pad?, kdy k tomu byl p?semn? zmocn?n nebo p?semn? pov??en a takov? pov??en? nebo pln? moc byly adres?tovi tohoto emailu p??padn? osob?, kterou adres?t zastupuje, p?edlo?eny nebo jejich existence je adres?tovi ?i osob? j?m zastoupen? zn?m?. This e-mail and any documents attached to it may be confidential and are intended only for its intended recipients. If you received this e-mail by mistake, please immediately inform its sender. Delete the contents of this e-mail with all attachments and its copies from your system. If you are not the intended recipient of this e-mail, you are not authorized to use, disseminate, copy or disclose this e-mail in any manner. The sender of this e-mail shall not be liable for any possible damage caused by modifications of the e-mail or by delay with transfer of the email. In case that this e-mail forms part of business dealings: - the sender reserves the right to end negotiations about entering into a contract in any time, for any reason, and without stating any reasoning. - if the e-mail contains an offer, the recipient is entitled to immediately accept such offer; The sender of this e-mail (offer) excludes any acceptance of the offer on the part of the recipient containing any amendment or variation. - the sender insists on that the respective contract is concluded only upon an express mutual agreement on all its aspects. - the sender of this e-mail informs that he/she is not authorized to enter into any contracts on behalf of the company except for cases in which he/she is expressly authorized to do so in writing, and such authorization or power of attorney is submitted to the recipient or the person represented by the recipient, or the existence of such authorization is known to the recipient of the person represented by the recipient.
X-Originating-<%= hostname %>-IP: [217.155.205.190] Dear Georg I find it a bit surprising that you end up with customer.x and customer.y. Can you share with us a toy example of two data.frames which exhibit this behaviour? On 06/06/2016 13:29, G.Maubach at weinwolf.de wrote:> Hi All, > > I merged two datasets: > > ds_merge1 <- merge(x = ds_bw_customer_4_match, y > ds_zww_customer_4_match, > by.x = "customer", by.y = "customer", > all.x = TRUE, all.y = FALSE) > > R created a new dataset with the variables customer.x and customer.y. I > would like to merge these two variable back together. I wrote a little > function (code can be run) for it: > > -- cut -- > > customer.x <- c("Miller", "Smith", NA, "Bird", NA) > customer.y <- c("Miller", NA, "Doe", "Fish", NA) > ds_test <- data.frame(customer.x, customer.y, stringsAsFactors = FALSE) > > t_merge_variables <- > function(dataset, > var1, > var2, > merged_var) { > > # Initialize > dataset[[merged_var]] = rep(NA, nrow(dataset)) > dataset[["mismatch"]] = rep(NA, nrow(dataset)) > > for (i in 1:nrow(dataset)) { > > # Check 1: var1 missing, var2 missing > if (is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[["mismatch"]] <- 1 # var1 & var2 are missing > > # Check 2: var1 filled, var2 missing > } else if (!is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Check 3: var1 missing, var2 filled > } else if (is.na(dataset[[i, var1]]) & > !is.na(dataset[i, var2])) { > dataset[[i, merged_var]] <- dataset[[i, var2]] > dataset[["mismatch"]] <- 0 > > # Check 4: var1 == var2 > } else if (dataset[[i, var1]] == dataset[[i, var2]]) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Leftover: var1 != var2 > } else { > dataset[[i, merged_var]] <- NA > dataset[["mismatch"]] <- 2 # var1 != var2 > } # end if > } # end for > return(dataset) > } > > ds_var_merge1 <- t_merge_variables(dataset = ds_test, > var1 = "customer.x", > var2 = "customer.y", > merged_var = "customer") > > ds_var_merge1 > > -- cut -- > > It is executed without error but delivers the wrong values in the variable > "mismatch". This variable is always 1 although it should be NA, 1 or 2 > respectively. > > Can you tell me why the variable is not correctly set? > > Kind regards > > Georg > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >-- Michael http://www.dewey.myzen.co.uk/home.html
Hi Michael it is simple set.seed(111) let=sample(letters[1:10],6, replace=T) dat1<-data.frame(let=let, customer=sample(1:10,6, replace=T)) let=sample(letters[1:10],6, replace=T) dat2<-data.frame(let=let, customer=sample(1:10,6, replace=T)) merge(dat1, dat2, by.x="let", by.y="let", all=T) Of course you could add customer variable to by parameter but sometimes it is necessary to leave it out. When you have two sets of analytical results and you have 2 variables operator but you want to merge those sets e.g. by date/hour of analysis. Regards Petr> -----Original Message----- > From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of Michael > Dewey > Sent: Monday, June 6, 2016 3:46 PM > To: G.Maubach at weinwolf.de; r-help at r-project.org > Subject: Re: [R] Merging variables > > X-Originating-<%= hostname %>-IP: [217.155.205.190] > > Dear Georg > > I find it a bit surprising that you end up with customer.x and customer.y. Can > you share with us a toy example of two data.frames which exhibit this > behaviour? > > On 06/06/2016 13:29, G.Maubach at weinwolf.de wrote: > > Hi All, > > > > I merged two datasets: > > > > ds_merge1 <- merge(x = ds_bw_customer_4_match, y > > ds_zww_customer_4_match, > > by.x = "customer", by.y = "customer", > > all.x = TRUE, all.y = FALSE) > > > > R created a new dataset with the variables customer.x and customer.y. > > I would like to merge these two variable back together. I wrote a > > little function (code can be run) for it: > > > > -- cut -- > > > > customer.x <- c("Miller", "Smith", NA, "Bird", NA) > > customer.y <- c("Miller", NA, "Doe", "Fish", NA) > > ds_test <- data.frame(customer.x, customer.y, stringsAsFactors > > FALSE) > > > > t_merge_variables <- > > function(dataset, > > var1, > > var2, > > merged_var) { > > > > # Initialize > > dataset[[merged_var]] = rep(NA, nrow(dataset)) > > dataset[["mismatch"]] = rep(NA, nrow(dataset)) > > > > for (i in 1:nrow(dataset)) { > > > > # Check 1: var1 missing, var2 missing > > if (is.na(dataset[[i, var1]]) & > > is.na(dataset[[i, var2]])) { > > dataset[["mismatch"]] <- 1 # var1 & var2 are missing > > > > # Check 2: var1 filled, var2 missing > > } else if (!is.na(dataset[[i, var1]]) & > > is.na(dataset[[i, var2]])) { > > dataset[[i, merged_var]] <- dataset[[i, var1]] > > dataset[["mismatch"]] <- 0 > > > > # Check 3: var1 missing, var2 filled > > } else if (is.na(dataset[[i, var1]]) & > > !is.na(dataset[i, var2])) { > > dataset[[i, merged_var]] <- dataset[[i, var2]] > > dataset[["mismatch"]] <- 0 > > > > # Check 4: var1 == var2 > > } else if (dataset[[i, var1]] == dataset[[i, var2]]) { > > dataset[[i, merged_var]] <- dataset[[i, var1]] > > dataset[["mismatch"]] <- 0 > > > > # Leftover: var1 != var2 > > } else { > > dataset[[i, merged_var]] <- NA > > dataset[["mismatch"]] <- 2 # var1 != var2 > > } # end if > > } # end for > > return(dataset) > > } > > > > ds_var_merge1 <- t_merge_variables(dataset = ds_test, > > var1 = "customer.x", > > var2 = "customer.y", > > merged_var = "customer") > > > > ds_var_merge1 > > > > -- cut -- > > > > It is executed without error but delivers the wrong values in the > > variable "mismatch". This variable is always 1 although it should be > > NA, 1 or 2 respectively. > > > > Can you tell me why the variable is not correctly set? > > > > Kind regards > > > > Georg > > > > ______________________________________________ > > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > https://stat.ethz.ch/mailman/listinfo/r-help > > PLEASE do read the posting guide > > http://www.R-project.org/posting-guide.html > > and provide commented, minimal, self-contained, reproducible code. > > > > -- > Michael > http://www.dewey.myzen.co.uk/home.html > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html > and provide commented, minimal, self-contained, reproducible code.________________________________ Tento e-mail a jak?koliv k n?mu p?ipojen? dokumenty jsou d?v?rn? a jsou ur?eny pouze jeho adres?t?m. Jestli?e jste obdr?el(a) tento e-mail omylem, informujte laskav? neprodlen? jeho odes?latele. Obsah tohoto emailu i s p??lohami a jeho kopie vyma?te ze sv?ho syst?mu. Nejste-li zam??len?m adres?tem tohoto emailu, nejste opr?vn?ni tento email jakkoliv u??vat, roz?i?ovat, kop?rovat ?i zve?ej?ovat. Odes?latel e-mailu neodpov?d? za eventu?ln? ?kodu zp?sobenou modifikacemi ?i zpo?d?n?m p?enosu e-mailu. V p??pad?, ?e je tento e-mail sou??st? obchodn?ho jedn?n?: - vyhrazuje si odes?latel pr?vo ukon?it kdykoliv jedn?n? o uzav?en? smlouvy, a to z jak?hokoliv d?vodu i bez uveden? d?vodu. - a obsahuje-li nab?dku, je adres?t opr?vn?n nab?dku bezodkladn? p?ijmout; Odes?latel tohoto e-mailu (nab?dky) vylu?uje p?ijet? nab?dky ze strany p??jemce s dodatkem ?i odchylkou. - trv? odes?latel na tom, ?e p??slu?n? smlouva je uzav?ena teprve v?slovn?m dosa?en?m shody na v?ech jej?ch n?le?itostech. - odes?latel tohoto emailu informuje, ?e nen? opr?vn?n uzav?rat za spole?nost ??dn? smlouvy s v?jimkou p??pad?, kdy k tomu byl p?semn? zmocn?n nebo p?semn? pov??en a takov? pov??en? nebo pln? moc byly adres?tovi tohoto emailu p??padn? osob?, kterou adres?t zastupuje, p?edlo?eny nebo jejich existence je adres?tovi ?i osob? j?m zastoupen? zn?m?. This e-mail and any documents attached to it may be confidential and are intended only for its intended recipients. If you received this e-mail by mistake, please immediately inform its sender. Delete the contents of this e-mail with all attachments and its copies from your system. If you are not the intended recipient of this e-mail, you are not authorized to use, disseminate, copy or disclose this e-mail in any manner. The sender of this e-mail shall not be liable for any possible damage caused by modifications of the e-mail or by delay with transfer of the email. In case that this e-mail forms part of business dealings: - the sender reserves the right to end negotiations about entering into a contract in any time, for any reason, and without stating any reasoning. - if the e-mail contains an offer, the recipient is entitled to immediately accept such offer; The sender of this e-mail (offer) excludes any acceptance of the offer on the part of the recipient containing any amendment or variation. - the sender insists on that the respective contract is concluded only upon an express mutual agreement on all its aspects. - the sender of this e-mail informs that he/she is not authorized to enter into any contracts on behalf of the company except for cases in which he/she is expressly authorized to do so in writing, and such authorization or power of attorney is submitted to the recipient or the person represented by the recipient, or the existence of such authorization is known to the recipient of the person represented by the recipient.
Hi David,
Hi Petr,
many thanks for your help. With your hints I got the idea how I could do
it and I came up with this solution:
-- cut --
#-------------------------------------------------------------------------------
# Module : t_merge_variables.R
# Author : Georg Maubach
# Date : 2016-06-06
# Update : 2016-06-06
# Description : Merge two variables
# Source System : R 3.2.5 (64 Bit)
# Target System : R 3.2.5 (64 Bit)
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
#--------1---------2---------3---------4---------5---------6---------7---------8
t_module_name = "t_merge_variables.R"
t_version = "2016-06-06"
cat(
paste0("\n",
t_module_name, " (Version: ", t_version, ")",
"\n", "\n",
"This software comes with ABSOLUTELY NO WARRANTY.",
"\n", "\n"))
# If do_test is not defined globally define it here locally by
un-commenting it
# Switch t_do_test to TRUE to run test
t_do_test <- FALSE
# [ Function Defintion
]--------------------------------------------------------
t_merge_variables <-
function(dataset,
var1,
var2,
merged_var) {
# Merges two variables with identical, different or missing values
#
# Args:
# dataset (data frame, data table):
# Object with dimnames, e.g. data frame, data table.
# var1 (character):
# Variable 1 to be merged.
# var2 (character):
# Variable 2 to be merged.
# merged_var (class based on input variable, coercion done if
possible):
# Variable with the merged variables var1 and var2.
#
# Operation:
# Var1 and var2 are merged like follows:
# if var1 == var2: merged_var <- var1
# if var1 != var2: merged_var <- -900 (-900 = indicating mismatch)
# if var1 is filled & var2 is missing: merged_var <- var1
# if var1 is missing & var2 is filled: merged_var <- var2
# if var1 is missing & var2 is filled: merged_var <- -999
# (-999 = indicating NA)
#
# Returns:
# Original dataset and variable given in "merged_var" will be
added.
#
# Error handling:
# None.
#
# Credits:
# https://www.mail-archive.com/r-help at r-project.org/msg236012.html
# Initialize
dataset[merged_var] = rep(NA, nrow(dataset))
dataset[merged_var] <-
# Check 1: var1 missing, var2 missing
ifelse(is.na(dataset[, var1]) & is.na(dataset[, var2]),
# then
dataset[[merged_var]] <- 0,
# Check 2: var1 filled, var2 missing
ifelse(!is.na(dataset[, var1]) & is.na(dataset[, var2]),
# then
dataset[[merged_var]] <- dataset[, var1],
# Check 3: var1 missing, var2 filled
ifelse(is.na(dataset[ , var1]) & !is.na(dataset[, var2]),
# then
dataset[[merged_var]] <- dataset[ , var2],
# Check 4: var1 == var2
ifelse(dataset[, var1] == dataset[, var2],
# then: use var1
dataset[[merged_var]] <- dataset[, var1],
#Leftover: var1 != var2
dataset[merged_var] <- 1))))
return(dataset)
}
# [ Test Defintion
]------------------------------------------------------------
t_test <- function(do_test = FALSE) {
if (do_test == TRUE) {
cat("\n", "\n", "Test function t_count_na()",
"\n", "\n")
# Example dataset
customer.x <- c("Miller", "Smith", NA,
"Bird", NA)
customer.y <- c("Miller", NA, "Doe",
"Fish", NA)
ds_test <-
data.frame(customer.x, customer.y, stringsAsFactors = FALSE)
# Call function
ds_merge <- t_merge_variables(
dataset = ds_test,
var1 = "customer.x",
var2 = "customer.y",
merged_var = "customer"
)
# Dataset after function call
ds_merge
}
}
# [ Test Run
]------------------------------------------------------------------
t_test(do_test = t_do_test)
# [ Clean up
]------------------------------------------------------------------
rm("t_do_test", "t_module_name", "t_version",
"t_test")
# EOF
-- cut --
It delivers the customer name if there is one or they match. If they don't
match it delivers 1. If both are missing it delivers 0.
This solution is for my applications sufficient.
Many thanks again for your help and giving me the ideas to solve my data
transformation task.
Kind regards
Georg
Von: PIKAL Petr <petr.pikal at precheza.cz>
An: "G.Maubach at weinwolf.de" <G.Maubach at weinwolf.de>,
"r-help at r-project.org" <r-help at r-project.org>,
Datum: 06.06.2016 15:04
Betreff: RE: [R] Merging variables
Hi
Not sure if this is the most effective or general solution but
Here you get 2 if the value is same in both columns, 1 if it is only in
one column and the other is NA and 0 if there is mismatch of values.
temp <- (ds_test[,2] %in% ds_test[,1])+(ds_test[,1] %in% ds_test[,2])
here you get 0 if the value is same or if there is mismatch, 1 if NA is in
first column, 2 if it is in second and 3 if in both.
temp2 <- (is.na(ds_test[,2])+2*is.na(ds_test[,1]))
and with combination you get 1 if you want value from first column, 2 if
from second, 4 if they are both NA, and -1 if there is mismatch.
temp2 + temp - 1
You could then construct ifelse command to select proper value.
Regards
Petr
> ds_test
customer.x customer.y
1 Miller Miller
2 Smith <NA>
3 <NA> Doe
4 Bird Fish
5 <NA> <NA>> ds_test+temp
Error in FUN(left, right) : non-numeric argument to binary
operator> (is.na(ds_test[,1])+2*is.na(ds_test[,2]))+temp
[1] 2 3 2 0 5> (is.na(ds_test[,1])+2*is.na(ds_test[,2]))+temp-2
[1] 0 1 0 -2 3> (is.na(ds_test[,1])+2*is.na(ds_test[,2]))+temp-1
[1] 1 2 1 -1 4> is.na(ds_test[,2])+2*is.na(ds_test[,1])
[1] 0 1 2 0 3> (is.na(ds_test[,2])+2*is.na(ds_test[,1]))+temp-1
[1] 1 1 2 -1 4> (is.na(ds_test[,2])+2*is.na(ds_test[,1]))+temp-1
> -----Original Message-----
> From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of
> G.Maubach at weinwolf.de
> Sent: Monday, June 6, 2016 2:30 PM
> To: r-help at r-project.org
> Subject: [R] Merging variables
>
> Hi All,
>
> I merged two datasets:
>
> ds_merge1 <- merge(x = ds_bw_customer_4_match, y >
ds_zww_customer_4_match,
> by.x = "customer", by.y = "customer",
> all.x = TRUE, all.y = FALSE)
>
> R created a new dataset with the variables customer.x and customer.y. I
> would like to merge these two variable back together. I wrote a little
function> (code can be run) for it:
>
> -- cut --
>
> customer.x <- c("Miller", "Smith", NA,
"Bird", NA)
> customer.y <- c("Miller", NA, "Doe",
"Fish", NA)
> ds_test <- data.frame(customer.x, customer.y, stringsAsFactors = FALSE)
>
> t_merge_variables <-
> function(dataset,
> var1,
> var2,
> merged_var) {
>
> # Initialize
> dataset[[merged_var]] = rep(NA, nrow(dataset))
> dataset[["mismatch"]] = rep(NA, nrow(dataset))
>
> for (i in 1:nrow(dataset)) {
>
> # Check 1: var1 missing, var2 missing
> if (is.na(dataset[[i, var1]]) &
> is.na(dataset[[i, var2]])) {
> dataset[["mismatch"]] <- 1 # var1 & var2 are
missing
>
> # Check 2: var1 filled, var2 missing
> } else if (!is.na(dataset[[i, var1]]) &
> is.na(dataset[[i, var2]])) {
> dataset[[i, merged_var]] <- dataset[[i, var1]]
> dataset[["mismatch"]] <- 0
>
> # Check 3: var1 missing, var2 filled
> } else if (is.na(dataset[[i, var1]]) &
> !is.na(dataset[i, var2])) {
> dataset[[i, merged_var]] <- dataset[[i, var2]]
> dataset[["mismatch"]] <- 0
>
> # Check 4: var1 == var2
> } else if (dataset[[i, var1]] == dataset[[i, var2]]) {
> dataset[[i, merged_var]] <- dataset[[i, var1]]
> dataset[["mismatch"]] <- 0
>
> # Leftover: var1 != var2
> } else {
> dataset[[i, merged_var]] <- NA
> dataset[["mismatch"]] <- 2 # var1 != var2
> } # end if
> } # end for
> return(dataset)
> }
>
> ds_var_merge1 <- t_merge_variables(dataset = ds_test,
> var1 = "customer.x",
> var2 = "customer.y",
> merged_var = "customer")
>
> ds_var_merge1
>
> -- cut --
>
> It is executed without error but delivers the wrong values in the
variable> "mismatch". This variable is always 1 although it should be NA, 1
or 2
> respectively.
>
> Can you tell me why the variable is not correctly set?
>
> Kind regards
>
> Georg
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-
> guide.html
> and provide commented, minimal, self-contained, reproducible code.
________________________________
Tento e-mail a jak?koliv k n?mu p?ipojen? dokumenty jsou d?v?rn? a jsou
ur?eny pouze jeho adres?t?m.
Jestli?e jste obdr?el(a) tento e-mail omylem, informujte laskav?
neprodlen? jeho odes?latele. Obsah tohoto emailu i s p??lohami a jeho
kopie vyma?te ze sv?ho syst?mu.
Nejste-li zam??len?m adres?tem tohoto emailu, nejste opr?vn?ni tento email
jakkoliv u??vat, roz?i?ovat, kop?rovat ?i zve?ej?ovat.
Odes?latel e-mailu neodpov?d? za eventu?ln? ?kodu zp?sobenou modifikacemi
?i zpo?d?n?m p?enosu e-mailu.
V p??pad?, ?e je tento e-mail sou??st? obchodn?ho jedn?n?:
- vyhrazuje si odes?latel pr?vo ukon?it kdykoliv jedn?n? o uzav?en?
smlouvy, a to z jak?hokoliv d?vodu i bez uveden? d?vodu.
- a obsahuje-li nab?dku, je adres?t opr?vn?n nab?dku bezodkladn? p?ijmout;
Odes?latel tohoto e-mailu (nab?dky) vylu?uje p?ijet? nab?dky ze strany
p??jemce s dodatkem ?i odchylkou.
- trv? odes?latel na tom, ?e p??slu?n? smlouva je uzav?ena teprve
v?slovn?m dosa?en?m shody na v?ech jej?ch n?le?itostech.
- odes?latel tohoto emailu informuje, ?e nen? opr?vn?n uzav?rat za
spole?nost ??dn? smlouvy s v?jimkou p??pad?, kdy k tomu byl p?semn?
zmocn?n nebo p?semn? pov??en a takov? pov??en? nebo pln? moc byly
adres?tovi tohoto emailu p??padn? osob?, kterou adres?t zastupuje,
p?edlo?eny nebo jejich existence je adres?tovi ?i osob? j?m zastoupen?
zn?m?.
This e-mail and any documents attached to it may be confidential and are
intended only for its intended recipients.
If you received this e-mail by mistake, please immediately inform its
sender. Delete the contents of this e-mail with all attachments and its
copies from your system.
If you are not the intended recipient of this e-mail, you are not
authorized to use, disseminate, copy or disclose this e-mail in any
manner.
The sender of this e-mail shall not be liable for any possible damage
caused by modifications of the e-mail or by delay with transfer of the
email.
In case that this e-mail forms part of business dealings:
- the sender reserves the right to end negotiations about entering into a
contract in any time, for any reason, and without stating any reasoning.
- if the e-mail contains an offer, the recipient is entitled to
immediately accept such offer; The sender of this e-mail (offer) excludes
any acceptance of the offer on the part of the recipient containing any
amendment or variation.
- the sender insists on that the respective contract is concluded only
upon an express mutual agreement on all its aspects.
- the sender of this e-mail informs that he/she is not authorized to enter
into any contracts on behalf of the company except for cases in which
he/she is expressly authorized to do so in writing, and such authorization
or power of attorney is submitted to the recipient or the person
represented by the recipient, or the existence of such authorization is
known to the recipient of the person represented by the recipient.
Hi Michael, yes, I was astonished about this behaviour either. I have worked with SPSS a lot - and that works different. I would like to share some of my data. Can you tell me how I can dump a dataset in a way that I can post it here as text? Kind regards Georg Von: Michael Dewey <lists at dewey.myzen.co.uk> An: G.Maubach at weinwolf.de, r-help at r-project.org, Datum: 06.06.2016 15:45 Betreff: Re: [R] Merging variables X-Originating-<%= hostname %>-IP: [217.155.205.190] Dear Georg I find it a bit surprising that you end up with customer.x and customer.y. Can you share with us a toy example of two data.frames which exhibit this behaviour? On 06/06/2016 13:29, G.Maubach at weinwolf.de wrote:> Hi All, > > I merged two datasets: > > ds_merge1 <- merge(x = ds_bw_customer_4_match, y > ds_zww_customer_4_match, > by.x = "customer", by.y = "customer", > all.x = TRUE, all.y = FALSE) > > R created a new dataset with the variables customer.x and customer.y. I > would like to merge these two variable back together. I wrote a little > function (code can be run) for it: > > -- cut -- > > customer.x <- c("Miller", "Smith", NA, "Bird", NA) > customer.y <- c("Miller", NA, "Doe", "Fish", NA) > ds_test <- data.frame(customer.x, customer.y, stringsAsFactors = FALSE) > > t_merge_variables <- > function(dataset, > var1, > var2, > merged_var) { > > # Initialize > dataset[[merged_var]] = rep(NA, nrow(dataset)) > dataset[["mismatch"]] = rep(NA, nrow(dataset)) > > for (i in 1:nrow(dataset)) { > > # Check 1: var1 missing, var2 missing > if (is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[["mismatch"]] <- 1 # var1 & var2 are missing > > # Check 2: var1 filled, var2 missing > } else if (!is.na(dataset[[i, var1]]) & > is.na(dataset[[i, var2]])) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Check 3: var1 missing, var2 filled > } else if (is.na(dataset[[i, var1]]) & > !is.na(dataset[i, var2])) { > dataset[[i, merged_var]] <- dataset[[i, var2]] > dataset[["mismatch"]] <- 0 > > # Check 4: var1 == var2 > } else if (dataset[[i, var1]] == dataset[[i, var2]]) { > dataset[[i, merged_var]] <- dataset[[i, var1]] > dataset[["mismatch"]] <- 0 > > # Leftover: var1 != var2 > } else { > dataset[[i, merged_var]] <- NA > dataset[["mismatch"]] <- 2 # var1 != var2 > } # end if > } # end for > return(dataset) > } > > ds_var_merge1 <- t_merge_variables(dataset = ds_test, > var1 = "customer.x", > var2 = "customer.y", > merged_var = "customer") > > ds_var_merge1 > > -- cut -- > > It is executed without error but delivers the wrong values in thevariable> "mismatch". This variable is always 1 although it should be NA, 1 or 2 > respectively. > > Can you tell me why the variable is not correctly set? > > Kind regards > > Georg > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guidehttp://www.R-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code. >-- Michael http://www.dewey.myzen.co.uk/home.html