struggling to get a mutate from 5 columns

You seem to be listing several columns from the existing data frame for each of the new columns. Do you mean to add the values of the various columns or to combine them in some other way? To add them, you would write

Totlumbergrade_outturn %>%
mutate(App_Lumber = Pr_App+Unpr1_App+Unpr2_App +Unpr3_App+Unpr4_App,
Str_Lumber = Pr_Str+Unpr1_Str+Unpr2_Str+Unpr3_Str+Unpr4_Str,
Ind_Lumber = Pr_Ind+Unpr1_Ind+Unpr2_Ind+Unpr3_Ind+Unpr4_Ind,
Out_Lumber = Pr_Out+Unpr1_Out+Unpr2_Out+Unpr3_Out+Unpr4_Out) -> NZlumber_grade
1 Like

These columns have values so I would like to get totals for all the five columns. I am quite new to R

@FJCC that is how I have written my code. However, the result only captures the first column and the other four are ignored. I hope this clarifies my problem

You marked the your question as answered ('Solved') -- did you mean to do that, or did you still have a question? From your follow-up posts, I wasn't sure.

@dromano sorry I must have done by mistake. I am still waiting for help. Thanks for reaching out

Doesn't the response by @FJCC answer it?

Otherwise, can you post a reproducible example so that we can see what data you are working with?

@williaml. I will try that. The code suggested is exactly what I have and it is not giving me the result I am expecting

Here is a working example.


Totlumbergrade_outturn <- data.frame(Pr_App = runif(5, 1,10),
                                     Unpr1_App = runif(5, 1,10),
                                     Unpr2_App = runif(5, 1,10),
                                     Unpr3_App = runif(5, 1,10),
                                     Unpr4_App = runif(5, 1,10),
                                     Pr_Str = runif(5, 1,10),
                                     Unpr1_Str = runif(5, 1,10),
                                     Unpr2_Str = runif(5, 1,10),
                                     Unpr3_Str = runif(5, 1,10),
                                     Unpr4_Str = runif(5, 1,10))
Totlumbergrade_outturn[, 1:5]
#>     Pr_App Unpr1_App Unpr2_App Unpr3_App Unpr4_App
#> 1 4.699339  6.336649  3.893776  9.493594  4.347624
#> 2 8.838073  5.188725  3.850541  6.913105  2.068490
#> 3 8.747775  7.378992  7.693174  8.625104  2.795483
#> 4 1.552568  3.137772  5.105933  5.822015  6.790961
#> 5 4.273220  5.783822  8.549490  4.142587  3.637941
Totlumbergrade_outturn[, 6:10]
#>     Pr_Str Unpr1_Str Unpr2_Str Unpr3_Str Unpr4_Str
#> 1 6.046605  6.906415  5.117969  3.075257  2.246494
#> 2 9.943055  6.373416  8.480787  6.277302  5.000038
#> 3 6.541763  5.394248  1.803512  2.132697  5.512707
#> 4 6.483513  3.704453  8.083777  2.776722  3.572820
#> 5 1.967593  7.680675  2.647683  4.984246  3.197511
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
Totlumbergrade_outturn %>%
  mutate(App_Lumber = Pr_App+Unpr1_App+Unpr2_App +Unpr3_App+Unpr4_App,
         Str_Lumber = Pr_Str+Unpr1_Str+Unpr2_Str+Unpr3_Str+Unpr4_Str) -> NZ_lumberGrade
