CODEWITHSUNDEEP
rorganization
anni
anni

Reputation: 83

How to get count of repeated entries in columns within a given row, R

I have the given data (smaller than actual set):

ID color1 color2 color3 color4
101 red red blue green
2 blue blue purple N/A
400 yellow yellow red red

I want a result of:

ID red blue green purple yellow
101 2 1 1 0 0
2 0 2 0 1 0
400 2 0 0 0 0

I've tried to use the unique command for a given row, but have been unable to get these results.

Upvotes: 0

Views: 23

Answers (1)

Onyambu
Onyambu

Reputation: 79328

is.na(df) <- df == 'N/A'
as.data.frame.matrix(table(cbind(df[1], unlist(df[-1]))))

    blue green purple red yellow
2      2     0      1   0      0
101    1     1      0   2      0
400    0     0      0   2      2

tidyverse:

library(tidyverse)
df %>%
   mutate(across(everything(), na_if, 'N/A')) %>% 
   pivot_longer(-ID, values_drop_na = TRUE) %>%
   count(ID, value) %>%
   pivot_wider(names_from = 'value', values_from = 'n',values_fill = 0)

# A tibble: 3 x 6
     ID  blue purple green   red yellow
  <int> <int>  <int> <int> <int>  <int>
1     2     2      1     0     0      0
2   101     1      0     1     2      0
3   400     0      0     0     2      2

Upvotes: 0

Related Questions