Loop to generate new columns and rows in a data frame

danut_horincas · April 6, 2024, 6:06pm

Hello ,
The firts two rows should look like:in fist row x1=30221627 in second x1=29621627, in fist row x2=30221627 in second row x2=29618432 and so on .

Thanks again

Buc | MT | Kg | Lung | medie | Pcs | CTT | util | V1 | V2 | V3 | V4 | V5 | V6 | V7 | V8 | V9 | V10 | W1 | W2 | W3 | W4 | W5 | W6 | W7 | W8 | W9 | W10 | x1 | x2 | x3 | x4 | x5 | x6 | x7 | x8 | x9 | x10 |

| - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
1 | 302 | 1884.48 | 2619 | 184 | 6.24 | 32 | 1.059782609 | 1778.176 | 216 | 216 | 184 | 213 | 184 | 184 | 184 | 213 | 184 | 216 | 27 | 27 | 32 | 28 | 29 | 32 | 32 | 28 | 32 | 28 | 1761264 | 1761264 | 1778176 | 1801128 | 1611472 | 1778176 | 1778176 | 1801128 | 1778176 | 1826496 |
2 | 296 | 1847.4 | 2568 | 213 | 6.24 | 28 | 1.046481592 | 1765.344 | 216 | 184 | 184 | 184 | 216 | 184 | 184 | 216 | 184 | 216 | 27 | 32 | 32 | 32 | 25 | 32 | 32 | 28 | 32 | 28 | 1726272 | 1742848 | 1742848 | 1742848 | 1598400 | 1742848 | 1742848 | 1790208 | 1742848 | 1790208 |

| dromano Early Adopter Group for Evaluating RStudio
April 6 |

| - |

danut_horincas:

```` r`

Looks like you're getting the hang of reprex! Just make sure to use the reprex output as-is, and not add anything, like the ```r that I quoted.

From your reprex, I can't tell if you have a complete solution, or if you still have a question — do you?

dromano · April 6, 2024, 6:15pm

Could you take this text out of the code block it's in? That is, just delete the pair of triple backticks (```) that is enclosing it? Only code should go in code blocks.

Thanks.

dromano · April 7, 2024, 12:55am

What does the (1) mean here? Since Buc is a column, I could imagine Buc(1) means Buc[1] (the value in the first row), but what about V(1) andW(1), since there are no columns called V and W?

danut_horincas · April 7, 2024, 7:29am

X[1]=B[1]*V[1]*W[1].Rigth and so on .thanks

danut_horincas · April 7, 2024, 9:09am

Sorry X[1]=Buc[1]*V[1]*W[1]

dromano · April 7, 2024, 10:45am

But X,V and W are not columns. In math-like notation, do you mean this?

Xi[j] = Buc[j]*Vi[j]*Wi[j]

for each row index j and column label i?

danut_horincas · April 7, 2024, 3:23pm

Yes .my mistake.I gave one example

dromano · April 7, 2024, 3:43pm

Thanks, Danut. And could you post the output from running sessionInfo() so we can see what tools you have access to?

danut_horincas · April 7, 2024, 4:20pm

R version 4.0.3 (2020-10-10)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] conflicted_1.2.0  shiny_1.6.0       data.table_1.13.6 reshape2_1.4.4   
 [5] formattable_0.2.1 lubridate_1.8.0   forcats_1.0.0     stringr_1.4.0    
 [9] dplyr_1.0.8       purrr_0.3.4       readr_2.1.2       tidyr_1.2.0      
[13] tibble_3.1.6      ggplot2_3.3.5     tidyverse_2.0.0  

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.8.3      ps_1.6.0          assertthat_0.2.1  digest_0.6.29    
 [5] utf8_1.1.4        mime_0.10         R6_2.5.1          plyr_1.8.6       
 [9] reprex_2.1.0      evaluate_0.23     highr_0.8         pillar_1.8.0     
