Merging dataframes with identical column names getting separated columns, in R-CodePudding

I'm just beginning in R. This should be a basic function. I'm trying to merge two dataframes ("with.arthritis" and "withOUT.arthritis"). Each dataframe has one row and two columns (mean_weight and sd_weight). The goal is to have a single table with 2 rows (old table names), 2 columns (old column names). For some reason I'm getting 3 columns.

Input:

Q3 <- merge(with.arthritis, withOUT, by="mean_weight", all=TRUE)
rownames(Q3)<- c("with.arthritis", "withOUT.arthritis")

Output:

                  mean_weight sd_weight.x sd_weight.y
with.arthritis         176.08          NA    46.30651
withOUT.arthritis      183.04    49.81196          NA

How do I get just the 2 columns?

dput(Q3)
structure(list(mean_weight = c(176.08, 183.04), sd_weight.x = c(NA, 
49.8119577586379), sd_weight.y = c(46.3065053125023, NA)), row.names = c("with.arthritis", 
"withOUT.arthritis"), class = "data.frame")

CodePudding user response：

What you can do is use coalesce to merge the columns with NA's like this:

library(dplyr)
Q3 %>% 
  mutate(sd_weight = coalesce(sd_weight.x, sd_weight.y)) %>%
  select(mean_weight, sd_weight)

Output:

                  mean_weight sd_weight
with.arthritis         176.08  46.30651
withOUT.arthritis      183.04  49.81196

CodePudding user response：

You actually just need to specify both merging columns.

merge(with.arthritis, withOUT.arthritis, by=c("mean_weight", "sd_weight"), all=TRUE)
#   mean_weight sd_weight
# 1      176.08  46.30651
# 2      183.04  49.81196

If you have only those two columns each you may even omit it.

merge(with.arthritis, withOUT.arthritis, all=TRUE) 
#   mean_weight sd_weight
# 1      176.08  46.30651
# 2      183.04  49.81196

You may give the rownames in the same step using a pipe.

merge(with.arthritis, withOUT.arthritis, all=TRUE) |> `rownames<-`(c('with.arthritis', 'withOUT.arthritis'))
#                   mean_weight sd_weight
# with.arthritis         176.08  46.30651
# withOUT.arthritis      183.04  49.81196

Data:

with.arthritis <- structure(list(mean_weight = 176.08, sd_weight = 46.30651), class = "data.frame", row.names = c(NA, 
-1L))
withOUT.arthritis <- structure(list(mean_weight = 183.04, sd_weight = 49.81196), class = "data.frame", row.names = c(NA, 
-1L))