melt dataframe - multiple columns - "Enhanced (new) functionality from data.tables"

user_1012249 0
Viewed 5873

Update: I should have been clearer that I was trying to check out the Enhanced functionality in reshaping using data.tables https://cran.r-project.org/web/packages/data.table/vignettes/datatable-reshape.html. Updated the title.

I have this data set with two sets of variables - Credit_Risk_Capital and Name_concentration. They are calculated per 2 methodologies - New and Old. When I melt them using the data.table package, the variable names default to 1 and 2. How can I change them to just Credit_Risk_Capital and Name_Concentration.

Here is the data set

    df <-data.table (id = c(1:100),Credit_risk_Capital_old= rnorm(100, mean = 400, sd = 60),
             NameConcentration_old= rnorm(100, mean = 100, sd = 10),
             Credit_risk_Capital_New =rnorm(100, mean = 200, sd = 10),
             NameConcentration_New = rnorm(100, mean = 40, sd = 10))
    old <- c('Credit_risk_Capital_old','NameConcentration_old')
   new<-c('Credit_risk_Capital_New','NameConcentration_New')
  t1<-melt(df, measure.vars = list(old,new), variable.name = "CapitalChargeType",value.name = c("old","new"))

Now instead of the elements in the CapitalChargeType Column getting tagged as 1's and 2's, I want them to be changed to Credit_risk_Capital and NameConcentration. I can obviously change them in a subsequent step using a 'match' function, but is there anyway I can do it within melt itself.

melt data.table r
3 Answers

While the question is very old, a newer answer might help those directed to this question via a search. In data.table's most recent development version, there is a new measure function for melt, from which you can do:

df <-data.table(
  id = c(1:100),
  Credit_risk_Capital_old= rnorm(100, mean = 400, sd = 60),
  NameConcentration_old= rnorm(100, mean = 100, sd = 10),
  Credit_risk_Capital_New =rnorm(100, mean = 200, sd = 10),
  NameConcentration_New = rnorm(100, mean = 40, sd = 10)
)

melt(df,
     id.vars = "id",
     measure(CapitalChargeType, value.name,
             pattern = "(.*)_(New|old)"))

To get the output:

        id   CapitalChargeType       old       New
     <int>              <char>     <num>     <num>
  1:     1 Credit_risk_Capital 409.89004 210.30058
  2:     2 Credit_risk_Capital 403.15172 197.26172
  3:     3 Credit_risk_Capital 374.90492 192.21152
  4:     4 Credit_risk_Capital 509.17491 195.39095
  5:     5 Credit_risk_Capital 429.48302 197.44441
 ---                                              
196:    96   NameConcentration  80.64747  37.61926
197:    97   NameConcentration 104.39483  13.86576
198:    98   NameConcentration 106.87475  23.15775
199:    99   NameConcentration 112.92373  44.51562
200:   100   NameConcentration 111.80915  38.40075

The new version should come on CRAN in some time, but until then, you can use the development version. I'll try to update this answer when the version moves to CRAN.

user_114496050
Related