[13] rlang_1.0.2       rstudioapi_0.15.0 miniUI_0.1.1.1    jquerylib_0.1.3  
[17] callr_3.7.0       R.utils_2.10.1    R.oo_1.24.0       rmarkdown_2.16   
[21] styler_1.9.1      htmlwidgets_1.5.3 munsell_0.5.0     compiler_4.0.3   
[25] httpuv_1.5.5      xfun_0.30         pkgconfig_2.0.3   clipr_0.7.1      
[29] pivottabler_1.5.0 htmltools_0.5.2   tidyselect_1.1.2  fansi_0.4.2      
[33] crayon_1.5.1      tzdb_0.3.0        withr_2.5.0       later_1.1.0.1    
[37] R.methodsS3_1.8.1 grid_4.0.3        jsonlite_1.8.0    xtable_1.8-4     
[41] gtable_0.3.1      lifecycle_1.0.1   DBI_1.2.2         magrittr_2.0.3   
[45] scales_1.2.0      cachem_1.0.4      cli_3.2.0         stringi_1.5.3    
[49] fs_1.5.2          promises_1.2.0.1  bslib_0.4.0       ellipsis_0.3.2   
[53] generics_0.1.3    vctrs_0.4.1       tools_4.0.3       R.cache_0.16.0   
[57] glue_1.6.2        hms_1.1.3         processx_3.5.3    fastmap_1.1.0    
[61] yaml_2.3.5        colorspace_2.0-0  memoise_2.0.1     knitr_1.40       
[65] sass_0.4.1

dromano · April 7, 2024, 5:14pm

Thanks, Danut; this is helpful to know.

dromano · April 7, 2024, 7:42pm

I think this might give you almost what you're looking for, with the tools you have now.

You start with a table containing the initial information that you want, like the one you shared as a pdf file (sto.pdf):

library(tidyverse)

tibble(a = 1:2, b = 3:4) -> sto

sto
#> # A tibble: 2 × 2
#>       a     b
#>   <int> <int>
#> 1     1     3
#> 2     2     4

then you replicate the table a certain number of times:

num_copies <- 3
sto %>%
  list() %>%
  rep(num_copies) -> sto_copied

sto_copied
#> [[1]]
#> # A tibble: 2 × 2
#>       a     b
#>   <int> <int>
#> 1     1     3
#> 2     2     4
#> 
#> [[2]]
#> # A tibble: 2 × 2
#>       a     b
#>   <int> <int>
#> 1     1     3
#> 2     2     4
#> 
#> [[3]]
#> # A tibble: 2 × 2
#>       a     b
#>   <int> <int>
#> 1     1     3
#> 2     2     4

then you mutate each copy in parallel; for example, by adding a random column called v:

sto_copied %>%
  map(
    ~ .x %>% mutate(v = sample(1:10, nrow(sto)))
  )
#> [[1]]
#> # A tibble: 2 × 3
#>       a     b     v
#>   <int> <int> <int>
#> 1     1     3     1
#> 2     2     4     6
#> 
#> [[2]]
#> # A tibble: 2 × 3
#>       a     b     v
#>   <int> <int> <int>
#> 1     1     3     9
#> 2     2     4     6
#> 
#> [[3]]
#> # A tibble: 2 × 3
#>       a     b     v
#>   <int> <int> <int>
#> 1     1     3     7
#> 2     2     4     5

^{Created on 2024-04-07 with reprex v2.0.2}

In the same way, each further step in your calculations can be applied to the separate tables in parallel.

danut_horincas · April 8, 2024, 7:46pm

I dont how to solve . I will give up and try to find solution from others sources .Thanks guy.

I am beginer in R.


Xi[j]=Buc[j]∗Vi[j]∗Wi[j] witout solution.
Excel are doing in  very simple way.

dromano · April 8, 2024, 7:59pm

A very good one!

Does the code I posted not work for you? It should work like the excel approach you mentioned. Or is it that you're not sure how to extend the code to work for your context?

