Unpivot or transpose columns into rows R

Question

Given the dataframe below:

dt <- data.frame("Year"=2020,
                 "Month"=c("Jan","Jan","Feb"),
                 "Location"=c("Store_1","Store_1","Store_2"),
                 "Apples"=c(100, 150, 120),
                 "Oranges"=c(50, 70, 50))

  Year Month Location Apples Oranges
1 2020   Jan  Store_1    100      50
2 2020   Jan  Store_1    150      70
3 2020   Feb  Store_2    120      50

How can I turn this table into the following table, basically by maintaining the first three columns and unpivoting the next two columns.

  Year Month Location   Type Values
1 2020   Jan  Store_1  Apple    100
2 2020   Jan  Store_1  Apple    150
3 2020   Feb  Store_2  Apple    120
4 2020   Jan  Store_1 Orange     50
5 2020   Jan  Store_1 Orange     70
6 2020   Feb  Store_2 Orange     50

Any hints or tips on this?

score 0 · Accepted Answer · answered Dec 30 '20 at 20:36

We can use pivot_longer from tidyr

library(dplyr)
library(tidyr)
dt %>%
    pivot_longer(cols = Apples:Oranges, names_to = 'Type',
         values_to = 'Values') %>% 
    arrange(Year, Type)

-output

# A tibble: 6 x 5
#   Year Month Location Type    Values
#  <dbl> <chr> <chr>    <chr>    <dbl>
#1  2020 Jan   Store_1  Apples     100
#2  2020 Jan   Store_1  Apples     150
#3  2020 Feb   Store_2  Apples     120
#4  2020 Jan   Store_1  Oranges     50
#5  2020 Jan   Store_1  Oranges     70
#6  2020 Feb   Store_2  Oranges     50

Unpivot or transpose columns into rows R

1 Answers1