NZ_lumberGrade[, c(1:5, 11)]
#>     Pr_App Unpr1_App Unpr2_App Unpr3_App Unpr4_App App_Lumber
#> 1 4.699339  6.336649  3.893776  9.493594  4.347624   28.77098
#> 2 8.838073  5.188725  3.850541  6.913105  2.068490   26.85893
#> 3 8.747775  7.378992  7.693174  8.625104  2.795483   35.24053
#> 4 1.552568  3.137772  5.105933  5.822015  6.790961   22.40925
#> 5 4.273220  5.783822  8.549490  4.142587  3.637941   26.38706
NZ_lumberGrade[, c(6:10,12)]
#>     Pr_Str Unpr1_Str Unpr2_Str Unpr3_Str Unpr4_Str Str_Lumber
#> 1 6.046605  6.906415  5.117969  3.075257  2.246494   23.39274
#> 2 9.943055  6.373416  8.480787  6.277302  5.000038   36.07460
#> 3 6.541763  5.394248  1.803512  2.132697  5.512707   21.38493
#> 4 6.483513  3.704453  8.083777  2.776722  3.572820   24.62128
#> 5 1.967593  7.680675  2.647683  4.984246  3.197511   20.47771

Created on 2020-03-18 by the reprex package (v0.3.0)

No, in your opening post, you use , whereas the response from @fjcc uses +.

For example:

mutate(App_Lumber = Pr_App,Unpr1_App,Unpr2_App,Unpr3_App,Unpr4_App)

vs

mutate(App_Lumber = Pr_App+Unpr1_App+Unpr2_App +Unpr3_App+Unpr4_App)

or to be clearer:

mutate(App_Lumber = Pr_App + Unpr1_App + Unpr2_App + Unpr3_App + Unpr4_App)

The code @FJCC suggested is actually different from the code you posted -- did you try copying and pasting @FJCC's code into a new R file and running it there? Also, it may be that your data is the source of trouble, so it would be good if you could post here, like this: First run the command

dput(head(Totlumbergrade_outturn,20))

which will produce output in your console (usually the lower left pane of your RStudio window), then copy and paste the output here, like this:

