Search code examples
rdataframereshapereshape2

How can I reshape my data, moving rows to new columns?


I know that my problem is trival, however now I'm learing methods how to reshape data in different ways, so please be understanding.

I have data like this:

Input = (
 'col1 col2
  A 2
  B 4
  A 7
  B 3
  A 4
  B 2
  A 4
  B 6
  A 3
  B 3')
df = read.table(textConnection(Input), header = T)

> df
   col1 col2
1     A    2
2     B    4
3     A    7
4     B    3
5     A    4
6     B    2
7     A    4
8     B    6
9     A    3
10    B    3

And I'd like to have something like this, where the column names are not important:

      col1 v1   v2   v3   v4   v5
1     A    2    7    4    4    3
2     B    4    3    2    6    3

So far, I did something like:

res_1 <- aggregate(col2 ~., df, toString)
  col1          col2
1    A 2, 7, 4, 4, 3
2    B 4, 3, 2, 6, 3

And it actually works, however, I have one column and valiues are comma separated, instead of being in new columns, so I decided to fix it up:

res_2 <- do.call("rbind", strsplit(res_1$col2, ","))
     [,1] [,2] [,3] [,4] [,5]
[1,] "2"  " 7" " 4" " 4" " 3"
[2,] "4"  " 3" " 2" " 6" " 3"

Adn finally combine it and remove unnecessary columns:

final <- cbind(res_1,res_2)
final$col2 <- NULL
  col1 1  2  3  4  5
1    A 2  7  4  4  3
2    B 4  3  2  6  3

So I have my desired output, but I'm not satisfied about the method, I'm sure there's one easy and short command for this. As I said I'd like to learn new more elegant options using different packages. Thanks!


Solution

  • You can simply do,

    do.call(rbind, split(df$col2, df$col1))
    #  [,1] [,2] [,3] [,4] [,5]
    #A    2    7    4    4    3
    #B    4    3    2    6    3
    

    You can wrap it to data.frame() to convert from matrix to df