Create crosstab from three values

Question

I have a data frame with three variables and I want the first variable to be the row names, the second variable to be the column names, and the third variable to be the values associated with those two parameters, with NA or blank where data may be missing. Is this easy/possible to do in R?

example input

structure(list(
  Player = c("1","1","2","2","3","3","4","4","5","5","6"),

  Type = structure(c(2L, 1L, 2L, 1L, 2L, 1L,2L, 1L, 2L, 1L, 1L),
     .Label = c("Long", "Short"), class = "factor"),
     Yards = c("23","41","50","29","11","41","48","12","35","27","25")),

  .Names = c("Player", "Type", "Yards"),

  row.names = c(NA, 11L),
  class = "data.frame")

I've tried those, but I've just been able to get a count of how many times the row value and column value show up together. How would I use those to make the third variable the values in the table instead of the freq of the first two? — alk3ckwd, Feb 13 '15 at 22:11
Could you give us an example of your input? Look at http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example for tips on giving an example. — MattLBeck, Feb 13 '15 at 22:13
See http://stackoverflow.com/questions/28501419/creating-a-connection-matrix-from-a-data-frame-in-r — Metrics, Feb 13 '15 at 22:20

MattLBeck · Accepted Answer · 2015-02-13T22:31:03.373

Using the sample data you gave:

df <- structure(list(Player = c("1", "1", "2", "2", "3", "3", "4", "4", "5",
 "5", "6"), Type = structure(c(2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 1L),
 .Label = c("Long", "Short"), class = "factor"), 
 Yards = c("23", "41", "50", "29", "11", "41", "48", "12", "35", "27", "25")), 
 .Names = c("Player", "Type", "Yards"), row.names = c(NA, 11L), 
 class = "data.frame")

   Player  Type Yards
1       1 Short    23
2       1  Long    41
3       2 Short    50
4       2  Long    29
5       3 Short    11
6       3  Long    41
7       4 Short    48
8       4  Long    12
9       5 Short    35
10      5  Long    27
11      6  Long    25

dcast will be able to tabulate the two variables.

library(reshape2)
df.cast <- dcast(df, Player~Type, value.var="Yards")

The Player column will be a column, so you need to do a bit extra to make it the row names of the data.frame

rownames(df.cast) <- df.cast$Player
df.cast$Player <- NULL

  Long Short
1   41    23
2   29    50
3   41    11
4   12    48
5   27    35
6   25  <NA>

That looks like it will work! I'll have to try it on my real data when I get home. I was looking at the reshape package too, but the melt/cast stuff just went a bit over my head. — alk3ckwd, Feb 13 '15 at 22:43

Create crosstab from three values

1 Answers1