compare two dataframes and get maximum values

Question

I am trying to compare two data frames (df1, df2) of same structure (same dimensions, column names, row names, etc) and keep the maximum values between the two data frames. I actually have hundreds of columns and rows, but here is some pretend data:

df1:
Date       Fruit  Num  Color 
2013-11-24 Banana 2 Yellow
2013-11-24 Orange  8 Orange
2013-11-24 Apple   7 Green
2013-11-24 Celery 10 Green

df2:
Date       Fruit  Num  Color 
2013-11-24 Banana 22 Yellow
2013-11-24 Orange  8 Orange
2013-11-24 Apple   7 Green
2013-11-24 Celery 1 Green

There are many examples on SO doing similar things but in python not R: Comparing two dataframes and getting the differences, Compare two dataframes to get comparison value in in another dataframe etc.

I tried a dplyr approach but I don't know how to do this correctly for all the columns (hundreds).

library(dplyr)
test <- rbind(df1, df2)
test2 <- test %>%
  group_by(Date) %>%
summarise(max = max(.))

Given my pretend data above, the desired output should be:

new.df:
Date       Fruit  Num  Color 
2013-11-24 Banana 22 Yellow
2013-11-24 Orange  8 Orange
2013-11-24 Apple   7 Green
2013-11-24 Celery 10 Green

Thanks for the help.

You could join the tables together by date, fruit, and color and then mutate a new column with the maximum of the two Num values. — cardinal40
That sounds like a nice approach too - I'll see if I can figure out how.. — KNN

M-- M-- · Accepted Answer · 2019-05-20T22:34:37

One possibility is grouping by all the non-numeric columns and then getting the max for numeric ones:

library(tidyverse)

rbind(df1, df2) %>%
    group_by_at(vars(one_of(names(select_if(df2,negate(is.numeric)))))) %>%
    summarise_if(is.numeric, max)

#> # A tibble: 4 x 4
#> # Groups:   Date, Fruit [4]
#>   Date       Fruit  Color    Num
#>   <fct>      <fct>  <fct>  <dbl>
#> 1 2013-11-24 Apple  Green      7
#> 2 2013-11-24 Banana Yellow    22
#> 3 2013-11-24 Celery Green     10
#> 4 2013-11-24 Orange Orange     8

^{Created on 2019-05-20 by the reprex package (v0.2.1)}

You can also try joining two dataframes and then keeping the maximum values:

df1 %>% right_join(df2, by=c("Date","Fruit","Color")) %>% 
        mutate(Num = pmax(Num.x, Num.y)) %>% select(-Num.x, -Num.y)

compare two dataframes and get maximum values

3 Answers