R reshape the data frame

Question

I have a data frame ema_sma_actual which is as mentioned below,

fiscal_quarter     actual    forecast_ema forecast_sma
         20071  6371428347           NA           NA
         20072  4370623177           NA           NA
         20073  3377139334    5056396953    5063969536
         20074  8380921297    6953659125    5762279366
         20081  7315765174    7054712149    6579419356
         20082 11325882146    9155297148    9075228726
         20083  6311248678    7653272913    8176319996

which i want to be transformed as

 type               Amount              fiscal_quarter
        actual    6371428347          20071
  forecast_ema         NA             20071
  forecast_sma         NA             20071
        actual    3377139334          20072
  forecast_ema    5056396953          20072
  forecast_sma    5063969536          20072
        actual    8380921297          20073
  forecast_ema    6953659125          20073
  forecast_sma    5762279366          20073
       actual    7315765174          20074
 forecast_ema    7054712149          20074
 forecast_sma    6579419356          20074
       actual   11325882146          20081
 forecast_ema    9155297148          20081
 forecast_sma    9075228726          20081
       actual    6311248678          20082
 forecast_ema    7653272913          20082
 forecast_sma    8176319996          20082
       actual    7356638637          20083
 forecast_ema    7559955775          20083
 forecast_sma    8312564876          20083

how can i do this?

Try `library(reshape2) ; df2 <- melt(df, "fiscal_quarter") ; df2[order(df2$fiscal_quarter), ]`. Assuming `df` is your data set — David Arenburg, Sep 15 '14 at 13:27

score 1 · Accepted Answer · answered Sep 15 '14 at 13:54

A tidyr solution

df<-read.table(text="fiscal_quarter     actual    forecast_ema forecast_sma
20071  6371428347           NA           NA
20072  4370623177           NA           NA
20073  3377139334    5056396953    5063969536
20074  8380921297    6953659125    5762279366
20081  7315765174    7054712149    6579419356
20082 11325882146    9155297148    9075228726
20083  6311248678    7653272913    8176319996", header=T)


library(tidyr) 
library(dplyr)

df %>% gather(key,amount, 2:4)

If you want it to be in the order above, you can add:

df %>% 
   gather(key,amount, 2:4) %>%
   arrange(fiscal_quarter, key)

R reshape the data frame

1 Answers1