Use loop for create multiple columns from another columns in dataframe in R

Question

I have a CSV file with 19 columns and 9 lines about average of rain months from pluviometers and coordinates (LongLat) of them. The columns are averages and the lines are the pluviometers. The CSV file can be acessed here: https://drive.google.com/file/d/1wGVT5etZomYW-Cb6R3KgHaV4mXrTjlu4/view?usp=sharing

The aim is create more columns to calculate rainfall factor by months applying a equation from average columns. The rainfall factor for January for each pluviometer (9 lines) will be calculate using column 7, rainfall for february using column 8, march by column 9...

I'm trying this code for each column and it works, but this code can be reduce and made with a loop?

library(tidyverse)
    
setwd("C:/scriptsr/R_postos_pluviometricos_interp_FUNCEME/")
        
#Code    
pluviometros <- read.csv("postos_fatorR.csv",
                                 header = T,
                                 sep = ",",
                                 stringsAsFactors = FALSE)
            
View(pluviometros)
            
pluviometros <- mutate(.data=pluviometros,R.JAN=67.355*((pluviometros[1:nrow(pluviometros),7]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.FEV=67.355*((pluviometros[1:nrow(pluviometros),8]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.MAR=67.355*((pluviometros[1:nrow(pluviometros),9]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.APR=67.355*((pluviometros[1:nrow(pluviometros),10]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.MAY=67.355*((pluviometros[1:nrow(pluviometros),11]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.JUN=67.355*((pluviometros[1:nrow(pluviometros),12]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.JUL=67.355*((pluviometros[1:nrow(pluviometros),13]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.AGO=67.355*((pluviometros[1:nrow(pluviometros),14]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.SEP=67.355*((pluviometros[1:nrow(pluviometros),15]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.OCT=67.355*((pluviometros[1:nrow(pluviometros),16]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.NOV=67.355*((pluviometros[1:nrow(pluviometros),17]^2)/pluviometros[1:nrow(pluviometros),19])^0.85,
                                       R.DEC=67.355*((pluviometros[1:nrow(pluviometros),18]^2)/pluviometros[1:nrow(pluviometros),19])^0.85
                                       )
View(pluviometros)

Ian Campbell · Accepted Answer · 2020-06-01 14:19:46Z

1

Here's an approach with mutate_at from dplyr:

library(dplyr)
data <- read.csv("https://docs.google.com/uc?id=1wGVT5etZomYW-Cb6R3KgHaV4mXrTjlu4&export=download")

data %>%
  mutate_at(vars(JAN:DEC),
            .funs = list(R = ~ 67.355 * ((.)^2 / TOTAL_pa)^0.85))

The only difference between yours and this is that the new columns end with _R instead of starting with R..

If you're really excited about the names you chose, you can add rename_at:

library(stringr)
data %>%
  mutate_at(vars(JAN:DEC),
            .funs = list(R = ~ 67.355 * ((.)^2 / TOTAL_pa)^0.85)) %>%
  rename_at(vars(JAN_R:DEC_R),
            .funs = list(~ paste0("R.",str_remove(.,"_R"))))

edited Jun 1, 2020 at 14:19

answered Jun 1, 2020 at 14:12

Ian Campbell

25.2k14 gold badges43 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matheus Marques Over a year ago

Works fine!! Thanks a lot!

Marcelo Fernando Befumo · Accepted Answer · 2020-06-01 14:31:25Z

1

Using R base there is no no need for looping, just using vectorized operations capability of R:

result <- 67.355 *(pluviometros[,7:18]^2 / pluviometros[,"TOTAL_pa"])^0.85 
names(result) <- paste("R", names(pluviometros)[7:18], sep=".")
pluviometros <- cbind(pluviometros, result)

answered Jun 1, 2020 at 14:31

Marcelo Fernando Befumo

3062 silver badges6 bronze badges

1 Comment

Matheus Marques Over a year ago

Works very well too! Thanks a lot, Marcelo!

JdeMello · Accepted Answer · 2020-06-01 14:44:54Z

Adding data.table solution:

library(data.table)

data <- data.table::fread("https://docs.google.com/uc?id=1wGVT5etZomYW-Cb6R3KgHaV4mXrTjlu4&export=download")

cols <- names(data)[which(names(data) == "JAN"):which(names(data) == "DEC")]

newCols <- paste0("R.", cols)

data[, (newCols) := lapply(.SD, function(x) 67.355* ((x^2) / TOTAL_pa)^0.85), .SDcols=cols]

Result:

> data[, c(cols, newCols), with=F]
       JAN     FEB     MAR     APR    MAY    JUN    JUL   AGO   SEP   OCT   NOV    DEC    R.JAN
1:  85.350 126.923 177.167 177.343 89.050 18.060  7.053 1.573 0.350 1.617 2.637 14.950 492.0292
2:  96.957 130.723 173.343 184.130 89.873 25.780 10.760 0.600 0.933 0.000 0.883 13.970 592.6067
3: 106.153 170.712 212.153 198.241 96.571 35.229 21.071 3.788 1.594 5.447 6.153 16.041 592.2811
4:  89.394 120.876 181.882 149.912 89.094 20.512  8.512 0.294 0.718 2.682 0.747 20.929 543.1993
5: 103.550 107.090 139.940 141.335 66.395 18.960  5.300 0.500 0.000 0.000 3.300 17.500 776.8248
6: 106.171 150.588 167.000 154.859 64.765 18.706  7.412 0.412 3.118 0.353 1.706 25.588 714.3158
7: 102.682 141.729 187.753 176.635 88.529 21.606  3.224 0.000 0.000 0.000 1.471  6.282 651.8239
8:  79.444 103.744 155.481 134.125 53.081 16.188  1.875 0.000 0.000 0.000 0.250  6.825 535.1631
9:  87.113 103.431 128.838 145.919 60.325 25.025  1.375 0.438 0.000 0.000 0.500 11.775 612.9724
       R.FEB    R.MAR    R.APR    R.MAY    R.JUN      R.JUL      R.AGO      R.SEP      R.OCT
1:  965.9696 1702.926 1705.803 528.8379 35.10468  7.0987276 0.55383926 0.04303884 0.58043280
2:  984.8734 1591.193 1763.168 520.8968 62.33945 14.1144143 0.10433719 0.22099416 0.00000000
3: 1328.2870 1921.970 1712.663 504.2925 90.81834 37.9059855 2.04993454 0.47062304 3.80111125
4:  907.2210 1817.094 1308.148 540.1039 44.47834  9.9721478 0.03265318 0.14898636 1.39995758
5:  822.5098 1296.191 1318.234 364.9203 43.34042  4.9640584 0.08970505 0.00000000 0.00000000
6: 1293.9688 1542.759 1356.976 308.2896 37.32912  7.7369606 0.05688601 1.77529109 0.04374185
7: 1127.3785 1818.392 1639.156 506.5652 46.06450  1.8149366 0.00000000 0.00000000 0.00000000
8:  842.4019 1675.845 1303.613 269.6365 35.81053  0.9172431 0.00000000 0.00000000 0.00000000
9:  820.7410 1192.270 1473.296 328.2051 73.54207  0.5301692 0.07582386 0.00000000 0.00000000
        R.NOV     R.DEC
1: 1.33300608 25.458625
2: 0.20124041 21.999656
3: 4.67617546 23.841660
4: 0.15936024 46.026439
5: 2.21840892 37.820963
6: 0.63686082 63.583139
7: 0.47811399  5.641004
8: 0.02984560  8.248154
9: 0.09496179 20.414250

Collectives™ on Stack Overflow

Use loop for create multiple columns from another columns in dataframe in R

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related