主页 / coding / 问题 / 79052264
Accepted
user430997
Asked: 2024-10-04 05:46:13 +0800 CST

在 R 中分割差异

  • 772

我在 R 中有一个如下所示的数据集：

data = structure(list(quarter = c("Q1 2005", "Q2 2005", "Q3 2005", "Q4 2005", 
"Q1 2006"), value = c(128.76, 178.83, 140.9, 188.3, 194.05)), class = "data.frame", row.names = c(NA, 
-5L))

我想扩展这个数据集，添加一个名为“月份”的额外列（例如一月、二月、三月……）...并且我想将差额分割到连续季度之间的所有月份，以便各个季度之间的数字仍然相加。

我尝试这样做：

library(dplyr)

expand_dataset <- function(data) {
quarter_to_months <- list(
    "Q1" = c("Jan", "Feb", "Mar"),
    "Q2" = c("Apr", "May", "Jun"),
    "Q3" = c("Jul", "Aug", "Sep"),
    "Q4" = c("Oct", "Nov", "Dec")
    )

expanded_data <- data.frame()

for (i in 1:(nrow(data) - 1)) {
    current_value <- data$value[i]
    next_value <- data$value[i + 1]
    
    diff <- (next_value - current_value) / 3
    
    quarter <- substr(data$quarter[i], 1, 2)
    year <- substr(data$quarter[i], 4, 7)
    months <- quarter_to_months[[quarter]]
    
    for (j in 1:3) {
        month_value <- current_value + (j - 1) * diff
        expanded_data <- rbind(expanded_data, data.frame(
            quarter = data$quarter[i],
            month = months[j],
            year = year,
            value = month_value
        ))
    }
    }

return(expanded_data)
}

expanded_data <- expand_dataset(data)
print(expanded_data)

这是正确的做法吗？有没有更简单的方法？

3 个回答

  1. Best Answer

    如果您的意思是按月份分割的总值等于您在原始数据集中分配的值，那么您的代码就不会创建该解决方案。

    有很多很多方法可以实现这一点。

    这个答案假设你想要一个值，总的来说，等于该季度的值。我使用了季度值的 1/3 并将其分配给每个月。

    该解决方案使用 dplyr 和 tidyr。

    d2 <- data %>% rowwise %>%    # use rowwise so that vals only uses 1 row of data
  mutate(months = case_when(
    str_detect(quarter, 'Q1') ~ list(month.abb[1:3]), # find strings like & assoc months
    str_detect(quarter, 'Q2') ~ list(month.abb[4:6]), 
    str_detect(quarter, 'Q3') ~ list(month.abb[7:9]), 
    TRUE ~ list(month.abb[10:12])),                 # if no other conditions met
    vals = list(rep(1/3 * value, 3))) %>%           # divide quarter vals amongst quarters' months
  unnest_longer(c(months, vals))      # get rid of the nested lists
# # A tibble: 15 × 4
#    quarter value months  vals
#    <chr>   <dbl> <chr>  <dbl>
#  1 Q1 2005  129. Jan     42.9
#  2 Q1 2005  129. Feb     42.9
#  3 Q1 2005  129. Mar     42.9
#  4 Q2 2005  179. Apr     59.6
#  5 Q2 2005  179. May     59.6
#  6 Q2 2005  179. Jun     59.6
#  7 Q3 2005  141. Jul     47.0
#  8 Q3 2005  141. Aug     47.0
#  9 Q3 2005  141. Sep     47.0
# 10 Q4 2005  188. Oct     62.8
# 11 Q4 2005  188. Nov     62.8
# 12 Q4 2005  188. Dec     62.8
# 13 Q1 2006  194. Jan     64.7
# 14 Q1 2006  194. Feb     64.7
# 15 Q1 2006  194. Mar     64.7
    • 3

  2. 在基础 R 中这更快也更容易：

    n <- nrow(data)
m <- month.abb[sequence(rep(3, n), as.numeric(substr(data$quarter,2,2))*3-2)]
transform(data[gl(n, 3),],month = m, value2 = value/3, row.names = NULL)

   quarter  value month   value2
