CODEWITHSUNDEEP
r
icedcoffee
icedcoffee

Reputation: 1015

How to condense a data frame into a matrix of "Yes" or "No" values in R?

Sorry if the title wasn't very self-explanatory. I have a data frame, df:

df <- structure(list(df = c("cat", "dog", "cat", "fish", 
"dog", "cat")), row.names = c("house1a", "house1b", 
"house2a", "house2b", "house3", "house4"
), class = "data.frame")

> df
          df
house1a  cat
house1b  dog
house2a  cat
house2b fish
house3   dog
house4   cat

I want to be able to have the animal types as rownames, and then have a "Yes" or "No" value if they are present in each household. Note that there are multiple rows per household - eg. house1a and house1b are both house1.

The resulting dataframe would look like this:

df_new <- structure(list(
house1 = c("Yes", "Yes", "No"), 
house2 = c("Yes", "No", "Yes"), 
house3 = c("No", "Yes", "No"), 
house4 = c("Yes", "No", "No")), 
row.names = c("cat", "dog", 
"fish"), class = "data.frame")

> df_new
     house1 house2 house3 house4
cat     Yes    Yes     No    Yes
dog     Yes     No    Yes     No
fish     No    Yes     No     No

What is the best way to create this in R?

Upvotes: 0

Views: 56

Answers (2)

gaut
gaut

Reputation: 5958

I would do this

df$house <- row.names(df)
res <- dplyr::group_by(df, house) 
res <- dplyr::summarise(res, containscat = "cat" %in% df, containsdog = "dog" %in% df)
> res
# A tibble: 6 x 3
house   containscat containsdog
<chr>   <lgl>       <lgl>      
  1 house1a TRUE        FALSE      
2 house1b FALSE       TRUE       
3 house2a TRUE        FALSE      
4 house2b FALSE       FALSE      
5 house3  FALSE       TRUE       
6 house4  TRUE        FALSE

Upvotes: 1

zx8754
zx8754

Reputation: 56149

Try:

table(df$df, substring(rownames(df), 1, 6))
#      house1 house2 house3 house4
# cat       1      1      0      1
# dog       1      0      1      0
# fish      0      1      0      0

Upvotes: 2

Related Questions