If you can describe what you would do in excel to get your desired result, I'm sure we can figure out solution in R.

danut_horincas · April 8, 2024, 8:35pm

#Loop over columns and rows in a data frame
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
library(formattable)
library(reshape2)
library(data.table)
#> 
#> Attaching package: 'data.table'
#> The following objects are masked from 'package:reshape2':
#> 
#>     dcast, melt
#> The following objects are masked from 'package:dplyr':
#> 
#>     between, first, last

mac <- read.csv("D:/TEMP/sto1.csv", dec=",")
mac1<- mac %>% mutate(Lung=sample(x = c(216,213,184),
                                  size = 129, replace = TRUE))
head(mac1)
#>   Buc      MT   Kg Lung
#> 1 302 1884.48 2619  184
#> 2 296  1847.4 2568  216
#> 3 304 1869.96 2599  216
#> 4 240  1497.6 2082  184
#> 5 385  2194.5 3050  216
#> 6 302  1887.5 2624  184
mac1 <- mac1 %>% 
  mutate(Buc = as.numeric(Buc),MT=as.numeric(MT))
mac1<-mac1%>%mutate(medie=round(MT/Buc,2))
mac1<-mac1%>%mutate(Pcs=floor((medie*1000-200)/Lung))
#str(mac)
mac1<-mac1%>%mutate(CTT=MT/(Buc*Lung*Pcs/1000))

mac1<-mac1%>%mutate(util=(Buc*Lung*Pcs/1000))

head(mac1)
#>   Buc      MT   Kg Lung medie Pcs      CTT     util
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176
#> 2 296 1847.40 2568  216  6.24  27 1.070167 1726.272
#> 3 304 1869.96 2599  216  6.15  27 1.054730 1772.928
#> 4 240 1497.60 2082  184  6.24  32 1.059783 1413.120
#> 5 385 2194.50 3050  216  5.70  25 1.055556 2079.000
#> 6 302 1887.50 2624  184  6.25  32 1.061481 1778.176
#################


#########################
y=rep(sample(c(216,213,184),
             size = 129,
             replace=TRUE))
y
#>   [1] 184 213 213 184 184 184 213 213 213 216 213 213 213 184 184 213 216 213
#>  [19] 213 184 184 184 184 184 213 213 213 213 216 184 184 216 216 216 184 213
#>  [37] 216 216 213 216 213 184 213 184 216 216 216 216 184 216 184 213 184 184
#>  [55] 184 184 213 213 213 216 184 216 213 216 184 184 213 216 213 216 216 184
#>  [73] 184 184 213 213 216 213 216 216 213 213 213 213 216 184 213 216 216 213
#>  [91] 213 216 184 213 213 213 213 216 184 216 216 213 213 184 216 216 184 216
#> [109] 216 216 213 213 213 184 213 216 216 184 216 184 216 213 184 213 216 216
#> [127] 213 184 216

