filter, spread and take difference between two values

Question

My objective: take the difference between two values in the var column. These two values are the values associated to the dates in the start_date and end_date column. The date column should be filtered by the start_date and end_date values.

I have data which looks like the following:

# A tibble: 26 x 5
   ID       date                start_date end_date       var
   <chr>    <dttm>              <date>     <date>       <dbl>
 1 23582520 2014-06-30 00:00:00 2014-07-31 2015-06-30  0.103 
 2 23582520 2014-07-31 00:00:00 2014-07-31 2015-06-30 -0.0835
 3 23582520 2014-08-31 00:00:00 2014-07-31 2015-06-30  0.0402
 4 23582520 2014-09-30 00:00:00 2014-07-31 2015-06-30 -0.175 
 5 23582520 2014-10-31 00:00:00 2014-07-31 2015-06-30  0.0673
 6 23582520 2014-11-30 00:00:00 2014-07-31 2015-06-30  0.0386
 7 23582520 2014-12-31 00:00:00 2014-07-31 2015-06-30  0.0255
 8 23582520 2015-01-31 00:00:00 2014-07-31 2015-06-30 -0.0400
 9 23582520 2015-02-28 00:00:00 2014-07-31 2015-06-30  0.0470
10 23582520 2015-03-31 00:00:00 2014-07-31 2015-06-30 -0.0293
# … with 16 more rows