```
<--- paste output here, and include the triple backticks you see here (```)
```

Ok. I am now trying to see what I will get and will revert shortly. Thank you very much

structure(list(Pr_App = c(22968, 144274.8318, NA, 32094.3, 29844.48, 
17372.16, 2328.156, 13351.68, 20248.8, 24464.4, NA, 20768, NA, 
NA, NA, NA, NA, NA, NA, NA), Unpr1_App = c(NA, NA, NA, 397.32, 
NA, 449.28, NA, NA, NA, NA, NA, 3206.5, NA, NA, NA, NA, NA, NA, 
NA, NA), Unpr2_App = c(2772, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Unpr3_App = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Unpr4_App = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Pr_Str = c(45936, 
NA, NA, 4584.9, NA, 5790.72, 1552.104, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA), Unpr1_Str = c(270270, NA, NA, 23044.56, 
NA, 4492.8, 12222.819, NA, NA, NA, NA, NA, 19350, NA, NA, NA, 
NA, NA, NA, NA), Unpr2_Str = c(22176, NA, NA, NA, NA, NA, 4383.83152, 
NA, NA, NA, 3846.752, NA, NA, NA, NA, NA, NA, NA, NA, NA), Unpr3_Str = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Unpr4_Str = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Pr_Ind = c(34452, 
32100.2682, NA, 4584.9, 7461.12, 5211.648, 1293.42, 4450.56, 
10903.2, 8595.6, NA, 5192, NA, NA, NA, NA, NA, NA, NA, NA), Unpr1_Ind = c(103950, 
NA, NA, 3973.2, NA, 1946.88, 4074.273, NA, NA, NA, NA, 2623.5, 
6450, NA, NA, NA, NA, NA, NA, NA), Unpr2_Ind = c(11088, NA, NA, 
NA, NA, NA, 962.30448, NA, NA, NA, 769.3504, NA, NA, NA, NA, 
NA, NA, NA, NA, NA), Unpr3_Ind = c(NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_), Unpr4_Ind = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_), Pr_Out = c(11484, NA, 
NA, 4584.9, NA, 579.072, NA, 4450.56, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), Unpr1_Out = c(41580, NA, NA, 12316.92, 
NA, 599.04, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA), Unpr2_Out = c(19404, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
3077.4016, NA, NA, NA, 62400, NA, NA, NA, NA, NA), Unpr3_Out = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Unpr4_Out = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -20L))

using, gives a sum that only picks up the first column for Pr. using + gives NA all through

Thanks for this suggestion. I however have around 180 variables. I have been working on various mutations and I still have more to do. I cannot identify the actual position for these variables. Let me try and see if this code with work after I first figure out their position

Here's something to do before you use @FJCC's code, but after seeing the structure of your table, there might be other ways of finding the totals you're looking for:

library(tidyverse)
Totlumbergrade_outturn <- 
structure(list(Pr_App = c(22968, 144274.8318, NA, 32094.3, 29844.48,
                          17372.16, 2328.156, 13351.68, 20248.8, 24464.4, NA, 20768, NA,
                          NA, NA, NA, NA, NA, NA, NA), Unpr1_App = c(NA, NA, NA, 397.32,
                                                                     NA, 449.28, NA, NA, NA, NA, NA, 3206.5, NA, NA, NA, NA, NA, NA,
                                                                     NA, NA), Unpr2_App = c(2772, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                            NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), Unpr3_App = c(NA_real_,
                                                                                                                                                       NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                       NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                       NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Unpr4_App = c(NA_real_,
                                                                                                                                                                                                                        NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                        NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                        NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Pr_Str = c(45936,
                                                                                                                                                                                                                                                                                      NA, NA, 4584.9, NA, 5790.72, 1552.104, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                      NA, NA, NA, NA, NA, NA, NA), Unpr1_Str = c(270270, NA, NA, 23044.56,
                                                                                                                                                                                                                                                                                                                                 NA, 4492.8, 12222.819, NA, NA, NA, NA, NA, 19350, NA, NA, NA,
                                                                                                                                                                                                                                                                                                                                 NA, NA, NA, NA), Unpr2_Str = c(22176, NA, NA, NA, NA, NA, 4383.83152,
                                                                                                                                                                                                                                                                                                                                                                NA, NA, NA, 3846.752, NA, NA, NA, NA, NA, NA, NA, NA, NA), Unpr3_Str = c(NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                         NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                         NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                         NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Unpr4_Str = c(NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Pr_Ind = c(34452,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        32100.2682, NA, 4584.9, 7461.12, 5211.648, 1293.42, 4450.56,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        10903.2, 8595.6, NA, 5192, NA, NA, NA, NA, NA, NA, NA, NA), Unpr1_Ind = c(103950,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  NA, NA, 3973.2, NA, 1946.88, 4074.273, NA, NA, NA, NA, 2623.5,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  6450, NA, NA, NA, NA, NA, NA, NA), Unpr2_Ind = c(11088, NA, NA,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   NA, NA, NA, 962.30448, NA, NA, NA, 769.3504, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   NA, NA, NA, NA, NA), Unpr3_Ind = c(NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      NA_real_, NA_real_, NA_real_), Unpr4_Ind = c(NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   NA_real_, NA_real_, NA_real_, NA_real_), Pr_Out = c(11484, NA,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 NA, 4584.9, NA, 579.072, NA, 4450.56, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       NA, NA, NA, NA, NA, NA), Unpr1_Out = c(41580, NA, NA, 12316.92,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              NA, 599.04, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              NA), Unpr2_Out = c(19404, NA, NA, NA, NA, NA, NA, NA, NA, NA,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 3077.4016, NA, NA, NA, 62400, NA, NA, NA, NA, NA), Unpr3_Out = c(NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), Unpr4_Out = c(NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   NA_real_, NA_real_, NA_real_, NA_real_, NA_real_)), class = c("tbl_df",
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 "tbl", "data.frame"), row.names = c(NA, -20L))
### end of structure command

# inspect
Totlumbergrade_outturn %>% head()
#> # A tibble: 6 x 20
#>    Pr_App Unpr1_App Unpr2_App Unpr3_App Unpr4_App Pr_Str Unpr1_Str
#>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>  <dbl>     <dbl>
#> 1  22968        NA       2772        NA        NA 45936    270270 
#> 2 144275.       NA         NA        NA        NA    NA        NA 
#> 3     NA        NA         NA        NA        NA    NA        NA 
#> 4  32094.      397.        NA        NA        NA  4585.    23045.
#> 5  29844.       NA         NA        NA        NA    NA        NA 
#> 6  17372.      449.        NA        NA        NA  5791.     4493.
#> # … with 13 more variables: Unpr2_Str <dbl>, Unpr3_Str <dbl>,
#> #   Unpr4_Str <dbl>, Pr_Ind <dbl>, Unpr1_Ind <dbl>, Unpr2_Ind <dbl>,
#> #   Unpr3_Ind <dbl>, Unpr4_Ind <dbl>, Pr_Out <dbl>, Unpr1_Out <dbl>,
#> #   Unpr2_Out <dbl>, Unpr3_Out <dbl>, Unpr4_Out <dbl>

# replace NA's with zeros (without saving), inspect
Totlumbergrade_outturn %>% 
  mutate_all(replace_na, 0) %>% head()
#> # A tibble: 6 x 20
#>   Pr_App Unpr1_App Unpr2_App Unpr3_App Unpr4_App Pr_Str Unpr1_Str Unpr2_Str
#>    <dbl>     <dbl>     <dbl>     <dbl>     <dbl>  <dbl>     <dbl>     <dbl>
#> 1 2.30e4        0       2772         0         0 45936    270270      22176
#> 2 1.44e5        0          0         0         0     0         0          0
#> 3 0.            0          0         0         0     0         0          0
#> 4 3.21e4      397.         0         0         0  4585.    23045.         0
#> 5 2.98e4        0          0         0         0     0         0          0
#> 6 1.74e4      449.         0         0         0  5791.     4493.         0
#> # … with 12 more variables: Unpr3_Str <dbl>, Unpr4_Str <dbl>,
#> #   Pr_Ind <dbl>, Unpr1_Ind <dbl>, Unpr2_Ind <dbl>, Unpr3_Ind <dbl>,
#> #   Unpr4_Ind <dbl>, Pr_Out <dbl>, Unpr1_Out <dbl>, Unpr2_Out <dbl>,
#> #   Unpr3_Out <dbl>, Unpr4_Out <dbl>

# save result
Totlumbergrade_outturn <- 
  Totlumbergrade_outturn %>% 
  mutate_all(replace_na, 0) 

Created on 2020-03-18 by the reprex package (v0.3.0)

If you start to reply to my post, and then click the speech bubble icon in the upper left of the editor, you can capture all the code, and then copy and paste into an R file to work with.

@Nekesa: What does the rows of your table correspond to?

Hi again @Nekesa: Here's the other approach I mentioned earlier, which is to store your data in what's sometimes called 'long' form:

# inspect long form
Totlumbergrade_outturn %>% 
  mutate(row = row_number()) %>% 
  pivot_longer(-row) %>% 
  filter(!is.na(value))

# find totals
Totlumbergrade_outturn %>% 
  mutate(row = row_number()) %>% 
  pivot_longer(-row) %>% 
  filter(!is.na(value)) %>% 
  separate(name, into = c('pr_unpr', 'type')) %>% 
  group_by(type) %>% 
  summarise(total = sum(value))

Thank you very much Dromano. I will try these and then share if I succeed. The rows are my participants (companies). They are 27

The totals from my previous code aren't sums by company, so you'd want to change the code for that --- were the NA's in your original table just combinations of companies and column types where there wasn't any data to enter?

@dromano I had to log off as I finished my day. NA I did not get any data as its not applicable. I am trying to solve that problem again