dataframe to table transform like this format can you help me?

Question

I want to Transform left data to right Table !! my code is,

Respond<-as.factor(c(1,2,3,4,5,6,7,8,9,10,11,12))

Major<-as.factor(c("AD","B","E","AD","B","AD","B","E","AD",
     "E","AD","E"))

score<-c(2.5,0.5,2,3,2,2,1,1,1,0.5,3,1.5)
data<-data.frame(Respond,Major,score)

After entering this code, I tried to use tapply function,.. and so on but i

couldn't find out way to make table like right one...

please help me !! experts~!!!

(1) `score` instead of `Soju`; (2) how do propose to go from `res1`...`res12` to 1..5? Is that just a row number? — r2evans, Mar 30 '18 at 01:16

Maurits Evers · Accepted Answer · 2018-03-30T01:46:30.687

1

The following reproduces your expected output:

library(dplyr);
library(magrittr);
data %>%
    group_by(Major) %>%
    mutate(number = 1:n()) %>%
    select(-Respond) %>%
    spread(Major, score) %>%
    select(number, B, E, AD)
    ## A tibble: 5 x 4
    #  number      B      E    AD
    #   <int>  <dbl>  <dbl> <dbl>
    #1      1  0.500  2.00   2.50
    #2      2  2.00   1.00   3.00
    #3      3  1.00   0.500  2.00
    #4      4 NA      1.50   1.00
    #5      5 NA     NA      3.00

edited Mar 30 '18 at 01:46

answered Mar 30 '18 at 01:21

Maurits Evers

49,617
4
47
68

thanks for your answer!! but the right table's numbers(1,2,3,4,5) are actually, sequence of survey so it's important to make same format ..!!! for example first survey responder in Major B's score is 0.5 !!!!!!!! third responder in Major AD's score is 2.0 !! – Daeyoon Mar 30 '18 at 01:31
@Daeyoon I don't understand what you're saying. How do you get the numbers `1,2,3,4,5` from the long table? – Maurits Evers Mar 30 '18 at 01:33
For example, why has `number = 5` only one entry `AD = 3`? I can see that there are 5 `AD` rows in your long table, all with different scores. So why `AD = 3`? You need to clearly state your rules! – Maurits Evers Mar 30 '18 at 01:37
as you see long table , the fifth AD'S score is 3.. ! which mean , row number of short table is sequence!! – Daeyoon Mar 30 '18 at 01:42
@Daeyoon I think I understood your issue; for future posts you really need to clearly state your rules of how you get from sample data to expected output; instead of relying on SO users to work this out themselves. – Maurits Evers Mar 30 '18 at 01:43
@MauritsEvers Okay, !! thanks for your advice , actually it's pretty hard to explain in english.. !! – Daeyoon Mar 30 '18 at 01:45
No problem @Daeyoon; we got there in the end:-) please consider upvoting and marking the question as solved (check mark on the left) to close the question. – Maurits Evers Mar 30 '18 at 01:47

score 1 · Answer 2 · answered Mar 30 '18 at 01:47

1

Another option is to convert to wide format with dcast, then map_df to order all columns by whether the values are NA.

require(tidyverse)
require(reshape2)
data %>% 
    dcast(Respond ~ Major) %>% 
    map_df(~ .[order(is.na(.))])

answered Mar 30 '18 at 01:47

IceCreamToucan

28,083
2
22
38

dataframe to table transform like this format can you help me?

2 Answers2