Avoiding looping for fastening code

General
1

I have the following 3 first column in a dataset. I need to take the product of all "change" from the same ID, with Year less of equal than the current year. To obtain this:

image

The following loop works, but I was trying to get rid of it to fasten my code. How can I write the same without the subscript i?

for (i in 1:nrow(df)){
df$cum_change[i] <- prod(df[df$Year <= df$Year[i] & df$ID == df$ID[i],"change"])
}

Given the large dataset, I am using (3.5 million observations), it takes forever.

Thanks,

2

Hello,
No need to give us 3.5 million records of input to play with.
But perhaps you can give us a small sample of the first 100 records.
Assuming your input data is in 'df' name , please do

dput(df[1:100,])

copy and paste the output here and we can import the first 100 records in that way.

4

Thanks for your prompt reply. Here is the first 5 IDs dataset. Is it how you want the output??

structure(list(ID = c(996063361177, 516855298290, 709081310288, 
468018962975, 965186151781, 451824367562, 468018962975, 451824367562, 
965186151781, 516855298290, 709081310288, 996063361177, 451824367562, 
468018962975, 965186151781, 516855298290, 709081310288, 996063361177, 
451824367562, 965186151781, 468018962975, 709081310288, 516855298290, 
996063361177, 451824367562, 965186151781, 468018962975, 516855298290, 
709081310288, 996063361177, 451824367562, 468018962975, 965186151781, 
516855298290, 709081310288, 996063361177, 451824367562, 965186151781, 
468018962975, 516855298290, 709081310288, 996063361177, 451824367562, 
468018962975, 965186151781, 516855298290, 709081310288, 996063361177, 
451824367562, 516855298290, 965186151781, 468018962975, 709081310288, 
996063361177, 451824367562, 516855298290, 965186151781, 468018962975, 
996063361177, 709081310288, 451824367562, 965186151781, 516855298290, 
468018962975, 996063361177, 709081310288, 965186151781, 516855298290, 
468018962975, 996063361177, 709081310288, 516855298290, 965186151781, 
468018962975, 996063361177, 709081310288, 516855298290, 468018962975, 
965186151781, 996063361177, 709081310288, 965186151781, 516855298290, 
468018962975, 996063361177, 709081310288, 516855298290, 965186151781, 
468018962975, 996063361177, 709081310288, 516855298290, 468018962975, 
965186151781, 996063361177, 709081310288, 516855298290, 965186151781, 
468018962975, 996063361177, 709081310288, 965186151781, 516855298290, 
468018962975, 996063361177, 709081310288, 516855298290, 965186151781, 
468018962975, 996063361177, 709081310288, 516855298290, 965186151781, 
996063361177, 468018962975, 709081310288, 516855298290, 965186151781, 
468018962975, 996063361177, 709081310288, 516855298290, 468018962975, 
965186151781, 996063361177, 709081310288, 965186151781, 468018962975, 
516855298290, 996063361177, 709081310288, 965186151781, 516855298290, 
468018962975, 996063361177, 709081310288, 516855298290, 965186151781, 
468018962975, 996063361177, 709081310288, 965186151781, 516855298290, 
468018962975, 996063361177, 709081310288, 516855298290, 965186151781, 
468018962975, 996063361177, 709081310288, 516855298290, 996063361177, 
965186151781, 468018962975, 709081310288, 965186151781, 516855298290, 
996063361177, 468018962975, 709081310288, 965186151781, 516855298290, 
996063361177, 709081310288, 468018962975, 965186151781, 996063361177, 
516855298290, 468018962975, 709081310288, 965186151781, 516855298290, 
996063361177, 709081310288, 468018962975, 965186151781, 516855298290, 
996063361177, 709081310288, 516855298290, 965186151781, 996063361177, 
709081310288, 965186151781, 516855298290, 996063361177, 709081310288, 
965186151781, 516855298290, 996063361177, 709081310288, 965186151781, 
516855298290, 996063361177, 709081310288, 965186151781, 516855298290, 
996063361177, 709081310288, 965186151781, 516855298290, 996063361177, 
709081310288, 965186151781, 516855298290, 996063361177, 709081310288, 
965186151781, 516855298290, 709081310288, 965186151781, 516855298290, 
709081310288, 965186151781, 516855298290, 709081310288, 965186151781, 
516855298290, 709081310288), Year = c(1L, 1L, 1L, 1L, 1L, 1L, 
2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 
4L, 4L, 5L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 6L, 6L, 7L, 7L, 
7L, 7L, 7L, 7L, 8L, 8L, 8L, 8L, 8L, 8L, 9L, 9L, 9L, 9L, 9L, 9L, 
10L, 10L, 10L, 10L, 10L, 10L, 11L, 11L, 11L, 11L, 11L, 11L, 12L, 
12L, 12L, 12L, 12L, 13L, 13L, 13L, 13L, 13L, 14L, 14L, 14L, 14L, 
14L, 15L, 15L, 15L, 15L, 15L, 16L, 16L, 16L, 16L, 16L, 17L, 17L, 
17L, 17L, 17L, 18L, 18L, 18L, 18L, 18L, 19L, 19L, 19L, 19L, 19L, 
20L, 20L, 20L, 20L, 20L, 21L, 21L, 21L, 21L, 21L, 22L, 22L, 22L, 
22L, 22L, 23L, 23L, 23L, 23L, 23L, 24L, 24L, 24L, 24L, 24L, 25L, 
25L, 25L, 25L, 25L, 26L, 26L, 26L, 26L, 26L, 27L, 27L, 27L, 27L, 
27L, 28L, 28L, 28L, 28L, 28L, 29L, 29L, 29L, 29L, 29L, 30L, 30L, 
30L, 30L, 30L, 31L, 31L, 31L, 31L, 31L, 32L, 32L, 32L, 32L, 32L, 
33L, 33L, 33L, 33L, 33L, 34L, 34L, 34L, 34L, 35L, 35L, 35L, 35L, 
36L, 36L, 36L, 36L, 37L, 37L, 37L, 37L, 38L, 38L, 38L, 38L, 39L, 
39L, 39L, 39L, 40L, 40L, 40L, 40L, 41L, 41L, 41L, 41L, 42L, 42L, 
42L, 43L, 43L, 43L, 44L, 44L, 44L, 45L, 45L, 45L), change = c(1.007, 
1.007, 1.007, 1.007, 1.007, 1.007, 1.008, 1.008, 1.008, 1.008, 
1.008, 1.008, 1.005, 1.005, 1.005, 1.005, 1.005, 1.005, 1.002, 
1.002, 1.002, 1.002, 1.002, 1.002, 1.003, 1.003, 1.003, 1.003, 
1.003, 1.003, 1.001, 1.001, 1.001, 1.001, 1.001, 1.001, 1.002, 
1.002, 1.002, 1.002, 1.002, 1.002, 1.002, 1.002, 1.002, 1.002, 
1.002, 1.002, 1.008, 1.008, 1.008, 1.008, 1.008, 1.008, 1.009, 
1.009, 1.009, 1.009, 1.009, 1.009, 1.005, 1.005, 1.005, 1.005, 
1.005, 1.005, 1.005, 1.005, 1.005, 1.005, 1.005, 1.003, 1.003, 
1.003, 1.003, 1.003, 1.005, 1.005, 1.005, 1.005, 1.005, 1.005, 
1.005, 1.005, 1.005, 1.005, 1.006, 1.006, 1.006, 1.006, 1.006, 
1.002, 1.002, 1.002, 1.002, 1.002, 1.002, 1.002, 1.002, 1.002, 
1.002, 1.003, 1.003, 1.003, 1.003, 1.003, 1.003, 1.003, 1.003, 
1.003, 1.003, 1.008, 1.008, 1.008, 1.008, 1.008, 1.004, 1.004, 
1.004, 1.004, 1.004, 1.006, 1.006, 1.006, 1.006, 1.006, 1.007, 
1.007, 1.007, 1.007, 1.007, 1.013, 1.013, 1.013, 1.013, 1.013, 
1.004, 1.004, 1.004, 1.004, 1.004, 1.002, 1.002, 1.002, 1.002, 
1.002, 1, 1, 1, 1, 1, 1.004, 1.004, 1.004, 1.004, 1.004, 1.002, 
1.002, 1.002, 1.002, 1.002, 1.005, 1.005, 1.005, 1.005, 1.005, 
1.003, 1.003, 1.003, 1.003, 1.003, 1.007, 1.007, 1.007, 1.007, 
1.007, 1.006, 1.006, 1.006, 1.006, 1.007, 1.007, 1.007, 1.007, 
1.005, 1.005, 1.005, 1.005, 1.003, 1.003, 1.003, 1.003, 1.003, 
1.003, 1.003, 1.003, 1.001, 1.001, 1.001, 1.001, 1.003, 1.003, 
1.003, 1.003, 1, 1, 1, 1, 1.002, 1.002, 1.002, 1, 1, 1, 1.003, 
1.003, 1.003, 1.006, 1.006, 1.006)), row.names = c(1L, 2L, 3L, 
4L, 5L, 6L, 452L, 664L, 1518L, 1847L, 1910L, 1994L, 31331L, 31751L, 
32839L, 32937L, 33018L, 34702L, 94756L, 96741L, 97797L, 98411L, 
98569L, 99589L, 186650L, 188412L, 188981L, 189609L, 190899L, 
191139L, 287412L, 288826L, 290323L, 291585L, 292909L, 292941L, 
388712L, 390365L, 390440L, 391695L, 393793L, 394011L, 489017L, 
490301L, 491869L, 492697L, 493994L, 494295L, 588214L, 590096L, 
590198L, 590993L, 593628L, 593643L, 685637L, 687625L, 688215L, 
688934L, 690873L, 690942L, 780972L, 783369L, 783426L, 783680L, 
786077L, 786488L, 876836L, 877263L, 877848L, 879711L, 879998L, 
969622L, 969679L, 970888L, 972065L, 972507L, 1061215L, 1061698L, 
1062056L, 1063261L, 1063867L, 1152098L, 1152117L, 1153084L, 1154678L, 
1155567L, 1242500L, 1242849L, 1242997L, 1245142L, 1246045L, 1332302L, 
1332460L, 1332643L, 1335219L, 1336004L, 1422036L, 1422341L, 1423721L, 
1424314L, 1425436L, 1510344L, 1510763L, 1511994L, 1513001L, 1514016L, 
1598351L, 1599346L, 1600226L, 1600657L, 1601882L, 1685584L, 1686140L, 
1687576L, 1687676L, 1688700L, 1771203L, 1772442L, 1773184L, 1773595L, 
1774600L, 1856625L, 1857110L, 1857254L, 1859271L, 1860071L, 1941240L, 
1941544L, 1941571L, 1943243L, 1944424L, 2024963L, 2025055L, 2026200L, 
2027221L, 2027660L, 2107547L, 2107856L, 2109257L, 2109991L, 2111057L, 
2189810L, 2190065L, 2192858L, 2193014L, 2193183L, 2271295L, 2272449L, 
2272758L, 2273108L, 2274499L, 2352519L, 2353229L, 2353391L, 2354656L, 
2355553L, 2432049L, 2432831L, 2433625L, 2434563L, 2435308L, 2511033L, 
2511263L, 2512691L, 2514456L, 2514680L, 2590216L, 2591756L, 2592177L, 
2592647L, 2593290L, 2667332L, 2668722L, 2669452L, 2670540L, 2670578L, 
2745036L, 2745126L, 2746587L, 2747762L, 2821136L, 2821816L, 2823495L, 
2824138L, 2897363L, 2897787L, 2899364L, 2900140L, 2973105L, 2973896L, 
2974672L, 2975354L, 3047662L, 3049020L, 3049821L, 3050624L, 3122247L, 
3123852L, 3124560L, 3124848L, 3195663L, 3197992L, 3198696L, 3198864L, 
3269773L, 3270964L, 3271640L, 3271797L, 3341490L, 3342881L, 3344241L, 
3413037L, 3414309L, 3415792L, 3484027L, 3484618L, 3486464L, 3553508L, 
3554623L, 3556263L), class = "data.frame")
5

I assigned what you shared with me to 'df' then did the following

library(tidyverse)
# arranging so that cumulative product will have defined ordering 
dft <-  as_tibble(df) %>% arrange(ID,Year)

dft2 <- dft %>% group_by(ID) %>% mutate(changecumprod= cumprod(change))
1 Like
6

Thanks so much !!! It works perfectly

7

great :slight_smile:
you're welcome

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.