{"id":2144,"date":"2021-10-28T06:39:54","date_gmt":"2021-10-28T06:39:54","guid":{"rendered":"http:\/\/optimumsportsperformance.com\/blog\/?p=2144"},"modified":"2021-10-28T12:51:17","modified_gmt":"2021-10-28T12:51:17","slug":"r-tips-tricks-excel-within-column-iteration-in-r-part-2","status":"publish","type":"post","link":"https:\/\/optimumsportsperformance.com\/blog\/r-tips-tricks-excel-within-column-iteration-in-r-part-2\/","title":{"rendered":"R Tips & Tricks: Excel Within Column Iteration in R (part 2)"},"content":{"rendered":"

Earlier this week<\/a><\/strong><\/span> I shared a method for doing within column iteration in R, as you might do in excel. For example, you want to create a new column that requires a starting value from a different column (or a 0 value) and then requires new values within that column to iterate over the prior values. The way I handled this was to use the accumuate()<\/strong> function available in the {tidyverse}<\/strong> package.<\/p>\n

The article got some good feedback and discussion on Twitter. For example, Thomas Mock<\/a><\/span><\/strong>, provided some examples of using the {slider}<\/strong> package to handle window functions, see HERE<\/a><\/span><\/strong> and HERE<\/a><\/span><\/strong>. The package looks to be very handy and easy to use. I’m going have to play around with it some more.<\/p>\n

Someone else asked, “how might we do this in a for() loop?”<\/em><\/strong><\/p>\n

It’s a good question. Sometimes you might need to use base R or sometimes the for() loop might be easier. So, let’s walk through an example:<\/p>\n

Simulate Data<\/span><\/strong><\/p>\n

First, we need to simulate a basic data set:<\/p>\n

\r\nlibrary(tidyverse)\r\n\r\ndf <- tibble(\r\n  id = 1:5,\r\n  val = c(5,7,3,4,2)\r\n)\r\n\r\ndf\r\n<\/pre>\n
\"\"<\/a><\/p>\n
Setting Up the Problem<\/strong><\/span><\/p>\n
Let’s say we want to create a new value that applies a very simple algorithm:<\/p>\n
New Value = observed + 2 * lag(new value)<\/strong><\/em><\/p>\n
Putting the above data in excel the formula and answer looks like this:<\/p>\n
\"\"<\/a><\/p>\n
Notice that the first new value starts with our initial observation (5) and then begins to iterate from there.<\/p>\n
Writing the for() loop<\/strong><\/span><\/p>\n
for() loops can sometimes be scary but if you sequentially think through what you are trying to do you can often come up with a reasonable solution. Let’s step through this one:<\/p>\n
    \n
  1. \u00a0We begin outside of the for() loop by creating two elements. We create N<\/strong> which simply gives us a count of the number of observations in our val<\/strong> column and we create new_val<\/strong> which is nothing more than a place holder for the new values we will create. Notice that the new_val<\/strong> place holder starts with the first element of the df$val <\/strong>column because, remember, we need to begin the new column with our first value observation in the val<\/strong> column. After that, I simply concatenate a bunch of NA values that will be populated with the new values that the for() loop will produce. Notice that I have NA repeat for\u00a0N-1<\/strong> times. This is important, as N\u00a0<\/strong>represents the number of observations in the val<\/strong> column and since we’ve already put a place holder in for the first observation we need to remove one of the NA’s to ensure the new_val<\/strong> column will be the same length as the val <\/strong>column.<\/li>\n
  2. Next, we create our loop. I specify that I want to iterate over all “i”<\/strong> iterations from 2 to N. Why 2? Because the first value is already specified, as discussed above. Inside the for() loop, for each iteration that the loop runs it will store the new value, “i”<\/strong> in the new_val<\/strong> vector we created above. The equation that we specified earlier is within the for loop and I use “i<\/strong>” to index the observations. For example, for the second observation, what the for() loop is doing is saying, df$val[2] + new_val[2 – 1]*2, <\/strong><\/em>and for the third time through the loop it says, df$val[3] + new_val[3 – 1]*2<\/strong>, <\/em>etc. until it goes through all N <\/strong>observations. Everything in the brackets is simply specifying the row indexes.<\/li>\n<\/ol>\n
    \r\n## We want to create a new value\r\n# New Value = observed + 2 * lag(new value)\r\n# The first value for the new value is the first observation in row one for value\r\n\r\nN <- length(df$val)\r\nnew_val <- c(df$val[1], rep(NA, N-1))\r\n\r\nfor(i in 2:N){\r\n\r\n  new_val[i] <- df$val[i] + new_val[i - 1]*2\r\n  \r\n}\r\n<\/pre>\n
     <\/p>\n
    Once the loop is done running we can simply attach the results to our data frame and see what it looks like:<\/p>\n
    \"\"<\/a><\/p>\n
    Same results as the excel sheet!<\/p>\n
    Wrapping this into a function<\/strong><\/span><\/p>\n
    After seeing how the for() loop works, you might want to wrap it up into a function so that you don’t need to do the first steps of creating an element for the number of iterations and vector place holder. Also, having it in a function might be useful if you need to frequently use it for other data sets.<\/p>\n
    We simply wrap all of the steps into a single function that takes an input of the data frame name and the value column that has your most recent observations. Run the function on the data set above and you will obtain the same output.<\/p>\n
    \r\niterate_column_func <- function(df, val){\r\n  \r\n  N <- length(df$val)\r\n  new_val <- c(df$val[1], rep(NA, N-1))\r\n  \r\n  for(i in 2:N){\r\n    \r\n    new_val[i] <- df$val[i] + new_val[i - 1]*2\r\n    \r\n  }\r\n  \r\n  df$new_val <- new_val\r\n  return(df)\r\n}\r\n\r\niterate_column_func(df, val)\r\n<\/pre>\n

    \nApplying the function to multiple subjects<\/strong><\/span><\/p>\n
    What if we have more than one subject that we need to apply the function to?<\/p>\n
    First, we simulate some more data:<\/p>\n
    \r\ndf_2 <- tibble(\r\n  subject = as.factor(rep(1:10, each = 5)),\r\n  id = rep(1:5, times = 10),\r\n  val = round(runif(n = 50, min = 10, max = 20), 0)\r\n)\r\n\r\ndf_2\r\n<\/pre>\n
    Next, I’m going to make a slight tweak to the function. I’m going to have the output get returned as a single column data frame.<\/p>\n
    \r\niterate_column_func <- function(x){\r\n  \r\n  N <- length(x)\r\n  new_val <- c(x[1], rep(NA, N-1))\r\n  \r\n  for(i in 2:N){\r\n    \r\n    new_val[i] <- x[i] + new_val[i - 1]*2\r\n    \r\n  }\r\n  \r\n  new_val <- as.data.frame(new_val)\r\n  return(new_val)\r\n}\r\n<\/pre>\n
    Now, I’m going to apply the custom function to my new data frame, with multiple subjects, using the group_modify()<\/strong> function in {tidyverse<\/strong>}. This function allows us to apply other functions to groups of subjects, iterating over them and producing a data frame as a result.<\/p>\n
     <\/p>\n
    \r\nnew_df <- df_2 %>%\r\n  group_by(subject) %>% \r\n  group_modify(~iterate_column_func(.x$val)) %>%\r\n  ungroup()\r\n<\/pre>\n
    Then, I simply bind this new data to the original data frame and we have our new_val<\/strong> produced within individual.<\/p>\n
    \r\ndf_2 %>%\r\n  bind_cols(new_df %>% select(-subject)) %>% as.data.frame()\r\n<\/pre>\n
    \"\"<\/a><\/p>\n
    Conclusion<\/strong><\/span><\/p>\n
    And there you go, within column iteration in R, just as you would do in excel. Part 1<\/a><\/strong><\/span> covered an approach in {tidyverse<\/strong>} while Part 2 used for() loops in base R to accomplish the same task.<\/p>\n
    The full code for this article is available on my GitHub page<\/a><\/span><\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Earlier this week I shared a method for doing within column iteration in R, as you might do in excel. For example, you want to create a new column that requires a starting value from a different column (or a 0 value) and then requires new values within that column to iterate over the prior […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[45],"tags":[],"class_list":["post-2144","post","type-post","status-publish","format-standard","hentry","category-r-tips-tricks"],"_links":{"self":[{"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/posts\/2144","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/comments?post=2144"}],"version-history":[{"count":3,"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/posts\/2144\/revisions"}],"predecessor-version":[{"id":2152,"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/posts\/2144\/revisions\/2152"}],"wp:attachment":[{"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/media?parent=2144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/categories?post=2144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/optimumsportsperformance.com\/blog\/wp-json\/wp\/v2\/tags?post=2144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}