Pivoting population data with multible sex specific age groups-CodePudding

I have the following population structure:

tibble(
  female = c("0-10", "10-20"),
  female_population = c(30000, 50000),
  male = c("0-10", "10-20"),
  male_population = c(33000, 45000),
  total = c("0-10", "10-20"),
  total_population = female_population   male_population
)

# A tibble: 2 x 6
  female female_population male  male_population total total_population
  <chr>              <dbl> <chr>           <dbl> <chr>            <dbl>
1 0-10               30000 0-10            33000 0-10             63000
2 10-20              50000 10-20           45000 10-20            95000

I'd like to pivot it so that I get a single collumn for age, sex and population like this:

tibble(
  sex = rep(c("female", "male", "total"), each = 2),
  age = rep(c("0-10", "10-20"), 3),
  population = c(30000, 50000, 33000, 45000, 63000, 95000)
)

# A tibble: 6 x 3
  sex    age   population
  <chr>  <chr>      <dbl>
1 female 0-10       30000
2 female 10-20      50000
3 male   0-10       33000
4 male   10-20      45000
5 total  0-10       63000
6 total  10-20      95000

Any ideas how to do that elegantly, maybe using pivot_longer?

CodePudding user response：

Try this using pivot_longer:

(%>% may be used instead of |>; the latter is in more recent versions of base R.)

library(tidyverse)

df <- tibble(
  female = c("0-10", "10-20"),
  female_population = c(30000, 50000),
  male = c("0-10", "10-20"),
  male_population = c(33000, 45000),
  total = c("0-10", "10-20"),
  total_population = female_population   male_population
)

df |> 
  pivot_longer(!contains("_"), names_to = "sex", values_to = "age") |> 
  mutate(population = case_when(
    sex == "female" ~ female_population,
    sex == "male"   ~ male_population,
    sex == "total"  ~ total_population
  )) |> 
  select(-contains("_")) |> 
  arrange(sex)

#> # A tibble: 6 × 3
#>   sex    age   population
#>   <chr>  <chr>      <dbl>
#> 1 female 0-10       30000
#> 2 female 10-20      50000
#> 3 male   0-10       33000
#> 4 male   10-20      45000
#> 5 total  0-10       63000
#> 6 total  10-20      95000

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