Adding a Proportion Column with Dplyr

user_14205564 0
Viewed 156

Let's say I had the following data frame, that was also altered to include counts of a,b, and c, based on whether or not they are classified by Z = 0 or 1

X <- (1:10)
Y<-  c('a','b','a','c','b','b','a','a','c','c')
Z <- c(0,1,1,1,0,1,0,1,1,1)
test_df <- data.frame(X,Y,Z)

(the code below was provided by a stack exchange member, thank you!)

res <- test_df %>% group_by(Y,Z) %>% summarise(N=n()) %>%
  pivot_wider(names_from = Z,values_from=N,
              values_fill = 0)

How might I add a column on the right which would indicate the proportion of each of the letters for which z=1, out of all appearances of that letter? It would seem that a basic summary statement should work but I figure out how...

My expected output would be something like

  Z=0 Z=1 PropZ=1
a  2   2     .5
b  1   2     .66
c  0   3     1
dplyr dataframe r
4 Answers

Perhaps this helps

library(dplyr)
library(tidyr)
test_df %>%
   group_by(Y, Z) %>% 
   summarise(N = n(), .groups = 'drop') %>% 
   left_join(test_df %>%
                group_by(Y) %>% 
                summarise(Prop = mean(Z == 1), .groups = 'drop')) %>% 
   pivot_wider(names_from = Z, values_from = N, values_fill = 0)

-output

# A tibble: 3 x 4
#  Y      Prop   `0`   `1`
#  <chr> <dbl> <int> <int>
#1 a     0.5       2     2
#2 b     0.667     1     2
#3 c     1         0     3
user_37322710

I am not sure if what is your expected output, but below might be some options

u <- xtabs(q ~ Y + Z, cbind(test_df, q = 1))
> u
   Z
Y   0 1
  a 2 2
  b 1 2
  c 0 3

or

> prop.table(u)
   Z
Y     0   1
  a 0.2 0.2
  b 0.1 0.2
  c 0.0 0.3
user_121587570 
  test_df %>% group_by(Y) %>%
  summarise( z0 = sum(Z == 0), z1 = sum(Z == 1) , PropZ = z1/n())
user_123476000

To calculate proportions of 1 for each letter you can use rowSums.

transform(res, prop_1 = `1`/rowSums(res[-1]))

In dplyr :

library(dplyr)

res %>%
  ungroup %>%
  mutate(prop_1 = `1`/rowSums(.[-1]))

#  Y       `0`   `1` prop_1
#  <chr> <int> <int>  <dbl>
#1 a         2     2  0.5  
#2 b         1     2  0.667
#3 c         0     3  1
user_39629140
Related