-3

I have the following dataframe:

long  band   d    Ret
 100   0     1    2.10
 100   0     2    1.96
 100   0     1    -1.10
 100   0     2    0.96
 100   0     1    0.54
 100   0     2    -2.12
 200   0     1    3.10
 200   0     2    -1.96
 200   0     1    5.66
 200   0     2    2.22
 200   0     1    -0.54
 200   0     2    0.25

And I want to transform it so that Ret values that correspond to the same long/band/d columns are in a row such that:

 long  band   d    Ret1    Ret2   Ret3
 100    0     1    2.10   -1.10   0.54
 100    0     2    1.96    0.9.   -2.12
 200    0     1    3.10    5.66   -0.54
 200    0     2    -1.96   2.22   0.25

I have tried 'transform' and melt but they don't have the condition to put the Rets side-by-side

Johnny
  • 81
  • 7
  • 1
    Possible duplicate of [How to reshape data from long to wide format?](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) – LAP Dec 12 '17 at 14:12
  • Yes similar but I don't seem to get it to work. If I do: x = reshape(df, idvar = c("long","d","band"), timevar = c("Dailyreturnt"), direction = "wide") it does specify by the long, d and band column – Johnny Dec 12 '17 at 14:28
  • 1
    Try using *spread* from tidyr package, you will have to remove spaces (NULL) though spread(your_dataframe,Ret,4,fill='') – Gangesh Dubey Dec 12 '17 at 14:39
  • Thanks I have done this and as I say, I have empty spaces. I have converted them to NA's but is there a way to delete these? Obviously na.omit and complete.cases remove the whole row but I just want the cell removed. – Johnny Dec 12 '17 at 15:47
  • A very crude way of improving it `code` df_mdfd <- as.data.frame(spread(df,Ret_Var,4)) for (k in (4:ncol(df_mdfd))){ for (i in (1:nrow(df_mdfd))) { for (j in (4:ncol(df_mdfd)-1)) { if (is.na(df_mdfd[i,j])&!(is.na((df_mdfd[i,j+1])))){ df_mdfd[i,j] <- df_mdfd[i,j+1] df_mdfd[i,j+1] <- NA } }}} – Gangesh Dubey Dec 12 '17 at 16:37

1 Answers1

1

with df as your input dataframe

library(tidyr)
df_mdfd <- as.data.frame(spread(df,Ret_Var,4))
for (k in (4:ncol(df_mdfd))){
for (i in (1:nrow(df_mdfd)))
{
  for (j in (4:ncol(df_mdfd)-1))
  {
    if (is.na(df_mdfd[i,j])&!(is.na((df_mdfd[i,j+1])))){
      df_mdfd[i,j] <- df_mdfd[i,j+1]
      df_mdfd[i,j+1] <- NA
    } }}}

df_output <- as.data.frame(t(na.omit(t(df_mdfd))))
colnames(df_output) <- c("long","band","d","Ret1","Ret2","Ret3")
df_output
Gangesh Dubey
  • 382
  • 1
  • 7
  • Great thank you. I was busy trying to sort the columns based on the d/long/band and then pasting the types into a column and then transferring from a long to wide data frame. But this is much more efficient. Thanks – Johnny Dec 12 '17 at 16:50