z= matrix(ncol = 10,nrow = 129)
for(year in 1:10) {
  # for each year sample the return 10000 times
  for(i in 1:129){
    z[i,year] = sample(y,1)
  }
}
colnames(z) <- paste0("V", 1:ncol(z))
head(z)
#>       V1  V2  V3  V4  V5  V6  V7  V8  V9 V10
#> [1,] 216 213 216 213 216 216 184 184 184 216
#> [2,] 216 184 213 184 184 184 216 213 216 213
#> [3,] 216 184 213 184 216 184 213 213 184 184
#> [4,] 216 213 184 216 216 184 216 216 213 216
#> [5,] 213 213 216 216 213 216 213 216 216 216
#> [6,] 213 213 213 184 213 216 213 213 184 213
data_new2 <- cbind(mac1, z)
head(data_new2)
#>   Buc      MT   Kg Lung medie Pcs      CTT     util  V1  V2  V3  V4  V5  V6  V7
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176 216 213 216 213 216 216 184
#> 2 296 1847.40 2568  216  6.24  27 1.070167 1726.272 216 184 213 184 184 184 216
#> 3 304 1869.96 2599  216  6.15  27 1.054730 1772.928 216 184 213 184 216 184 213
#> 4 240 1497.60 2082  184  6.24  32 1.059783 1413.120 216 213 184 216 216 184 216
#> 5 385 2194.50 3050  216  5.70  25 1.055556 2079.000 213 213 216 216 213 216 213
#> 6 302 1887.50 2624  184  6.25  32 1.061481 1778.176 213 213 213 184 213 216 213
#>    V8  V9 V10
#> 1 184 184 216
#> 2 213 216 213
#> 3 213 184 184
#> 4 216 213 216
#> 5 216 216 216
#> 6 213 184 213
#ADAUGAT
head(data_new2)
#>   Buc      MT   Kg Lung medie Pcs      CTT     util  V1  V2  V3  V4  V5  V6  V7
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176 216 213 216 213 216 216 184
#> 2 296 1847.40 2568  216  6.24  27 1.070167 1726.272 216 184 213 184 184 184 216
#> 3 304 1869.96 2599  216  6.15  27 1.054730 1772.928 216 184 213 184 216 184 213
#> 4 240 1497.60 2082  184  6.24  32 1.059783 1413.120 216 213 184 216 216 184 216
#> 5 385 2194.50 3050  216  5.70  25 1.055556 2079.000 213 213 216 216 213 216 213
#> 6 302 1887.50 2624  184  6.25  32 1.061481 1778.176 213 213 213 184 213 216 213
#>    V8  V9 V10
#> 1 184 184 216
#> 2 213 216 213
#> 3 213 184 184
#> 4 216 213 216
#> 5 216 216 216
#> 6 213 184 213
library(tidyverse)
#> Warning: package 'readr' was built under R version 4.3.2
library(conflicted)
library(dplyr)
(colnames <- colnames(data_new2))
#>  [1] "Buc"   "MT"    "Kg"    "Lung"  "medie" "Pcs"   "CTT"   "util"  "V1"   
#> [10] "V2"    "V3"    "V4"    "V5"    "V6"    "V7"    "V8"    "V9"    "V10"
(cols_to_process <- colnames[startsWith(colnames, "V")])
#>  [1] "V1"  "V2"  "V3"  "V4"  "V5"  "V6"  "V7"  "V8"  "V9"  "V10"
(new_names <- sub("^V", "W", cols_to_process))
#>  [1] "W1"  "W2"  "W3"  "W4"  "W5"  "W6"  "W7"  "W8"  "W9"  "W10"
for(i in seq_along(cols_to_process)){
  medie_i <- data_new2$medie[[i]]
  MT_i <- data_new2$MT[[i]]
  Buc_i<-data_new2$Buc[[i]]
  data_new2[[ new_names[[ i ]] ]] <-floor((medie_i*1000-200)/ data_new2[[ cols_to_process[[ i ]] ]])
  
 
}
head(data_new2)
#>   Buc      MT   Kg Lung medie Pcs      CTT     util  V1  V2  V3  V4  V5  V6  V7
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176 216 213 216 213 216 216 184
#> 2 296 1847.40 2568  216  6.24  27 1.070167 1726.272 216 184 213 184 184 184 216
#> 3 304 1869.96 2599  216  6.15  27 1.054730 1772.928 216 184 213 184 216 184 213
#> 4 240 1497.60 2082  184  6.24  32 1.059783 1413.120 216 213 184 216 216 184 216
#> 5 385 2194.50 3050  216  5.70  25 1.055556 2079.000 213 213 216 216 213 216 213
#> 6 302 1887.50 2624  184  6.25  32 1.061481 1778.176 213 213 213 184 213 216 213
#>    V8  V9 V10 W1 W2 W3 W4 W5 W6 W7 W8 W9 W10
#> 1 184 184 216 27 28 27 28 25 28 32 32 32  28
#> 2 213 216 213 27 32 27 32 29 32 28 28 27  28
#> 3 213 184 184 27 32 27 32 25 32 28 28 32  33
#> 4 216 213 216 27 28 32 27 25 32 28 28 27  28
#> 5 216 216 216 28 28 27 27 25 28 28 28 27  28
#> 6 213 184 213 28 28 27 32 25 28 28 28 32  28
(colnames <- colnames(data_new2))
#>  [1] "Buc"   "MT"    "Kg"    "Lung"  "medie" "Pcs"   "CTT"   "util"  "V1"   
#> [10] "V2"    "V3"    "V4"    "V5"    "V6"    "V7"    "V8"    "V9"    "V10"  
#> [19] "W1"    "W2"    "W3"    "W4"    "W5"    "W6"    "W7"    "W8"    "W9"   
#> [28] "W10"


