CODEWITHSUNDEEP
rbinary
justzara
justzara

Reputation: 13

split single column to several columns of binary matrix

I have a big dataset in R which several individuals listed in several rows in one column for one area.

      ID Elevation Year Individual.code
1  Area1      11.0 2009              AA
2  Area1      11.0 2009              AB
3  Area3      79.5 2009              AA
4  Area3      79.5 2009              AC
5  Area3      79.5 2009              AD
6  Area5      57.5 2010              AE
7  Area5      57.5 2010              AB
8  Area7     975.0 2011              AA
9  Area7     975.0 2011              AB

I want to create a matrix by splitting the “individual code” into binary matrix without losing rest of the variables i.e. ID, Elevation and Year 

#     ID Elevation Year AA AB AC AD AE
#1 Area1      11.0 2009  1  1  0  0  0
#2 Area3      79.5 2009  1  0  1  1  0
#3 Area5      57.5 2010  0  1  0  0  1
#4 Area7     975.0 2011  1  1  0  0  0

Upvotes: 1

Views: 73

Answers (3)

akrun
akrun

Reputation: 887223

You could try dplyr/tidyr

library(dplyr)
library(tidyr)
spread(dat, Individual.code, Individual.code) %>% 
                  mutate_each(funs((!is.na(.))+0L), AA:AE)   
#     ID Elevation Year AA AB AC AD AE
#1 Area1      11.0 2009  1  1  0  0  0
#2 Area3      79.5 2009  1  0  1  1  0
#3 Area5      57.5 2010  0  1  0  0  1
#4 Area7     975.0 2011  1  1  0  0  0

Or you can use reshape from base R

 res <- reshape(cbind(dat, Col=1), idvar=c('ID', 'Elevation', 'Year'), 
           timevar='Individual.code', direction='wide')
 res[is.na(res)] <- 0

Upvotes: 1

Roland
Roland

Reputation: 132706

DF <- read.table(text = "      ID Elevation Year Individual.code
                 1  Area1      11.0 2009              AA
                 2  Area1      11.0 2009              AB
                 3  Area3      79.5 2009              AA
                 4  Area3      79.5 2009              AC
                 5  Area3      79.5 2009              AD
                 6  Area5      57.5 2010              AE
                 7  Area5      57.5 2010              AB
                 8  Area7     975.0 2011              AA
                 9  Area7     975.0 2011              AB", header = TRUE)

library(reshape2)
dcast(DF, ID + Elevation + Year ~ Individual.code, 
      fun.aggregate = function(x) as.integer(length(x) > 0))
#     ID Elevation Year AA AB AC AD AE
#1 Area1      11.0 2009  1  1  0  0  0
#2 Area3      79.5 2009  1  0  1  1  0
#3 Area5      57.5 2010  0  1  0  0  1
#4 Area7     975.0 2011  1  1  0  0  0

Upvotes: 1

Tyler Rinker
Tyler Rinker

Reputation: 109874

Here's one approach:

dat <- read.table(text = "      ID Elevation Year Individual.code
1  Area1      11.0 2009              AA
2  Area1      11.0 2009              AB
3  Area3      79.5 2009              AA
4  Area3      79.5 2009              AC
5  Area3      79.5 2009              AD
6  Areas      57.5 2010              AE
7  Area5      57.5 2010              AB
8  Area7     975.0 2011              AA
9  Area7     975.0 2011              AB", header = TRUE)

if (!require("pacman")) install.packages("pacman"); library(pacman)
p_load(qdapTools, dplyr)

mtabulate(split(dat[["Individual.code"]], dat[["ID"]])) %>%
    matrix2df("ID") %>%
    left_join(distinct(select(dat, -Individual.code)), .)  

##      ID Elevation Year AA AB AC AD AE
## 1 Area1      11.0 2009  1  1  0  0  0
## 2 Area3      79.5 2009  1  0  1  1  0
## 3 Area5      57.5 2010  0  1  0  0  1
## 4 Area7     975.0 2011  1  1  0  0  0

Upvotes: 1

Related Questions