CODEWITHSUNDEEP
rdataframeaddition
dain
dain

Reputation: 752

R - combining two data frames with missing and out of order rows

I have two data frames

merge_test_1 = t(data.frame("cats" = 1, "dogs" = 2, "horses" = 4))
merge_test_2 = t(data.frame("horses" = 1, "dogs" = 3, "rabbits" = 2))

They look like this:

       [,1]
cats      1
dogs      2
horses    4

        [,1]
horses     1
dogs       3
rabbits    2

Note how they don't have all row names in common, and the ones they do share are also in a different order. I would like to combine them together, so that I get something like this as a result:

       [,1]  [,2]
cats      1     0
dogs      2     3
horses    4     1
rabbits   0     2

The order in the final data frame doesn't matter so much, so long as they are combined properly. I tried using the "merge" commands but I couldn't quite figure it out.

Upvotes: 1

Views: 71

Answers (2)

skaminsky
skaminsky

Reputation: 91

I agree with the prior comments about moving the rownames to a variable, but if you'd prefer to not do this and only use base R functions, the following code should work (although the non matching cells will be NA instead of 0). 

merge_test_1 = t(data.frame(cats = 1, dogs = 2, horses = 4))
merge_test_2 = t(data.frame(horses = 1, dogs = 3, rabbits = 2))
merge(merge_test_1, merge_test_2, by = 0, all = TRUE)
#>   Row.names V1.x V1.y
#> 1      cats    1   NA
#> 2      dogs    2    3
#> 3    horses    4    1
#> 4   rabbits   NA    2

Upvotes: 3

Clarinetist
Clarinetist

Reputation: 1187


library(dplyr)
merge_test_1 = t(data.frame(cats = 1, dogs = 2, horses = 4)) %>% as.data.frame()
merge_test_2 = t(data.frame(horses = 1, dogs = 3, rabbits = 2)) %>% as.data.frame()

# change row names into actual column
merge_test_1 <- tibble::rownames_to_column(merge_test_1)
merge_test_2 <- tibble::rownames_to_column(merge_test_2)

df <- full_join(merge_test_1, merge_test_2, by = "rowname")

# change NAs to 0
df[is.na(df)] <- 0

df
#>   rowname V1.x V1.y
#> 1    cats    1    0
#> 2    dogs    2    3
#> 3  horses    4    1
#> 4 rabbits    0    2

Upvotes: 3

Related Questions