英文:
How to Divide a Row with Another Row by Pipe Function in R
问题
我正在尝试通过将两行的值相除来创建一个名为**'DB Q'**的新列，但我无法做到。我以为可以使用rowwise()函数来实现，但它不起作用，我不确定是因为在执行时同时保留其他列，还是语法只是简单错误。
附上我编写的代码，理想情况下，我想创建更多的行，所以最好通过管道传递。有人能帮助我吗？
library(readxl)library(dplyr)Pd_Clean <- Pd_Clean %>%rowwise(SKU) %>%mutate(`DB Q` = `BH Q` / SI)
我正在处理的数据框如下所示：
structure(list(SKU = c("XYZ_20645", "XYZ_20645"), `Key Figures` = c("BH Q","SI"), `Jan-19` = c(NA, 1), `Feb-19` = c(NA, 1), `Mar-19` = c(NA,1), `Apr-19` = c(NA, 1), `May-19` = c(NA, 1), `Jun-19` = c(NA,1), `Jul-19` = c(NA, 1), `Aug-19` = c(NA, 1), `Sep-19` = c(NA,1), `Oct-19` = c(NA, 1), `Nov-19` = c(NA, 1), `Dec-19` = c(13,1), `Jan-20` = c(NA, 1), `Feb-20` = c(14, 1), `Mar-20` = c(NA,1), `Apr-20` = c(NA, 1), `May-20` = c(NA, 1), `Jun-20` = c(NA,1), `Jul-20` = c(NA, 1), `Aug-20` = c(NA, 1.419196), `Sep-20` = c(NA,0.853283), `Oct-20` = c(NA, 0.799518), `Nov-20` = c(24, 0.709845), `Dec-20` = c(NA, 0.891075), `Jan-21` = c(NA, 1.113528), `Feb-21` = c(NA,0.593329), `Mar-21` = c(14, 0.914788), `Apr-21` = c(NA, 1.111516), `May-21` = c(NA, 1.287797), `Jun-21` = c(110, 1.254029), `Jul-21` = c(NA,1.052091), `Aug-21` = c(35, 1.491094), `Sep-21` = c(NA, 0.893486), `Oct-21` = c(59, 0.831871), `Nov-21` = c(79, 0.738738), `Dec-21` = c(316,0.910051)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA,-2L))
预期输出如下：在黄色突出显示的行是需要创建的新行，蓝色突出显示的单元格是数学公式。
英文:
I am trying to create a new column named 'DB Q' by dividing the value of the two rows and I am not able to do it. I thought I could do it with rowwise() function but it is not working, I am not sure if I am complicating this by keeping the other column as well while performing or the syntax is just simply wrong.
Attached is the code which I wrote, ideally I want to create a lot more rows so passing it through pipe is preferred. Can someone help me out.
library(readxl)library(dplyr)Pd_Clean <- Pd_Clean %>%rowwise(SKU) %>%mutate(`DB Q`= `BH Q`/SI)
The data frame I am working with is given below:
structure(list(SKU = c("XYZ_20645", "XYZ_20645"), `Key Figures` = c("BH Q","SI"), `Jan-19` = c(NA, 1), `Feb-19` = c(NA, 1), `Mar-19` = c(NA,1), `Apr-19` = c(NA, 1), `May-19` = c(NA, 1), `Jun-19` = c(NA,1), `Jul-19` = c(NA, 1), `Aug-19` = c(NA, 1), `Sep-19` = c(NA,1), `Oct-19` = c(NA, 1), `Nov-19` = c(NA, 1), `Dec-19` = c(13,1), `Jan-20` = c(NA, 1), `Feb-20` = c(14, 1), `Mar-20` = c(NA,1), `Apr-20` = c(NA, 1), `May-20` = c(NA, 1), `Jun-20` = c(NA,1), `Jul-20` = c(NA, 1), `Aug-20` = c(NA, 1.419196), `Sep-20` = c(NA,0.853283), `Oct-20` = c(NA, 0.799518), `Nov-20` = c(24, 0.709845), `Dec-20` = c(NA, 0.891075), `Jan-21` = c(NA, 1.113528), `Feb-21` = c(NA,0.593329), `Mar-21` = c(14, 0.914788), `Apr-21` = c(NA, 1.111516), `May-21` = c(NA, 1.287797), `Jun-21` = c(110, 1.254029), `Jul-21` = c(NA,1.052091), `Aug-21` = c(35, 1.491094), `Sep-21` = c(NA, 0.893486), `Oct-21` = c(59, 0.831871), `Nov-21` = c(79, 0.738738), `Dec-21` = c(316,0.910051)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA,-2L))
The Expected output is given below: The row highlighted in yellow is the new row which needs to be created and the cell highlighted in blue is the mathematical formulae.
答案1
得分: 1
以下是翻译好的代码部分：
第一段代码：
Pd_Clean[3, -c(1:2)] <- lapply(Pd_Clean[-c(1:2)],function(x) ifelse(is.na(x[1]), 0,round(x[1] / x[2])))Pd_Clean[3, 1:2] <- c(Pd_Clean[1,1], "DB Q")
第二段代码：
Pd_Clean[3, -c(1:2)] <- ifelse(is.na(Pd_Clean[3, -c(1:2)]), 0,Pd_Clean[1, -c(1:2)] / Pd_Clean[2, -c(1:2)])
输出部分：
Pd_Clean[22:38] # 图中的列# `Aug-20` `Sep-20` `Oct-20` `Nov-20` `Dec-20` `Jan-21` `Feb-21` `Mar-21` `Apr-21` `May-21` `Jun-21` `Jul-21` `Aug-21` `Sep-21` `Oct-21` `Nov-21` `Dec-21`# <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl># 1 NA NA NA 24 NA NA NA 14 NA NA 110 NA 35 NA 59 79 316# 2 1.42 0.853 0.800 0.710 0.891 1.11 0.593 0.915 1.11 1.29 1.25 1.05 1.49 0.893 0.832 0.739 0.910# 3 0 0 0 34 0 0 0 15 0 0 88 0 23 0 71 107 347
希望这有所帮助。如果有任何其他问题，请随时提出。
英文:
One base R approach is to use simple indexing to create a new column then lapply to perform the function across columns:
Pd_Clean[3, -c(1:2)] <- lapply(Pd_Clean[-c(1:2)],function(x) ifelse(is.na(x[1]), 0,round(x[1] / x[2])))Pd_Clean[3, 1:2] <- c(Pd_Clean[1,1], "DB Q")
You could also do the first step without lapply(), but not as clean in my option:
Pd_Clean[3, -c(1:2)] <- ifelse(is.na(Pd_Clean[3, -c(1:2)]), 0,Pd_Clean[1, -c(1:2)] / Pd_Clean[2, -c(1:2)])
Output:
Pd_Clean[22:38] # columns in image# `Aug-20` `Sep-20` `Oct-20` `Nov-20` `Dec-20` `Jan-21` `Feb-21` `Mar-21` `Apr-21` `May-21` `Jun-21` `Jul-21` `Aug-21` `Sep-21` `Oct-21` `Nov-21` `Dec-21`# <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl># 1 NA NA NA 24 NA NA NA 14 NA NA 110 NA 35 NA 59 79 316# 2 1.42 0.853 0.800 0.710 0.891 1.11 0.593 0.915 1.11 1.29 1.25 1.05 1.49 0.893 0.832 0.739 0.910# 3 0 0 0 34 0 0 0 15 0 0 88 0 23 0 71 107 347
It's a little unclear what you mean by "I want to create a lot more rows" so if you provide more robust example data I am happy to update.
答案2
得分: -2
我看不到你附上的图片。这是你想要的输出吗？我写了一段相当丑陋的代码，我相信有更好的方法，也许可以使用 c_across：
Pd_Clean |>t() |>data.frame() |>mutate(across(everything(), as.numeric)) |>mutate(answer = X1/X2) |>t() |>data.frame() |>suppressWarnings()
英文:
I can't see the image you attached. Is this the output you wanted? I wrote a pretty ugly code and I'm sure there is a better way, maybe with c_across:
Pd_Clean |>t() |>data.frame() |>mutate(across(everything(),as.numeric)) |>mutate(answer = X1/X2) |>t() |>data.frame() |>suppressWarnings()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论