在R中创建一个动态函数 [英] Create a dynamic function in R
问题描述
您在这些栏目中会看到代码，例如DN1，DG1，DN2等。这些代码具有与它们相关的值，但这些值与本练习无关，即它们可能是任何事情。
对于Q1 - Q4，值是使用公式计算的，例如产品A在Q1中的预计价格= DN1 + 1。因此，如果DN1 = 3，则预测价格为3 + 1 = 4.遵循类似的逻辑，并且所有产品的Q1-Q4中的每个值都基于公式计算。
如何编辑我的函数，使其更具动态性/高效性，如果公式发生变化，我可以轻松地进行更改，例如如果第二季度产品D的预计价格公式发生变化，我应该可以轻松地进行修改，而无需去修改代码。
我也许希望使用一个看起来像这样的excel / csv：
单元格公式周期
值[1] = DN1 + 1 Q1
值[2] = DK1 Q2
值[3] = DK1 Q3
值[4] = DK1 Q4
值[5] = DN2 + 3例如从DN2更改）
然后，我需要做的是，在公式中进行更改在excel文件中输入合适的单元格，并通过函数传递csv / excel来获得输出。
我的数据框可重复使用的代码：
Q1 <-c（= DN1 + 1，= DN2 + 3，= DN3 * 4，= DN4-1， =（DN1 + DN2 + DN3 + DN4）
Q2 <-c（= DK1，= DK2，= DN3 + DK3，= DN4 + DL3- DJ2-DK3）/ S20 + S22）+ S223 + Y446 / ZQ282-W223），= DG1 + DG2 + DG3 + DG4）$ （DK1），DK2（DK1），DK3（DK1），DK3（-Q3） -DK3）/ S20 + S22）+ S223 + Y446 / ZQ282-W223），= DG1 + DG2 + DG3 + DG4）
Q4 <-c（= DK1，= DK2，DG3 （-1qtr）+ DK3，= DG4（-1qtr）+ DL3-DM1 *（DM2 +（（-DJ7-DK3）/ S20 + S22）+ S223 + Y446 / ZQ282-W223），DG1 + DG2 + DG3 + DG4）
D1 < - 数据帧（Q1，Q2，Q3，Q4）
D1 $ Product< -c（A，B，C D，Total）
D1 $ Original_Price <-c（DN1，DN2，DN3，DN4，DN7）
D1 $ New_Price< -c（ DG1，DG2，DG3，DG4，DG7）
D1 <-D1 [，c（5,6,7,1,2,3,4）]
函数的可重复代码：目前，公式都是硬编码的。该函数将数据帧作为输入/参数，其中包含两列1.）代码列DN1，DN2等。2）与代码相关的值列22（DN1的值），48（DN2的值）等等。
function_1 <-function（dta）$ b （A，A，A，A，B，B，B，B，C， C，C，C，D，D，D，D，Total，Total，Total，Total， - c（DN1，DN1，DN1，DN1，DN2，DN2，DN2，DN2，DN3，DN3，DN3，DN3 ，DN4，DN4，DN4，DN4，DN7，DN7，DN7，DN7）
New_Price <-c（DG1，DG1 DG1， DG1， DG2， DG2， DG2， DG2， DG3， DG3， DG3， DG3， DG4， DG4， （Q1，Q2，Q3，Q4，DG4，DG4 Q1\" ， Q2， Q3， 第四季度， Q1， Q2， Q3， 第四季度， Q1， Q2， Q3， 第四季度， Q1 ，Q2，Q3，Q4）
值<-c（n / a，n / a，n / a，n / a，一， N / A， N / A， N / A， N / A， N / A， N / A， N / A， N / A ，n / a，n / a，n / a，n / a，n / a，n / a，n / a在c（1：nrow（dta）））{
assign（as.character（dta [i，1]），dta [i，2]）
print（dta [i，1]）
print（_）
print（dta [i，2]）
}
值[1]< - DN1 + 1
值[2] <-DK1
值[3] < - DK1
值[4] < - DK1
值[5] < - DN2
值[6] < - DK2
值[7] <-DK2
值[8] <-DK2
值[9] <-DN3
值[10] <-DN3 + DK3
值[11] < - as.numeric（值[10]）+ DK3
值[12] < - as.numeric（值[11]）+ DK3
值[13]< （DM2 +（（-DJ7-DK3）/ S20 + S22）+ S223 + Y446 / ZQ282-W223）
Value [15] -DN4
Value [14] <-DN4 + DL3- （数值[14]）+ DL3-DM1 *（DM2 +（（0-DX7-DK3）/ S20 + S22）+ S223 + Y446 / ZQ282 -W223）
Value [16] （数值[15]）+ DL3-DM1 *（DM2 +（（0-DX7-DK3）/ S20 + S22）+ S223 + Y446 / ZQ282 -W223）
值[17] < - DN1 + DN2 + DN3 + DN4
< 18]< - as.numeric（值[2]）+ as.numeric（值[6]）+ as.numeric（值[10]） + as.numeric（值[14]）
值[19]< - as.numeric（值[3]）+ as.numeric（值[7]）+ as.numeric（值[11]） + as.numeric（值[15]）
值[20] < - as.numeric（值[4]）+ as.numeric（值[8] ）+ as.numeric（值[12]）+ as.numeric（值[16]）
输出< - data.frame（Product，Original_Price，New_Price，Period，Value）
}
更新：
如何修改代码以将硬编码引用替换为上一季度值例如 Value [11]< - as.numeric（Value [10]）+ DK3
假设值将以后缀提供，但数据正在按季度接收，意味着相同的单元代码将重复4次（每个季度一次），即
代码值期间
DN1 200 Q1
DN1 300 Q2
DN1 400 Q3
DN1 500 Q4
解决方案目前还不完全清楚你在这里要做什么，但这是我的理解。你有一个表格，每个单元格的内容根据特定的列进行计算。您希望能够更新公式并重新计算表格中的值，而不用硬编码所有内容。
我的解决方案是编写一个函数，被更新并且每个单元格都是你想要使用的公式。公式必须写成字符。然后您可以使用
eval（parse（））来完成这项工作。 试试：＃创建数据和公式数据框
x< - 10
y< - 5
形式< -c（x + y，
x-y，
x / y，
x * y）
form_df< - as.data.frame（matrix（forms，nrow = 2，ncol = 2），stringsAsFactors = F）
＃V1 V2
＃1 x + yx / y
＃2 x - yx * y
df1 < - data.frame（col1 = c（15,6），col2 = c（50,50））
＃col1 col2
＃1 15 50
＃2 6 50
＃根据这个
dyndf < - 函数（x，y，df_old， form_df）{
df_new< - sapply（form_df，function（z1）sapply（z1，function（z2）eval（parse（text = z2））））
df_old [df_new！= df_old]< ; - df_new [df_new！= df_old]
df_old
}
dyndf（x，y，df1，form_df）
＃col1 col2
＃1 15 2
＃2 5 50
一些解释...函数并不复杂，但基本上我们取公式数据框架，我们评估每个单元格中的每个函数并创建一个新的数据框架。这个新的数据框看起来很可怕，因为它缺少列名和其他类型的信息。因此，我们只需检查哪些值已更新，然后将其覆盖。
通过重写函数，您可以使其适合您自己的问题。 b
$ b编辑：一个向量的简单例子，其中任何元素都可以依赖于前面的元素...
<$ cc（1 + 1，vec [1] + 2，vec [1] * vec [2]，vec <-1:5
形式< （vec）vec [i] < - eval（parse（text = form（））[vec [1]vec [5]）
[i]））
vec
＃[1] 2 4 8 4 5
I have a dataframe with 7 columns. The 3 columns Product, Original Price and New Price are self explanatory. Then, Q1-Q4 are average projected prices over 4 quarters.
What you will see in these columns are codes e.g. DN1, DG1, DN2 etc. These codes have values associated with them but the values are irrelevant for this exercise i.e. they could be anything.
For Q1 - Q4, each cell value is calculated using a formula e.g. projected price for Product A in Q1 = DN1+1. So if DN1= 3, then projected price is 3+1 = 4. Similar logic is followed and each value in Q1-Q4 for all products is calculated based on formulas.
How do I edit my function to make it more dynamic/efficient in the sense that should the formulas change, I am able to easily make that change e.g. if formula for projected price for Product D in Q2 changes, I should be able to make that change easily without having to go and amend the code.
I was hoping to perhaps use an excel/csv that will look something like this:
Cell Formula Period Value[1] =DN1+1 Q1 Value[2] =DK1 Q2 Value[3] =DK1 Q3 Value[4] =DK1 Q4 Value[5] =DN2+3 (this value e.g. changed from DN2)All i would then have to do is, make the change in the formula in the appropriate cell in the excel file and pass the csv/excel through the function to get the output.
Reproducible code for my dataframe:
Q1<-c("=DN1+1","=DN2+3","=DN3*4","=DN4-1","=DN1 + DN2 + DN3 + DN4") Q2<-c("=DK1", "=DK2","=DN3+DK3","=DN4+DL3- DM1 * ( DM2 + ((-DJ7-DK3) / S20+ S22) + S223 + Y446 / ZQ282 - W223)","=DG1 + DG2 + DG3 + DG4") Q3<-c("=DK1","=DK2","=DG3 (-1 qtr) +DK3","=DG4(-1 qtr)+DL3- DM1 * ( DM2 + ((-DJ7-DK3) / S20+ S22) + S223 + Y446 / ZQ282 - W223)","=DG1 + DG2 + DG3 + DG4") Q4<-c("=DK1","=DK2","DG3 (-1 qtr) +DK3","=DG4(-1 qtr)+DL3- DM1 * ( DM2 + ((-DJ7-DK3) / S20+ S22) + S223 + Y446 / ZQ282 - W223)","DG1 + DG2 + DG3 + DG4") D1<-data.frame(Q1,Q2,Q3,Q4) D1$Product<-c("A","B","C","D","Total") D1$Original_Price<-c("DN1","DN2","DN3","DN4","DN7") D1$New_Price<-c("DG1","DG2","DG3","DG4","DG7") D1<-D1[,c(5,6,7,1,2,3,4)]Reproducible code for function: Currently, the formulas are all hardcoded. This function takes a dataframe as an input/argument which contains two columns 1.) Column for codes e.g. DN1, DN2 etc. 2.) Column for values associated with codes e.g. 22 (value for DN1), 48 (value for DN2)..etc.
function_1<-function(dta) { Product <- c("A","A","A","A","B","B","B","B","C","C","C","C","D","D","D","D","Total","Total","Total","Total") Original_Price <- c("DN1","DN1","DN1","DN1","DN2","DN2","DN2","DN2","DN3","DN3","DN3","DN3","DN4","DN4","DN4","DN4","DN7","DN7","DN7","DN7") New_Price <- c("DG1","DG1","DG1","DG1","DG2","DG2","DG2","DG2","DG3","DG3","DG3","DG3","DG4","DG4","DG4","DG4","DG7","DG7","DG7","DG7") Period <- c("Q1","Q2","Q3","Q4","Q1","Q2","Q3","Q4","Q1","Q2","Q3","Q4","Q1","Q2","Q3","Q4","Q1","Q2","Q3","Q4") Value <- c("n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a") for (i in c (1:nrow(dta))) { assign(as.character(dta[i,1]), dta[i,2]) print(dta[i,1]) print ("_") print(dta[i,2]) } Value[1] <- DN1+1 Value[2] <- DK1 Value[3] <- DK1 Value[4] <- DK1 Value[5] <- DN2 Value[6] <- DK2 Value[7] <- DK2 Value[8] <- DK2 Value[9] <- DN3 Value[10] <- DN3 + DK3 Value[11] <- as.numeric(Value[10]) + DK3 Value[12] <- as.numeric(Value[11]) + DK3 Value[13] <- DN4 Value[14] <- DN4+DL3- DM1 * ( DM2 + ((-DJ7-DK3) / S20+ S22) + S223 + Y446 / ZQ282 - W223) Value[15] <- as.numeric(Value[14]) + DL3 - DM1 * ( DM2 + ((0 -DJ7-DK3) / S20 + S22) + S223 + Y446 / ZQ282 - W223) Value[16] <- as.numeric(Value[15]) + DL3 - DM1 * ( DM2 + ((0 -DJ7-DK3) / S20 + S22) + S223 + Y446 / ZQ282 - W223) Value[17] <- DN1 + DN2 + DN3 + DN4 Value[18] <- as.numeric(Value[2]) + as.numeric(Value[6]) + as.numeric(Value[10]) + as.numeric(Value[14]) Value[19] <- as.numeric(Value[3]) + as.numeric(Value[7]) + as.numeric(Value[11]) + as.numeric(Value[15]) Value[20] <- as.numeric(Value[4]) + as.numeric(Value[8]) + as.numeric(Value[12]) + as.numeric(Value[16]) output <- data.frame(Product,Original_Price,New_Price,Period,Value) }UPDATE: How do I amend the code to replace the hard-coded reference to previous quarter values e.g.
Value[11] <- as.numeric(Value[10]) + DK3I coded the function on the assumption the values will be provided with a suffix, but the data is being received by quarter meaning the same cell code will repeat 4 times (once for each quarter) i.e.
Code Value Period DN1 200 Q1 DN1 300 Q2 DN1 400 Q3 DN1 500 Q4So my function will fail since it won't pick up correct values.
解决方案It's not entirely clear what you are trying to do here, but this is how I understood it.. You have a table where each cell's content is calculated according to a specific column. You want to be able to update the formulas and recalculate the values within your table, without hardcoding everything.
My solution would be to write a function where you supply a table to be updated AND a table where each cell is the formula that you want to use. The formulas have to be written down as characters. You then can use
eval(parse())to make this work.Let's try:
# Create data and a formula data frame x <- 10 y <- 5 forms <- c("x + y", "x - y", "x / y", "x * y") form_df <- as.data.frame(matrix(forms, nrow = 2, ncol = 2), stringsAsFactors = F) # V1 V2 # 1 x + y x / y # 2 x - y x * y df1 <- data.frame(col1 = c(15,6), col2 = c(50,50)) # col1 col2 # 1 15 50 # 2 6 50 # Create a function that creates a data frame from this dyndf <- function(x, y, df_old, form_df) { df_new <- sapply(form_df,function(z1) sapply(z1, function(z2) eval(parse(text=z2)))) df_old[df_new != df_old] <- df_new[df_new != df_old] df_old } dyndf(x,y,df1,form_df) # col1 col2 # 1 15 2 # 2 5 50Some explanations... The function is not that complicated, but basically we take the formula data frame, we evaluate every function in each cell and create a new data frame. This new data frame looks horrible because it lacks column names and other kinds of information. So, we simply check which values are updated, and then overwrite those.
By rewriting the function, you can probably make it fit your own problem.
Edit: A quick example of a vector where any element may rely on the previous elements...
vec <- 1:5 forms <- c("1 + 1", "vec[1] + 2", "vec[1] * vec[2]", "vec[3] / vec[1]", "vec[5]") for (i in 1:length(vec)) vec[i] <- eval(parse(text=forms[i])) vec # [1] 2 4 8 4 5
这篇关于在R中创建一个动态函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
