Calculating the frequency of a factor with specific formula-CodePudding

I have data set like this:

df <- data.frame( ID = c("A","A","A","B","B","B","C","C","C"), 
                       levels = c( "Y", "R", "O","Y", "R", "O","Y", "R", "O" ),
                       Counts=c(5,1,5,10,2,1,3,5,8))

  ID levels Counts
  A      Y      5
  A      R      1
  A      O      5
  B      Y     10
  B      R      2
  B      O      1
  C      Y      3
  C      R      5
  C      O      8

I want to create another column that has a percentage of the second column(levels) like this formula

freq=(Y O/Y O R)*100

So now the data frame should look like this :

ID freq
A  0.1
B  0.2
C  0.3

I tried a couple of solutions but it did not work can you please help me?

CodePudding user response：

Using pivot_wider

df1 %>%
  pivot_wider(id_cols = ID, values_from = Counts, names_from = levels) %>%
  mutate(freq = (Y O/Y O R)*100,
         freq. = (Y O)/(Y O R)*100) # %>% select(-Y, -R, -O)

  ID        Y     R     O  freq freq.
  <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A         5     1     5 1200   90.9
2 B        10     2     1 1310   84.6
3 C         3     5     8 1867.  68.8

I'm not sure what does your formula want.

CodePudding user response：

You may try using match -

library(dplyr)

df %>%
  group_by(ID) %>%
  summarise(freq = (Counts[match('Y', levels)]   Counts[match('O', levels)])/sum(Counts))

#   ID     freq
#  <chr> <dbl>
#1 A     0.909
#2 B     0.846
#3 C     0.688