Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Transpose data by groups in R

I have data in the following structure:

x <- read.table(header=T, text="
X Y D S
a e 1 10
a e 2 20
a f 1 50
b c 1 40
b c 2 30
b c 3 60
b d 1 10 
b d 2 20")

And I want to get the following result:

X Y   1   2   3
a e  10  20
a f  50
b c  40  30  60
b d  10  20

For every combination of columns X and Y I would like to transpose data in column S by order in column D.

I thought xtabs() will work, but I don't think so, my best version is:

xtabs(formula=S~Y+D,data=x)

With result:

   D
Y    1  2  3
  c 40 30 60
  d 10 20  0
  e 10 20  0
  f 50  0  0
like image 248
Tomas Greif Avatar asked Jun 29 '13 16:06

Tomas Greif


People also ask

How to transpose a data frame in R?

There are two common methods you can use to transpose a data frame in R: Method 1: Use Base R #transpose data frame t (df) Method 1: Use data.table library(data.table) #transpose data frame df_t <- transpose (df) #redefine row and column names rownames (df_t) <- colnames (df) colnames (df_t) <- rownames (df)

How to divide the data into groups in R?

Divide the Data into Groups in R Programming – split () function Last Updated : 30 Jun, 2020 split () function in R Language is used to divide a data vector into groups as defined by the factor provided. Syntax: split (x, f, drop = FALSE)

How to use split () function in R language?

split () function in R Language is used to divide a data vector into groups as defined by the factor provided. Syntax: split (x, f, drop = FALSE) Parameters: x: represents data vector or data frame. f: represents factor to divide the data. drop: represents logical value which indicates if levels that do not occur should be dropped.

What is transposing in data structure?

Transposing means converting rows to columns and columns to rows col1 col2 col3 col4 r1 1 6 11 16 r2 2 7 12 17 r3 3 8 13 18 r4 4 9 14 19 r5 5 10 15 20


2 Answers

require(reshape2)
dcast(x, X + Y ~ D, value.var="S")

If you want to fill empty entries with 0 instead of NA (which is the default), then,

dcast(x, X + Y ~ D, value.var="S", fill=0)
like image 101
Arun Avatar answered Sep 16 '22 22:09

Arun


A solution in base R:

> reshape(x, timevar="D", idvar=c("X","Y"), direction="wide")
  X Y S.1 S.2 S.3
1 a e  10  20  NA
3 a f  50  NA  NA
4 b c  40  30  60
7 b d  10  20  NA
like image 28
Ferdinand.kraft Avatar answered Sep 20 '22 22:09

Ferdinand.kraft