Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

R: Collapse multiple boolean columns into single attribute column with new rows for each combination [duplicate]

Trying to melt or collapse a dataframe with multiple boolean columns into a two column database with an id column and a column for the collapsed values BUT each value results in a new row.

Example beginning:

      A S1 S2 S3 S4
1   ex1  1  0  0  0
2   ex2  0  1  0  0
3   ex3  0  0  1  0
4   ex4  1  1  0  0
5   ex5  0  1  0  1
6   ex6  0  1  0  0
7   ex7  1  1  1  0
8   ex8  0  1  1  0
9   ex9  0  0  1  0
10 ex10  1  0  0  0

Desired output:

A   Type
ex1 S1
ex2 S2
ex3 S3
ex4 S1
ex4 S2
ex5 S2
ex5 S4
ex6 S2
ex7 S1
ex7 S2
ex7 S3
ex8 S2
ex8 S3
ex9 S3
ex10 S1

Thanks in advance!

like image 586
Sean Kelso Avatar asked Jun 28 '18 22:06

Sean Kelso


People also ask

How do I convert multiple columns to rows in R?

Thus, to convert columns of an R data frame into rows we can use transpose function t. For example, if we have a data frame df with five columns and five rows then we can convert the columns of the df into rows by using as. data. frame(t(df)).

How do I separate columns and rows in R?

To split a column into multiple columns in the R Language, We use the str_split_fixed() function of the stringr package library. The str_split_fixed() function splits up a string into a fixed number of pieces.

How do you change columns to rows in R?

Rotating or transposing R objects frame so that the rows become the columns and the columns become the rows. That is, you transpose the rows and columns. You simply use the t() command.

How do I find rows and columns in R?

The ncol() function in R programming R programming helps us with ncol() function by which we can get the information on the count of the columns of the object. That is, ncol() function returns the total number of columns present in the object.


1 Answers

in base R:

 subset(cbind(A=dat[,1],stack(dat[-1])),values==1,-2)
      A ind
1   ex1  S1
4   ex4  S1
7   ex7  S1
10 ex10  S1
12  ex2  S2
14  ex4  S2
15  ex5  S2
16  ex6  S2
17  ex7  S2
18  ex8  S2
23  ex3  S3
27  ex7  S3
28  ex8  S3
29  ex9  S3
35  ex5  S4

In the tidyverse:

library(tidyverse)
dat%>%
   gather(Type,j,-A)%>%
   filter(j==1)%>%
   select(-j)
      A Type
1   ex1   S1
2   ex4   S1
3   ex7   S1
4  ex10   S1
5   ex2   S2
6   ex4   S2
7   ex5   S2
8   ex6   S2
9   ex7   S2
10  ex8   S2
11  ex3   S3
12  ex7   S3
13  ex8   S3
14  ex9   S3
15  ex5   S4
like image 92
KU99 Avatar answered Sep 28 '22 04:09

KU99