1

I change my data to xts to do simple calculations, so I first reshape my data from long to wide, do my calculations and want to reshape it back.

First my data looks like: 

          date seriesid       totret
1912-08-15    57409  0.000000000
1912-09-15    57409 -0.500000000
1912-10-15    57409  0.000000000
1912-11-15    57409  0.000000000
1912-12-15    57409  0.000000000
1913-01-15    57409  0.000000000
1913-02-15    57409  0.000000000
1913-03-15    57409  0.500000000
1913-04-15    57409 -0.250000000
1913-05-15    57409  0.000000000
1913-06-15    57409  0.000000000
1913-07-15    57409  0.000000000
1913-08-15    57409  0.000000000
1913-09-15    57409  0.000000000
1925-02-15    57746  0.001984127
1925-03-15    57746  0.049504950
1925-05-15    57746  0.190476190
1925-06-15    57746 -0.088333333
1925-08-15    57746  0.015384615
1925-09-15    57746  0.030303030

Then i recast it with:

z <-reshape(PreCRSP, idvar="date", timevar = "seriesid", direction ="wide")
z <- as.data.table(z)
z <- as.xts.data.table(z)

port_rolling_sd_xts <- rollapply(z,FUN = sd,width = 36)

Now I have the matrix below and want to transfer this back, how can I do this?

index totret.57409 totret.57746 totret.55630 totret.55578
1824-01-15           NA           NA           NA           NA
1824-02-15           NA           NA           NA           NA
1824-03-15           NA           NA           NA           NA
1824-04-15           NA           NA           NA           NA
1824-05-15           NA           NA           NA           NA

I want this to be back in the format:

date seriesid  sd
1912-08-15    57409  0.000000000
1912-09-15    57409 -0.500000000
1912-10-15    57409  0.000000000
1912-11-15    57409  0.000000000

answer was: 

sd <- tidyr::spread(Pre, key = "seriesid", value = "totret")
sd <- as.data.table(sd)
sd <- as.xts.data.table(sd)
sd <- rollapply(sd, FUN = sd, width = 36) 
sd <- as.data.table(sd)
SD <- tidyr::gather(sd, seriesid, sd, -index, na.rm=TRUE)
names(SD) <- c("date","seriesid","sd")
PreCRSPwithsd <- merge(Pre, SD, by=c("date", "seriesid"), all.x=TRUE)
Bart
  • 317
  • 4
  • 18
  • 1
    If you are uisng `data.table`, I think you can use `melt` – akrun Jul 23 '19 at 14:57
  • Can you share some reproducible example data ? Using `dput` or any means here [how-to-make-a-great-r-reproducible-example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) would be a great help ! – cbo Jul 23 '19 at 14:58
  • You could also use tidyr::gather (wide to long) and tidyr::spread (long to wide) – cbo Jul 23 '19 at 15:00
  • Thanks, Tidyr worked! – Bart Jul 23 '19 at 15:34

1 Answers1

0

here is a good overview of reshape2 and tidyr options:

http://www.cookbook-r.com/Manipulating_data/Converting_data_between_wide_and_long_format/

dasds
  • 170
  • 1
  • 11