Subtract rows of dataframe from another dataframe based on common value in id columns

Question

I have two dataframes:

> head(df1)
    UG  S N_l N_b Girder1 Girder2 Girder3 Girder4 Girder5 Girder6 Girder7 Source
1   84 12   6   7       6       6       6       6       6       6       6   Code
2  124  9   4   7       4       4       4       4       3       3       3   Code
9   84  9   4   7       4       4       4       4       3       3       3   Code
24 124 12   6   7       6       6       6       6       6       6       6   Code
45 124 15   8   7       8       8       8       8       8       7       3   Code
49  84 15   8   7       8       8       8       8       8       7       3   Code

> head(df2)
  UG  S N_b N_l Girder1 Girder2 Girder3 Girder4 Girder5 Girder6 Girder7 Source
1 84  9   5   3      NA       2       3       3       3       2      NA    CSi
2 84 12   5   4      NA       2       3       4       3       3      NA    CSi
3 84 15   5   5      NA       3       3       5       3       3      NA    CSi
4 92  9   5   3      NA       2       3       3       3       2      NA    CSi
5 92 12   5   4      NA       2       3       4       3       3      NA    CSi
6 92 15   5   5      NA       3       3       5       3       3      NA    CSi

When I want to see the different between the two dataframes for columns that start with Girder.

lanes.difference <- df2[5:11]-df1[5:11]

This only goes in order of row numbers but that is not what I am trying to do. I want to subtract the rows of df1 from df2 when the columns UG, S, N_l, and N_b are the same.

DATA:

> dput(df1)
structure(list(UG = c(84, 124, 84, 124, 124, 84, 84, 124, 116, 
100, 108, 92, 84, 124, 116, 108, 100, 92, 124, 116, 108, 100, 
92, 84), S = c(12, 9, 9, 12, 15, 15, 12, 9, 9, 9, 9, 9, 9, 12, 
12, 12, 12, 12, 15, 15, 15, 15, 15, 15), N_l = c(6, 4, 4, 6, 
8, 8, 4, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5), 
N_b = c(7, 7, 7, 7, 7, 7, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 
5, 5, 5, 5, 5, 5, 5), Girder1 = c(6, 4, 4, 6, 8, 8, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA), Girder2 = c(6, 4, 4, 6, 8, 8, 4, 3, 3, 3, 3, 3, 3, 4, 
4, 4, 4, 4, 5, 5, 5, 5, 5, 5), Girder3 = c(6, 4, 4, 6, 8, 
8, 4, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5), 
Girder4 = c(6, 4, 4, 6, 8, 8, 4, 3, 3, 3, 3, 3, 3, 4, 4, 
4, 4, 4, 5, 5, 5, 5, 5, 5), Girder5 = c(6, 3, 3, 6, 8, 8, 
3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3), Girder6 = c(6, 
3, 3, 6, 7, 7, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
3, 3, 3, 3), Girder7 = c(6, 3, 3, 6, 3, 3, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
Source = c("Code", "Code", "Code", "Code", "Code", "Code", 
"Code", "Code", "Code", "Code", "Code", "Code", "Code", "Code", 
"Code", "Code", "Code", "Code", "Code", "Code", "Code", "Code", 
"Code", "Code")), row.names = c(1L, 2L, 9L, 24L, 45L, 49L, 
67L, 68L, 69L, 73L, 74L, 75L, 83L, 134L, 135L, 137L, 138L, 139L, 
199L, 200L, 204L, 205L, 206L, 211L), class = "data.frame")

> dput(df2)
structure(list(UG = c(84L, 84L, 84L, 92L, 92L, 92L, 100L, 100L, 
100L, 108L, 108L, 108L, 116L, 116L, 116L, 124L, 124L, 124L, 84L, 
84L, 84L, 124L, 124L, 124L), S = c(9L, 12L, 15L, 9L, 12L, 15L, 
9L, 12L, 15L, 9L, 12L, 15L, 9L, 12L, 15L, 9L, 12L, 15L, 9L, 12L, 
15L, 9L, 12L, 15L), N_b = c(5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 7L, 7L, 7L, 7L, 7L, 7L), 
N_l = c(3L, 4L, 5L, 3L, 4L, 5L, 3L, 4L, 5L, 3L, 4L, 5L, 3L, 
4L, 5L, 3L, 4L, 5L, 4L, 6L, 8L, 4L, 6L, 8L), Girder1 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 2L, 3L, 3L, 2L, 3L, 3L), Girder2 = c(2L, 2L, 3L, 
2L, 2L, 3L, 2L, 2L, 3L, 2L, 2L, 3L, 2L, 2L, 3L, 2L, 2L, 3L, 
3L, 3L, 8L, 3L, 3L, 8L), Girder3 = c(3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 6L, 
8L, 3L, 6L, 8L), Girder4 = c(3L, 4L, 5L, 3L, 4L, 5L, 3L, 
4L, 5L, 3L, 4L, 5L, 3L, 4L, 5L, 3L, 4L, 5L, 4L, 6L, 8L, 3L, 
6L, 8L), Girder5 = c(3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 6L, 8L, 3L, 6L, 8L
), Girder6 = c(2L, 3L, 3L, 2L, 3L, 3L, 2L, 3L, 3L, 2L, 3L, 
3L, 2L, 3L, 3L, 2L, 3L, 3L, 3L, 3L, 7L, 3L, 3L, 8L), Girder7 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 3L, 3L, 3L, 3L, 3L, 3L), Source = c("CSi", "CSi", 
"CSi", "CSi", "CSi", "CSi", "CSi", "CSi", "CSi", "CSi", "CSi", 
"CSi", "CSi", "CSi", "CSi", "CSi", "CSi", "CSi", "CSi", "CSi", 
"CSi", "CSi", "CSi", "CSi")), row.names = c(NA, -24L), class = "data.frame")

