Trying to create a date column for tibble. Want to take the values from the index of a prices xts obkect

Question

The first error I get is from the index(.))) line. Which is object '.' not found. When I put '.' into the index line I get an error in the spread(asset, return), which is Each row of output must be identified by a unique combination of keys. Please find code and spread error:

# Converting Daily Prices to Monthly Returns in the tidyverse
asset_returns_dplyr = 
  prices |>
  to.monthly(indexAt = "lastof", OHLC = FALSE) |>
  # convert the index to a date
  data.frame(date = index(.)) |>
  # now remove the index because it got converted to row names
  remove_rownames() |>
  gather(asset, prices, -date) |>
  group_by(asset) |> 
  mutate(returns = (log(prices) - log(lag(prices)))) |>
  select(-prices) |>
  spread(asset, returns) |>
  select(date, symbols)
  na.omit()

Error in `spread()`:
! Each row of output must be identified by a unique combination of keys.
ℹ Keys are shared for 625 rows
• 501, 502, 503, 504, 505, 506, 507, 508, 509, 510, 511, 512, 513, 514, 515, 516, 517, 
518, 519, 520, 521, 522,
  523, 524, 525, 526, 527, 528, 529, 530, 531, 532, 533, 534, 535, 536, 537, 538, 539, 5 
540, 541, 542, 543, 544,
545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 559, 560, 561, 
562, 

structure(c(117.827011108398, 120.846908569336, 120.57389831543, 
121.103385925293, 120.772438049316, 120.424964904785, 
120.73104095459, 
121.690849304199, 121.682556152344, 121.59984588623, 
42.5093727111816, 
43.1672821044922, 42.7486114501953, 42.9654159545898, 
42.7785224914551, 
42.5392799377441, 42.7560882568359, 43.3018455505371, 
43.3915596008301, 
43.4438934326172, 34.7362937927246, 35.6893730163574, 
35.6421546936035, 
35.9212112426758, 35.7108459472656, 35.5734710693359, 
35.6765060424805, 
35.7194404602051, 35.693675994873, 35.6850929260254, 
35.7743263244629, 
36.4761009216309, 36.2179832458496, 36.2905693054199, 
36.0163269042969, 
35.6936645507812, 35.8469200134277, 36.1615180969238, 
35.8711166381836, 
36.0243835449219, 86.7947540283203, 86.6931762695312, 
86.4743957519531, 
86.5681304931641, 86.5212860107422, 86.5993957519531, 
86.5369033813477, 
86.5290603637695, 86.6150131225586, 86.6931762695312), class = 
c("xts", 
"zoo"), src = "yahoo", updated = structure(1685658877.66735, class 
= c("POSIXct", 
"POSIXt")), index = structure(c(1356912000, 1357084800, 1357171200, 
1357257600, 1357516800, 1357603200, 1357689600, 1357776000, 
1357862400, 
1358121600), tzone = "UTC", tclass = "Date"), dim = c(10L, 5L
), dimnames = list(NULL, c("SPY", "EFA", "IJS", "EEM", "AGG")))

The dot (".") symtax is meant to be used with maggritr pipes (`%>%`) not the R native pipe (`|>`). — br00t, Jun 01 '23 at 22:11
Please add the input data `prices` to your post by using `dput()` see: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — br00t, Jun 01 '23 at 22:18
@broot I have edited to contain the first 15 rows using dput() — MNK2008-, Jun 02 '23 at 00:08

G. Grothendieck · Accepted Answer · 2023-06-04T15:48:45.977

There are several problems here:

code posted to SO should be complete. The library statement(s) are missing.
spread and gather are normally no longer used and pivot_wider and pivot_longer from tidyr are used instead.
dot cannot be used with |>, only with %>% .
the code can be simplified significantly by not converting to data frame and using diff.xts

This gives

library(quantmod) # also pulls in zoo and xts

rets <- prices |>
  to.monthly(indexAt = "lastof", OHLC = FALSE) |>
  diff(log = TRUE)
rets
##                   SPY        EFA        IJS         EEM          AGG
## 2013-01-31 0.03151816 0.02174571 0.02694797 0.006965536 -0.001171007

If you did want to convert an xts object to a data frame use fortify.zoo. For example:

rets |>
  fortify.zoo(name = "date")
##         date        SPY        EFA        IJS         EEM          AGG
## 1 2012-12-31         NA         NA         NA          NA           NA
## 2 2013-01-31 0.03151816 0.02174571 0.02694797 0.006965536 -0.001171007

Trying to create a date column for tibble. Want to take the values from the index of a prices xts obkect

1 Answers1