CODEWITHSUNDEEP
rdataframereshapereshape2
Adamm
Adamm

Reputation: 2306

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!

Upvotes: 1

Views: 120

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 269371

The question is tagged with reshape2 and reshape so we show the use of that package and the base reshape function. Also the use of dplyr/tidyr is illustrated. Finally we show a data.table solution and a second base R solution using xtabs.

reshape2 Add a group column and then convert from long to wide form:

library(reshape2)

df2 <- transform(df, group = paste0("v", ave(1:nrow(df), col1, FUN = seq_along)))
dcast(df2, col1 ~ group, value.var = "col2")

giving:

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

2) reshape Using df2 from (1) we have the following base R solution using the reshape function:

wide <- reshape(df2, dir = "wide", idvar = "col1", timevar = "group")
names(wide) <- sub(".*\\.", "", names(wide))
wide

giving:

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

3) dplyr/tidyr

library(dplyr)
library(tidyr)

df %>%
  group_by(col1) %>%
  mutate(group = paste0("v", row_number())) %>%
  ungroup %>%
  pivot_wider(names_from = "group", values_from = "col2")

giving:

# A tibble: 2 x 6
  col1     v1    v2    v3    v4    v5
  <fct> <int> <int> <int> <int> <int>
1 A         2     7     4     4     3
2 B         4     3     2     6     3

4) data.table

library(data.table)

as.data.table(df)[, as.list(col2), by = col1]

giving:

   col1 V1 V2 V3 V4 V5
1:    A  2  7  4  4  3
2:    B  4  3  2  6  3

5) xtabs Another base R solution uses df2 from (1) and xtabs. This produces an object of class c("xtabs", "table")`. Note that it labels the dimensions.

xtabs(col2 ~., df2)

giving:

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

Upvotes: 1

Sotos
Sotos

Reputation: 51582

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

Upvotes: 1

Related Questions