(cols_to_process <-colnames( data_new2 %>% select(V1:W10)))
#>  [1] "V1"  "V2"  "V3"  "V4"  "V5"  "V6"  "V7"  "V8"  "V9"  "V10" "W1"  "W2" 
#> [13] "W3"  "W4"  "W5"  "W6"  "W7"  "W8"  "W9"  "W10"
(new_names <- sub("^W", "x", cols_to_process))
#>  [1] "V1"  "V2"  "V3"  "V4"  "V5"  "V6"  "V7"  "V8"  "V9"  "V10" "x1"  "x2" 
#> [13] "x3"  "x4"  "x5"  "x6"  "x7"  "x8"  "x9"  "x10"
for(i in seq_along(cols_to_process)){
  V_i<-data_new2$V[[i]]
    W_i<-data_new2$W[[i]]
  Buc_i<-data_new2$Buc[[i]]
    data_new2[[ new_names[[ i ]] ]] <-Buc_i *data_new2[[ cols_to_process[[ i ]] ]]
  
 
}
head(data_new2)
#>   Buc      MT   Kg Lung medie Pcs      CTT     util    V1    V2    V3    V4
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176 65232 63048 65664 51120
#> 2 296 1847.40 2568  216  6.24  27 1.070167 1726.272 65232 54464 64752 44160
#> 3 304 1869.96 2599  216  6.15  27 1.054730 1772.928 65232 54464 64752 44160
#> 4 240 1497.60 2082  184  6.24  32 1.059783 1413.120 65232 63048 55936 51840
#> 5 385 2194.50 3050  216  5.70  25 1.055556 2079.000 64326 63048 65664 51840
#> 6 302 1887.50 2624  184  6.25  32 1.061481 1778.176 64326 63048 64752 44160
#>      V5    V6    V7    V8    V9   V10 W1 W2 W3 W4 W5 W6 W7 W8 W9 W10   x1    x2
#> 1 83160 65232 59248 56304 65688 63720 27 28 27 28 25 28 32 32 32  28 8640  8820
#> 2 70840 55568 69552 65178 77112 62835 27 32 27 32 29 32 28 28 27  28 8640 10080
#> 3 83160 55568 68586 65178 65688 54280 27 32 27 32 25 32 28 28 32  33 8640 10080
#> 4 83160 55568 69552 66096 76041 63720 27 28 32 27 25 32 28 28 27  28 8640  8820
#> 5 82005 65232 68586 66096 77112 63720 28 28 27 27 25 28 28 28 27  28 8960  8820
#> 6 82005 65232 68586 65178 65688 62835 28 28 27 32 25 28 28 28 32  28 8960  8820
#>     x3   x4   x5    x6   x7   x8   x9  x10
#> 1 5886 7560 7000 10080 9216 9408 9856 7924
#> 2 5886 8640 8120 11520 8064 8232 8316 7924
#> 3 5886 8640 7000 11520 8064 8232 9856 9339
#> 4 6976 7290 7000 11520 8064 8232 8316 7924
#> 5 5886 7290 7000 10080 8064 8232 8316 7924
#> 6 5886 8640 7000 10080 8064 8232 9856 7924
#############

