R: frequencies across rows

mapleleaf 0
Viewed 32

Consider this data frame:

library(dplyr)

one <- c("no", "no", "no", "no", "yes", "yes", "yes", "yes")
two <- c("apple", "banana", "orange", "carrot", "apple", "banana", "orange", "carrot")
three <- c(4, 5, 6, 7, 3, 4, 5, 6)

df <- data.frame(one, two, three)
df

one    two three
1  no  apple     4
2  no banana     5
3  no orange     6
4  no carrot     7
5 yes  apple     3
6 yes banana     4
7 yes orange     5
8 yes carrot     6

Then I pivot wider

df2 <- df %>%
  pivot_wider(names_from = one, values_from = three) 

 two    no    yes  
  <chr>  <chr> <chr>
1 apple  4     3    
2 banana 5     4    
3 orange 6     5    
4 carrot 7     6

Now, I want the relative frequencies across rows, but I cannot figure out to get there. There are the desired columns:

desired_column_no <- c(4/7,5/9,6/11,7/13)
desired_column_yes <- c(3/7,4/9,5/11,6/13)

df2 %>%
  cbind(desired_column_no,
        desired_column_yes)

    two no yes desired_column_no desired_column_yes
1  apple  4   3         0.5714286          0.4285714
2 banana  5   4         0.5555556          0.4444444
3 orange  6   5         0.5454545          0.4545455
4 carrot  7   6         0.5384615          0.4615385

I've been playing around with group_by(), summarize() and across(), but haven't gotten it to work correctly. Any help is greatly appreciated!

data-wrangling tidyr dplyr r
2 Answers

  1. Don't use data.frame(cbind(.)), you're corrupting your data by converting numbers to strings. While it's reversible (and in general, "mostly" reversible but not always), it's also perfectly avoidable. Just use data.frame(.).

  2. We can use across on your wider format.

df <- data.frame(one,two,three) %>%
  pivot_wider(names_from = one, values_from = three) 
df %>%
  mutate(
    across(c(no, yes), ~ . / (no + yes),
           .names = "desired_column_{.col}")
  )
# # A tibble: 4 x 5
#   two       no   yes desired_column_no desired_column_yes
#   <chr>  <dbl> <dbl>             <dbl>              <dbl>
# 1 apple      4     3             0.571              0.429
# 2 banana     5     4             0.556              0.444
# 3 orange     6     5             0.545              0.455
# 4 carrot     7     6             0.538              0.462
r2evans0

With proportions, before pivot_wider:

library(dplyr)
library(tidyr)
df %>% 
  group_by(two) %>% 
  mutate(prop = proportions(three)) %>% 
  pivot_wider(names_from = one, values_from = c(three, prop)) 

  two    three_no three_yes prop_no prop_yes
  <chr>     <dbl>     <dbl>   <dbl>    <dbl>
1 apple         4         3   0.571    0.429
2 banana        5         4   0.556    0.444
3 orange        6         5   0.545    0.455
4 carrot        7         6   0.538    0.462
Maël0
Related