CODEWITHSUNDEEP
rdplyr
KJA
KJA

Reputation: 343

Divide 2 columns from 2 different dataframes

Does anybody know how to divide two columns from two different dataframes when there are multiple columns to id from?

Example:

library(dplyr)
  name <- c('A','A', 
            'B','B')
  month = c("oct 2018", "nov 2018", 
            "oct 2018",  "nov 2018")
  var1 = c("99", "99", 
            "99",  "99")
  value <- seq(1:length(month))
  df1 = data.frame(name, month, var1, value)

  df2 = df1
  df2["var1"] = c("992", "992", "992", "992")
  df2["value"] = c(2, 4, 6, 8)

  df1

  df2

Output

> df1
  name    month var1 value
1    A oct 2018   99     1
2    A nov 2018   99     2
3    B oct 2018   99     3
4    B nov 2018   99     4

> df2
  name    month var1 value
1    A oct 2018  992     2
2    A nov 2018  992     4
3    B oct 2018  992     6
4    B nov 2018  992     8

Does anybody know how to create a new dataframe that divides the "value"-column in df2 by the value column of df1? The method should be possible to use also when there are more columns than in the current example.

Upvotes: 0

Views: 1335

Answers (3)

akrun
akrun

Reputation: 887881

We can use data.table as well to do a join and create the column 'value' by dividing the 'value' column by the corresponding column in the other dataset while joining on 'name' and 'month'

library(data.table)
df3 <- copy(df1)
setDT(df3)[df2, value := value/i.value, on = .(name, month)]
df3
#   name    month var1 value
#1:    A oct 2018   99   0.5
#2:    A nov 2018   99   0.5
#3:    B oct 2018   99   0.5
#4:    B nov 2018   99   0.5

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389265

In base R, we can do merge

df3 <- merge(df1, df2, by = c("name", "month")) 
df3$value <- df3$value.x/df3$value.y
df3
#  name    month var1.x value.x var1.y value.y value
#1    A nov 2018     99       2    992       4   0.5
#2    A oct 2018     99       1    992       2   0.5
#3    B nov 2018     99       4    992       8   0.5
#4    B oct 2018     99       3    992       6   0.5

You can drop value.x and value.y column if they are not needed.

Upvotes: 1

G. Grothendieck
G. Grothendieck

Reputation: 270238

Join the two data frames together and then perform the division and drop unwanted columns that were generated by the join (assuming you want computed value column to replace the value columns from the original data frames). Depending on what you want you may need a different *_join.

library(dplyr)
df1 %>% 
  inner_join(df2, by = c("name", "month")) %>% 
  mutate(value = value.x / value.y) %>%
  select(-value.x, -value.y)

giving:

  name    month var1.x var1.y value
1    A oct 2018     99    992   0.5
2    A nov 2018     99    992   0.5
3    B oct 2018     99    992   0.5
4    B nov 2018     99    992   0.5

Upvotes: 1

Related Questions