r - 如何在r中创建多个pivot_longer()列？

Question

我正在处理具有多个累积字段列和多个每日字段列的数据集，并且按使用 pivot_longer 将它们转换为:

1. 单/长 累计字段列 和
2. 单/长 每日字段列

数据快照

"Updated.On","State","First.Dose.Administered","Second.Dose.Administered","Daily_First_dose","Daily_Second_dose"
2021-07-09,"India",297184419,69894633,2071121,1141754
2021-07-09,"Andaman and Nicobar Islands",164079,51646,795,6336
2021-07-09,"Andhra Pradesh",13372990,3379660,25571,11168
2021-07-09,"Arunachal Pradesh",583021,108393,8423,3430
2021-07-09,"Assam",6462249,1328646,52913,7681
2021-07-09,"Bihar",15741778,2638686,262299,55007
2021-07-09,"Chandigarh",483886,110610,4351,3491
2021-07-09,"Chhattisgarh",7438953,1819709,64193,42342
2021-07-09,"Dadra and Nagar Haveli and Daman and Diu",458057,57874,7122,1745
2021-07-09,"Delhi",6761634,2049318,95352,35848

将列转换为:

1. 俱乐部 First.Dose.Administered, Second.Dose.Administered cols 至names_to =“Dose_type”，values_to =“Dose_Administered”
2. 俱乐部 Daily_First_dose、Daily_Second_dose 列改为 names_to = "Dose_type_daily"、values_to = "Dose_Administered_daily"

所需数据框示例:

Updated.On State Dose_type                Dose_Administered Dose_type_daily   Dose_Administered_daily 
2021-07-09 India First.Dose.Administered  297184419         Daily_First_dose  2071121
2021-07-09 India Second.Dose.Administered  69894633         Daily_Second_dose 1141754

df 可以从以下位置下载:

library(tidyverse)
library(lubridate)

file_url1 <- "https://raw.githubusercontent.com/johnsnow09/covid19-df_stack-code/main/df_vaccination_june.csv"

df_vaccination <- read.csv(url(file_url1))

df_vaccination <- df_vaccination %>%
  mutate(Updated.On = as.Date(Updated.On))

尝试:我尝试了下面的代码，但不确定如何创建两个不同的values_to列？

df_vaccination %>% 
  pivot_longer(cols = c(First.Dose.Administered:Second.Dose.Administered, 
                        Daily_First_dose:Daily_Second_dose),
               names_to = c("Dose_type","Dose_type_daily"), 
               values_to = c("Dose_Administered","Dose_Administered_daily"))

Answer 1

我认为您需要使用正确的 names_pattern regex 参数两次 pivot_longer 。

library(tidyverse)

df_vaccination %>%
  pivot_longer(cols = 3:4, names_to = c('dose_adminstered','.value'), 
               names_pattern = '(.*)\\.(Administered)') %>%
  pivot_longer(cols = 3:4, names_to = c('dose_type_daily', '.value'), 
               names_pattern = '(.*)_(.*)$')

#> # A tibble: 3,552 x 6
#>    Updated.On State         dose_adminstered Administered dose_type_daily   dose
#>    <date>     <chr>         <chr>                   <int> <chr>            <int>
#>  1 2021-07-09 India         First.Dose          297184419 Daily_First     2.07e6
#>  2 2021-07-09 India         First.Dose          297184419 Daily_Second    1.14e6
#>  3 2021-07-09 India         Second.Dose          69894633 Daily_First     2.07e6
#>  4 2021-07-09 India         Second.Dose          69894633 Daily_Second    1.14e6
#>  5 2021-07-09 Andaman and ~ First.Dose             164079 Daily_First     7.95e2
#>  6 2021-07-09 Andaman and ~ First.Dose             164079 Daily_Second    6.34e3
#>  7 2021-07-09 Andaman and ~ Second.Dose             51646 Daily_First     7.95e2
#>  8 2021-07-09 Andaman and ~ Second.Dose             51646 Daily_Second    6.34e3
#>  9 2021-07-09 Andhra Prade~ First.Dose           13372990 Daily_First     2.56e4
#> 10 2021-07-09 Andhra Prade~ First.Dose           13372990 Daily_Second    1.12e4
#> # ... with 3,542 more rows

---

要为每个州仅返回 2 行，您可以执行此解决方法

df_vaccination[1:4] %>%
  pivot_longer(cols = 3:4, names_to = c('dose_adminstered','.value'), 
               names_pattern = '(.*)\\.(Administered)') %>%
  cbind(df_vaccination[5:6] %>% pivot_longer(everything(), names_to = c('dose_type_daily', '.value'), 
               names_pattern = '(.*)_(.*)$'))

    Updated.On                                    State dose_adminstered Administered dose_type_daily    dose
1   2021-07-09                                    India       First.Dose    297184419     Daily_First 2071121
2   2021-07-09                                    India      Second.Dose     69894633    Daily_Second 1141754
3   2021-07-09              Andaman and Nicobar Islands       First.Dose       164079     Daily_First     795
4   2021-07-09              Andaman and Nicobar Islands      Second.Dose        51646    Daily_Second    6336
5   2021-07-09                           Andhra Pradesh       First.Dose     13372990     Daily_First   25571
6   2021-07-09                           Andhra Pradesh      Second.Dose      3379660    Daily_Second   11168
7   2021-07-09                        Arunachal Pradesh       First.Dose       583021     Daily_First    8423
8   2021-07-09                        Arunachal Pradesh      Second.Dose       108393    Daily_Second    3430
.
.
.