How make a dataframe wider with new column names?

user_15184843 0
Viewed 38

I have the following dataset:

structure(list(year = c(2018, 2018, 2018, 2018, 2018, 2019, 2019, 
2019, 2019, 2019, 2020, 2020, 2020, 2020, 2020, 2021, 2021, 2021, 
2021, 2021), TOTS = c(22653.66, 3776.36, 786769.68, 66467.18, 
199209.4, 11775.4230769231, 15522.0384615385, 730920.557692308, 
120094.576923077, 130204.961538462, 17893.6923076923, 24584.9807692308, 
817194.788461538, 72970.9807692308, 108324.673076923, 60074.1424882649, 
15968.8804545455, 761367.376818182, 60402.795, 90225.7270337195
), TOTL = c(82043.56, 22908.66, 135925.42, 50448.36, 762679.18, 
83680.1538461538, 35655.2115384615, 95892.4807692308, 57961.5, 
726726.423076923, 162654.5, 41344.6153846154, 110685.038461538, 
40149.4615384615, 664171.403846154, 382768.048313859, 66280.6981818182, 
102604.390454545, 55316.4409090909, 459641.12922295), kind = c("Audi", 
"BMW", "Suzuki", "MB", "Honda", "Audi", "BMW", "Suzuki", "MB", 
"Honda", "Audi", "BMW", "Suzuki", "MB", "Honda", "Audi", "BMW", 
"Suzuki", "MB", "Honda")), row.names = c(NA, -20L), groups = structure(list(
    year = c(2018, 2019, 2020, 2021), .rows = structure(list(
        1:5, 6:10, 11:15, 16:20), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), row.names = c(NA, 4L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

Could you help to transform the data, so that it will have 4 rows (by year), and TOTS and TOTL are separated by groups i.e. TOTS_AUDI,TOTS_BMW,TOTS_SUZUKI ...

I tried dcast(test,day + TOTL + TOTS ~ kind, fun.aggregate = sum), however it does not work as characters are on the left and on the right of ~

r
1 Answers
library(tidyr)    

df %>% 
   pivot_wider(names_from = kind, 
               names_glue =   "{.value}_{kind}",
               values_from = c(TOTS, TOTL))
    
# A tibble: 4 x 11
   year TOTS_Audi TOTS_BMW TOTS_Suzuki TOTS_MB TOTS_Honda TOTL_Audi TOTL_BMW TOTL_Suzuki TOTL_MB TOTL_Honda
  <dbl>     <dbl>    <dbl>       <dbl>   <dbl>      <dbl>     <dbl>    <dbl>       <dbl>   <dbl>      <dbl>
1  2018    22654.    3776.     786770.  66467.    199209.    82044.   22909.     135925.  50448.    762679.
2  2019    11775.   15522.     730921. 120095.    130205.    83680.   35655.      95892.  57962.    726726.
3  2020    17894.   24585.     817195.  72971.    108325.   162654.   41345.     110685.  40149.    664171.
4  2021    60074.   15969.     761367.  60403.     90226.   382768.   66281.     102604.  55316.    459641.
user_162337000
Related