With 2 unique ID`s.

I can filter the date down using the following:

x %>%
  filter(date == as.Date(start_date) | date == as.Date(end_date))

Which gives:

# A tibble: 4 x 5
  ID       date                start_date end_date       var
  <chr>    <dttm>              <date>     <date>       <dbl>
1 23582520 2014-07-31 00:00:00 2014-07-31 2015-06-30 -0.0835
2 23582520 2015-06-30 00:00:00 2014-07-31 2015-06-30 -0.0547
3 26550410 2014-07-31 00:00:00 2014-07-31 2015-06-30 -0.0644
4 26550410 2015-06-30 00:00:00 2014-07-31 2015-06-30  0.0357

Now here I want to spread the data based on the date column. So it will look like:

ID         date        var_date_2014_07_31   var_date_2015_06_30
23582520   2014-07-31  -0.0835                   -0.0547
26550410   2014-07-31  -0.0644                    0.0357

Then I can calculate the difference between these two columns.

Hopefully this is a much clearer question.

Data:

data <- structure(list(ID = c("23582520", "23582520", "23582520", "23582520", 
"23582520", "23582520", "23582520", "23582520", "23582520", "23582520", 
"23582520", "23582520", "23582520", "26550410", "26550410", "26550410", 
"26550410", "26550410", "26550410", "26550410", "26550410", "26550410", 
"26550410", "26550410", "26550410", "26550410"), date = structure(c(1404086400, 
1406764800, 1409443200, 1412035200, 1414713600, 1417305600, 1419984000, 
1422662400, 1425081600, 1427760000, 1430352000, 1433030400, 1435622400, 
1404086400, 1406764800, 1409443200, 1412035200, 1414713600, 1417305600, 
1419984000, 1422662400, 1425081600, 1427760000, 1430352000, 1433030400, 
1435622400), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    start_date = structure(c(16282, 16282, 16282, 16282, 16282, 
    16282, 16282, 16282, 16282, 16282, 16282, 16282, 16282, 16282, 
    16282, 16282, 16282, 16282, 16282, 16282, 16282, 16282, 16282, 
    16282, 16282, 16282), class = "Date"), end_date = structure(c(16616, 
    16616, 16616, 16616, 16616, 16616, 16616, 16616, 16616, 16616, 
    16616, 16616, 16616, 16616, 16616, 16616, 16616, 16616, 16616, 
    16616, 16616, 16616, 16616, 16616, 16616, 16616), class = "Date"), 
    var = c(0.102981060743332, -0.0835381224751472, 0.0402144975960255, 
    -0.17477397620678, 0.0672925934195518, 0.0386120080947876, 
    0.0254716500639916, -0.0400183498859406, 0.0469573326408863, 
    -0.0292906425893307, 0.0193761736154556, 0.0120538137853146, 
    -0.0546624027192593, 0.0234585143625736, -0.064396433532238, 
    0.0212319251149893, 0.02939822524786, 0.0147255659103394, 
    0.0681618079543114, -0.117890320718288, 0.10926142334938, 
    -0.0095117473974824, 0.0205932725220919, 0.095668613910675, 
    0.0239877179265022, 0.0357008874416351)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -26L))

EDIT: Does this solve my issue?

> data %>%
+   filter(date == as.Date(start_date) | date == as.Date(end_date)) %>%
+   spread(key = date, value = var)
# A tibble: 2 x 5
  ID       start_date end_date   `2014-07-31` `2015-06-30`
  <chr>    <date>     <date>            <dbl>        <dbl>
1 23582520 2014-07-31 2015-06-30      -0.0835      -0.0547
2 26550410 2014-07-31 2015-06-30      -0.0644       0.0357

How can I better do this if I have multiple dates and not just 2014-07-31 and 2015-06-30.

EDIT: On the full data:

Error: Each row of output must be identified by a unique combination of keys.
Keys are shared for 92 rows:
* 512, 4359
* 3019, 5482
* 3946, 5084
* 1556, 3228
* 822, 5501
* 1985, 5155
* 4634, 4636
* 3706, 5800
* 4778, 4780
* 1478, 1480
* 2152, 2154
* 2871, 3835
* 3012, 3999
* 3366, 5329
* 1736, 5655
* 419, 3317
* 1219, 5466
* 4530, 5578
* 954, 4856
* 3569, 3571
* 513, 4360
* 3020, 5483
* 3947, 5085
* 1557, 3229
* 823, 5502
* 1986, 5156
* 4635, 4637
* 3707, 5801
* 4779, 4781
* 1479, 1481
* 2153, 2155
* 2872, 3836
* 3013, 4000
* 3367, 5330
* 1737, 5656
* 420, 3318
* 1220, 5467
* 4531, 5579
* 955, 4857
* 3570, 3572
* 2110, 3265
* 4101, 4371
* 1574, 3230
* 2111, 3266
* 4102, 4372
* 1575, 3231
Do you need to create unique ID with tibble::rowid_to_column()?
Call `rlang::last_error()` to see a backtrace

EDIT 2:

Running:

x %>%
  filter(date == as.Date(start_date) | date == as.Date(end_date)) %>%
  mutate(id = row_number()) %>%
  spread(key = date, value = var) %>%
  mutate(diff = `2014-07-31` -`2015-06-30` )

# A tibble: 4 x 7
  ID       start_date end_date      id `2014-07-31` `2015-06-30`  diff
  <chr>    <date>     <date>     <int>        <dbl>        <dbl> <dbl>
1 23582520 2014-07-31 2015-06-30     1      -0.0835      NA         NA
2 23582520 2014-07-31 2015-06-30     2      NA           -0.0547    NA
3 26550410 2014-07-31 2015-06-30     3      -0.0644      NA         NA
4 26550410 2014-07-31 2015-06-30     4      NA            0.0357    NA

Answer 1

if you are only interested in the difference of var you do not need to spread the data. You can filter and then group by ID:

data %>%
  filter(date == as.Date(start_date) | date == as.Date(end_date)) %>%
  arrange(date) %>%
  group_by(ID, start_date, end_date) %>%
  summarise(var_diff = var[2] - var[1],
            var_start = var[1],
            var_end = var[2])

# A tibble: 2 x 6
# Groups:   ID, start_date [2]
  ID       start_date end_date   var_diff var_start var_end
  <chr>    <date>     <date>        <dbl>     <dbl>   <dbl>
1 23582520 2014-07-31 2015-06-30   0.0289   -0.0835 -0.0547
2 26550410 2014-07-31 2015-06-30   0.100    -0.0644  0.0357

If you want to keep the var column you can use mutate instead of summarise:

data %>%
  filter(date == as.Date(start_date) | date == as.Date(end_date)) %>%
  arrange(date) %>%
  group_by(ID) %>%
  mutate(var_diff = var[2] - var[1])

# A tibble: 4 x 6
# Groups:   ID [2]
  ID       date                start_date end_date       var var_diff
  <chr>    <dttm>              <date>     <date>       <dbl>    <dbl>
1 23582520 2014-07-31 00:00:00 2014-07-31 2015-06-30 -0.0835   0.0289
2 26550410 2014-07-31 00:00:00 2014-07-31 2015-06-30 -0.0644   0.100 
3 23582520 2015-06-30 00:00:00 2014-07-31 2015-06-30 -0.0547   0.0289
4 26550410 2015-06-30 00:00:00 2014-07-31 2015-06-30  0.0357   0.100 

Answer 2

An option with data.table

library(data.table)
setDT(data)[as.Date(date) == as.Date(start_date)|
    date == as.Date(end_date)][order(date), var_diff := last(var) - first(var), ID][]