danut_horincas · April 8, 2024, 8:38pm

My solution modified values from colymns V1 to V10 and the results in columns X1 to x10 wrong.

dromano · April 8, 2024, 9:13pm

Let's concentrate on only the first row of the data and only on columns with the number 1 in their names:

How does this differ from what you want:

structure(list(Buc = 302, MT = 1884.48, Kg = 2619), row.names = c(NA, 
-1L), class = "data.frame") -> mac

mac 
#>   Buc      MT   Kg
#> 1 302 1884.48 2619

library(tidyverse)

mac1 <- mac %>% mutate(Lung = 184)

mac1 <- mac1 %>% 
  mutate(Buc = as.numeric(Buc),MT=as.numeric(MT))
mac1<-mac1%>%mutate(medie=round(MT/Buc,2))
mac1<-mac1%>%mutate(Pcs=floor((medie*1000-200)/Lung))
#str(mac)
mac1<-mac1%>%mutate(CTT=MT/(Buc*Lung*Pcs/1000))

mac1<-mac1%>%mutate(util=(Buc*Lung*Pcs/1000))

mac1
#>   Buc      MT   Kg Lung medie Pcs      CTT     util
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176

mac1 <- mac1 %>% mutate(V1 = 216)

mac1
#>   Buc      MT   Kg Lung medie Pcs      CTT     util  V1
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176 216

mac1 <- 
  mac1 %>% 
  mutate(W1 = floor((medie * 1000 - 200) / V1))

mac1
#>   Buc      MT   Kg Lung medie Pcs      CTT     util  V1 W1
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176 216 27

mac1 <- 
  mac1 %>% 
  mutate(X1 = Buc * V1 * W1)

mac1
#>   Buc      MT   Kg Lung medie Pcs      CTT     util  V1 W1      X1
#> 1 302 1884.48 2619  184  6.24  32 1.059783 1778.176 216 27 1761264

^{Created on 2024-04-08 with reprex v2.0.2}

danut_horincas · April 9, 2024, 4:19am

I know these aproach but I would prefer something  accurate  let say , because we are discusing about  10  formula that should by edited:mutate(X1 = Buc * V1 * W1,X2 = Buc * V2 * W2,X3 = Buc * V3 * W3,X4 = Buc * V5 * W5,X6 = Buc * V6 * W6
         ,X7 = Buc * V7 * W7,X8 = Buc * V8 * W8,X9 = Buc * V9 * W9,X10 = Buc * V10 * W10).If the initial data frame will have 100 columns ore more?

danut_horincas · April 9, 2024, 11:07am

A very good one!

Does the code I posted not work for you? It should work like the excel approach you mentioned. Or is it that you're not sure how to extend the code to work for your context?

If you can describe what you would do in excel to get your desired result, I'm sure we can figure out solution in R.

(Attachment Test formula.xlsx is missing)

dromano · April 9, 2024, 11:23am

I know these aproach but I would prefer something accurate let say , because we are discusing about 10 formula that should by edited:mutate(X1 = Buc * V1 * W1,X2 = Buc * V2 * W2,X3 = Buc * V3 * W3,X4 = Buc * V5 * W5,X6 = Buc * V6 * W6
,X7 = Buc * V7 * W7,X8 = Buc * V8 * W8,X9 = Buc * V9 * W9,X10 = Buc * V10 * W10).If the initial data frame will have 100 columns ore more?

So to confirm, this (below) is the correct result for one row and one set of V, W, X columns, is that correct?

Doing the same for many rows and many sets V, W, X columns is only couple of simple additional steps, but to get everything correct, it's important for you to confirm whether this simple example is correct.

danut_horincas · April 9, 2024, 12:12pm

Confirm the result :302*216*27=1761264