How do I specify a particular column order in pivot_wider

user_12823747 0
Viewed 2676

Dataframe indrur consists of variable S447.1 that indicates mode of transport. I use dplyr to group by the variable and convert it to wide format. 

library(dplyr)      
    indrurmodes<-indrur %>% 
          group_by(V024,V025,V190) %>%count(S447.1) %>% 
          pivot_wider(names_from = S447.1,values_from = n, values_fill = list(n = 0))

It gives me the following output. But I want the columns in a particular order - GovtAmb,OthAmb,Jeep,Tempo,Scooter,Bus,Cart,Foot,NA. I have to execute the above code for multiple filtered dataframes. Each time, I get a different order. This makes it difficult to perform further arithmetic operations where I use column index as reference.How do I maintain the desired column order?

       V024     V025    V190 GovtAmb Jeep   Tempo Foot  NA     OthAmb Scooter  Bus  Cart
3   Andaman     Rural   Middle  67   21     16     16   615     2     3        4      0
4   Andaman     Rural   Richer  66   39     20     3    617     2     1        0      0
5   Andaman     Rural   Richest 21   18     5      2    278     0     2        5      1
6   Andhra      Rural   Poorest 25   0      35     4    294     5     0        6      0
dplyr r
1 Answers

You have two options. 

library(dplyr)
library(tidyr)

#Order of the columns you want
vec <- c("GovtAmb","OthAmb","Jeep","Tempo","Scooter","Bus","Cart","Foot",NA)

1) Using pivot_wider :

pivot_wider returns the column in the order they appear. So you can arrange the data in the order you want before using pivot_wider. 

indrur %>% 
   group_by(V024,V025,V190) %>%
   count(S447.1) %>% 
   arrange(match(S447.1, vec)) %>%
   pivot_wider(names_from = S447.1,values_from = n, values_fill = list(n = 0))

2) Use the older spread which gives the column order based on factor levels. 

indrur %>% 
  group_by(V024,V025,V190) %>%
  count(S447.1) %>% 
  mutate(S447.1 = factor(S447.1, levels = vec)) %>%
  spread(S447.1,n,fill = 0)
user_39629140
Related