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'?
pivot_wider() "widens" data, increasing the number of columns and decreasing the number of rows.
The pivot_wider() function from the tidyr package in R can be used to pivot a data frame from a long format to a wide format.
Similarly to readr , dplyr and tidyr are also part of the tidyverse. These packages were loaded in R's memory when we called library(tidyverse) earlier.
We can specify both columns to pivot_wider () ’s values_from argument. pivot_wider () pivots variable, then creates columns by combining the value of measure with each column name specified in values_from.
(Here, the grouping variables are numbers, but they are typically factors, sometimes characters.) A plain pivot_wider () gives the wrong column order: Sorting the tibble by the column variable gives the wrong row order: Subsorting by the original row variable still gives the wrong row order.
And for other type of variables (numeric, factors, dates, date-times, …), the order () gives the natural ordering. I have now used pivot_wider () quite a few times (mostly with factor or numeric variables), and the ‘new’ default of arranging the columns by the row the values first appear in in the data frame have never been the desired order.
We can use the pivot_wider () function to pivot this data frame into a wide format: Notice that the values from the stat column are now used as column names and the values from the amount column are used as cell values in these new columns.
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"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With