How to subset data from multiple dataframes for a for loop

Question

I want to calculate the weekly price change in a list of products that I have, but I am having issues even pulling in the values. df1 is a list of products and the dates I am interested in, df2 is the price history of all products. I want to add a new column to df1 with the % price change for 1 week (1 week from date in df1)

df1:                
Item    Date       1W Px Change
A       1/1/17     
B       2/1/17
A       2/2/17
C       2/2/17
D       3/1/17

df2:
Date      A    B    C    D
1/1/17    1    5    10   50
1/2/17    2    5    9    40
1/3/17    3    5    10   42

The dataframes have thousands of products and dates. I tried with a for loop to create a new vector with just the price from the day in df1 but getting a replacement has length zero error.

price <- rep(NA,nrow(df1))
for(i in 1:nrow(df1)){
    price[i] <- df2[which(df2$Date==df1$Date[i]),df1$Item[i]]
  }

Any ideas on how to either fix the for loop, or if there is a better way for me to do this? Thanks

Lukáš Hejtmánek · Accepted Answer · 2017-03-25 19:00:03Z

1

There are some possible problems with this approach. First issue might be with comparing times. Check if you really have same time format in both tables and that they compare as you expect.

Second, you don't consider NULL being returned. Try this modification

for(i in 1:nrow(df1)){
   value <- df2[which(df2$Date==df1$Date[i]), df1$Item[i]]
   if(!is.null(value)) price[i] <- value 
}

And lastly, check that you have df$Item as character and not factor. Otherwise you might be getting weird outcomes (factors in df1$Item[i] are returned as Int, not as char)

answered Mar 25, 2017 at 19:00

Lukáš Hejtmánek

3351 silver badge9 bronze badges

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

Collectives™ on Stack Overflow

How to subset data from multiple dataframes for a for loop

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related