CODEWITHSUNDEEP
rtidyrspread
MayaGans
MayaGans

Reputation: 1845

turn pivot_wider() into spread()

I love the new tidyr pivot_wider function but since it hasn't been officially added to the CRAN package I was wondering how to convert the following code into the older spread() function (I do not have access to the server to DL tidyr from github)

test <- data.frame(x = c(1,1,2,2,2,2,3,3,3,4),
                   y = c(rep("a", 5), rep("b", 5)))


test %>%
  count(x, y) %>%
  group_by(x) %>%
  mutate(prop = prop.table(n)) %>%
  mutate(v1 = paste0(n, ' (', round(prop, 2), ')')) %>%
  pivot_wider(id_cols = x, names_from = y, values_from = v1)

Desired Output:

# A tibble: 4 x 3
# Groups:   x [4]
      x a        b       
  <dbl> <chr>    <chr>   
1     1 2 (1)    NA      
2     2 3 (0.75) 1 (0.25)
3     3 NA       3 (1)   
4     4 NA       1 (1)

I tried (but is not quite right):

test %>%
  count(x, y) %>%
  group_by(x) %>%
  mutate(prop = prop.table(n)) %>%
  mutate(v1 = paste0(n, ' (', round(prop, 2), ')')) %>%
  spread(y, v1) %>%
  select(-n, -prop)

Any help appreciated!

Upvotes: 1

Views: 175

Answers (2)

Rushabh Patel
Rushabh Patel

Reputation: 2764

You can use data.table package:

> library(data.table)
> setDT(test)[,.(n=.N),by=.(x,y)][,.(y=y,n=n,final=gsub('\\(1\\)','',paste0(n,'(',round(prop.table(n),2), ')'))),by=x]


   x y n   final
1: 1 a 2       2
2: 2 a 3 3(0.75)
3: 2 b 1 1(0.25)
4: 3 b 3       3
5: 4 b 1       1

Upvotes: 2

akrun
akrun

Reputation: 886948

One option is to remove the columns 'n', 'prop' before the spread statement as including them would create unique rows with that column values as well

library(dplyr)
library(tidyr)
test %>%
   count(x, y) %>%
   group_by(x) %>%
   mutate(prop = prop.table(n)) %>%
   mutate(v1 = paste0(n, ' (', round(prop, 2), ')')) %>% 
   select(-n, -prop) %>% 
   spread(y, v1)
# A tibble: 4 x 3
# Groups:   x [4]
#      x a        b       
#  <dbl> <chr>    <chr>   
#1     1 2 (1)    <NA>    
#2     2 3 (0.75) 1 (0.25)
#3     3 <NA>     3 (1)   
#4     4 <NA>     1 (1)

Or using base R

tbl <- table(test)
tbl[] <- paste0(tbl, "(", prop.table(tbl, 1), ")")

Upvotes: 3

Related Questions