如何在R中多次执行PIVOT_WIDER？

发布于 2025-01-24 04:27:23 字数 1006 浏览 0 评论 0原文

以下是示例数据和所需结果。手头的任务是多次旋转（或者至少我认为是多次），以创建每个行的完整历史系列。目前，每个QTR（四分之一）是一排。命名惯例并不那么重要。我知道如何做一个pivot_wider，但是每月列的名称的创建使我感到困惑。

 area <- c("000000","000000","000000","000000","000003","000003","000003","000003")
 indcode <- c("432100","432100","432100","432100","432100","432100","432100","432100")
 Year <- c("2019","2019","2019","2019","2019","2019","2019","2019")
 qtr <- c("01","02","03","04","01","02","03","04")
 month1_emplvl <-c(100,101,102,103,44,44,46,52)
 month2_emplvl <-c(100,101,103,104,48,44,52,41)
 month3_emplvl <-c(101,100,102,99,44,45,46,47)

 testdata <- data.frame(area,indcode,Year,qtr,month1_emplvl,month2_emplvl,month3_emplvl)

期望的结果

  area    indcode    2019-01     2019-02   2019-03    2019-04   2019-05   2019-06 and so on...
  000000    432100      100         100        101      101        101        100
  000003    432100       44          48         44       44        44          45

原文

Below is the sample data and the desired result. The task at hand is to pivot multiple times(or at least I think it is multiple times) in order to create a complete historical series per row. At the moment, each qtr (quarter) is a row. The naming convention is not all that important. I know how to do a pivot_wider but the creating of the monthly column names has me stumped.

 area <- c("000000","000000","000000","000000","000003","000003","000003","000003")
 indcode <- c("432100","432100","432100","432100","432100","432100","432100","432100")
 Year <- c("2019","2019","2019","2019","2019","2019","2019","2019")
 qtr <- c("01","02","03","04","01","02","03","04")
 month1_emplvl <-c(100,101,102,103,44,44,46,52)
 month2_emplvl <-c(100,101,103,104,48,44,52,41)
 month3_emplvl <-c(101,100,102,99,44,45,46,47)

 testdata <- data.frame(area,indcode,Year,qtr,month1_emplvl,month2_emplvl,month3_emplvl)

Desired result

  area    indcode    2019-01     2019-02   2019-03    2019-04   2019-05   2019-06 and so on...
  000000    432100      100         100        101      101        101        100
  000003    432100       44          48         44       44        44          45

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

唐婉 2025-01-31 04:27:23

这是您要寻找的解决方案（将所有几个月传递给values_from参数）吗？

df <- pivot_wider(testdata, values_from = c(month1_emplvl, month2_emplvl, month3_emplvl), names_from = c(Year, qtr))

Is this the solution you're looking for (pass all the months to the values_from argument)?

df <- pivot_wider(testdata, values_from = c(month1_emplvl, month2_emplvl, month3_emplvl), names_from = c(Year, qtr))

回复收藏 0 原文

烏雲後面有陽光 2025-01-31 04:27:23

我不知道您要多次使用枢轴，但我认为您可以使用

library(tidyr)
library(dplyr)
library(stringr)

testdata %>% 
  pivot_longer(starts_with("month"), names_pattern = "month(\\d)_*") %>% 
  mutate(
    new_name = 
      paste(Year, 
            str_pad((as.integer(qtr) - 1) * 3 + as.integer(name), 
                    width = 2, 
                    side = "left", 
                    pad = "0"),
            sep = "-"),
    .keep = "unused") %>%   
  pivot_wider(names_from = new_name)

此回报

# A tibble: 2 x 14
  area  indcode `2019-01` `2019-02` `2019-03` `2019-04` `2019-05` `2019-06` `2019-07` `2019-08`
  <chr> <chr>       <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
1 0000~ 432100        100       100       101       101       101       100       102       103
2 0000~ 432100         44        48        44        44        44        45        46        52
# ... with 4 more variables: `2019-09` <dbl>, `2019-10` <dbl>, `2019-11` <dbl>,
#   `2019-12` <dbl>

I don't know how you want to apply pivoting multiple times, but I think you could use

library(tidyr)
library(dplyr)
library(stringr)

testdata %>% 
  pivot_longer(starts_with("month"), names_pattern = "month(\\d)_*") %>% 
  mutate(
    new_name = 
      paste(Year, 
            str_pad((as.integer(qtr) - 1) * 3 + as.integer(name), 
                    width = 2, 
                    side = "left", 
                    pad = "0"),
            sep = "-"),
    .keep = "unused") %>%   
  pivot_wider(names_from = new_name)

This returns

# A tibble: 2 x 14
  area  indcode `2019-01` `2019-02` `2019-03` `2019-04` `2019-05` `2019-06` `2019-07` `2019-08`
  <chr> <chr>       <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
1 0000~ 432100        100       100       101       101       101       100       102       103
2 0000~ 432100         44        48        44        44        44        45        46        52
# ... with 4 more variables: `2019-09` <dbl>, `2019-10` <dbl>, `2019-11` <dbl>,
#   `2019-12` <dbl>

回复收藏 0 原文

~没有更多了~