CODEWITHSUNDEEP
rdataframereshape
Next Door Engineer
Next Door Engineer

Reputation: 2876

Converting row entries to columns in R

I have my data in a dataframe in the following format:

ID  GRP   VAL
1    A    23
2    A    43
3    A    12
4    A    65
1    B    43
2    B    87
3    B    45
4    B    76
1    C    45
2    C    76
3    C    24
4    C    75

I want to convert to the following format:

ID   A    B    C
1    23   43   45
2    43   87   76
3    12   45   24
4    65   76   75

How can I get this done?

Is there a name for this kind of transformation?

Upvotes: 0

Views: 141

Answers (1)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193497

This is a very common "long" to "wide" transformation that is referred to as "reshaping" your data.

Here are a few options:

In base R:

> reshape(mydf, direction = "wide", idvar="ID", timevar="GRP")
  ID VAL.A VAL.B VAL.C
1  1    23    43    45
2  2    43    87    76
3  3    12    45    24
4  4    65    76    75
> xtabs(VAL ~ ID + GRP, mydf)
   GRP
ID   A  B  C
  1 23 43 45
  2 43 87 76
  3 12 45 24
  4 65 76 75

You can also use dcast from the "reshape2" package (there's also dcast.data.table which is a rewrite of dcast but gives you a speed boost).

> library(reshape2)
> dcast(mydf, ID ~ GRP, value.var="VAL")
  ID  A  B  C
1  1 23 43 45
2  2 43 87 76
3  3 12 45 24
4  4 65 76 75

If you prefer chaining or piping commands together (it honestly hasn't grown on me yet), you can look into the "tidyr" package in conjunction with the "dplyr" package, with which you can do:

# devtools::install_github("hadley/tidyr")
library(dplyr)
library(tidyr)
mydf %>%
  group_by(ID) %>%
  spread(GRP, VAL)
# Source: local data frame [4 x 4]
# 
#   ID  A  B  C
# 1  1 23 43 45
# 2  2 43 87 76
# 3  3 12 45 24
# 4  4 65 76 75

Upvotes: 5

Related Questions