How obtain the difference from two observations from the same column in R

user_14017031 0
Viewed 60

I have a dataset with 3 different indices, and I need the difference between HPI 1 and HPI2 and the difference between HPI2 and HPI3, both for rent and sale. The first thing that comes to my mind is to transform this DF into one with one column per HPI and then create new columns with a simple subtraction, but I don't know how to do it. The other idea is to rest the values from the same column conditioning the operation and the date. This is an example of the data

  ~Index,    ~Value, ~Operation, ~Year,
  "HPI1",    0.9,    "Sale", "2017",
  "HPI2",    1.1,    "Sale", "2017",
  "HPI3",    0.89,   "Sale", "2017",
  "HPI1",    1.12,   "Rent", "2017",
  "HPI2",    0.85,   "Rent", "2017",
  "HPI3",    1.22,   "Rent", "2017",
  "HPI1",  0.91,   "Sale", "2018",
  "HPI2",  1.02,   "Sale", "2018",
  "HPI3",    0.9,    "Sale", "2018",
  "HPI1",    1.1,    "Rent", "2018",
  "HPI2",    0.89,   "Rent", "2018",
  "HPI3",    1.12,   "Rent", "2018",)

I would be very thankful for any help. Thanks!

subtraction r
3 Answers

First of all, you must reorganize your data. The data.table package will help you. Try this (df is your data):

library(data.table)
dt <- as.data.table(df)
dt <- dcast(dt,...~Index,value.var="Value")

Output:

#    Operation Year HPI1 HPI2 HPI3
# 1:      Rent 2017 1.12 0.85 1.22
# 2:      Rent 2018 1.10 0.89 1.12
# 3:      Sale 2017 0.90 1.10 0.89
# 4:      Sale 2018 0.91 1.02 0.90

Then you can calculate whatever you want. Try:

dt <- dt %>% mutate(Col1=HPI1-HPI2, Col2=HPI2-HPI3)

Output:

#    Operation Year HPI1 HPI2 HPI3  Col1  Col2
# 1:      Rent 2017 1.12 0.85 1.22  0.27 -0.37
# 2:      Rent 2018 1.10 0.89 1.12  0.21 -0.23
# 3:      Sale 2017 0.90 1.10 0.89 -0.20  0.21
# 4:      Sale 2018 0.91 1.02 0.90 -0.11  0.12
user_146695450

Here is another way:

library(dplyr)
library(tidyr)

df <- tribble(
  ~Index,    ~Value, ~Operation, ~Year,
  "HPI1",    0.9,    "Sale", "2017",
  "HPI2",    1.1,    "Sale", "2017",
  "HPI3",    0.89,   "Sale", "2017",
  "HPI1",    1.12,   "Rent", "2017",
  "HPI2",    0.85,   "Rent", "2017",
  "HPI3",    1.22,   "Rent", "2017",
  "HPI1",  0.91,   "Sale", "2018",
  "HPI2",  1.02,   "Sale", "2018",
  "HPI3",    0.9,    "Sale", "2018",
  "HPI1",    1.1,    "Rent", "2018",
  "HPI2",    0.89,   "Rent", "2018",
  "HPI3",    1.12,   "Rent", "2018")

df %>%
  group_by(Year, Operation) %>% 
  pivot_wider(names_from = Index, values_from = Value) %>%
  mutate(HPI1_HPI2_diff = HPI1 - HPI2, 
         HPI2_HPI3_diff = HPI2 - HPI3)

# A tibble: 4 x 7
# Groups:   Year, Operation [4]
  Operation Year   HPI1  HPI2  HPI3 HPI1_HPI2_diff HPI2_HPI3_diff
  <chr>     <chr> <dbl> <dbl> <dbl>          <dbl>          <dbl>
1 Sale      2017   0.9   1.1   0.89         -0.2             0.21
2 Rent      2017   1.12  0.85  1.22          0.27           -0.37
3 Sale      2018   0.91  1.02  0.9          -0.110           0.12
4 Rent      2018   1.1   0.89  1.12          0.21           -0.23
user_143145200
  1. Transform to wide format with pivot_wider from tidyr package.
  2. Calculate the difference.
df %>% 
  pivot_wider(
    names_from = "Index", 
    values_from = "Value"
  ) %>% 
  mutate(diff_1v2 = HPI1 - HPI2,
         diff_1_3 = HPI2 - HPI3)

Output:

# A tibble: 4 x 7
  Operation Year   HPI1  HPI2  HPI3 diff_1v2 diff_1_3
  <chr>     <chr> <dbl> <dbl> <dbl>    <dbl>    <dbl>
1 Sale      2017   0.9   1.1   0.89   -0.2       0.21
2 Rent      2017   1.12  0.85  1.22    0.27     -0.37
3 Sale      2018   0.91  1.02  0.9    -0.110     0.12
4 Rent      2018   1.1   0.89  1.12    0.21     -0.23
user_133216470
Related