0

I have two dataframes, I want to subtract the value of column in one dataframe to another, if dataframes are equal in value of another column.

I have two dataframes A and B that are similar to the following:

[A]
Col1   Col2
 1      cat
 2      dog
 3      bird
 4      cat
 5      dog

[B]
 Col1    Col2
[cat]   1
[dog]   2
[bird]  3

I want to be able to add the values A$Col1 + B$Col2 if A$Col2 matches the tag of [B] and create a list with the results that will have the same length as the rows in [A]

I have tried this code 

(A$Col1-B$Col2)[A$Col2==B$Col1]

which seems to work, but a following warning shows up:

longer object length is not a multiple of shorter object length

GileBrt
  • 1,830
  • 3
  • 20
  • 28
Joy In Data Stuff
  • 27
  • 1
  • 6
  • Joy in Data Stuff: to improve your question, please provide the *desired output*. In your case, that would be a dataframe that shows what you want your output to be (typically you hand-code this). [Here is a good read](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). It will increase the chance that someone answers your question in a great way! – Marian Minar Jun 13 '19 at 23:18

2 Answers2

0

Here is a tidyverse-style example to join the two dataframes and substract the columns. You can then take the new column and convert it to a list or whatever you need.

library(tidyverse)

A <- tibble(
  Col1 = 1:5, 
  Col2 = c("cat", "dog", "bird", "cat", "dog")
)
B <- tibble(
  Col1 = c("cat", "dog", "bird"),
  Col2 = 1:3
)

A %>% 
  left_join(B, by = c("Col2" = "Col1")) %>% 
  mutate(Col3 = Col1 - Col2.y)
eastclintw00d
  • 2,250
  • 1
  • 9
  • 18
0

Use Left_Join and then create a new column for difference.

merge(A, B, by.x = "Col2", by.y = "Col1", all.x = TRUE) %>% mutate(Difference = Col1 - Col2.y )

 Col2 Col1 Col2.y Difference
1 bird    3      3          0
2  cat    1      1          0
3  cat    4      1          3
4  dog    2      2          0
5  dog    5      2          3
Jason Mathews
  • 765
  • 3
  • 13