Search code examples
rdataframedplyrdata.tabletidyverse

Multiple column pivot/transpose using R dataframes

I have this dataframe:

my_df = tibble("name_1"= c("year", "month", "toyota", "hyundai"), 
       "name_2" = c("year", "month", "auris", "iconiq"), 
       "unk_1" = c(2020, 'Jan', 100, 150),
       "unk_2" = c(2020, 'feb', 200, 400))

How can i transpose two columns - or using a pivot_longer() to get the following dataframe:

new_df = tibble("car_name" = c("toyota", "hyundai"), 
       "model" = c("auris", "iconiq"), 
       "year" = c(2020, 2020), 
        'month' = c("Jan", "Feb"), 
        'list_price' = c(100, 150), 
       "sell_price" = c(200, 400))

the pivot or transpose should include both the month and year rows effectively.

I have tried data.table and pivot_longer() but still stuck.

Solution

  • With data.table::transpose

    setNames(
  cbind(my_df[3:4, 1:2], data.table::transpose(my_df[,3:4])),
  c("car_name", "model", "year", "month", "list_price", "sell_price")
)
#>   car_name  model year month list_price sell_price
#> 3   toyota  auris 2020   Jan        100        150
#> 4  hyundai iconiq 2020   feb        200        400