Ytukyg

I have a dataframe object in R, sample of which is as follows:

4   5   3

4   5   9

4   5   2

4   6   4

4   10  4

4   10  3

4   10  7

4   10  2

4   9   3

4   9   7

4   10  4

4   10  3

4   6   8

4   5   4

12  3   6

12  4   1

12  4   2

12  4   7

From this dataframe, I want to create a new dataframe of 20 columns, as follows:

1. Only one row in the new dataframe,for each unique value in $1. Hence for this sample data, the new dataframe should have 2 rows(unique 4,12).




2. $2 represents the column number of the new dataframe, in which the value of $3(of this dataframe) is to be filled. If there are repeating cases, the median of the values of $3 is to be taken. For example, for
   $1 value 4, 5 is repeated 4 times, and in the new dataframe, column 5 of the first row should have the value median(3,9,2,4) =3.




3. All other column values are zero.

A sample output for this data would be as follows:

0 0 0 0 3 4 0 0 3 4 0 0 0 0 0 0 0 0 0 0

0 0 3 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

How can we do this in R? A huge thanks in advance!

Are you sure that your expected outcome is correct? I think there is an error in calculating the median in your question, as also pointed out in the comments. You could do it as follows:

library(dplyr)



df$V1 <- as.numeric(as.factor(df$V1))

values <- df %>% group_by(V1,V2) %>% summarise(median=median(V3))

new_df <- matrix(0,nrow=length(unique(df$V1)), ncol=20)



for(i in 1:nrow(new_df)){

  for(j in 1:ncol(new_df)){

    value <- values$median[values$V1==i & values$V2==j]

    if(length(value)>0){

      new_df[i,j] = value  

    }

  }

}



new_df



    [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12] [,13] [,14] [,15] [,16] [,17] [,18] [,19] [,20]

[1,]    0    0    0    0  3.5    6    0    0    5   3.5     0     0     0     0     0     0     0     0     0     0

[2,]    0    0    6    2  0.0    0    0    0    0   0.0     0     0     0     0     0     0     0     0     0     0

The outcome is slightly different though.

df = transform(df,V1=factor(V1))

fill = matrix(0,length(levels(df$V1)),20)

df2=aggregate(V3~.,df,function(x)floor(median(x)))

fill[cbind(as.integer(df2$V1),df2$V2)]=df2$V3

fill

     [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12] [,13] [,14]

[1,]    0    0    0    0    3    6    0    0    5     3     0     0     0     0

[2,]    0    0    6    2    0    0    0    0    0     0     0     0     0     0

     [,15] [,16] [,17] [,18] [,19] [,20]

[1,]     0     0     0     0     0     0

[2,]     0     0     0     0     0     0