当功能在R中将单个行分解为多个时，复制列数据 [英] Copy column data when function unaggregates a single row into multiple in R

问题描述

我需要帮助每年总计（对于许多举措），并使用简单的分公式将其分解到每个月。我需要为每个不同的几个列组合执行此操作，同时将从年度损坏的列复制到每个月的总数。循环将公式应用于两列，并循环遍历向量中的每个不同组。我试图在下面的例子中解释，因为它有点复杂。

I need help in taking an annual total (for each of many initiatives) and breaking that down to each month using a simple division formula. I need to do this for each distinct combination of a few columns while copying down the columns that are broken from annual to each monthly total. The loop will apply the formula to two columns and loop through each distinct group in a vector. I tried to explain in an example below as it's somewhat complex.

我有什么：

| Init | Name | Date |Total Savings|Total Costs| 
|  A   | John | 2015 |    TotalD   |   TotalD  |
|  A   | Mike | 2015 |    TotalE   |   TotalE  |
|  A   |  Rob | 2015 |    TotalF   |   TotalF  |
|  B   | John | 2015 |    TotalG   |   TotalG  |
|  B   | Mike | 2015 |    TotalH   |   TotalH  |
......
| Init | Name | Date |Total Savings|Total Costs| 
|  A   | John | 2016 |    TotalI   |   TotalI  |
|  A   | Mike | 2016 |    TotalJ   |   TotalJ  |
|  A   |  Rob | 2016 |    TotalK   |   TotalK  |
|  B   | John | 2016 |    TotalL   |   TotalL  |
|  B   | Mike | 2016 |    TotalM   |   TotalM  |

我要循环第一行的功能，以总节省和总成本，除以12，其中Date = 2015和9，其中Date = 2016（YTD到9月），并为每个行创建一个单独的行。我基本上打破了一年的总数，并在一年的每一个月创造了一排。我需要帮助，运行该循环复制列Init，名称，直到Init，Name组合没有区别。另外，请注意，根据年份划分的公式也会有所不同。我想我可以分开2015年和2016年的数据集，并使用两个不同的功能并合并，如果这将更容易。下面应该是输出：

I'm going to loop a function for the first row to take the "Total Savings" and "Total Costs" and divide by 12 where Date = 2015 and 9 where Date = 2016 (YTD to Sept) and create an individual row for each. I'm essentially breaking out an annual total in a row and creating a row for each month of the year. I need help in running that loop to copy also columns "Init", "Name", until "Init", "Name" combination are not distinct. Also, note the formula for the division based on the year will be different as well. I suppose I could separate the datasets for 2015 and 2016 and use two different functions and merge if that would be easier. Below should be the output:

| Init | Name | Date       |Monthly Savings|Monthly Costs| 
|  A   | John | 01-01-2015 |   TotalD/12*  |   MonthD    |
|  A   | John | 02-01-2015 |    MonthD     |   MonthD    |
|  A   | John | 03-01-2015 |    MonthD     |   MonthD    |
...
|  A   | Mike | 01-01-2016 |    TotalE/9*  |   TotalE    |
|  A   | Mike | 02-01-2016 |    TotalE     |   TotalE    |
|  A   | Mike | 03-01-2016 |    TotalE     |   TotalE    |
...
|  B   | John | 01-01-2015 |   TotalG/12*  |   MonthD    |
|  B   | John | 02-01-2015 |    MonthG     |   MonthD    |
|  B   | John | 03-01-2015 |    MonthG     |   MonthD    |

TotalD / 12 * = MonthD - 这是2015年的公式

TotalE / 9 * =月份 - 这是2016年的公式

TotalD/12* = MonthD - this is the formula for 2015
TotalE/9* = MonthE - this is the formula for 2016

任何帮助将不胜感激...

Any help would be appreciated...

推荐答案

作为一个开始，这里有一些可重现的数据，列中描述了

As a start, here are some reproducible data, with the columns described:

myData <-
  data.frame(
    Init = rep(LETTERS[1:3], each = 4)
    , Name = rep(c("John", "Mike"), each = 2)
    , Date = 2015:2016
    , Savings = (1:12)*1200
    , Cost = (1:12)*2400
  )

接下来，将除数设置为每年使用：

Next, set the divisor to be used for each year:

toDivide <-
  c("2015" = 12, "2016" = 9)

然后，我正在使用 magrittr pipe，因为我将数据分成单行，然后循环使用 lapply 将每行扩展到适当数量的行（9或12），节省和成本除以月数。最后， dplyr 的 bind_rows 将行重合在一起。

Then, I am using the magrittr pipe as I split the data up into single rows, then looping through them with lapply to expand each row into the appropriate number of rows (9 or 12) with the savings and costs divided by the number of months. Finally, dplyr's bind_rows stitches the rows back together.

myData %>%
  split(1:nrow(.)) %>%
  lapply(function(x){
    temp <- data.frame(
      Init = x$Init
      , Name = x$Name
      , Date = as.Date(paste(x$Date
                           , formatC(1:toDivide[as.character(x$Date)]
                                     , width = 2, flag = "0")
                           , "01"
                           , sep = "-"))
      , Savings = x$Savings / toDivide[as.character(x$Date)]
      , Cost = x$Cost / toDivide[as.character(x$Date)]
    )
  }) %>%
  bind_rows()

头

    Init Name       Date  Savings      Cost
1      A John 2015-01-01 100.0000  200.0000
2      A John 2015-02-01 100.0000  200.0000
3      A John 2015-03-01 100.0000  200.0000
4      A John 2015-04-01 100.0000  200.0000
5      A John 2015-05-01 100.0000  200.0000
6      A John 2015-06-01 100.0000  200.0000

与每个展开的行相似的条目。

with similar entries for each expanded row.

这篇关于当功能在R中将单个行分解为多个时，复制列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！