r语言 - 使用pivot_longer()与多个values_to重塑long

Question

我试图重塑长以下数据集:

country  Data Type       Product   2007    [...]   2021
Austria  Retail Volume    id1      125             140
Austria  Retail Value     id1      15000           12000
Austria  Retail Volume    id2      75              25

我想重塑它(用pivot_longer()，看在上帝的份上不要重塑)有两个值_to，一个是零售量，一个是零售价值!我该怎么做呢?

country  Product   Year    Retail Volume Retail Value   
Austria      id1      2007            
Austria      id1      2007         
Austria      id2      2007               

问题是values_to的维度必须是1!

我尝试了pivot_longer()，尝试了不同的帖子，没有帮助…

Answer 1

一个选择是先做一个pivot_longer，然后再做一个pivot_wider:

library(tidyr)
dat |> 
tidyr::pivot_longer(matches("\d{4}")) |> 
tidyr::pivot_wider(names_from = c(Data, Type), values_from = value)
#> # A tibble: 4 × 5
#>   country Product name  Retail_Volume Retail_Value
#>   <chr>   <chr>   <chr>         <int>        <int>
#> 1 Austria id1     2007            125        15000
#> 2 Austria id1     2021            140        12000
#> 3 Austria id2     2007             75           NA
#> 4 Austria id2     2021             25           NA

dat <- read.table(text = "
country  Data Type       Product   2007    2021
Austria  Retail Volume    id1      125             140
Austria  Retail Value     id1      15000           12000
Austria  Retail Volume    id2      75              25           
", header = TRUE, check.names = FALSE)

Answer 2

看起来你必须先pivot_longer()，然后pivot_wider():

library(tidyverse)
df <- tribble(
~country, ~Data_Type, ~Product, ~`2007`, ~`2021`,
"Austria", "Retail Volume", "id1", 125, 140,
"Austria", "Retail Value", "id1", 15000, 12000,
"Austria", "Retail Volume", "id2", 765, 25,
)
df  |> 
pivot_longer(
c(`2007`, `2021`),
names_to = "year",
values_to = "value"
) |> 
pivot_wider(
names_from = Data_Type,
values_from = value
)
#> # A tibble: 4 × 5
#>   country Product year  `Retail Volume` `Retail Value`
#>   <chr>   <chr>   <chr>           <dbl>          <dbl>
#> 1 Austria id1     2007              125          15000
#> 2 Austria id1     2021              140          12000
#> 3 Austria id2     2007              765             NA
#> 4 Austria id2     2021               25             NA

^{创建于2023-04-03 with reprex v2.0.2}