0

I have two data frames like this:

sample data1
1 300
1 200
2 150
2 300
2 250
3 200
3 240
4 180
5 190
... ..
n 200
sample data2
1 150
2 230
3 260
4 200
... ..
n 200

I want data1 to be subtracted from data1 according to the sample. That is, 1: 300-150, 200-150; 2: 150-230, 300-230...

user438383
  • 5,716
  • 8
  • 28
  • 43
ASNSXG
  • 1
  • You'll need to join your data together first on the `sample` columns. See here: https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right for how to do this 'inner join'. Once you have `data2` brought across from the second table, you can then do the subtraction on the same length columns. – thelatemail May 24 '22 at 03:52
  • `transform(merge(df1, df2, all.x = TRUE), subs = data1 - data2)` – Onyambu May 24 '22 at 04:09
  • 'library(plyr) df1 = join(df1, df2, type = "inner")' @thelatemail hi I check the link and find this code works for me! many thanks! – ASNSXG May 24 '22 at 04:22
  • @onyambu hi I tried the code you write but it's failed since the length is different. but inner join the table is work for me. thank you for your suggestion! – ASNSXG May 24 '22 at 04:25
  • the code works look at the solution below. Merge and inner join are exactly the same, you can also use `mutate` instead of transform – Onyambu May 24 '22 at 04:34

1 Answers1

0
transform(merge(df1, df2, all.x = TRUE), subs = data1 - data2)
   sample data1 data2 subs
1       1   200   150   50
2       1   300   150  150
3       2   150   230  -80
4       2   300   230   70
5       2   250   230   20
6       3   200   260  -60
7       3   240   260  -20
8       4   180   200  -20
9       5   190    NA   NA
10      n   200   200    0

df1 <- structure(list(sample = c("1", "1", "2", "2", "2", "3", "3", 
"4", "5", "n"), data1 = c(300L, 200L, 150L, 300L, 250L, 200L, 
240L, 180L, 190L, 200L)), class = "data.frame", row.names = c(NA, 
-10L))

df2 <- structure(list(sample = c("1", "2", "3", "4", "n"), data2 = c(150L, 
230L, 260L, 200L, 200L)), class = "data.frame", row.names = c(NA, 
-5L))
Onyambu
  • 67,392
  • 3
  • 24
  • 53