can you post your df here with dput (https://stackoverflow.com/questions/49994249/example-of-using-dput) so that we can recreate this question — Kay, Sep 21 '20 at 19:08
is there a 1-1 match between the two data frames, ie, if you just sorted both by the four key columns and then do what you did at first `df2_sorted[5:11]-df1_sorted[5:11]` — rawr, Sep 21 '20 at 19:25
Yes, I tried to asnwer the question, Is there a shorter way? — Maral Dorri, Sep 21 '20 at 19:33

score 1 · Accepted Answer · answered Sep 21 '20 at 21:46

First of all I would encourage you to look into the tidyverse package. Calling dataframe columns by names is not a very nice practice, irrespective of whether you use base R or some package.

Otherwise there are many other packages which help tremendously in data manipulation, please google what you prefer.

library(tidyverse)

First I prefix the df name to their respective column names

colnames(df1) <- paste("df1", colnames(df1), sep = "_")
colnames(df2) <- paste("df2", colnames(df2), sep = "_")

Then I join them to one df. I cannot use left join because you don't have a common identity column

df12 <- cbind(df1, df2)

This is the part done in tidyverse. First I filter columns in df1 and df2 that are equal. Then I create (mutate) a new column which takes difference of two Girder columns. You can add as many variables you want in the mutate, I did with Girder2

df12 %>% filter(df1_UG == df2_UG | df1_S == df2_S | df1_N_l == df2_N_l | df1_N_b == df1_N_b) %>% 
mutate(Girder2Diff = df1_Girder2 - df2_Girder2)

You can google mutate, filter to learn more how they work.

This took some time, but hope it is helpful

score 0 · Answer 2 · answered Sep 21 '20 at 19:35

As @Kay suggested, I sorted the two dataframes the same way. I am not sure if there is a shorter way.

order.S <- c(9,12,15)
df1 <- df1[order(match(df1$S, order.S)), ]
order.UG <- c(84,92,100,108,116,124)
df1 <- df1[order(match(df1$UG, order.UG)), ]
order.N_b <- c(5,7)
df1 <- df1[order(match(df1$N_b, order.N_b)), ]
lanes.difference <- df2[5:11]-df1[5:11]

Subtract rows of dataframe from another dataframe based on common value in id columns

2 Answers2