Merge data frames and divide rows by group

user_13249862 0
Viewed 71

I would like to divide the values from df1 over the values from df2. In this reproducible example, I am able to sum these values. What about the division? Thanks in advance!

df1 <- data.frame(country = c("a", "b", "c"), year1 = c(1, 2, 3), year2 = c(1, 2, 3))
df2 <- data.frame(country = c("a", "b", "d"), year1 = c(1, 2, NA), year2 = c(1, 2, 3))

df3 <- bind_rows(df1, df2) %>%
  mutate_if(is.numeric, tidyr::replace_na, 0) %>%
  group_by(country) %>%
  summarise_all(., sum, na.rm = TRUE) %>%
  na_if(., 0)

Expected result is:

# A tibble: 4 x 3
  country year1 year2
  <chr>   <dbl> <dbl>
1 a           1     1
2 b           1     1
3 c          NA    NA
4 d          NA    NA
dplyr dataframe division r
2 Answers

As there are groups with 2 rows and some with 1, use an if/else condition within summarise/across to divide the first element by the last if there are two elements or else return NA

library(dplyr) # version 1.0.4
library(tidyr)
bind_rows(df1, df2) %>% 
    mutate(across(where(is.numeric), replace_na, 0)) %>% 
    group_by(country) %>% 
    summarise(across(everything(), ~ if(n() == 2) first(.)/last(.) 
          else NA_real_))

-output

# A tibble: 4 x 3
#  country year1 year2
#* <chr>   <dbl> <dbl>
#1 a           1     1
#2 b           1     1
#3 c          NA    NA
#4 d          NA    NA
user_37322710

Here is a base R option using merge + split.default

df <- merge(df1, df2, by = "country", all = TRUE)
cbind(
  df[1],
  list2DF(lapply(
    split.default(df[-1], gsub("\\.(x|y)", "", names(df)[-1])),
    function(v) do.call("/", v)
  ))
)

which gives

  country year1 year2
1       a     1     1
2       b     1     1
3       c    NA    NA
4       d    NA    NA
user_121587570
Related