1  Q1 2005 128.76   Jan 42.92000
2  Q1 2005 128.76   Feb 42.92000
3  Q1 2005 128.76   Mar 42.92000
4  Q2 2005 178.83   Apr 59.61000
5  Q2 2005 178.83   May 59.61000
6  Q2 2005 178.83   Jun 59.61000
7  Q3 2005 140.90   Jul 46.96667
8  Q3 2005 140.90   Aug 46.96667
9  Q3 2005 140.90   Sep 46.96667
10 Q4 2005 188.30   Oct 62.76667
11 Q4 2005 188.30   Nov 62.76667
12 Q4 2005 188.30   Dec 62.76667
13 Q1 2006 194.05   Jan 64.68333
14 Q1 2006 194.05   Feb 64.68333
15 Q1 2006 194.05   Mar 64.68333

    如果是 tidyverse：

    图书馆（tidyverse）

    data %>%
  mutate(month = map(parse_number(quarter)*3-2, 
                     ~month.abb[seq(.x, len=3)]), value2=value/3) %>%
   unnest_longer(month)
# A tibble: 15 × 4
   quarter value month value2
   <chr>   <dbl> <chr>  <dbl>
 1 Q1 2005  129. Jan     42.9
 2 Q1 2005  129. Feb     42.9
 3 Q1 2005  129. Mar     42.9
 4 Q2 2005  179. Apr     59.6
 5 Q2 2005  179. May     59.6
 6 Q2 2005  179. Jun     59.6
 7 Q3 2005  141. Jul     47.0
 8 Q3 2005  141. Aug     47.0
 9 Q3 2005  141. Sep     47.0
10 Q4 2005  188. Oct     62.8
11 Q4 2005  188. Nov     62.8
12 Q4 2005  188. Dec     62.8
13 Q1 2006  194. Jan     64.7
14 Q1 2006  194. Feb     64.7
15 Q1 2006  194. Mar     64.7
    • 2

  3. 基于 for 循环中的计算的 tidyverse 方法

    library(dplyr)
library(tidyr)

data %>% 
  separate_wider_delim(quarter, names=c("quarter", "year"), " ") %>% 
  mutate(Next = lead(value)) %>%
  reframe(value, Next, 
          month = as.Date(paste("01", month.abb, year), "%d %b %Y"), 
          .by = c(quarter, year)) %>% 
  filter(quarter == quarters(month)) %>% 
  mutate(month = format.Date(month, "%b"), 
         new_value = value + ((Next - value) / 3) * (row_number() - 1),
         Next = NULL,
         .by = c(quarter, year))

    输出

    # A tibble: 15 × 5
   quarter year  value month new_value
   <chr>   <chr> <dbl> <chr>     <dbl>
 1 Q1      2005   129. Jan        129.
 2 Q1      2005   129. Feb        145.
 3 Q1      2005   129. Mar        162.
 4 Q2      2005   179. Apr        179.
 5 Q2      2005   179. May        166.
 6 Q2      2005   179. Jun        154.
 7 Q3      2005   141. Jul        141.
 8 Q3      2005   141. Aug        157.
 9 Q3      2005   141. Sep        172.
10 Q4      2005   188. Oct        188.
11 Q4      2005   188. Nov        190.
12 Q4      2005   188. Dec        192.
13 Q1      2006   194. Jan         NA 
14 Q1      2006   194. Feb         NA 
15 Q1      2006   194. Mar         NA

    如果你只想要每季度的分割值

    data %>% 
  separate_wider_delim(quarter, names=c("quarter", "year"), " ") %>% 
  reframe(value = value / 3, 
          month = as.Date(paste("01", month.abb, year), "%d %b %Y"), 
          .by = c(quarter, year)) %>% 
  filter(quarter == quarters(month)) %>% 
  mutate(month = format.Date(month, "%b"))
# A tibble: 15 × 4
   quarter year  value month
   <chr>   <chr> <dbl> <chr>
 1 Q1      2005   42.9 Jan  
 2 Q1      2005   42.9 Feb  
 3 Q1      2005   42.9 Mar  
 4 Q2      2005   59.6 Apr  
 5 Q2      2005   59.6 May  
 6 Q2      2005   59.6 Jun  
 7 Q3      2005   47.0 Jul  
 8 Q3      2005   47.0 Aug  
 9 Q3      2005   47.0 Sep  
10 Q4      2005   62.8 Oct  
11 Q4      2005   62.8 Nov  
12 Q4      2005   62.8 Dec  
13 Q1      2006   64.7 Jan  
14 Q1      2006   64.7 Feb  
15 Q1      2006   64.7 Mar
    • 0

相关问题