Order of variable names pivot_wider

10
votes

How can you change the order of the variable names in pivot_wider to have he names_from before the values_from?

Using the us_rent_income dataset:

df <- us_rent_income %>% 
  pivot_wider(names_from = NAME,
              values_from = c(estimate, moe))

This gives results like 'estimate_Alabama', how do we change the order of the variable so it is 'Alabama_estimate'?

2
rdplyrtidyr

2 Answers

16
votes

Edit: As of tidyr 1.1.0 the order of the variable names can be controlled with the names_glue argument:

us_rent_income %>%
  pivot_wider(
    names_from = NAME,
    values_from = c(estimate, moe),
    names_glue = "{NAME}_{.value}"
  )

Old answer:

The documentation for pivot_wider() states "If values_from contains multiple values, the value will be added to the front of the output column" so there doesn't seem to be any way to control this as part of the reshape. Instead, it has to be done afterwards.

Assuming there are no other variable names in the dataset that contain _ (if so, the separator can be changed to something unique using the names_sep argument), one approach would be:

library(tidyr)
    
df <- us_rent_income %>% 
  pivot_wider(names_from = NAME,
              values_from = c(estimate, moe)) %>%
  setNames(nm = sub("(.*)_(.*)", "\\2_\\1", names(.)))

head(names(df))

[1] "GEOID"  "variable"  "Alabama_estimate"  "Alaska_estimate"  "Arizona_estimate"  "Arkansas_estimate"
0
votes

A possible solution:

df <- us_rent_income %>% 
  pivot_wider(names_from = NAME,
              values_from = c(estimate, moe))

names(df) <- sapply(strsplit(names(df), "_"), 
                    function(x) if(length(x)>1) paste0(x[2],"_",x[1]) else x)

names(df)
# [1] "GEOID" "variable" "Alabama_estimate" "Alaska_estimate" 
#     "Arizona_estimate" "Arkansas_estimate" "California_estimate" ...