CODEWITHSUNDEEP
rdataframe
storaged
storaged

Reputation: 1847

melt/cast method to transform data.frame

seems like the problem is going to be absolutely basic, but I cannot work out a simple way to do it.

I have a simple data.frame

               date quantile   value
         2020-12-26     0.05  261346
         2020-12-26     0.95  283721
         2021-01-02     0.05  334478
         2021-01-02     0.95  365978
         2021-01-09     0.05  405375
         2021-01-09     0.95  443708
         2021-01-16     0.05  473139
         2021-01-16     0.95  517392

df <- structure(list(
  target_end_date = structure(c(18622, 18622, 18629, 18629, 18636, 18636, 18643, 18643), class = "Date"), 
  quantile = c(0.05, 0.95, 0.05, 0.95, 0.05, 0.95, 0.05, 0.95), 
  value = c(261346, 283721, 334478, 365978, 405375, 443708, 473139, 517392)), 
  class = "data.frame", 
  row.names = c(5L, 23L, 53L, 71L, 101L, 119L, 149L, 167L))

And I need to transform it into:

   target_end_date     q05     q95
        2020-12-26 1261346 1283721 
        2021-01-02 1334478 1365978
        2021-01-09 1405375 1443708
        2021-01-16 1473139 1517392

As I said, sounds simple, but the only solution I came up with is this brutal reshaping:

df %>% 
  reshape::cast(value + target_end_date ~ quantile, fill = 0 ) %>% 
  .[,c(2,3,4)] %>% 
  aggregate(.~target_end_date, ., sum)

Does anybody have a simpler idea and more elegant idea, because this one looks really bad for me.

Thanks in advance!

Upvotes: 1

Views: 43

Answers (3)

ThomasIsCoding
ThomasIsCoding

Reputation: 101363

A base R option using reshape

reshape(
  aggregate(value~.,df,sum),
  idvar = "target_end_date",
  timevar = "quantile",
  direction = "wide"
)

gives

    target_end_date value.0.05 value.0.95
5        2020-12-26     261346     283721
53       2021-01-02     334478     365978
101      2021-01-09     405375     443708
149      2021-01-16     473139     517392

Upvotes: 1

Onyambu
Onyambu

Reputation: 79228

You can also use dcast from reshape2

reshape2::dcast(df,target_end_date~quantile, sum)

  target_end_date   0.05   0.95
1      2020-12-26 261346 283721
2      2021-01-02 334478 365978
3      2021-01-09 405375 443708
4      2021-01-16 473139 517392

And for data.table you could do:

 data.table::setDT(df)
 data.table::dcast(df,target_end_date~quantile, sum)

  target_end_date   0.05   0.95
1:      2020-12-26 261346 283721
2:      2021-01-02 334478 365978
3:      2021-01-09 405375 443708
4:      2021-01-16 473139 517392

Upvotes: 4

akrun
akrun

Reputation: 887118

In base R, we can use xtabs

xtabs(value ~ target_end_date + quantile, df)

-output

#         quantile
#target_end_date   0.05   0.95
#     2020-12-26 261346 283721
#     2021-01-02 334478 365978
#     2021-01-09 405375 443708
#     2021-01-16 473139 517392

Or this can be done with pivot_wider from tidyr

library(dplyr)
library(tidyr)
df %>% 
  pivot_wider(names_from = quantile, values_from = value, values_fn = sum)

-output

# A tibble: 4 x 3
#  target_end_date `0.05` `0.95`
#  <date>           <dbl>  <dbl>
#1 2020-12-26      261346 283721
#2 2021-01-02      334478 365978
#3 2021-01-09      405375 443708
#4 2021-01-16      473139 517392

Upvotes: 3

Related Questions