[R] Antwort: RE: Antwort: Re: Merging variables
Marc Schwartz
marc_schwartz at me.com
Wed Jun 8 16:44:46 CEST 2016
Hi,
Sorry for jumping in late here, but the issue is that you are only specifying "CustId" as the key column to merge (join) on.
Thus, other columns in the two data frames that may have the same name, as is the case with "CustName", have the default suffix as defined by the argument 'suffixes', appended to the column names to make them unique in the resultant data frame. You cannot have two columns with the same name in a data frame.
The description of the 'suffixes' argument, which defaults to c(".x",".y"), is:
"a character vector of length 2 specifying the suffixes to be used for making unique the names of columns in the result which not used for merging (appearing in by etc)."
If you were to use:
merge(ds_temp_1, ds_temp_2, by = c("CustId", "CustName"), all.x = TRUE)
CustId CustName sales CustGroup
1 1001 Miller 100 1
2 1002 Smith 500 2
3 1003 Doe 300 3
4 1004 White 50 NA
5 1005 Black 700 NA
6 1006 Nobody 10 NA
where you do the join on both columns, your issue is resolved.
That, of course, presumes that the combination of CustId and CustName are uniquely associated with each other.
Regards,
Marc Schwartz
> On Jun 8, 2016, at 8:55 AM, G.Maubach at weinwolf.de wrote:
>
> Hi Petr,
>
> thanks for your reply.
>
> I prepared little example for you:
>
> -- cut --
>
> ds_temp_1 <-
> structure(list(
> CustId = c(1001, 1002, 1003, 1004, 1005, 1006),
> CustName = c("Miller", "Smith", "Doe", "White", "Black",
> "Nobody"),
> sales = c(100, 500, 300, 50, 700, 10)
> ),
> .Names = c("CustId",
> "CustName", "sales"), row.names = c(NA, 6L), class =
> "data.frame")
>
> ds_temp_2 <-
> structure(
> list(
> CustId = c(1001, 1002, 1003),
> CustName = c("Miller",
> "Smith", "Doe"),
> CustGroup = c(1, 2, 3)
> ),
> .Names = c("CustId",
> "CustName", "CustGroup"),
> row.names = c(NA, 3L),
> class = "data.frame"
> )
>
> ds_merge <- merge(ds_temp_1, ds_temp_2,
> by.x = "CustId", all.x = TRUE,
> by.y = "CustId", all.y = FALSE)
>
> ds_merge
>
> -- cut --
>
> which gives
>
> ds_merge
> CustId CustName.x sales CustName.y CustGroup
> 1 1001 Miller 100 Miller 1
> 2 1002 Smith 500 Smith 2
> 3 1003 Doe 300 Doe 3
> 4 1004 White 50 <NA> NA
> 5 1005 Black 700 <NA> NA
> 6 1006 Nobody 10 <NA> NA
>
> where CustName is split into CustName.x and CustName.y.
>
> What I would like to have is:
>
> ds_merge
> CustId CustName sales CustGroup
> 1 1001 Miller 100 1
> 2 1002 Smith 500 2
> 3 1003 Doe 300 3
> 4 1004 White 50 NA
> 5 1005 Black 700 NA
> 6 1006 Nobody 10 NA
>
> That is CustName in a single variable cause the values within that
> variable are identical. I guess because of NA for some cases in ds_temp_2
> R generates CustName.x and CustName.y.
>
> Is there a simple way of merging a dataset and having R return a single
> variable is the values are identical or missing in either one of the
> datasets?
>
> Kind regards
>
> Georg
>
>
>
>
>
> Von: PIKAL Petr <petr.pikal at precheza.cz>
> An: "G.Maubach at weinwolf.de" <G.Maubach at weinwolf.de>,
> Kopie: "r-help at r-project.org" <r-help at r-project.org>
> Datum: 07.06.2016 13:11
> Betreff: RE: [R] Antwort: Re: Merging variables
>
>
>
> Hi
>
>> -----Original Message-----
>> From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of
>> G.Maubach at weinwolf.de
>> Sent: Tuesday, June 7, 2016 8:19 AM
>> To: Michael Dewey <lists at dewey.myzen.co.uk>
>> Cc: r-help at r-project.org
>> Subject: [R] Antwort: Re: Merging variables
>>
>> Hi Michael,
>>
>> yes, I was astonished about this behaviour either. I have worked with
> SPSS a
>> lot - and that works different.
>
> If you want to join two data frames by common names you can use use
>
> merge(dat1, dat2, ....)
>
> without specifing by. From help page:
>
> By default the data frames are merged on the columns with names they both
> have, but separate specifications of the columns can be given by by.x and
> by.y. The rows in the two data frames that match on the specified columns
> are extracted, and joined together.
>
>>
>> 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?
>
> copy result of dput directly to your mail
>
> dput(dat)
> structure(list(hz = c(0, 25, 50), vykon = c(0, 11.6, 22.6)), .Names =
> c("hz",
> "vykon"), row.names = c(NA, -3L), class = "data.frame")
>
> We can use
>
> dat <- structure(list(hz = c(0, 25, 50), vykon = c(0, 11.6, 22.6)), .Names
> = c("hz",
> "vykon"), row.names = c(NA, -3L), class = "data.frame")
>
> to reconstruct the object.
>
> Regards
> Petr
>
>>
>> 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 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
More information about the R-help
mailing list