0

I want to create a new variable subtracting two variables from two different datasets. But I need that R make the difference only for the values that are referred to the same thing... The "same thing" in my case is the a third variable COD_PROV which indicates the city.

I need that R make the difference between the Real Wage variable of dataset 1 and the Real Wage variable of dataset 2, but only if the COD_PROV of these real wages is the same.

This is an example of the dataset 1

COD_PROV     Real wage 
1            1962,18
6            1742,85
5            1541,81
96           1612,2
4            1574
3            1823,53
103          1584,49
2            1666,21
7            1747,81
10           2066,42
8            1498,01
11           1871,34
9            1770,41
15           2240,03
16           1729,17
17           1773,38
13           1832,57

Datset 2 has the same framework, but some values of COD_PROV are missing

COD_PROV     Real wage 
1            4962,18
6            1542,85
5            3541,81
4            1564
3            1223,53
2            1446,21
7            1557,81
10           2226,42
8            1458,01
11           1843,34
16           1439,17
17           1883,38
13           1992,57

I've tried this

new <- mutate( dataset1, `Wage Difference ` = dataset1$`Real wage` - dataset2$`Real wage` )

but R of course replies

Error in mutate():
ℹ In argument: Wage difference = ... - dataset1$Real wage.
Caused by error:
! Wage difference must be size 105 or 1, not 106.
Run rlang::last_error() to see where the error occurred.

I suppose that the reason is that dataset 2 has less observations than dataset1 ( in particular some values of COD_PROV are missing)... How can I apply the difference only for the same values of COD_PROV ?

io_boh
  • 193
  • 7

2 Answers2

1

I think you should join/merge these together and then calculate the difference.

library(dplyr)
dataset1 %>%
  left_join(dataset2, by = "COD_PROV", suffix = c("", ".y")) %>%
  mutate(diff = `Real wage` - `Real wage.y`)
#    COD_PROV Real wage Real wage.y  diff
# 1         1   1962.18     4962.18 -3000
# 2         6   1742.85     1542.85   200
# 3         5   1541.81     3541.81 -2000
# 4        96   1612.20          NA    NA
# 5         4   1574.00     1564.00    10
# 6         3   1823.53     1223.53   600
# 7       103   1584.49          NA    NA
# 8         2   1666.21     1446.21   220
# 9         7   1747.81     1557.81   190
# 10       10   2066.42     2226.42  -160
# 11        8   1498.01     1458.01    40
# 12       11   1871.34     1843.34    28
# 13        9   1770.41          NA    NA
# 14       15   2240.03          NA    NA
# 15       16   1729.17     1439.17   290
# 16       17   1773.38     1883.38  -110
# 17       13   1832.57     1992.57  -160

For more information on what join/merge means, see How to join (merge) data frames (inner, outer, left, right), What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?.

Data

dataset1 <- structure(list(COD_PROV = c(1L, 6L, 5L, 96L, 4L, 3L, 103L, 2L, 7L, 10L, 8L, 11L, 9L, 15L, 16L, 17L, 13L), "Real wage" = c(1962.18, 1742.85, 1541.81, 1612.2, 1574, 1823.53, 1584.49, 1666.21, 1747.81, 2066.42, 1498.01, 1871.34, 1770.41, 2240.03, 1729.17, 1773.38, 1832.57)), row.names = c(NA, -17L), class = "data.frame")
dataset2 <- structure(list(COD_PROV = c(1L, 6L, 5L, 4L, 3L, 2L, 7L, 10L, 8L, 11L, 16L, 17L, 13L), "Real wage" = c(4962.18, 1542.85, 3541.81, 1564, 1223.53, 1446.21, 1557.81, 2226.42, 1458.01, 1843.34, 1439.17, 1883.38, 1992.57)), row.names = c(NA, -13L), class = "data.frame")
r2evans
  • 141,215
  • 6
  • 77
  • 149
0

We can use {powerjoin}:

powerjoin::power_left_join(dataset1, dataset2, by = "COD_PROV", conflict = `-`)
#>    COD_PROV Real wage
#> 1         1     -3000
#> 2         6       200
#> 3         5     -2000
#> 4        96        NA
#> 5         4        10
#> 6         3       600
#> 7       103        NA
#> 8         2       220
#> 9         7       190
#> 10       10      -160
#> 11        8        40
#> 12       11        28
#> 13        9        NA
#> 14       15        NA
#> 15       16       290
#> 16       17      -110
#> 17       13      -160

Created on 2023-03-17 with reprex v2.0.2

moodymudskipper
  • 46,417
  • 11
  • 121
  • 167