R: Turning columns into rows (appending the columns)

Question

I've the following dataset and I would like to find out how to switch the column into rows in R.

Pet Shop	Year	dog	fish	turtle
A	2021	275	20	NA
A	2020	250	NA	50
A	2019	NA	NA	NA
B	2021	350	75	NA
B	2020	NA	55	85
B	2019	NA	75	NA
C	2021	280	NA	NA
C	2020	NA	NA	NA
C	2019	NA	60	NA

Does anyone have any suggestions as to how I can append the columns as rows?

Pet Shop	Year	Animal	Prices
A	2021	dog	275
A	2021	fish	20
A	2021	turtle	NA
A	2020	dog	250
A	2020	fish	NA
A	2020	turtle	50
A	2019	dog	NA
A	2019	fish	NA
A	2019	turtle	NA
B	2021	dog	350
B	2021	fish	75
B	2021	turtle	NA
B	2020	dog	NA
B	2020	fish	55
B	2020	turtle	85

... and onwards

Thanks in advance!

score 1 · Accepted Answer · answered Feb 25 '22 at 06:16

library(tidyverse)

df <- data.frame(
  stringsAsFactors = FALSE,
          Pet.Shop = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
              Year = c(2021L,2020L,2019L,2021L,
                       2020L,2019L,2021L,2020L,2019L),
               dog = c(275L, 250L, NA, 350L, NA, NA, 280L, NA, NA),
              fish = c(20L, NA, NA, 75L, 55L, 75L, NA, NA, 60L),
            turtle = c(NA, 50L, NA, NA, 85L, NA, NA, NA, NA)
)

df %>% 
  as_tibble() %>% 
  pivot_longer(cols = dog:turtle,
               names_to = "Animal",
               values_to = "Prices")

#> # A tibble: 27 x 4
#>    Pet.Shop  Year Animal Prices
#>    <chr>    <int> <chr>   <int>
#>  1 A         2021 dog       275
#>  2 A         2021 fish       20
#>  3 A         2021 turtle     NA
#>  4 A         2020 dog       250
#>  5 A         2020 fish       NA
#>  6 A         2020 turtle     50
#>  7 A         2019 dog        NA
#>  8 A         2019 fish       NA
#>  9 A         2019 turtle     NA
#> 10 B         2021 dog       350
#> # ... with 17 more rows

^{Created on 2022-02-25 by the reprex package (v2.0.1)}

R: Turning columns into rows (appending the columns)

1 Answers1