Search code examples
rtransformationreshapereshape2melt

Transform columns to row without create "variable" and "value"


I´ve seen a lot of example of transformations similars but not the same yet. Hope not to be wrong.

I wopuld like to transform this DF:

      Reference   Amount    Reference.2   Amount.2
1:   20171201     100,00 €    20171204    110,00 €

To something like that:

     Reference   Amount
   1: 20171201   100,00 €
   2: 20171204   110,00 €

Thank you.


Solution

  • If you are really just dealing with pairs of columns and you don't want a "variable" or "value" column, then maybe you can just do:

    matrix(c(t(df)), ncol = 2, byrow = TRUE)
    ##      [,1]       [,2]     
    ## [1,] "20171201" "100,00€"
    ## [2,] "20171204" "110,00€"
    ## [3,] "20171202" "101,00€"
    ## [4,] "20171205" "110,00€"
    

    From there, convert to data.frame or data.table or tbl or whatever you prefer to work with....

    But, I don't know why you wouldn't just do:

    library(data.table)
    melt(as.data.table(df), measure.vars = patterns("Reference", "Amount"), 
         value.name = c("Reference", "Amount"))[, variable := NULL][]
    #    Reference  Amount
    # 1:  20171201 100,00€
    # 2:  20171202 101,00€
    # 3:  20171204 110,00€
    # 4:  20171205 110,00€
    

    Sample data (from a deleted answer by @Florian):

    df = read.table(text='Reference   Amount    Reference.2   Amount.2
    1:   20171201     100,00€    20171204    110,00€
    2:   20171202     101,00€    20171205    110,00€',header=T)