CODEWITHSUNDEEP
rpivotdplyrtidyr
Guy
Guy

Reputation: 75

dplyr pivot table

I want to obtain a pivot table with descending value. 

library(dplyr)
library(tidyr)

h<-mtcars %>%
  group_by(cyl, gear) %>%
  tally() %>%
  spread(gear, n, fill = 0)
h<-h%>% add_rownames("index")

i<-mtcars %>%
  group_by(cyl, gear) %>%
  tally() %>%
  spread(cyl, n, fill = 0)

To obtain the sum of the values 

j<-i%>%
  select(-1)%>%
  summarise_each(funs(sum))

k<-t(j)
k<- as.data.frame(k)
k<-tbl_df(k)
k<-k%>%add_rownames("index")

l<-left_join(h,k,by="index")
l<-l%>%
  select(-1)%>%
  arrange(desc(V1))

Is there another way to do the same in dplyr?

Upvotes: 2

Views: 7553

Answers (1)

akrun
akrun

Reputation: 887118

We group by 'cyl', 'gear', get the frequency count (tally()), reshape from 'long' to 'wide' (using spread from tidyr), ungroup to remove the attributes (previously, it used to work without this), use mutate to create 'V1' as the sum of each row (using rowSums) and finally arrange (order) the rows based on values in 'V1'.

library(dplyr)
library(tidyr)

mtcars %>%
   group_by(cyl, gear) %>% 
   tally() %>%
   spread(gear, n, fill=0) %>% 
   ungroup() %>%
   mutate(V1= rowSums(.[-1])) %>% 
   arrange(desc(V1))
#    cyl     3     4     5    V1
#  <dbl> <dbl> <dbl> <dbl> <dbl>
#1     8    12     0     2    14
#2     4     1     8     2    11
#3     6     2     4     1     7

Upvotes: 11

Related Questions