rdataframereorganize

How to create a column to reorganize data frame in R?


I have a data frame that looks like this:

Date        ACO   AJM   ATI

1/1/2019    0.59  0.28  0.39
1/2/2019    0.35  0.36  0.45
1/3/2019    0.46  0.37  0.63

How can I reorganize the data frame so that it looks like this:

 Date        id_station   Value

 1/1/2019    ACO          0.59
 1/1/2019    AJM          0.28
 1/1/2019    ATI          0.39
 1/2/2019    ACO          0.35 
 1/2/2019    AJM          0.36
 1/2/2019    ATI          0.45
 1/3/2019    ACO          0.46
 1/3/2019    AJM          0.37
 1/3/2019    ATI          0.63

Basically, I want to create 2 columns with names id_station and Value to reorganize my data frame.


Solution

  • Here might be options, using data.table

    library(data.table)
    
    > melt(setDT(df), id.var = "Date")
           Date variable value
    1: 1/1/2019      ACO  0.59
    2: 1/2/2019      ACO  0.35
    3: 1/3/2019      ACO  0.46
    4: 1/1/2019      AJM  0.28
    5: 1/2/2019      AJM  0.36
    6: 1/3/2019      AJM  0.37
    7: 1/1/2019      ATI  0.39
    8: 1/2/2019      ATI  0.45
    9: 1/3/2019      ATI  0.63
    

    or

    > setDT(df)[, rev(stack(.SD)), Date]
           Date ind values
    1: 1/1/2019 ACO   0.59
    2: 1/1/2019 AJM   0.28
    3: 1/1/2019 ATI   0.39
    4: 1/2/2019 ACO   0.35
    5: 1/2/2019 AJM   0.36
    6: 1/2/2019 ATI   0.45
    7: 1/3/2019 ACO   0.46
    8: 1/3/2019 AJM   0.37
    9: 1/3/2019 ATI   0.63