Pivot wider with multiple columns

Question

I have a data set that looks like this

df <- data.frame(Country = c("USA","USA","USA","UK","UK","UK"),
                 Measure = c("Revenue","Profit","emp","Revenue","Profit","emp"),
                 Year_19 = c("250000","100000","15","140000","25000","4"),
                 Year_20 = c("130000","44000","8","76000","13000","3"))

I want to rearrange it in such a way that the final data appears as such

       Year_19_Revenue   Year_19_Profit    Year_19_emp    Year_20_Revenue   Year_20_Profit    Year_20_emp
USA      250000            100000             15             130000          44000                8
UK       140000            25000              4              76000           13000                3

Can't understand how to do this. Can anyone please help? Thanks

score 3 · Accepted Answer · answered Feb 03 '21 at 18:51

We can specify multiple columns in values_from

library(dplyr)
library(tidyr)
df %>% 
   pivot_wider(names_from = Measure, values_from = c(Year_19, Year_20))

-output

# A tibble: 2 x 7
#  Country Year_19_Revenue Year_19_Profit Year_19_emp Year_20_Revenue Year_20_Profit Year_20_emp
#  <chr>   <chr>           <chr>          <chr>       <chr>           <chr>          <chr>      
#1 USA     250000          100000         15          130000          44000          8          
#2 UK      140000          25000          4           76000           13000          3

thank you very much. I should have got it. new to using pivot and R — FinRC, Feb 03 '21 at 18:59

score 1 · Answer 2 · answered Feb 03 '21 at 21:09

A base R option

reshape(
  df,
  direction = "wide",
  idvar = "Country",
  timevar = "Measure"
)

gives

  Country Year_19.Revenue Year_20.Revenue Year_19.Profit Year_20.Profit
1     USA          250000          130000         100000          44000
4      UK          140000           76000          25000          13000
  Year_19.emp Year_20.emp
1          15           8
4           4           3

A data.table option with dcast

> dcast(setDT(df),Country~Measure,value.var = c("Year_19","Year_20"))
   Country Year_19_Profit Year_19_Revenue Year_19_emp Year_20_Profit
1:      UK          25000          140000           4          13000
2:     USA         100000          250000          15          44000
   Year_20_Revenue Year_20_emp
1:           76000           3
2:          130000           8

Pivot wider with multiple columns

2 Answers2