how can i make R system reorganize this tibble

Question

i have this table

df <- tibble(
  club = c("Avai", "Figueirense"),
  "2019_revenue" = runif(min = 100, max =300, n = 2),
  "2018_revenue" = runif(min = 100, max =300, n = 2),
  "2017_revenue" = runif(min = 100, max =300, n = 2),
  "2019_division" = c("A","B"),
  "2018_division" = c("B","B"),
  "2017_division" = c("A","B")
)

and i want to reorganize this, so the coluns can be "club","year", "revenue" and "division", in this order. whant can i do to make this? can i use pivot_longer?

score 1 · Answer 1 · answered Sep 24 '21 at 18:38

We can capture the characters before of after _ in the column names with names_pattern and use that in pivot_longer

library(dplyr)
library(tidyr)
df %>%
   pivot_longer(cols = -club, names_to = c("year", ".value"), 
        names_pattern = "(\\d+)_(.*)")

-output

# A tibble: 6 x 4
  club        year  revenue division
  <chr>       <chr>   <dbl> <chr>   
1 Avai        2019     128. A       
2 Avai        2018     178. B       
3 Avai        2017     214. A       
4 Figueirense 2019     291. B       
5 Figueirense 2018     154. B       
6 Figueirense 2017     282. B

Or may also use names_sep

df %>%
    pivot_longer(cols = -club, names_to = c("year", ".value"), names_sep = "_")

how can i make R system reorganize this tibble

1 Answers1