CODEWITHSUNDEEP
rdataframetransformation
beckylllin1992
beckylllin1992

Reputation: 3

R - How to covert a long format dataset to a matrix that has multiple values in each cell

I have a long format data set, which similar like

 1. ReactionTime X(a categorical variable) y(a categorical variable)
 2. 1.23        * 1     *                      4
 3. 2.33        *  2    *                       4
 4. 3.45        *  3    *                       5
 5. 1.44        *  4   *                        2
 6. 1.27       *   5   *                        6
 7. 5.44       *   5    *                       5
 8. 3.22        *  7     *                      4
 9. 3.22        *  8      *                     2
 10. 3.56       *   1      *                    4

and I want to covert the above dataset into a matrix with variable x on the horizontal line and variable y on the vertical line. But you can see the first and last observation share the same "cell", both of them have x variable in 1 and y variable in 4. My intention is to calculate the mean of reaction times of first and last observations and put the mean to the cell, so is there anyway I can do that? Thank you!

Upvotes: 0

Views: 77

Answers (5)

G. Grothendieck
G. Grothendieck

Reputation: 269852

Try tapply. No packages are used.

tapply(df$ReactionTime, df[c("Y", "X")], mean)

giving:

   X
Y       1    2    3    4    5    7    8
  2    NA   NA   NA 1.44   NA   NA 3.22
  4 2.395 2.33   NA   NA   NA 3.22   NA
  5    NA   NA 3.45   NA 5.44   NA   NA
  6    NA   NA   NA   NA 1.27   NA   NA

Note: df in reproducible form is:

df <- structure(list(ReactionTime = c(1.23, 2.33, 3.45, 1.44, 1.27, 
5.44, 3.22, 3.22, 3.56), X = c(1L, 2L, 3L, 4L, 5L, 5L, 7L, 8L, 
1L), Y = c(4L, 4L, 5L, 2L, 6L, 5L, 4L, 2L, 4L)), .Names = c("ReactionTime", 
"X", "Y"), class = "data.frame", row.names = c(NA, -9L))

Upvotes: 0

VictorZurkowski
VictorZurkowski

Reputation: 202

Using SQL, with the package sqldf

df = read.table(header = T, text = "ReactionTime X Y
                 1.23 1 4
                2.33 2 4
                3.45 3 5
                1.44 4 2
                1.27 5 6
                5.44 5 5
                3.22 7 4
                3.22 8 2
                3.56 1 4")


#.............................................................
library(sqldf)
c=data.frame(c=sort(unique(df$X)))
r=data.frame(r=sort(unique(df$Y)))

DAT=sqldf("select r.r , c.c , 
          avg(ReactionTime) as M
          from r cross join c
          left join df
          on r.r=df.Y and c.c=df.X
          group by r.r, c.c 
          order by r.r, c.c")


M = matrix(DAT$M, nrow=dim(r)[1], ncol=dim(c)[1], byrow=TRUE)
dimnames(M)=list(r$r, c$c)
# M
#      1    2    3    4    5    7    8
# 2    NA   NA   NA 1.44   NA   NA 3.22
# 4 2.395 2.33   NA   NA   NA 3.22   NA
# 5    NA   NA 3.45   NA 5.44   NA   NA
# 6    NA   NA   NA   NA 1.27   NA   NA

Upvotes: 0

VictorZurkowski
VictorZurkowski

Reputation: 202

Using Base R

    df = read.table(header = T, text = "ReactionTime X Y
                 1.23 1 4
                 2.33 2 4
                 3.45 3 5
                 1.44 4 2
                 1.27 5 6
                 5.44 5 5
                 3.22 7 4
                 3.22 8 2
                 3.56 1 4")



c=sort(unique(df$X))
r=sort(unique(df$Y))
W=unique(df[2:3])

M=matrix(NA,ncol=length(c), nrow=length(r))
dimnames(M)=list( r , c )

for( i in 1:dim(W)[1] ){
  c0=which(c==W[i,1])
  r0=which(r==W[i,2])
  A=which(df$X==W[i,1] & df$Y==W[i,2])

  M[r0, c0] = mean(df$ReactionTime[A],na.rm = TRUE)
}

Upvotes: 0

agstudy
agstudy

Reputation: 121588

You are trying to reshape data from the long format to the wide format. dcast from the data.table is designed for this kind of operation:

library(data.table)
dcast(data=setDT(dx),formula = X~Y,
      fun.aggregate = mean,value.var = "ReactionTime",fill = 0)
# X    2     4    5    6
# 1: 1 0.00 2.395 0.00 0.00
# 2: 2 0.00 2.330 0.00 0.00
# 3: 3 0.00 0.000 3.45 0.00
# 4: 4 1.44 0.000 0.00 0.00
# 5: 5 0.00 0.000 5.44 1.27
# 6: 7 0.00 3.220 0.00 0.00
# 7: 8 3.22 0.000 0.00 0.00

Upvotes: 1

Z.Lin
Z.Lin

Reputation: 29095

If I understand your question correctly, the following should work:

library(dplyr); library(tidyr); library(tibble)

df %>%
  # calculate mean reaction time for each cell
  group_by(X, Y) %>%
  summarise(ReactionTime = mean(ReactionTime)) %>%
  ungroup() %>%

  # spread cells (if you don't want NAs in empty cells, use the 2nd version)
  spread(Y, ReactionTime) %>%
  # spread(Y, ReactionTime, fill = 0) %>%

  # convert to matrix with X in row names & Y in column names
  remove_rownames() %>%
  column_to_rownames("X") %>%
  as.matrix()

     2     4    5    6
1   NA 2.395   NA   NA
2   NA 2.330   NA   NA
3   NA    NA 3.45   NA
4 1.44    NA   NA   NA
5   NA    NA 5.44 1.27
7   NA 3.220   NA   NA
8 3.22    NA   NA   NA

Data:

df <- read.table(header = T, text = "ReactionTime X Y
1.23 1 4
2.33 2 4
3.45 3 5
1.44 4 2
1.27 5 6
5.44 5 5
3.22 7 4
3.22 8 2
3.56 1 4")

Upvotes: 1

Related Questions