How can I transpose my table by selected column values in R?-CodePudding

I'm working in R. how can I transpose my table creating new columns by client. The rows would be by idMarket and Section, and the other columns would give the Score of each client in those Markets and Section.

idMarket  idSection idClient  Score  %

2          99         23       100    1
2          99         56       25     0,2
3          67         23       56     0,5
3          67         56       50     0,3

Expected table:


idMarket  idSection  Client23  #   Client56   %56

2          99         100      1        25      0,2
3          67          56      0,5      50      0,3

Thanks!!

CodePudding user response：

pivot_wider(df, c(idMarket, idSection), names_from = idClient, values_from = c(Score, `%`))
# A tibble: 2 x 6
  idMarket idSection Score_23 Score_56 `%_23` `%_56`
     <int>     <int>    <int>    <int>  <dbl>  <dbl>
1        2        99      100       25    1      0.2
2        3        67       56       50    0.5    0.3


reshape(df, dir='wide', timevar = 'idClient', idvar = c('idMarket', 'idSection'), sep='_')
  idMarket idSection Score_23 %_23 Score_56 %_56
1        2        99      100  1.0       25  0.2
3        3        67       56  0.5       50  0.3

CodePudding user response：

I went in what is probably a less efficient way, but I thought I'd give an alternative using dplyr and tidyr. What I did is I gathered the Score/% columns together, then made a new name using the Client/%, then spread the data wide as you requested. This won't be in the exact order of the table you wanted, but still gives you the same data:

library(dplyr)
library(tidyr)

dat<-data.frame("idMarket" = c(2,2,3,3),
                "idSection" = c(99, 99, 67, 67),
                "idClient" = c(23, 56 ,23, 56),
                "Score" = c(100, 25, 56, 50),
                "%" = c("1", "0,2", "0,5", "0,3"),
                check.names = F, stringsAsFactors = F
)

dat2<-dat%>%
  tidyr::gather("key", "value", Score, `%`)%>%
  mutate(key = ifelse(key == "Score", paste0("Client", idClient), paste0("%", idClient)))%>%
  select(-idClient)%>%
  tidyr::spread(key, value)
dat2
  idMarket idSection # %56 Client23 Client56
1        2        99   1 0,2      100       25
2        3        67 0,5 0,3       56       50