Making rows into collums

[Isakhaug]

Hi,

I have a dataframe with year, companies and prices. Below is example (Note, I have several companies (up to 400) and years (from 1980 to 2020))

data_frame<-cbind(year=c(2010,2011,2010,2011,2012,2011,2012,2013, 2014),
company=c("comp1", "comp1", "comp2","comp2","comp2",
"comp3","comp3","comp3","comp3"),
price=c(92.8,132.5,90.4,143.74,150.1,38.06,33.1,33.8,34.2))

This output:

 year   company price   

[1,] "2010" "comp1" "92.8"
[2,] "2011" "comp1" "132.5"
[3,] "2010" "comp2" "90.4"
[4,] "2011" "comp2" "143.74"
[5,] "2012" "comp2" "150.1"
[6,] "2011" "comp3" "38.06"
[7,] "2012" "comp3" "33.1"
[8,] "2013" "comp3" "33.8"
[9,] "2014" "comp3" "34.2"

.
.
I would like to get companies as the col names, with price as the corresponding values. I would like to transform it to look like this:

  year   comp1   comp2    comp3  

[1,] "2010" "92.8" "90.4" "N/A"
[2,] "2011" "132.5" "143.74" "38.06"
[3,] "2012" "N/A" "150.1" "33.1"
[4,] "2013" "N/A" "N/A" "33.8"
[5,] "2014" "N/A" "N/A" "34.2"

Any tips?

Thanks!

[mattwarkentin]

Hi @Isakhaug,

This should do it:

library(tidyverse)

df <- cbind(
  year=c(2010,2011,2010,2011,2012,2011,2012,2013, 2014),
  company=c("comp1", "comp1", "comp2","comp2","comp2",
            "comp3","comp3","comp3","comp3"),
  price=c(92.8,132.5,90.4,143.74,150.1,38.06,33.1,33.8,34.2)
) %>% 
  as_tibble()

pivot_wider(
  data = df,
  id_cols = year,
  names_from = company,
  values_from = price
)
#> # A tibble: 5 × 4
#>   year  comp1 comp2  comp3
#>   <chr> <chr> <chr>  <chr>
#> 1 2010  92.8  90.4   <NA> 
#> 2 2011  132.5 143.74 38.06
#> 3 2012  <NA>  150.1  33.1 
#> 4 2013  <NA>  <NA>   33.8 
#> 5 2014  <NA>  <NA>   34.2