How to transpose a data set into a particular way in R

E_Pesantez_7971006 0
Viewed 54

Could you please help me with this one. I have a data set of this kind.

sample <- matrix(c(1, 'A', 0.17,
                   1, 'B', 0.80,
                   1, 'C', 0.10,
                   1, 'D', 0.35,
                   2, 'A', 0.44,
                   2, 'B', 0.24,
                   2, 'C', 0.39,
                   2, 'D', 0.65,
                   3, 'A', 0.61,
                   3, 'B', 0.94,
                   3, 'C', 0.19,
                   3, 'D', 0.65), nrow=12, ncol=3, byrow=T)

colnames(sample) <- c('year', 'country', 'X')
as.data.frame(sample)

which outputs

   year country    X
1     1       A 0.17
2     1       B  0.8
3     1       C  0.1
4     1       D 0.35
5     2       A 0.44
6     2       B 0.24
7     2       C 0.39
8     2       D 0.65
9     3       A 0.61
10    3       B 0.94
11    3       C 0.19
12    3       D 0.65

What trick do I need to get a data set like the following one?

year    A     B       C       D
1   0.17    0.80    0.10    0.35
2   0.44    0.24    0.39    0.65
3   0.61    0.94    0.19    0.65
dataset r
4 Answers

Your code is missing the first line or two so I reformatted it a bit. Here's a solution using tidyr:

library(dplyr)
library(tidyr)
df <- read.table(text = 
      "1,  'A',    0.17
       1,  'B',    0.80
       1,  'C',    0.10
       1,  'D',    0.35
       2,  'A',    0.44
       2,  'B',    0.24
       2,  'C',    0.39
       2,  'D',    0.65
       3,  'A',    0.61
       3,  'B',    0.94
       3,  'C',    0.19
       3,  'D',    0.65", sep = ",", strip.white = TRUE)

colnames(df)<-c('year', 'country',  'X')

df %>%
  arrange(year, country) %>%
  pivot_wider(names_from = country, values_from = X)
#> # A tibble: 3 x 5
#>    year     A     B     C     D
#>   <int> <dbl> <dbl> <dbl> <dbl>
#> 1     1  0.17  0.8   0.1   0.35
#> 2     2  0.44  0.24  0.39  0.65
#> 3     3  0.61  0.94  0.19  0.65

Created on 2020-04-10 by the reprex package (v0.3.0)

user_131575360

What you are trying to accomplish is called reshaping a dataset from long to wide.

Using the reshape package, the following should do it:

d_long<- melt(yourdatasetname, id.vars = c("year"))

d_wide<-dcast(d_long, year ~ country, sum)

user_122445400

Referring ColorStatistics's answer, The data set already looks in melt form. Just doing a dcast is enough.

> data<-as.data.frame(sample)
> library(data.table)
> dtable <- dcast(data = data, year~country) 
> dtable
      year    A    B    C    D
1    1 0.17  0.8  0.1 0.35
2    2 0.44 0.24 0.39 0.65
3    3 0.61 0.94 0.19 0.65
user_132444320

An base R option is using reshape like below

dfout <- reshape(df,
                 direction = "wide",
                 idvar = "year",
                 timevar = "country")

such that

> dfout
  year  X.A  X.B  X.C  X.D
1    1 0.17  0.8  0.1 0.35
5    2 0.44 0.24 0.39 0.65
9    3 0.61 0.94 0.19 0.65
user_121587570
Related